SQL Data (Rows and Tables)

SQL Exercise 1: Inserting Rows

Before we begin let’s talk about what a Schema is:

A table in a database is like a grid with rows and columns. Each column holds a specific type of information (like a property), and each row is an entry or example of the item the table represents. In SQL, the structure of a table—what columns it has and what kind of data each column can store—is defined by something called the database schema.

Inserting New Data

To add new data to a database, we use an INSERT statement. It tells the database which table to add the data to, which columns we’re filling, and the values we want to insert. Usually, you need to provide a value for each column in the table. You can also insert several rows at once by listing them one after another.

INSERT INTO mytable
VALUES (value_or_expr, another_value_or_expr, …),
       (value_or_expr_2, another_value_or_expr_2, …),
       …;

In some cases, if you have incomplete data and the table contains columns that support default values, you can insert rows with only the columns of data you have by specifying them explicitly.

INSERT INTO mytable
(column, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
      (value_or_expr_2, another_value_or_expr_2, …),
      …;

When using an INSERT statement, the number of values must match the number of columns you list. Even though this can make the statement longer, it’s more flexible in the long run. For example, if you add a new column with a default value later, you won’t need to update existing INSERT statements.

You can also use math or string expressions when inserting values. This helps make sure the data is formatted the way you want when it goes into the table.

Example Insert statement with expressions

INSERT INTO boxoffice
(movie_id, rating, sales_in_millions)
VALUES (1, 9.9, 283742034 / 1000000);

Exercise 2 Updating Rows

Besides adding new data, you’ll often need to change existing data. To do this, you use an UPDATE statement. Like INSERT, you need to say which table you’re updating, which columns you’re changing, and which rows should be updated. Also, make sure the new values match the correct data types for each column.

UPDATE mytable
SET column = value_or_expr, 
    other_column = another_value_or_expr, 
    …
WHERE condition;

Example

Before

After

Exercise 2 Deleting Rows

When you need to delete data from a table in the database, you can use a DELETE statement, which describes the table to act on, and the rows of the table to delete through the WHERE clause.

If you decide to leave out the WHERE constraint, then all rows are removed, which is a quick and easy way to clear out a table completely (if intentional).

DELETE FROM mytable
WHERE condition;

Exercise 3 Creating Tables

When you have new entities and relationships to store in your database, you can create a new database table using the CREATE TABLE statement.

CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

A table’s structure is defined by its schema, which lists the columns in the table. Each column has:

• A name

• A data type (like number or text)

• Optional rules (like limits on what values are allowed)

• An optional default value if none is provided

If you try to create a table that already exists, most databases will show an error. To avoid this, you can use IF NOT EXISTS in your CREATE TABLE statement to skip creating the table if it’s already there.

Common Data Types Most databases support common data types like:

• Numbers (e.g., INTEGER, FLOAT)

• Text (e.g., VARCHAR, TEXT)

• Dates and times

• Booleans (TRUE or FALSE)

• Binary data (e.g., images or files)

These are useful when deciding how each column should store information.

Data Type	Description
INTEGER, BOOLEAN	Stores whole numbers like counts or ages. In some systems, BOOLEAN is represented as 0 (false) or 1 (true).
FLOAT, DOUBLE, REAL	Stores decimal numbers for more precise values like measurements. Use different types depending on the level of precision needed.
CHARACTER(n), VARCHAR(n), TEXT	Stores text. CHARACTER and VARCHAR have a max length (longer values may be cut), which helps with performance. TEXT is used for longer strings.
DATE, DATETIME	Stores date and time values. Useful for tracking events or timelines, but can be tricky when dealing with time zones.
BLOB	Stores binary data like images or files. These are not human-readable and often require metadata to interpret them correctly later.

Each column can have rules, called constraints, that control what kind of values are allowed. This isn’t a full list, but here are some common ones you might find helpful.

Constraint	Description
PRIMARY KEY	Makes sure each value in this column is unique and can be used to identify each row in the table.
AUTOINCREMENT	Automatically adds and increases a number in this column for each new row. Usually used with INTEGER types. Not supported in all databases.
UNIQUE	Makes sure all values in this column are different from one another. Similar to PRIMARY KEY, but doesn’t have to identify each row.
NOT NULL	Makes sure the column can’t be left empty (no NULL values allowed).
CHECK (expression)	Adds a rule to the column using a condition. For example, you can require that a number is positive or that text starts with a certain letter.
FOREIGN KEY	Links a column to a column in another table to make sure the data matches. This helps keep related data consistent between tables.

Example Create Table

CREATE TABLE movies (
    id INTEGER PRIMARY KEY,
    title TEXT,
    director TEXT,
    year INTEGER, 
    length_minutes INTEGER
);

Exercise 4 Altering tables

As your data changes over time, SQL provides a way for you to update your corresponding tables and database schemas by using the ALTER TABLE statement to add, remove, or modify columns and table constraints.

The syntax for adding a new column is similar to the syntax when creating new rows in the CREATE TABLE statement. You need to specify the data type of the column along with any potential table constraints and default values to be applied to both existing and new rows. In some databases like MySQL, you can even specify where to insert the new column using the FIRST or AFTER clauses, though this is not a standard feature.

ALTER TABLE mytable
ADD column DataType OptionalTableConstraint 
    DEFAULT default_value;

Removing Columns

Dropping columns is as easy as specifying the column to drop, however, some databases (including SQLite) don’t support this feature. Instead you may have to create a new table and migrate the data over.

ALTER TABLE mytable
DROP column_to_be_deleted;

Renaming the Table

ALTER TABLE mytable
RENAME TO new_table_name;

Exercise 5 Dropping Tables

In some rare cases, you may want to remove an entire table including all of its data and metadata, and to do so, you can use the DROP TABLE statement, which differs from the DELETE statement in that it also removes the table schema from the database entirely.

DROP TABLE IF EXISTS mytable;

Like the CREATE TABLE statement, the database may throw an error if the specified table does not exist, and to suppress that error, you can use the IF EXISTS clause.

In addition, if you have another table that is dependent on columns in table you are removing (for example, with a FOREIGN KEY dependency) then you will have to either update all dependent tables first to remove the dependent rows or to remove those tables entirely.