Manipulation

Create

CREATE statements allow us to create a new table in the database.

The statement below creates a new table named celebs:

SQL

CREATE TABLE celebs (
   id INTEGER, 
   name TEXT, 
   age INTEGER
);

• CREATE TABLE is a clause that tells SQL you want to create a new table.
• celebs is the name of the table.
• (id INTEGER, name TEXT, age INTEGER) is a list of parameters defining each column, or attribute in the table and its data type:
• id is the first column in the table. It stores values of data type INTEGER
• name is the second column in the table. It stores values of data type TEXT
• age is the third column in the table. It stores values of data type INTEGER

Insert

The INSERT statement inserts a new row into a table, you can use it when you want to add new records.

The statement below enters a record for Justin Bieber into the celebs table:

SQL

INSERT INTO celebs (id, name, age) 
VALUES (1, 'Justin Bieber', 22);

• INSERT INTO is a clause that adds the specified row or rows.
• celebs is the table the row is added to.
• (id, name, age) are parameters identifying the columns that data will be inserted into.
• VALUES is a clause that indicates the data being inserted.
• (1, 'Justin Bieber', 22) are parameters identifying the values being inserted.
• 1: an integer that will be added to id column;
• 'Justin Bieber': text that will be added to name column;
• 22: an integer that will be added to age column.

To go further

It is possible to insert multiple rows at once using one INSERT statement:

SQL

INSERT INTO MyTable
   ( Column1, Column2, Column3 )
VALUES
   ('John', 123, 'Lloyds Office'), 
   ('Jane', 124, 'Lloyds Office'), 
   ('Billy', 125, 'London Office'),
   ('Miranda', 126, 'Bristol Office');

Select

SELECT statements are used to fetch data from a database.

In the statement below, SELECT returns all data in the name column of the celebs table.

SQL

SELECT name FROM celebs;

• SELECT is a clause that indicates that the statement is a query. You will use SELECT every time you query data from a database.
• name specifies the column to query data from.
• FROM celebs specifies the name of the table to query data from. In this statement, data is queried from the celebs table.
• SELECT statements always return a new table called the result set.

You can also query data from all columns in a table with SELECT:

SQL

SELECT * FROM celebs;

Important

* is a special wildcard character. It allows you to select every column in a table without having to name each one individually.

Alter

The ALTER TABLE statement adds a new column to a table.

The statement below adds a new column twitter_handle to the celebs table:

SQL

ALTER TABLE celebs 
ADD COLUMN twitter_handle TEXT;

• ALTER TABLE is a clause that lets you make the specified changes.
• celebs is the name of the table that is being changed.
• ADD COLUMN is a clause that lets you add a new column to a table:
• twitter_handle is the name of the new column being added;
• TEXT is the data type for the new column;

Important

NULL is a special value in SQL that represents missing or unknown data. Here, the rows that existed before the column was added have NULL (∅) values for twitter_handle.

Update

The UPDATE statement edits a row in a table, you can use it when you want to change existing records.

The statement below updates the record with an id value of 4 to have the twitter_handle @taylorswift13.

SQL

UPDATE celebs 
SET twitter_handle = '@taylorswift13' 
WHERE id = 4; 

• UPDATE is a clause that edits a row in the table.
• celebs is the name of the table.
• SET is a clause that indicates the column to edit.
• twitter_handle is the name of the column that is going to be updated;
• @taylorswift13 is the new value that is going to be inserted into the twitter_handle column.
• WHERE is a clause that indicates which row(s) to update with the new column value. Here the row with a 4 in the id column is the row that will have the twitter_handle updated to @taylorswift13.

Delete

The DELETE FROM statement deletes one or more rows from a table. You can use it when you want to delete existing records.

The statement below deletes all records in the celeb table with no twitter_handle:

SQL

DELETE FROM celebs 
WHERE twitter_handle IS NULL;

• DELETE FROM is a clause that lets you delete rows from a table.
• celebs is the name of the table we want to delete rows from.
• WHERE is a clause that lets you select which rows you want to delete. Here we want to delete all of the rows where the twitter_handle column IS NULL.
• IS NULL is a condition in SQL that returns true when the value is NULL and false otherwise.

Constraints

Constraints that add information about how a column can be used are invoked after specifying the data type for a column. They can be used to tell the database to reject inserted data that does not adhere to a certain restriction.

The statement below sets constraints on the celebs table:

SQL

CREATE TABLE celebs2 (
   id INTEGER PRIMARY KEY, 
   name TEXT UNIQUE,
   date_of_birth TEXT NOT NULL,
   date_of_death TEXT DEFAULT 'Not Applicable'
);

• PRIMARY KEY columns can be used to uniquely identify the row. Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation which will not allow you to insert the new row.
• UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.
• NOT NULL columns must have a value. Attempts to insert a row without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.
• DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.