Essential Queries Guide

Learn how to perform essential SQL operations such as creating tables, inserting data, and using aggregate functions like MAX, MIN, and AVG.

The Essential Queries Guide offers a concise collection of commonly-used SQL queries. It's designed to help developers and database administrators quickly find syntax and examples for typical database operations, from table creation and data insertion to effective data retrieval and manipulation.

Creating a Table

To create new tables:

CREATE TABLE t1 ( a INT );

CREATE TABLE t2 ( b INT );

CREATE TABLE student_tests (
 name CHAR(10), test CHAR(10),
 score TINYINT, test_date DATE
);

For more details, see the official CREATE TABLE documentation.

Inserting Records

To add data into your tables:

INSERT INTO t1 VALUES (1), (2), (3);

INSERT INTO t2 VALUES (2), (4);

INSERT INTO student_tests
 (name, test, score, test_date) VALUES
 ('Chun', 'SQL', 75, '2012-11-05'),
 ('Chun', 'Tuning', 73, '2013-06-14'),
 ('Esben', 'SQL', 43, '2014-02-11'),
 ('Esben', 'Tuning', 31, '2014-02-09'),
 ('Kaolin', 'SQL', 56, '2014-01-01'),
 ('Kaolin', 'Tuning', 88, '2013-12-29'),
 ('Tatiana', 'SQL', 87, '2012-04-28'),
 ('Tatiana', 'Tuning', 83, '2013-09-30');

For more information, see the official INSERT documentation.

Using AUTO_INCREMENT

The AUTO_INCREMENT attribute automatically generates a unique identity for new rows.

Create a table with an AUTO_INCREMENT column:

CREATE TABLE student_details (
 id INT NOT NULL AUTO_INCREMENT, name CHAR(10),
 date_of_birth DATE, PRIMARY KEY (id)
);

When inserting, omit the id field; it will be automatically generated:

INSERT INTO student_details (name,date_of_birth) VALUES
 ('Chun', '1993-12-31'),
 ('Esben','1946-01-01'),
 ('Kaolin','1996-07-16'),
 ('Tatiana', '1988-04-13');

Verify the inserted records:

SELECT * FROM student_details;

+----+---------+---------------+
| id | name    | date_of_birth |
+----+---------+---------------+
|  1 | Chun    | 1993-12-31    |
|  2 | Esben   | 1946-01-01    |
|  3 | Kaolin  | 1996-07-16    |
|  4 | Tatiana | 1988-04-13    |
+----+---------+---------------+

For more details, see the AUTO_INCREMENT documentation.

Querying from two tables on a common value (JOIN)

To combine rows from two tables based on a related column:

SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b;

This type of query is a join. For more details, consult the documentation on JOINS.

Finding the Maximum Value

To find the maximum value in a column:

SELECT MAX(a) FROM t1;

+--------+
| MAX(a) |
+--------+
|      3 |
+--------+

See the MAX() function documentation. For a grouped example, refer to Finding the Maximum Value and Grouping the Results below.

Finding the Minimum Value

To find the minimum value in a column:

SELECT MIN(a) FROM t1;

+--------+
| MIN(a) |
+--------+
|      1 |
+--------+

See the MIN() function documentation.

Finding the Average Value

To calculate the average value of a column:

SELECT AVG(a) FROM t1;

+--------+
| AVG(a) |
+--------+
| 2.0000 |
+--------+

See the AVG() function documentation.

Finding the Maximum Value and Grouping the Results

To find the maximum value within groups:

SELECT name, MAX(score) FROM student_tests GROUP BY name;

+---------+------------+
| name    | MAX(score) |
+---------+------------+
| Chun    |         75 |
| Esben   |         43 |
| Kaolin  |         88 |
| Tatiana |         87 |
+---------+------------+

Further details are available in the MAX() function documentation.

Ordering Results

To sort your query results (e.g., in descending order):

SELECT name, test, score FROM student_tests 
 ORDER BY score DESC; -- Use ASC for ascending order

+---------+--------+-------+
| name    | test   | score |
+---------+--------+-------+
| Kaolin  | Tuning |    88 |
| Tatiana | SQL    |    87 |
| Tatiana | Tuning |    83 |
| Chun    | SQL    |    75 |
| Chun    | Tuning |    73 |
| Kaolin  | SQL    |    56 |
| Esben   | SQL    |    43 |
| Esben   | Tuning |    31 |
+---------+--------+-------+

For more options, see the ORDER BY documentation.

Finding the Row with the Minimum of a Particular Column

To find the entire row containing the minimum value of a specific column across all records:

SELECT name, test, score FROM student_tests 
 WHERE score = (SELECT MIN(score) FROM student_tests);

+-------+--------+-------+
| name  | test   | score |
+-------+--------+-------+
| Esben | Tuning |    31 |
+-------+--------+-------+

Finding Rows with the Maximum Value of a Column by Group

To retrieve the full record for the maximum value within each group (e.g., highest score per student):

SELECT name, test, score FROM student_tests st1
 WHERE score = (SELECT MAX(st2.score) FROM student_tests st2 WHERE st1.name = st2.name);

+---------+--------+-------+
| name    | test   | score |
+---------+--------+-------+
| Chun    | SQL    |    75 |
| Esben   | SQL    |    43 |
| Kaolin  | Tuning |    88 |
| Tatiana | SQL    |    87 |
+---------+--------+-------+

Calculating Age

Use the TIMESTAMPDIFF function to calculate age from a birth date.

To see the current date (optional, for reference):

SELECT CURDATE() AS today;

+------------+
| today      |
+------------+
| 2014-02-17 | -- Example output; actual date will vary
+------------+

To calculate age as of a specific date (e.g., '2014-08-02'):

SELECT name, date_of_birth, TIMESTAMPDIFF(YEAR, date_of_birth, '2014-08-02') AS age
  FROM student_details;

+---------+---------------+------+
| name    | date_of_birth | age  |
+---------+---------------+------+
| Chun    | 1993-12-31    |   20 |
| Esben   | 1946-01-01    |   68 |
| Kaolin  | 1996-07-16    |   18 |
| Tatiana | 1988-04-13    |   26 |
+---------+---------------+------+

To calculate current age, replace the specific date string (e.g., '2014-08-02') with CURDATE().

See the TIMESTAMPDIFF() documentation for more.

Using User-defined Variables

User-defined variables can store values for use in subsequent queries within the same session.

Example: Set a variable for the average score and use it to filter results.

SELECT @avg_score := AVG(score) FROM student_tests;

+-------------------------+
| @avg_score:= AVG(score) |
+-------------------------+
|            67.000000000 |
+-------------------------+

SELECT * FROM student_tests WHERE score > @avg_score;

+---------+--------+-------+------------+
| name    | test   | score | test_date  |
+---------+--------+-------+------------+
| Chun    | SQL    |    75 | 2012-11-05 |
| Chun    | Tuning |    73 | 2013-06-14 |
| Kaolin  | Tuning |    88 | 2013-12-29 |
| Tatiana | SQL    |    87 | 2012-04-28 |
| Tatiana | Tuning |    83 | 2013-09-30 |
+---------+--------+-------+------------+

Example: Add an incremental counter to a result set.

SET @count = 0;

SELECT @count := @count + 1 AS counter, name, date_of_birth FROM student_details;

+---------+---------+---------------+
| counter | name    | date_of_birth |
+---------+---------+---------------+
|       1 | Chun    | 1993-12-31    |
|       2 | Esben   | 1946-01-01    |
|       3 | Kaolin  | 1996-07-16    |
|       4 | Tatiana | 1988-04-13    |
+---------+---------+---------------+

See User-defined Variables for more.

View Tables in Order of Size

To list all tables in the current database, ordered by their size (data + index) in megabytes:

SELECT table_schema AS `DB`, table_name AS `TABLE`,
  ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
  FROM information_schema.TABLES
  WHERE table_schema = DATABASE() -- This clause restricts results to the current database
  ORDER BY (data_length + index_length) DESC;

+--------------------+---------------------------------------+-----------+
| DB                 | Table                                 | Size (MB) | -- Example Output
+--------------------+---------------------------------------+-----------+
| your_db_name       | some_large_table                      |      7.05 |
| your_db_name       | another_table                         |      6.59 |
...
+--------------------+---------------------------------------+-----------+

Removing Duplicates

To remove duplicate rows based on specific column values, while keeping one instance (e.g., the instance with the highest id).

This example assumes id is a unique primary key and duplicates are identified by the values in column f1. It keeps the row with the maximum id for each distinct f1 value.

Setup sample table and data:

CREATE TABLE t (id INT, f1 VARCHAR(2));

INSERT INTO t VALUES (1,'a'), (2,'a'), (3,'b'), (4,'a');

To delete duplicate rows, keeping the one with the highest id for each group of f1 values:

DELETE t_del FROM t AS t_del
INNER JOIN (
    SELECT f1, MAX(id) AS max_id
    FROM t
    GROUP BY f1
    HAVING COUNT(*) > 1 -- Identify groups with actual duplicates
) AS t_keep ON t_del.f1 = t_keep.f1 AND t_del.id < t_keep.max_id;

This query targets rows for deletion (t_del) where their f1 value matches an f1 in a subquery (t_keep) that has duplicates, and their id is less than the maximum id found for that f1 group.

Verify results after deletion:

SELECT * FROM t;

+------+------+
| id   | f1   |
+------+------+
|    3 | b    |
|    4 | a    |
+------+------+

---

_{This page is licensed: CC BY-SA / Gnu FDL}