A. Data Definition Language (DDL)
CREATE queries are used to create a new database or table.
CREATE TABLE table_name ( column_1 datatype_1, column_2 datatype_2, );
ALTER queries are used to modify the structure of a database or a table such as adding a new column, change the data type, drop, or rename an existing column, etc.
ALTER TABLE table_name ADD column_name datatype;
DROP queries are used to delete a database or table. You should also be careful when using this type of query because it will remove everything, including table definition along with all the data, indexes, triggers, constraints and permission specifications for that table.
DROP TABLE table_name;
TRUNCATE queries are used to clean the table, remove all the existing records, but not the table itself.
TRUNCATE TABLE table_name;
B. Data Manipulation Language (DML): managing data within table object.
SELECT … FROM …is the most basic and commonly used query in SQL. It’s used for retrieving data from a table.
SELECT query is broken down into four main parts:
- ORDER BY
Let’s look deeper
- To see data of an entire table:
SELECT * FROM table_name;
- To see data in some specific columns:
SELECT column_name(s) FROM table_name;
- To see data from your table based on some conditions, this is the case for
WHEREto be used:
SELECT column_name(s) FROM table_name WHERE condition(s);
WHERE in a
SELECT query, we add one or more conditions and restrict the number the records affected by the query.
Or in other words, it’s being a filter to filter out only the records that match the conditions as the result.
SELECT * FROM students WHERE state_code = 'CA'
That query to show every record from the table students that match the state_code “CA”.
ORDER BYis a clause that indicates you want to sort the result set by a particular column either alphabetically or numerically.
SELECT column_name FROM table_name ORDER BY column_name ASC | DESC;
INSERT INTO queries are used to insert one or more rows of data (new records) into an existing table.
INSERT INTO table_name (column_1, column_2, column_3, ...) VALUES (value_1, value_2, value_3, ...);
INSERT INTO students (full_name, student_id, state_code) VALUES (“Alex Jonas”, 234, "CA");
UPDATE queries are used to modify an existing table and update it with new data based on some conditions.
UPDATE table_name SET column_1 = value_1, column_2 = value_2, ... WHERE condition;
DELETE FROM queries are used to remove the records from a table based on some conditions.
DELETE FROM is similar to
TRUNCATE but it limits the number of rows being affected by the query using the conditions.
DELETE FROM table_name WHERE condition;
C. Aggregate Functions
AVG()returns the average value for a numeric column.
SELECT AVG(column_name) FROM table_name;
SUM()returns the sum of all the values in a column.
SELECT SUM(column_name) FROM table_name;
ROUND()rounds the values in the column to the number of decimal places specified by the integer.
SELECT ROUND(column_name, integer) FROM table_name;
MAX()returns the largest value in a column.
SELECT MAX(column_name) FROM table_name;
MIN()returns the smallest value in a column.
SELECT MIN(column_name) FROM table_name;
COUNT()counts the number of rows where the column is not NULL.
SELECT COUNT(column_name) FROM table_name;
D. Additional clauses and functions
- You can use
ASto rename a column or table temporarily using an alias on the result view.
SELECT column_name AS 'Alias' FROM table_name;
BETWEENoperator is used to select the value within a certain range.
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value_1 AND value_2;
GROUP BYis a clause in SQL that is only used with aggregate functions (COUNT, MAX, MIN, SUM, AVG). It 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;
HAVINGis used to replace
WHEREto work with aggregate functions.
WHEREclause introduces a condition on individual rows;
HAVINGclause introduces a condition on aggregations.
HAVING is typically used with
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;
IS NOT NULLare used to test whether a column value is empty or not.
SELECT column_name(s) FROM table_name WHERE column_name IS NULL;
LIKEis a special operator used with the WHERE clause to search for a specific pattern in a column.
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
- You can use
LIMITto specify the maximum number of records you want to show in a result set.
SELECT * FROM table_name LIMIT number;
ORis used to combine two or more conditions in a where clause. The results have to match at least one of the conditions specified.
SELECT * FROM table_name WHERE condition_1 OR condition_2;
SELECT DISTINCTreturns unique values in the specified column(s).
SELECT DISTINCT column_name FROM table_name;
OUTER JOINwill combine rows from different tables even if the join condition is not met. Every row in the left table is returned in the result set, and if the join condition is not met, then NULL values are used to fill in the columns from the right table.
SELECT column_name(s) FROM table_1 LEFT JOIN table_2 ON table_1.column_name = table_2.column_name;
INNER JOINwill 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;
Need a good tool for learning and practising SQL? Try TablePlus.
It’s modern, native tool with elegant GUI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. It’s free anyway.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS.