The most commonly used SQL Queries, with examples
A. Data Definition Language (DDL)
1. CREATE
CREATE
queries are used to create a new database or table.
CREATE TABLE table_name (
column_1 datatype_1,
column_2 datatype_2,
);
2. ALTER
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;
3. DROP
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;
4. TRUNCATE
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.
1. SELECT
SELECT … FROM …
is the most basic and commonly used query in SQL. It’s used for retrieving data from a table.
A common SELECT
query is broken down into four main parts:
- SELECT
- FROM
- WHERE
- 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
WHERE
to be used:
SELECT column_name(s)
FROM table_name
WHERE condition(s);
By using 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.
Example:
SELECT * FROM students
WHERE state_code = 'CA'
That query to show every record from the table students that match the state_code “CA”.
ORDER BY
is 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;
2. INSERT
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, ...);
Example:
INSERT INTO students (full_name, student_id, state_code)
VALUES (“Alex Jonas”, 234, "CA");
3. UPDATE
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;
4. DELETE
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
AS
to rename a column or table temporarily using an alias on the result view.
SELECT column_name AS 'Alias'
FROM table_name;
- The
BETWEEN
operator 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 BY
is 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;
HAVING
is used to replaceWHERE
to work with aggregate functions.WHERE
clause introduces a condition on individual rows;HAVING
clause introduces a condition on aggregations.
HAVING
is typically used with GROUP BY
.
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;
IS NULL
andIS NOT NULL
are used to test whether a column value is empty or not.
SELECT column_name(s)
FROM table_name
WHERE column_name IS NULL;
LIKE
is 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
LIMIT
to specify the maximum number of records you want to show in a result set.
SELECT *
FROM table_name
LIMIT number;
OR
is 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 DISTINCT
returns unique values in the specified column(s).
SELECT DISTINCT column_name
FROM table_name;
- An
OUTER JOIN
will 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;
- An
INNER JOIN
will 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.