Iliya.Faz

Posted on Aug 25

SQL Cheat sheet

#database #sql #beginners #tutorial

Long story short, I came back after a long break from programming. Thought I should write this not only to review the concepts myself, but to make sure I have a good cheat sheet for future occasions.

This articles aims to quickly review important SQL concepts in a few minutes of reading.

This article/cheat sheet is divided into 3 parts:
1- Tables, rows, and useful SQL statements
2- SQL joins
3- SQL relationships

How does SQL work?

Related pieces of data are stored in a table (similar to collections in MongoDB)
For instance: Users, Customers, BlogPosts, etc.

Each table has rows and columns. Columns are properties, rows are records.
For instance:

age ,username, displayName, can be columns
{age: 28, username: "john_doe", displayName: "John doe"} can be a row.

Notes:

SQL statements must end with semicolons.
SQL statements are NOT case sensitive. (select is the same thing as SELECT)

Useful SQL statements:

1 - `SELECT`

Used to select data from a database.

SYNTAX

SELECT _column1_, _column2, ..._  
FROM _table_name_;

EXAMPLE

SELECT CustomerName, City FROM Customers;

NOTE: To select all columns, use SELECT *

SELECT * FROM Customers;

2- `SELECT DISTINCT`

Used to select data from a database and return ONLY unique/distinct values.

SYNTAX

SELECT DISTINCT _column1_, _column2, ..._  
FROM _table_name_;

EXAMPLE

SELECT DISTINCT Country FROM Customers;

This statement lists all the different countries our customers are from.
It does not list a country twice.

3- `WHERE`

Used to filter records based on a specified condition.

SYNTAX

SELECT _column1_, _column2_, ...
FROM _table_name_
WHERE _condition_;

EXAMPLE

SELECT * FROM Customers
WHERE Country = 'Germany';

The statement above selects all customers whose country is Germany.

NOTE: WHERE can be used with different operators, such as =, >, <, >=, <=, <> and !=.
For instance:

SELECT * FROM Customers
WHERE Age >= 18;

This statement selects all customers whose age is 18 or higher.

4- `ORDER BY`

Used to sort the returned records by one or more columns.

SYNTAX


SELECT _column1_, _column2_, ...
FROM _table_name_
ORDER BY _column_ _ASC|DESC_;

EXAMPLE

SELECT * FROM Customers
ORDER BY Country;

This statement sorts the customers alphabetically by their country.
By default, ORDER BY sorts values in ascending order.

NOTE: To sort in descending order, use DESC.

SELECT * FROM Customers
ORDER BY Country DESC;

This statement sorts the customers in descending alphabetical order by their country.

NOTE: ASC can be used to explicitly specify ascending order (Although it's not necessarily needed).

SELECT * FROM Customers
ORDER BY Country ASC;

5- `INSERT INTO`

Used to insert new records into a table.

SYNTAX

INSERT INTO _table_name_ (_column1_, _column2_, ...)
VALUES (_value1_, _value2_, ..._);

EXAMPLE

INSERT INTO Customers (CustomerName, Country, Age)
VALUES ('John Doe', 'Germany', 28);

This statement adds a new customer to the Customers table.
The values are inserted into the columns in the same order they are listed.

NOTE: You can insert multiple records with one statement.

INSERT INTO Customers (CustomerName, Country, Age)
VALUES
('John Doe', 'Germany', 28),
('Jane Doe', 'France', 31);

6- `DELETE`

Used to delete records from a table.

SYNTAX

DELETE FROM _table_name_
WHERE _condition_;

EXAMPLE

DELETE FROM Customers
WHERE CustomerName = 'John Doe';

This statement deletes all customers whose name is John Doe.

NOTE: Be careful when using DELETE without a WHERE condition.

DELETE FROM Customers;

This statement deletes ALL records from the Customers table.
It does not delete the table itself.

7- `UPDATE`

Used to modify existing records in a table.

SYNTAX

UPDATE _table_name_
SET _column1_ = _value1_, _column2_ = _value2_, ...
WHERE _condition_;

EXAMPLE

UPDATE Customers
SET Country = 'Germany'
WHERE CustomerName = 'John Doe';

This statement changes the country of all customers whose name is John Doe to Germany.

NOTE: Be careful when using UPDATE without a WHERE condition.

UPDATE Customers
SET Country = 'Germany';

This statement changes the country to Germany for ALL customers.

SQL JOINs

A JOIN is used to combine rows from two or more tables based on a related column between them.

For instance, imagine we have these two tables:

Customers

CustomerID	CustomerName
1	John
2	Jane

Orders

OrderID	CustomerID	Product
101	1	Laptop
102	1	Mouse
103	2	Keyboard

The CustomerID column connects the two tables.

Common joins and how they're used:

1- `INNER JOIN`

Returns only records that have a match in BOTH tables.

SYNTAX

SELECT _column1_, _column2_, ...
FROM _table1_
INNER JOIN _table2_
ON _table1_._column_ = _table2_._column_;

EXAMPLE

SELECT Customers.CustomerName, Orders.Product
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

This statement returns customers who have an order.
Customers without an order are not included. ### 2- LEFT JOIN Returns ALL records from the left table, and the matching records from the right table. If there is no match, the columns from the right table contain NULL.

EXAMPLE

SELECT Customers.CustomerName, Orders.Product
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

This statement returns ALL customers.
If a customer has no orders, their Product will be NULL.

3- `RIGHT JOIN`

Returns ALL records from the right table, and the matching records from the left table.
If there is no match, the columns from the left table contain NULL.

EXAMPLE

SELECT Customers.CustomerName, Orders.Product
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

This statement returns ALL orders.
If an order has no matching customer, the customer columns will be NULL.

NOTE: RIGHT JOIN can usually be rewritten as a LEFT JOIN by switching the order of the tables.

4- `FULL OUTER JOIN`

Returns ALL records from both tables.

EXAMPLE

SELECT Customers.CustomerName, Orders.Product
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

This statement returns every customer and every order.
Matching records are combined.
Records without a match are still included (if a record has no match in one of the tables, the columns from that table will be NULL).

NOTE: Not all SQL databases support FULL OUTER JOIN.

5- `CROSS JOIN`

Returns every possible combination of rows from the two tables.

EXAMPLE

SELECT Customers.CustomerName, Products.ProductName
FROM Customers
CROSS JOIN Products;

If there are 3 customers and 4 products, this statement returns 12 rows.
Every customer is paired with every product.
CROSS JOIN does not require an ON condition.

SQL Relations

Relations are used to connect records in different tables.

For instance, instead of storing a customer's orders directly inside the Customers table, we can store customers and orders in separate tables and connect them using IDs.

Different kinds of relationships in SQL:

1- One-to-One relationships

In a one-to-one relationship, each record in one table is related to at most one record in another table.

For instance:

Users
-----
id
username

UserProfiles
------------
id
user_id
bio

Each user can have one profile, and each profile belongs to one user.

EXAMPLE

CREATE TABLE UserProfiles (
    id INT PRIMARY KEY,
    user_id INT UNIQUE,
    bio VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES Users(id)
);

FOREIGN KEY creates a relationship between the two tables.
UNIQUE prevents multiple profiles from referencing the same user.

2- One-to-Many relationships

In a one-to-many relationship, one record can be related to many records in another table.

For instance:

Customers
---------
id
name

Orders
------
id
customer_id
product

One customer can have many orders, but each order belongs to one customer.

EXAMPLE

CREATE TABLE Orders (
    id INT PRIMARY KEY,
    customer_id INT,
    product VARCHAR(100),
    FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

customer_id is a foreign key.
It references Customers.id.
Multiple orders can have the same customer_id.

To get a customer's orders, use a JOIN.

SELECT Customers.name, Orders.product
FROM Customers
INNER JOIN Orders
ON Customers.id = Orders.customer_id;

3- Many-to-Many relationships

In a many-to-many relationship, many records in one table can be related to many records in another table.

For instance, a student can take many courses, and a course can have many students.

We cannot directly store this relationship in either table.
Instead, we can create a third table, commonly referred to as a junction table/join table.

Students
--------
id
name

Courses
-------
id
name

StudentCourses
--------------
student_id
course_id

EXAMPLE

CREATE TABLE StudentCourses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(id),
    FOREIGN KEY (course_id) REFERENCES Courses(id)
);

StudentCourses connects students and courses.
Each row represents one relationship between a student and a course.
The combination of student_id and course_id is unique because it is the primary key.

To find all courses taken by a student:

SELECT Students.name, Courses.name
FROM Students
INNER JOIN StudentCourses
ON Students.id = StudentCourses.student_id
INNER JOIN Courses
ON StudentCourses.course_id = Courses.id;

Extra notes:

What is a foreign key

A foreign key is a column that references a key in another table.

For instance:

CREATE TABLE Orders (
    id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

customer_id is a foreign key, it references Customers.id. This creates a relationship between the two tables.

Using `ON DELETE`

When a record that other records reference is deleted, you can define what should happen to the related records.

For instance:

CREATE TABLE Orders (
    id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES Customers(id)
        ON DELETE CASCADE
);

ON DELETE CASCADE means that when a customer is deleted, all orders belonging to that customer are also deleted.

Other alternatives to CASCADE include:

ON DELETE SET NULL: sets the foreign key to NULL.
ON DELETE RESTRICT: prevents the referenced record from being deleted if related records exist.
ON DELETE NO ACTION: generally prevents the deletion when the foreign key constraint would be violated.

Using `ON UPDATE`

You can define what happens when a referenced key is updated.

EXAMPLE

CREATE TABLE Orders (
    id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES Customers(id)
        ON UPDATE CASCADE
);

ON UPDATE CASCADE automatically updates the foreign key when the referenced key changes.

NOTE: In practice, primary keys typically remain unchanged, so ON UPDATE CASCADE is less commonly needed than ON DELETE CASCADE.

Tip: Save this article in Obsidian.md, so you can quickly go through the terms whenever needed in the future.
Hope this was useful. See you in the next one!

DEV Community

SQL Cheat sheet

How does SQL work?

1 - `SELECT`

2- `SELECT DISTINCT`

3- `WHERE`

4- `ORDER BY`

5- `INSERT INTO`

6- `DELETE`

7- `UPDATE`

SQL JOINs

1- `INNER JOIN`

3- `RIGHT JOIN`

4- `FULL OUTER JOIN`

5- `CROSS JOIN`

SQL Relations

1- One-to-One relationships

2- One-to-Many relationships

3- Many-to-Many relationships

What is a foreign key

Using `ON DELETE`

Using `ON UPDATE`

Top comments (0)

How does SQL work?

1 - SELECT

2- SELECT DISTINCT

3- WHERE

4- ORDER BY

5- INSERT INTO

6- DELETE

7- UPDATE

SQL JOINs

1- INNER JOIN

3- RIGHT JOIN

4- FULL OUTER JOIN

5- CROSS JOIN

SQL Relations

1- One-to-One relationships

2- One-to-Many relationships

3- Many-to-Many relationships

What is a foreign key

Using ON DELETE

Using ON UPDATE

1 - `SELECT`

2- `SELECT DISTINCT`

3- `WHERE`

4- `ORDER BY`

5- `INSERT INTO`

6- `DELETE`

7- `UPDATE`

1- `INNER JOIN`

3- `RIGHT JOIN`

4- `FULL OUTER JOIN`

5- `CROSS JOIN`

Using `ON DELETE`

Using `ON UPDATE`