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.
To create new tables:
For more details, see the official CREATE TABLE documentation.
To add data into your tables:
For more information, see the official INSERT documentation.
The AUTO_INCREMENT attribute automatically generates a unique identity for new rows.
Create a table with an AUTO_INCREMENT column:
When inserting, omit the id field; it will be automatically generated:
Verify the inserted records:
For more details, see the documentation.
To combine rows from two tables based on a related column:
This type of query is a join. For more details, consult the documentation on .
To find the maximum value in a column:
See the documentation. For a grouped example, refer to Finding the Maximum Value and Grouping the Results below.
To find the minimum value in a column:
See the MIN() function documentation.
To calculate the average value of a column:
See the documentation.
To find the maximum value within groups:
Further details are available in the MAX() function documentation.
To sort your query results (e.g., in descending order):
For more options, see the documentation.
To find the entire row containing the minimum value of a specific column across all records:
To retrieve the full record for the maximum value within each group (e.g., highest score per student):
Use the TIMESTAMPDIFF function to calculate age from a birth date.
To see the current date (optional, for reference):
To calculate age as of a specific date (e.g., '2014-08-02'):
To calculate current age, replace the specific date string (e.g., '2014-08-02') with CURDATE().
See the documentation for more.
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.
Example: Add an incremental counter to a result set.
See for more.
To list all tables in the current database, ordered by their size (data + index) in megabytes:
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:
To delete duplicate rows, keeping the one with the highest id for each group of f1 values:
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:
This page is licensed: CC BY-SA / Gnu FDL
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
);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');CREATE TABLE student_details (
id INT NOT NULL AUTO_INCREMENT, name CHAR(10),
date_of_birth DATE, PRIMARY KEY (id)
);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');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 |
+----+---------+---------------+SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b;SELECT MAX(a) FROM t1;+--------+
| MAX(a) |
+--------+
| 3 |
+--------+SELECT MIN(a) FROM t1;+--------+
| MIN(a) |
+--------+
| 1 |
+--------+SELECT AVG(a) FROM t1;+--------+
| AVG(a) |
+--------+
| 2.0000 |
+--------+SELECT name, MAX(score) FROM student_tests GROUP BY name;+---------+------------+
| name | MAX(score) |
+---------+------------+
| Chun | 75 |
| Esben | 43 |
| Kaolin | 88 |
| Tatiana | 87 |
+---------+------------+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 |
+---------+--------+-------+SELECT name, test, score FROM student_tests
WHERE score = (SELECT MIN(score) FROM student_tests);+-------+--------+-------+
| name | test | score |
+-------+--------+-------+
| Esben | Tuning | 31 |
+-------+--------+-------+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 |
+---------+--------+-------+SELECT CURDATE() AS today;+------------+
| today |
+------------+
| 2014-02-17 | -- Example output; actual date will vary
+------------+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 |
+---------+---------------+------+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 |
+---------+--------+-------+------------+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 |
+---------+---------+---------------+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 |
...
+--------------------+---------------------------------------+-----------+CREATE TABLE t (id INT, f1 VARCHAR(2));INSERT INTO t VALUES (1,'a'), (2,'a'), (3,'b'), (4,'a');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;SELECT * FROM t;+------+------+
| id | f1 |
+------+------+
| 3 | b |
| 4 | a |
+------+------+