SQL Cheat Sheet

Karan

Published in

Learning SQL

19 min readJun 19, 2024

Learn SQL in one shot

Photo by Caspar Camille Rubin on Unsplash

Welcome to the comprehensive SQL Cheat Sheet! In the world of databases and data manipulation, SQL (Structured Query Language) stands as a cornerstone. Whether you’re a seasoned database administrator, a software engineer, or just getting started with SQL, having a quick reference guide to essential SQL commands and concepts is invaluable.

This cheat sheet is designed to be your go-to resource, providing a concise yet comprehensive overview of SQL syntax, queries, functions, and best practices. Whether you need to retrieve specific data from a database, modify table structures, or optimize your queries for performance, you’ll find the key information you need right here.

What is SQL And Why It Is Important

SQL, or Structured Query Language, is a specialized programming language designed for managing and manipulating relational databases. It provides a standardized way to interact with databases, enabling users to store, retrieve, update, and delete data efficiently and effectively.

SQL is important for several reasons:

Universal Language: SQL is a standardized language adopted by most database management systems (DBMS), including popular ones like MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. Learning SQL allows you to work with a variety of databases without having to learn different query languages.
Data Manipulation: SQL provides powerful commands for retrieving, updating, and deleting data from databases. With SQL, you can write queries to filter specific data, perform calculations, aggregate results, and modify records as needed.
Data Definition: SQL allows you to define the structure of your database, including creating tables, defining relationships between tables, and establishing constraints to ensure data integrity. This capability is crucial for designing well-organized and efficient databases.
Data Control: SQL enables you to control access to your database by managing user permissions and privileges. You can grant or revoke permissions to users and define roles to control who can perform certain actions on the database.
Data Analysis and Reporting: SQL is widely used in data analysis and reporting. Analysts and data scientists use SQL to extract insights from large datasets, perform complex calculations, and generate reports that drive business decisions.
Integration with Applications: SQL integrates seamlessly with programming languages like Python, Java, and PHP, allowing developers to build applications that interact with databases. By embedding SQL queries within application code, developers can create dynamic and data-driven applications.
Scalability and Performance: SQL databases are highly scalable and optimized for performance. With SQL, you can write efficient queries that retrieve data quickly, even from large datasets. Additionally, SQL databases support indexing and optimization techniques to further enhance performance.
Data Security: SQL provides robust security features to protect sensitive data. You can encrypt data, implement access controls, and audit database activities to ensure compliance with security standards and regulations.

Basic SQL Queries

SELECT statement:

The SELECT statement is used to retrieve data from a database.
It allows you to specify which columns you want to retrieve and from which tables.

-- Retrieve all columns from the "employees" table
SELECT * FROM employees;

-- Retrieve specific columns (name and salary) from the "employees" table
SELECT name, salary FROM employees;

WHERE clause:

The WHERE clause is used to filter records based on a specified condition.
It allows you to select only the rows that meet the specified criteria.

-- Retrieve employees whose salary is greater than 50000
SELECT * FROM employees WHERE salary > 50000;

-- Retrieve employees who work in the Sales department
SELECT * FROM employees WHERE department = 'Sales';

ORDER BY clause:

The ORDER BY clause is used to sort the result set in ascending or descending order.
You can specify one or more columns to sort by.

-- Retrieve employees sorted by salary in ascending order
SELECT * FROM employees ORDER BY salary ASC;

-- Retrieve employees sorted by name in descending order
SELECT * FROM employees ORDER BY name DESC;

DISTINCT keyword:

The DISTINCT keyword is used to return unique values in the result set.
It removes duplicate rows from the query results.

-- Retrieve unique departments
SELECT DISTINCT department FROM employees;

-- Retrieve unique combinations of department and location
SELECT DISTINCT department, location FROM employees;

LIMIT clause:

The LIMIT clause is used to restrict the number of rows returned by a query.
It is often used with the ORDER BY clause to retrieve a subset of rows.

