Master SQL core concepts with this comprehensive cheatsheet! This comprehensive SQL cheatsheet is the perfect resource for anyone who wants to learn SQL core concepts with examples. Covering a wide range of topics, from basic queries to advanced concepts, this cheatsheet has everything you need to start writing SQL effectively.
Whether you're a beginner or an experienced developer, this cheatsheet is a must-have for anyone who wants to learn SQL core concepts quickly and easily.
SQL (Structured Query Language) is a special query language for working with relational databases (e.g. MySQL, PostgreSQL, Oracle, MariaDB). SQL queries are constructed from a set of operators, which are the usual words of the English language.
This section describes examples of basic operations (create/read/update/delete) for working with data in SQL tables.
CREATE DATABASE store;
You can define an unlimited number of tables in the database, which will store the necessary data.
Note that each SQL query ends with a semicolon.
At the table creation stage, data types are specified and various attributes are defined for all columns.
π 8 Best Database Books for Beginners and Experienced Developers
CREATE TABLE clients (
id SERIAL PRIMARY KEY,
firstName VARCHAR(50) NOT NULL,
lastName VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE,
phone VARCHAR(20) UNIQUE,
age SMALLINT NOT NULL,
gender VARCHAR(6) NOT NULL,
isMarried BOOLEAN,
createdAt TIMESTAMP,
updatedAt TIMESTAMP
);
Below is a list of the main types for a PostgreSQL database.
Other databases may have slightly different data types and descriptions. Therefore, if you encounter errors, refer to the documentation.
Type | Values | Description |
---|---|---|
smallint int2 | Numbers from -32768 to +32767 | Takes up 2 bytes. |
integer int4 int | Numbers from -2147483648 to +2147483647 | Takes up 4 bytes. |
bigint int8 | Numbers from -9223372036854775808 to +9223372036854775807 | Takes up 8 bytes. |
numeric decimal | Numbers with an integer part up to 131072 digits and up to 131072 decimal places | Accepts 2 parameters precision (total number of digits) and scale (number of digits after the decimal point). numeric(5, 3) β 22,725 decimal(10, 1) β 52538,4 |
real float4 | Numbers from 1E-37 to 1E+37 | Takes up 4 bytes. |
double precision float8 | Numbers from 1E-307 to 1E+308 | Takes up 8 bytes. |
serial | Auto incrementing numeric values from 1 to 2147483647 | It takes 4 bytes. The value for this type is selected automatically, depending on the values of the previous element. Great for unique IDs. |
smallserial | Auto incrementing numeric values from 1 to 32767 | ΠΠ°Π½ΠΈΠΌΠ°Π΅Ρ 2 Π±Π°ΠΉΡΠ°. |
bigserial | Auto incrementing numeric values from 1 to 9223372036854775807 | Takes up 8 bytes. |
π 10 Best MySQL Books for Beginners and Experienced Developers
Type | Values | Description |
---|---|---|
character char | Fixed length strings | Accepts a parameter that specifies the number of characters in the string. char(5) β hello |
character varying varchar | Variable length strings | Accepts a parameter that specifies the maximum number of characters in the string. varchar(5) β abc, abcd, abcde |
text | Free length text | Suitable for storing text articles, reviews, descriptions. |
π 10 Essential MongoDB Books for Beginners and Experienced Developers
Type | Values | Description |
---|---|---|
timestamp | Date and time from 4713 B.C. to 294276 A.D. | Takes up 8 bytes. |
timestamp with time zone | Date and time from 4713 B.C. to 294276 A.D. including time zone data | Takes up 8 bytes. |
date | Dates from 4713 B.C. to 5874897 A.D. | Takes up 4 bytes. |
time | Time from 00:00:00 to 24:00:00 | Takes up 8 bytes. |
time with time zone | Time from 00:00:00+1459 to 24:00:00-1459 | Takes up 12 bytes. |
Type | Values | Description |
---|---|---|
point | Format point (x,y) | Takes up 16 bytes. |
line | Line in the format {A,B,C} | Takes up 32 bytes. |
lseg | A segment in the format ((x1,y1),(x2,y2)) | Takes up 32 bytes. |
box | Rectangle in the format ((x1,y1),(x2,y2)) | Takes up 32 bytes. |
path | A set of connected points in the format ((x1,y1),...) | Takes up 16+16n bytes. |
polygon | A polygon in the format ((x1,y1),...) | Takes up 40+16n bytes. |
circle | Circle in the format <(x,y),r> | Takes up 24 bytes. |
Type | Values | Description |
---|---|---|
boolean | true / false | The following values can be specified instead of true: TRUE, 't', 'true', 'y', 'yes', 'on', '1'. Instead of FALSE: FALSE, 'f', 'false', 'n', 'no', 'off', '0'. |
bytea | Data as binary strings | |
json | JSON in text form | |
jsonb | JSON in binary format | |
uuid | Stores UUID strings | |
xml | Data in XML format |
Attributes allow you to specify additional properties for table columns.
PRIMARY KEY
β indicates that the column stores a unique identifier.CREATE TABLE test (
id SERIAL PRIMARY KEY
);
UNIQUE
β indicates that each element in the column will be unique.CREATE TABLE emails (
email VARCHAR(50) UNIQUE
);
NULL
β indicates that the value in the column may be missing (by default, all columns except PRIMARY KEY
allow no value, so you do not need to specify it explicitly.)
NOT NULL
β indicates that the value in the column cannot be empty.
CREATE TABLE users (
firstName VARCHAR(30) NOT NULL,
lastName VARCHAR(30) NOT NULL
);
DEFAULT
β specifies the value to be assigned by default.CREATE TABLE messages (
text VARCHAR(200) DEFAULT 'Hello World'
);
CHECK
β specifies the range of values that can be stored in the column.CREATE TABLE users (
firstName VARCHAR(50),
age INTEGER NOT NULL CHECK(age > 0 AND age < 100)
);
INSERT INTO clients (firstName, lastName, age, gender, isMarried)
VALUES ('Alex', 'Smith', 25, 'male', false);
You can insert multiple elements at once by listing the values for the new element in new brackets:
INSERT INTO messages (title, body) VALUES
('MSG-1', 'Hello World'),
('MSG-2', 'SQL is awesome'),
('MSG-3', 'Have a nice day!');
Remember that many operators can be combined with each other.
Get all elements of the table with values of all its columns:
SELECT * FROM clients;
Get all elements of a table with values of certain columns:
SELECT firstName, lastName, phone FROM clients;
Get the first 20
elements of the table:
SELECT * FROM clients LIMIT 20;
Get the first 10
table elements starting from position 50
(pagination):
SELECT * FROM clients LIMIT 10 OFFSET 50;
Get all items where the gender
column is equal to the value "male":
SELECT * FROM clients WHERE gender = 'male';
Get all elements where the age
column is 25 and the isMarried
column is false:
SELECT * FROM clients WHERE age = 25 AND isMarried = false;
Get all items where the firstName
column is "Alex" or the lastName
column is "Smith":
SELECT * FROM clients WHERE firstName = 'Alex' OR lastName = 'Smith';
Get all table elements where the firstName
column can have one of the listed values: "John", "Mike", "Kane":
SELECT * FROM clients WHERE firstName IN ('John', 'Mike', 'Kane');
Get all items where the age
column values are between 20 and 30:
SELECT * FROM clients WHERE age BETWEEN 20 AND 30;
Get all items where the phone
column values are not empty:
SELECT * FROM clients WHERE phone IS NOT NULL;
Get all values of the lastName
column without repetitions (i.e., only unique values):
SELECT DISTINCT(lastName) FROM clients;
The LIKE
and NOT LIKE
operators are used to search for pattern data. The templates themselves use special wildcards:
%
β a wildcard, which indicates that any number of characters can be in its place._
β the wildcard, which indicates that there can only be one character in its place.Get all elements of the table, where the value of the firstName
column starts with the character "A":
SELECT * FROM clients WHERE firstName LIKE 'A%';
Get all elements of the table, where the value of the firstName
column starts with one of the following characters: "A", "B", "C":
SELECT * FROM clients WHERE firstName LIKE '[ABC]%';
Get all elements of the table, where the 2nd character in the firstName
column is not equal to "o":
SELECT * FROM clients WHERE firstName NOT LIKE '_o%';
Get all elements of the table sorted by column firstName
in ascending order:
SELECT * FROM clients ORDER BY firstName ASC;
Get all elements of the table sorted by the age
column in descending order:
SELECT * FROM clients ORDER BY age DESC;
Get all elements of the table sorted by the lastName
column in descending order, and then by the id
column in ascending order:
SELECT * FROM clients ORDER BY lastName DESC, id ASC;
Change the value of the phone
column in the element with an id
column value of 42:
UPDATE clients SET phone = '+123987654' WHERE id = 42;
Change the values of the city
and age
columns with the values of gender
= "female" and name
= "Sophia":
UPDATE clients SET city = 'Paris', age = 33 WHERE gender = 'famale' AND name = "Sophia";
Remove the item from the table where the id
column value = 1:
DELETE FROM clients WHERE id = 137;
Remove elements from the table, where the column values city
= "Prague" and age
= 22:
DELETE FROM clients WHERE city = 'Prague' AND age = 22;
SELECT first_name AS name, last_name AS surname FROM clients;
name | surname
-------------+----------
Fowler | Ebbutt
Huntley | Giabucci
Michel | Cogman
Bartholomew | Mecco
Donelle | Lambin
Add a new column city
to the table clients
:
ALTER TABLE clients ADD COLUMN city VARCHAR(50);
Delete the isMarried
column from the clients
table:
ALTER TABLE clients DROP COLUMN isMarried;
Rename column firstName
to fName
in table clients
:
ALTER TABLE clients RENAME COLUMN firstName TO fName;
Rename the table clients
to users
ALTER TABLE clients RENAME TO users;
Aggregate functions are used to summarize/count data.
Count the total number of elements in the table:
SELECT COUNT(*) FROM clients;
Get the maximum/minimum value of the age
column:
SELECT MAX(age) FROM clients;
SELECT MIN(age) FROM clients;
Calculate the total sum of all elements in the age
column:
SELECT SUM(age) FROM clients;
Calculate the average value of the elements of the age
column:
SELECT AVG(age) FROM clients;
Group the data from the table clients
by column gender
and output in the column total
the total number of elements for each value of gender
:
SELECT gender, COUNT(gender) AS total FROM clients GROUP BY gender;
Instead of the name of the column on which the grouping, you can specify its sequence number in the
SELECT
statement:
SELECT gender, COUNT(gender) AS total FROM clients GROUP BY 1; # Similar to the query above
gender | total
-------------+-------
Male | 368
Female | 245
Group the data from the table clients
by column gender
and then by column age
, display for each resulting element the average value of the column balance
and sort everything in ascending order by column age
:
SELECT gender, age, AVG(balance) AS avg_money FROM clients GROUP BY gender, age ORDER BY age;
gender | age | avg_money
-------------+-----+------------------------
Male | 18 | 31699.250000000000
Female | 18 | 21025.000000000000
Male | 19 | 16963.166666666667
Female | 19 | 25118.400000000000
Male | 20 | 23203.500000000000
Female | 20 | 22956.875000000000
Male | 21 | 19032.400000000000
Female | 21 | 27047.800000000000
Source: https://github.com
#sql #database