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 );
CREATE TABLE flights( id INTEGER PRIMARY KEY AUTOINCREMENT, origin TEXT NOT NULL, destination TEXT NOT NULL, duration INTEGER NOT NULL );
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;
The INSERT statement inserts a new row into a table.
-- 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);
INSERT INTO students (id, name, birthday) VALUES (1, 'Jane', '1990-10-12');
The ALTER TABLE statement adds a new column to a table.
ALTER TABLE table_name ADD COLUMN column DATA_TYPE;
ALTER TABLE students ADD COLUMN email TEXT;
The UPDATE statement edits a row in a table.
UPDATE table_name SET column1 = value1, column2 = value2 WHERE some_column = some_value;
UPDATE flights SET duration = 430 WHERE origin = "New York" AND destination = "London";
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;
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;
DELETE FROM flights WHERE destination = "Tokyo";
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:
!=not equal to
>=greater than or equal to
<=less than or equal to
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;
SELECT imdb_rating AS 'IMDb' FROM movies;
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 sorts the results either alphabetically or numerically.
SELECT column_name FROM table_name ORDER BY column_name ASC | DESC;
SELECT * FROM names WHERE year > 18 ORDER BY year DESC;
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 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 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 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;
SELECT name, origin, destination FROM flights JOIN passengers ON passengers.flight_id = flights.id;
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 %cfinds 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%rfinds any values that start with ‘a’ and end with ‘r’.
SELECT name FROM address WHERE name LIKE 'Jo%';
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;
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';
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 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;