-- Retrieve the first 5 employees
SELECT * FROM employees LIMIT 5;

-- Retrieve the top 10 highest-paid employees
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

LIKE operator:

The LIKE operator is used to search for a specified pattern in a column.
It can be used with wildcard characters (% and _) to perform pattern matching.

-- Retrieve employees whose name starts with 'J'
SELECT * FROM employees WHERE name LIKE 'J%';

-- Retrieve employees whose name contains 'son'
SELECT * FROM employees WHERE name LIKE '%son%';

Aggregate functions (COUNT, SUM, AVG, MAX, MIN):

Aggregate functions perform calculations on a set of values and return a single value.
They are often used with the GROUP BY clause to group the results.

-- Count the number of employees
SELECT COUNT(*) FROM employees;

-- Calculate the total salary of all employees
SELECT SUM(salary) FROM employees;

-- Calculate the average salary of employees in each department
SELECT department, AVG(salary) FROM employees GROUP BY department;

-- Find the highest salary
SELECT MAX(salary) FROM employees;

-- Find the lowest salary
SELECT MIN(salary) FROM employees;

GROUP BY clause:

The GROUP BY clause is used to group rows that have the same values into summary rows.
It is often used with aggregate functions to perform calculations on each group.

-- Calculate the total salary for each department
SELECT department, SUM(salary) FROM employees GROUP BY department;

-- Count the number of employees in each department
SELECT department, COUNT(*) FROM employees GROUP BY department;

HAVING clause:

The HAVING clause is used to filter groups based on a specified condition.
It is similar to the WHERE clause but is used with aggregate functions and the GROUP BY clause.

-- Retrieve departments with more than 5 employees
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;

-- Retrieve departments with an average salary greater than 50000
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;

BETWEEN operator:

The BETWEEN operator is used to retrieve values within a specified range.
It is inclusive, meaning it includes both the start and end values.

-- Retrieve employees with salaries between 40000 and 60000
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

-- Retrieve orders with order dates between '2022-01-01' and '2022-12-31'
SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';

IN operator:

The IN operator is used to specify multiple values in a WHERE clause.
It checks whether a value matches any value in a list.

-- Retrieve employees in the Sales and Marketing departments
SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');

-- Retrieve orders with the status 'Sh'
SELECT * FROM employees WHERE order_status = 'Sh'

INNER JOIN:

The INNER JOIN returns rows from both tables that have matching values in the specified column(s).
It selects records that have matching values in both tables.

-- Retrieve employees along with their departments
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

LEFT JOIN (or LEFT OUTER JOIN):

The LEFT JOIN returns all rows from the left table and the matched rows from the right table.
If there is no match, NULL values are returned for columns from the right table.

-- Retrieve all employees along with their departments (including employees without departments)
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

RIGHT JOIN (or RIGHT OUTER JOIN):

The RIGHT JOIN returns all rows from the right table and the matched rows from the left table.
If there is no match, NULL values are returned for columns from the left table.

-- Retrieve all departments along with their employees (including departments without employees)
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

FULL JOIN (or FULL OUTER JOIN):

The FULL JOIN returns all rows when there is a match in either the left or right table.
It returns NULL values for columns where there is no match.

-- Retrieve all employees and departments, showing unmatched rows from both tables
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;

CROSS JOIN:

The CROSS JOIN returns the Cartesian product of rows from the two tables.
It joins every row from the first table with every row from the second table

-- Retrieve all possible combinations of employees and departments
SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

Self-join:

A self-join is a regular join, but the table is joined with itself.
It is often used to compare rows within the same table.

-- Retrieve employees and their managers (assuming a manager_id column in the employees table)
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id;

Subqueries

Single-row subqueries:

A single-row subquery returns only one row of results.
It can be used in a WHERE clause to filter rows based on a single value.

-- Retrieve employees whose salary is greater than the average salary
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Multiple-row subqueries:

