SQL Commands

CREATE statement

CREATE statements allow us to create a new table in the database. We can specify the name of the table and the name of each column in the table.

CREATE TABLE table_name (
   column1 DATA_TYPE,
   column2 DATA_TYPE,
   column3 DATA_TYPE
);

table_name refers to the name of the table that the command is applied to.
(column_1 data_type, column_2 data_type, column_3 data_type) is a parameter.

For example

CREATE TABLE flights(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    origin TEXT NOT NULL,
    destination TEXT NOT NULL,
    duration INTEGER NOT NULL
);

SELECT statement

SELECT selects data from a database.

SELECT column1, column2
FROM table_name;

In case you’d like to query data from a database, use *

SELECT *
FROM table_name;

INSERT statement

The INSERT statement inserts a new row into a table.

Insert into columns in order.
Insert into columns by name.

-- Insert into columns in order:
INSERT INTO table_name
VALUES (value1, value2);

-- Insert into columns by name:
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

For example

INSERT INTO students (id, name, birthday)
VALUES (1, 'Jane', '1990-10-12');

ALTER TABLE statement

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

ALTER TABLE table_name
ADD COLUMN column DATA_TYPE;

For example

ALTER TABLE students
ADD COLUMN email TEXT;

UPDATE statement

The UPDATE statement edits a row in a table.

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE some_column = some_value;

UPDATE is a clause that edits a row in the table.
table_name is the name of the table.
SET is a clause that indicates the column to edit.

column1 is the name of the column that is going to be updated
value1 is the new value that is going to be inserted into the column1 column.

WHERE is a clause that indicates which row(s) to update with the new column value.

For example

UPDATE flights
    SET duration = 430
    WHERE origin = "New York"
    AND destination = "London";

CASE statement

CASE statements can create different outputs (usually in the SELECT statement). It is SQL’s way of handling if-then logic.

SELECT column_name,
  CASE
    WHEN condition THEN 'Result_1'
    WHEN condition THEN 'Result_2'
    ELSE 'Result_3'
  END
FROM table_name;

DELETE statement

The DELETE statement deletes one or more rows in a table.

The WHERE clause specifies which rows should be deleted. If the WHERE clause is omitted, all records will be deleted.

DELETE FROM table_name
WHERE some_column = some_value;

For example

DELETE FROM flights WHERE destination = "Tokyo";

WHERE clause

The WHERE clause filters rows that match a certain condition. The query below selects all records where the year equals 2014.

SELECT name
FROM student
WHERE year = 2014;

Comparison operators used with the WHERE clause are:

= equal to
!= not equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to

AS keyword

AS allows you to rename a column or table using an alias. The new name needs to be inside of single quotes.

SELECT column_name AS 'Alias'
FROM table_name;

For example

SELECT imdb_rating AS 'IMDb'
FROM movies;

DISTINCT clause

DISTINCT returns unique values in the output. It filters out all duplicate values in the specified column(s).

SELECT DISTINCT specifies unique values in the specified column(s).

SELECT DISTINCT column
FROM table_name;

ORDER BY clause

ORDER BY clause sorts the results either alphabetically or numerically.

SELECT column_name
FROM table_name
ORDER BY column_name ASC | DESC;

DESC is a keyword used to sort the results in descending order.
ASC is a keyword used to sort the results in ascending order (default). Note: ORDER BY always goes after WHERE (if WHERE is present).

For example

SELECT *
FROM names
WHERE year > 18
ORDER BY year DESC;

LIMIT Clause

The LIMIT clause specifies the maximum number of rows the result set will have. This saves space on our screen and makes our queries run faster.

SELECT column_name(s)
FROM table_name
LIMIT number;

LIMIT always goes at the very end of the query. Also, it is not supported in all SQL databases.

The query below limits the result set to 3 rows.

SELECT *
FROM names
LIMIT 3;

GROUP BY clause

GROUP BY is used in collaboration with the SELECT statement to arrange identical data into groups.

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;

HAVING

HAVING is used because the WHERE keyword could not be used with aggregate functions.

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;

INNER JOIN clause

INNER JOIN combine rows from different tables if the join condition is true.

SELECT column_name(s)
FROM table_1
JOIN table_2
  ON table_1.column_name = table_2.column_name;

For example

SELECT name, origin, destination
FROM flights
JOIN passengers
ON passengers.flight_id = flights.id;

SQL operators

LIKE operator

LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column % is wildcard character that can be used with LIKE

LIKE c% finds any values that start with the letter ‘c’
LIKE %c finds any values that end with the letter ‘c’
LIKE %re% finds values that have ‘re’ in any position
LIKE _a% finds any values that have the letter ‘a’ in the second index
LIKE a_%_% finds any values that start with ‘a’ and are at least 3 characters in length.
LIKE a%r finds any values that start with ‘a’ and end with ‘r’.

SELECT name
FROM address
WHERE name LIKE 'Jo%';

IS NULL operator

IS NULL and IS NOT NULL are operators used with the WHERE clause to test for empty values.

SELECT column_name(s)
FROM table_name
WHERE column_name IS NULL;

BETWEEN operator

The BETWEEN operator is used in a WHERE clause to filter the result set within a certain range. The values can be numbers, text or dates.

Example 1: This code filters the result including names with birth years from 1970 up to, and including 2000.

SELECT *
FROM name
WHERE birthyear BETWEEN 1970 AND 2000;

Example 2: This code filters the result including students with names that begin with the letter ‘C’ up to, but not including ones that begin with ‘H’.

SELECT *
FROM students
WHERE name BETWEEN 'C' AND 'H';

AND Operator

The AND operator combine multiple conditions. Both conditions must be true for the row to be included in the result set.

SELECT column_name(s)
FROM table_name;
WHERE first_condition
AND second_condition;

OR operator

OR operator filters the result set to only include rows if any condition is true.

SELECT column_name
FROM table_name
WHERE column_name = value_1
   OR column_name = value_2;