SQL Cheatsheet: Learn SQL Core Concepts with Examples

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.

📙 10 Best SQL Books for Beginners and Advanced

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.

Basics of SQL

This section describes examples of basic operations (create/read/update/delete) for working with data in SQL tables.

Creating a new database

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.

Creating a table

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
);

Data types

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.

Numeric types

Symbol types

📚 10 Best MySQL Books for Beginners and Experienced Developers

Date and time

📕 10 Essential MongoDB Books for Beginners and Experienced Developers 

Geometric types

Other

Attributes

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)
);

Adding data

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!');

Data extraction

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;

Searching for pattern data

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%';

Sorting data

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;

Updating data

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";

Deleting data

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;

Aliases

SELECT first_name AS name, last_name AS surname FROM clients;

    name     | surname
-------------+----------
 Fowler      | Ebbutt
 Huntley     | Giabucci
 Michel      | Cogman
 Bartholomew | Mecco
 Donelle     | Lambin

Editing tables

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

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;

Grouping

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