A multiple-row subquery returns multiple rows of results.
It can be used with comparison operators such as IN, ANY, and ALL to compare a single value with multiple values returned by the subquery.

-- Retrieve employees who work in departments with more than 10 employees
SELECT name, department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 10);

Correlated subqueries:

A correlated subquery is evaluated once for each row processed by the outer query.
It can reference columns from the outer query, allowing it to be dependent on the outer query.

-- Retrieve employees whose salary is greater than the average salary in their department
SELECT name, department_id, salary
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

Constraints

PRIMARY KEY:

The PRIMARY KEY constraint uniquely identifies each record in a table.
It must contain unique values and cannot contain NULL values.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

FOREIGN KEY:

The FOREIGN KEY constraint establishes a relationship between two tables.
It ensures referential integrity by enforcing a link between a column in one table and a column in another table.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

UNIQUE constraint:

The UNIQUE constraint ensures that all values in a column are unique.
It allows NULL values, but only one NULL value is allowed per column.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50) UNIQUE,
    price DECIMAL(10, 2)
);

NOT NULL constraint:

The NOT NULL constraint ensures that a column cannot contain NULL values.
Each value in the column must be specified when a new record is inserted.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT NOT NULL
);

DEFAULT constraint:

The DEFAULT constraint provides a default value for a column when no value is specified.
It automatically assigns the default value to the column if a new record is inserted without specifying a value.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) DEFAULT 'example@example.com'
);

CHECK constraint:

The CHECK constraint specifies a condition that must be met for each row in a table.
It allows you to enforce data integrity by restricting the values that can be inserted or updated in a column.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    grade CHAR(1),
    CHECK (age >= 18),
    CHECK (grade IN ('A', 'B', 'C', 'D', 'F'))
);

DDL (Data Definition Language):

CREATE TABLE:

The CREATE TABLE statement is used to create a new table in a database.
It specifies the table name and defines the columns and their data types.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    grade CHAR(1)
);

ALTER TABLE:

The ALTER TABLE statement is used to modify an existing table’s structure.
It allows you to add, modify, or drop columns and constraints.

-- Add a new column to the students table
ALTER TABLE students
ADD COLUMN email VARCHAR(100);

-- Modify the data type of the age column
ALTER TABLE students
ALTER COLUMN age INT NOT NULL;

-- Drop the grade column from the students table
ALTER TABLE students
DROP COLUMN grade;

DROP TABLE:

The DROP TABLE statement is used to delete an existing table from the database.
It permanently removes all data and structure associated with the table.

DROP TABLE students;

CREATE INDEX:

The CREATE INDEX statement is used to create an index on one or more columns of a table.
It improves query performance by allowing faster data retrieval based on the indexed columns.

-- Create an index on the student_id column of the students table
CREATE INDEX idx_student_id ON students(student_id);

-- Create a composite index on the name and age columns of the students table
CREATE INDEX idx_name_age ON students(name, age);

DROP INDEX:

The DROP INDEX statement is used to remove an existing index from the database.
It deletes the index and frees up the space occupied by it.

-- Drop the index named idx_student_id
DROP INDEX idx_student_id;

-- Drop the composite index named idx_name_age
DROP INDEX idx_name_age;

DML (Data Manipulation Language):

INSERT INTO:

The INSERT INTO statement is used to add new records (rows) to a table.
It specifies the table name and the values to be inserted into each column.

-- Insert a new student into the students table
INSERT INTO students (student_id, name, age, grade)
VALUES (1, 'John Doe', 20, 'A');

-- Insert multiple students into the students table
INSERT INTO students (student_id, name, age, grade)
VALUES (2, 'Jane Smith', 22, 'B'),
       (3, 'Michael Johnson', 21, 'C');

UPDATE:

The UPDATE statement is used to modify existing records in a table.
It specifies which table to update, which columns to change, and the new values.

-- Update the age of the student with student_id = 1
UPDATE students
SET age = 21
WHERE student_id = 1;

-- Update the grade of all students to 'A'
UPDATE students
SET grade = 'A';

DELETE FROM:

The DELETE FROM statement is used to remove existing records from a table.
It specifies which table to delete from and can include a WHERE clause to specify conditions for deletion.

-- Delete the student with student_id = 2
DELETE FROM students
WHERE student_id = 2;

-- Delete all students older than 25
DELETE FROM students
WHERE age > 25;

DDL (Data Control Language):

GRANT:

The GRANT statement is used to give specific privileges to a user or role.
It allows users to perform certain actions on database objects like tables, views, and procedures.

-- Grant SELECT privilege on the students table to the user 'user1'
GRANT SELECT ON students TO user1;

-- Grant INSERT, UPDATE, and DELETE privileges on the orders table to the role 'sales'
GRANT INSERT, UPDATE, DELETE ON orders TO sales;

REVOKE:

The REVOKE statement is used to revoke previously granted privileges from a user or role.
It removes the specified privileges, restricting the user’s access to the database objects.

-- Revoke SELECT privilege on the students table from the user 'user1'
REVOKE SELECT ON students FROM user1;

-- Revoke INSERT, UPDATE, and DELETE privileges on the orders table from the role 'sales'
REVOKE INSERT, UPDATE, DELETE ON orders FROM sales;

Transactions

COMMIT:

The COMMIT statement is used to permanently save the changes made in the current transaction to the database.
Once committed, the changes become visible to other transactions.

-- Start a transaction
BEGIN TRANSACTION;

-- Update the balance of a bank account
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456;

-- Commit the transaction
COMMIT;

ROLLBACK:

The ROLLBACK statement is used to undo the changes made in the current transaction.
It rolls back the transaction to its starting point and discards all changes.

-- Start a transaction
BEGIN TRANSACTION;

-- Update the balance of a bank account
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456;

-- Rollback the transaction
ROLLBACK;

SAVEPOINT:

The SAVEPOINT statement is used to set a named point within a transaction.
It allows you to create intermediate points to which you can later rollback.

-- Start a transaction
BEGIN TRANSACTION;

-- Update the balance of a bank account
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;

-- Set a savepoint
SAVEPOINT before_transfer;

-- Update the balance of another bank account
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456;

-- Rollback to the savepoint
ROLLBACK TO before_transfer;

-- Update the balance of another bank account
UPDATE accounts SET balance = balance + 100 WHERE account_id = 789;

-- Commit the transaction
COMMIT;

Views

CREATE VIEW:

The CREATE VIEW statement is used to create a virtual table based on the result set of a SELECT query.
It allows you to save complex queries as views, which can be queried like regular tables.

-- Create a view to display the names and ages of students with a grade of 'A'
CREATE VIEW view_students_grade_A AS
SELECT name, age
FROM students
WHERE grade = 'A';

ALTER VIEW:

The ALTER VIEW statement is used to modify the definition of an existing view.
It allows you to add or remove columns, change the query, or rename the view.

-- Add a new column to the view view_students_grade_A
ALTER VIEW view_students_grade_A
ADD COLUMN email VARCHAR(100);

-- Modify the query of the view view_students_grade_A
ALTER VIEW view_students_grade_A
AS
SELECT name, age, email
FROM students
WHERE grade = 'A';

DROP VIEW:

The DROP VIEW statement is used to remove an existing view from the database.
It permanently deletes the view definition and its associated metadata.

-- Drop the view view_students_grade_A
DROP VIEW view_students_grade_A;

Functions

String functions:

String functions operate on string values and manipulate them in various ways.
Common string functions include CONCAT, SUBSTRING, UPPER, and LOWER.

-- CONCAT: Concatenate two or more strings
SELECT CONCAT('Hello ', 'World') AS concatenated_string;

-- SUBSTRING: Extract a substring from a string
SELECT SUBSTRING('Hello World', 7) AS substring_from_7th_character;

-- UPPER: Convert a string to uppercase
SELECT UPPER('hello') AS uppercase_string;

-- LOWER: Convert a string to lowercase
SELECT LOWER('WORLD') AS lowercase_string;

Numeric functions:

Numeric functions operate on numeric values and perform calculations.
Common numeric functions include ROUND, ABS, CEIL, and FLOOR.

-- ROUND: Round a number to a specified number of decimal places
SELECT ROUND(3.14159, 2) AS rounded_number;

-- ABS: Get the absolute value of a number
SELECT ABS(-10) AS absolute_value;

-- CEIL: Round a number up to the nearest integer
SELECT CEIL(4.3) AS ceil_value;

-- FLOOR: Round a number down to the nearest integer
SELECT FLOOR(4.9) AS floor_value;

Date functions:

Date functions operate on date and time values and perform operations like extraction or manipulation.
Common date functions include NOW, DATE, YEAR, MONTH, and DAY.

-- NOW: Get the current date and time
SELECT NOW() AS current_datetime;

-- DATE: Extract the date part from a datetime value
SELECT DATE('2022-05-16 12:34:56') AS extracted_date;

-- YEAR: Extract the year from a datetime value
SELECT YEAR('2022-05-16') AS extracted_year;

-- MONTH: Extract the month from a datetime value
SELECT MONTH('2022-05-16') AS extracted_month;

-- DAY: Extract the day from a datetime value
SELECT DAY('2022-05-16') AS extracted_day;

Advanced SQL:

Common Table Expressions (CTEs):

Common Table Expressions (CTEs) are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
They allow you to break down complex queries into simpler, more manageable parts.

-- Define a CTE to get the list of employees with their departments
WITH EmployeeDepartment AS (
    SELECT e.name AS employee_name, d.department_name
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.department_id
)
-- Query the CTE
SELECT * FROM EmployeeDepartment;

Window functions:

Window functions perform calculations across a set of rows related to the current row.
They allow you to perform aggregate functions without grouping the rows into a single output row.

-- Calculate the average salary per department and display it alongside each employee's salary
SELECT name, salary, AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM employees;

Pivoting and Unpivoting:

Pivoting involves rotating data from a row-level to a column-level format, while unpivoting does the reverse.
They are useful for transforming data to meet reporting or analysis requirements.

-- Pivot the table to show total sales amount for each product category
SELECT *
FROM (
    SELECT category, amount
    FROM sales
) AS source_table
PIVOT (
    SUM(amount)
    FOR category IN ('Electronics', 'Clothing', 'Books')
) AS pivot_table;

-- Unpivot the table to show each product category and its total sales amount
SELECT category, amount
FROM (
    SELECT 'Electronics', electronics_sales AS amount
    FROM sales_summary
    UNION ALL
    SELECT 'Clothing', clothing_sales
    FROM sales_summary
    UNION ALL
    SELECT 'Books', books_sales
    FROM sales_summary
) AS unpivoted_table;

Hierarchical queries:

Hierarchical queries retrieve data that is organized in a hierarchical structure, such as organizational charts or bill of materials.
They use recursive queries or special clauses like CONNECT BY in Oracle.

-- Retrieve all employees and their managers in a hierarchical structure
SELECT employee_id, name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Recursive queries:

Recursive queries allow a query to refer to its own output, enabling you to perform operations like walking a graph or generating hierarchical data.

-- Generate a sequence of numbers from 1 to 10 recursively
WITH RECURSIVE NumberSequence AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM NumberSequence WHERE num < 10
)
SELECT * FROM NumberSequence;

Regular Expressions:

Regular Expressions (regex) are patterns used to match character combinations in strings.
They are useful for tasks such as searching, validation, and data extraction.

-- Find all email addresses in a text column
SELECT email
FROM contacts
WHERE email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

Indexes and Performance Optimization:

Index types (e.g., B-tree, Hash, Bitmap):

Indexes are data structures used to improve the speed of data retrieval operations on database tables.
Different types of indexes have different internal structures and are suitable for different types of queries.
Common index types include B-tree, Hash, and Bitmap indexes.

-- Create a B-tree index on the "email" column of the "users" table
CREATE INDEX idx_email ON users (email);

-- Create a Hash index on the "city" column of the "addresses" table
CREATE INDEX idx_city ON addresses USING HASH (city);

-- Create a Bitmap index on the "status" column of the "orders" table
CREATE INDEX idx_status ON orders USING BITMAP (status);

-- Drop the index named idx_email from the "users" table
DROP INDEX idx_email;

-- Drop the index named idx_city from the "addresses" table
DROP INDEX idx_city;

-- Drop the index named idx_status from the "orders" table
DROP INDEX idx_status;

Analyzing and optimizing queries:

Analyzing and optimizing queries is essential for improving database performance.
Techniques include using proper indexing, optimizing query structure, and rewriting queries.

-- Analyze query performance and identify slow queries
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

-- Optimize a query by rewriting it to use a join instead of a subquery
EXPLAIN SELECT u.name, a.city
FROM users u
JOIN addresses a ON u.address_id = a.address_id
WHERE u.age > 30;

Using EXPLAIN to understand query execution plans:

The EXPLAIN command is used to analyze and understand the execution plan of a query.
It shows how the database engine executes the query, including which indexes are used, the order of operations, and estimated costs.

-- Use EXPLAIN to understand the execution plan of a query
EXPLAIN SELECT * FROM users WHERE age > 30;

Backup and Recovery:

Backup strategies (full, incremental, differential):

Backup strategies determine how and when backups of a database are taken to ensure data recovery and continuity.

Example: Suppose you have a database named “SalesDB” with daily changes:

Full Backup: Taken on Sunday to back up the entire database.
Incremental Backup: Taken on Monday to back up data changed since Sunday’s backup.
Incremental Backup: Taken on Tuesday to back up data changed since Monday’s backup.
Differential Backup: Taken on Saturday to back up data changed since Sunday’s backup.

Restore procedures:

Restore procedures define how backups are used to restore a database to a previous state.
The procedure depends on the type of backup taken (full, incremental, or differential).

Example: Suppose you want to restore the “SalesDB” database:

Full Restore: Restore the full backup taken on Sunday.
Incremental Restore: Restore the full backup taken on Sunday, then apply incremental backups taken since then (Monday, Tuesday, etc.).
Differential Restore: Restore the full backup taken on Sunday, then apply the differential backup taken on Saturday.

SQL Server Example:

-- Full Restore
RESTORE DATABASE SalesDB FROM DISK = 'C:\Backup\SalesDB_Full.bak' WITH REPLACE;

-- Incremental Restore
RESTORE DATABASE SalesDB FROM DISK = 'C:\Backup\SalesDB_Full.bak' WITH REPLACE;
RESTORE DATABASE SalesDB FROM DISK = 'C:\Backup\SalesDB_Incremental_Monday.bak' WITH NORECOVERY;
RESTORE DATABASE SalesDB FROM DISK = 'C:\Backup\SalesDB_Incremental_Tuesday.bak' WITH NORECOVERY;
-- Repeat for each incremental backup, then:
RESTORE DATABASE SalesDB WITH RECOVERY;

-- Differential Restore
RESTORE DATABASE SalesDB FROM DISK = 'C:\Backup\SalesDB_Full.bak' WITH REPLACE;
RESTORE DATABASE SalesDB FROM DISK = 'C:\Backup\SalesDB_Differential_Saturday.bak' WITH RECOVERY;

Security

User management:

User management involves creating, modifying, and deleting database users.
Users are accounts that can connect to the database and perform actions.
User management also includes setting authentication methods and user privileges.

-- Create a new user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

-- Modify user privileges
ALTER USER 'existinguser'@'localhost' IDENTIFIED BY 'newpassword';

-- Delete a user
DROP USER 'user_to_delete'@'localhost';

Role-based access control:

Role-based access control (RBAC) is a method of restricting access to database resources based on roles.
Roles are collections of privileges that can be granted to users or other roles.
Users are assigned roles, and their access privileges are determined by the roles they have.

-- Create a new role
CREATE ROLE 'admin';

-- Grant privileges to the role
GRANT SELECT, INSERT, UPDATE, DELETE ON database.table TO 'admin';

-- Assign the role to a user
GRANT 'admin' TO 'user';

-- Revoke a role from a user
REVOKE 'admin' FROM 'user';

Granting and revoking permissions:

Granting permissions involves giving users or roles access to database objects or operations.
Revoking permissions removes previously granted access.

-- Grant SELECT permission on a table to a user
GRANT SELECT ON database.table TO 'user';

-- Grant INSERT, UPDATE, DELETE permissions on a table to a role
GRANT INSERT, UPDATE, DELETE ON database.table TO 'role';

-- Revoke SELECT permission on a table from a user
REVOKE SELECT ON database.table FROM 'user';

-- Revoke INSERT, UPDATE, DELETE permissions on a table from a role
REVOKE INSERT, UPDATE, DELETE ON database.table FROM 'role';

Miscellaneous:

Case statements:

Case statements allow you to perform conditional logic within SQL queries.
They evaluate conditions and return a result based on the first matching condition.

SELECT 
    CASE 
        WHEN age < 18 THEN 'Child'
        WHEN age BETWEEN 18 AND 65 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM users;

UNION and UNION ALL:

UNION and UNION ALL are used to combine the results of two or more SELECT statements.
UNION removes duplicate rows, while UNION ALL retains all rows.

-- Using UNION to combine results without duplicates
SELECT id, name FROM table1
UNION
SELECT id, name FROM table2;

-- Using UNION ALL to combine results with duplicates
SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2;

INTERSECT and EXCEPT:

INTERSECT returns the common rows between two SELECT statements.
EXCEPT returns the rows that are in the first SELECT statement but not in the second.

-- Using INTERSECT to find common rows
SELECT id, name FROM table1
INTERSECT
SELECT id, name FROM table2;

-- Using EXCEPT to find rows in the first SELECT but not in the second
SELECT id, name FROM table1
EXCEPT
SELECT id, name FROM table2;

Handling NULL values:

NULL represents missing or unknown data.
Functions like COALESCE or CASE can be used to handle NULL values.

-- Using COALESCE to replace NULL values with a default
SELECT COALESCE(column_name, 'default_value') FROM table;

-- Using CASE to handle NULL values conditionally
SELECT 
    CASE 
        WHEN column_name IS NULL THEN 'No value'
        ELSE column_name
    END AS column_alias
FROM table;

Why didn’t I Include Normalization and denormalization?

Normalization and denormalization concepts are typically not included in SQL cheat sheets for the following reasons:

Conceptual Nature:

Normalization and denormalization are theoretical concepts related to database design rather than specific SQL syntax or commands. SQL cheat sheets generally focus on practical, executable SQL commands and functions that users can directly apply to their queries.

Complexity and Detail:

Normalization involves a series of rules and design principles (e.g., 1NF, 2NF, 3NF, BCNF) that require detailed explanations and examples to understand fully. This level of detail is beyond the scope of a cheat sheet, which aims to provide quick, concise references.

Practical Application:

Implementing normalization and denormalization requires a deep understanding of the database schema and the specific requirements of the application. This process involves strategic planning and design considerations that can’t be condensed into a cheat sheet format.

Focus on Queries:

SQL cheat sheets are typically focused on writing queries, manipulating data, and managing database objects. Normalization and denormalization are more about how to structure and design the database tables, which is more relevant to database modeling rather than query writing.

Conclusion

In summary, SQL is a fundamental skill for anyone working with data, from database administrators and software developers to data analysts and business professionals. By mastering SQL, you gain the ability to manage and manipulate data effectively, making you a valuable asset in the world of technology and business.