sql-cheat-sheet-snippet

SQL Cheat Sheet | 2024 Edition | PDF

Tim S

Tim S

Tim currently works at FAANG as a data analyst. He's been in the industry for 5+ years and writes for Big Tech Interviews on the weekends. His favorite language is SQL and calls Denver, CO home.

This SQL cheat sheet is designed to help you prepare for SQL interviews by providing quick access to essential SQL commands, syntax, and practical tips. By the end of this guide, you should have a solid foundation to tackle all SQL interview questions confidently.

Want to download a SQL Interview Cheat Sheet PDF? You can do so for free here!

Proficiency in SQL is a fundamental requirement for most, if not all, data professionals. SQL, or Structured Query Language, is essential for managing and analyzing data stored in relational databases, a common scenario in various industries. Mastering SQL is crucial whether you’re aiming for a role as a business analyst, data analyst, data engineer, or data scientist.

SQL is in high demand across industries due to its robust capability to handle vast datasets, its role as the industry standard for relational database management, and its ease of use compared to other programming languages.

Companies rely on SQL for data retrieval, manipulation, and management, making it a critical skill for data professionals. Furthermore, SQL’s integration with different tools and programming languages enhances its versatility and applicability in real-world scenarios.

In this guide, we’ll cover:

This cheat sheet is structured to provide quick, easy-to-understand examples and explanations of common SQL commands and techniques. Each section focuses on different aspects of SQL, from basic commands to advanced functions, ensuring comprehensive coverage of the topics you’ll need to know for your interview. Keep this guide handy as a reference while you study and practice SQL, and you’ll be well-prepared to demonstrate your skills effectively.

Use this SQL cheat sheet as a reference to brush up on your SQL knowledge, practice common queries, and ensure you’re ready to impress at your next SQL interview. Whether you need an SQL commands cheat sheet, an SQL joins cheat sheet or an SQL syntax cheat sheet, this guide has you covered.

The following tables are used in the examples throughout this SQL guide: 

Table: Orders
+----------+---------+-----------+-------------+--------+
| order_id | sale_id | client_id | city        | amount |
+----------+---------+-----------+-------------+--------+
| 1        | 1       | 101       | Los Angeles | 1000.00|
| 2        | 2       | 102       | New York    | 1500.50|
| 3        | 3       | 103       | Chicago     | 2000.00|
| 4        | 4       | 104       | New York    | 2500.75|
| 5        | NULL    | NULL      | Los Angeles | 3000.00|
+----------+---------+-----------+-------------+--------+

Table: Clients
+-----------+--------------+-------------+
| client_id | client_name  | city        |
+-----------+--------------+-------------+
| 101       | John Doe     | New York    |
| 102       | Jane Smith   | New York    |
| 103       | Jim Beam     | Los Angeles |
| 104       | Jill Jackson | Los Angeles |
| 105       | Jack Johnson | Chicago     |
+-----------+--------------+-------------+

Basic SQL Commands

sql-interview-pdf

SQL commands are the core tools for interacting with databases. They allow you to retrieve, manipulate, and manage data effectively.

In this section, we’ll explore essential SQL commands that form the foundation of database interactions, making this your essential SQL commands cheat sheet. These commands allow you to manage the data stored within your tables effectively, making this your go-to SQL language cheat sheet for manipulation commands. 

  1. Data Retrieval

Retrieving data from a database is a fundamental task in SQL—and it’s often the first step in data analysis. The following commands help you extract and work with the data stored in your database.

1.1 SELECT: Basic Syntax and examples

The SELECT statement is used to retrieve data from a database. There are two options: 

  • SELECT ALL (*)
SELECT *
FROM table_name;

For example: 

SELECT *
FROM orders; 

This query fetches all the columns from the orders table as follows:

+----------+---------+-----------+-------------+---------+
| order_id | sale_id | client_id | city        | amount  |
+----------+---------+-----------+-------------+---------+
| 1        | 1       | 101       | Los Angeles | 1000.00 |
| 2        | 2       | 102       | New York    | 1500.50 |
| 3        | 3       | 103       | Chicago     | 2000.00 |
| 4        | 4       | 104       | New York    | 2500.75 |
| 5        | NULL    | NULL      | Los Angeles | 3000.00 |
+----------+---------+-----------+-------------+---------+
  • SELECT Specific Columns

To retrieve specific columns, you can list the column names separated by commas to retrieve particular columns. 

SELECT column_1, column_2, …
FROM table_name;

For example: 

SELECT order_id, sale_id, client_id, city, amount
FROM orders; 


This query retrieves the order ID, sale ID, client ID, city, and amount columns from the orders table:

+----------+---------+-----------+-------------+---------+
| order_id | sale_id | client_id | city        | amount  |
+----------+---------+-----------+-------------+---------+
| 1        | 1       | 101       | Los Angeles | 1000.00 |
| 2        | 2       | 102       | New York    | 1500.50 |
| 3        | 3       | 103       | Chicago     | 2000.00 |
| 4        | 4       | 104       | New York    | 2500.75 |
| 5        | NULL    | NULL      | Los Angeles | 3000.00 |
+----------+---------+-----------+-------------+---------+

1.2 WHERE: Filtering Data

The WHERE clause is used to filter records based on specified conditions. 

SELECT column1, column2, …
FROM table_name
WHERE condition;

For example: 

SELECT order_id, sale_id, client_id, city, amount
FROM orders
WHERE city = ‘New York’;

This query fetches records from the clients table where the city is ‘New York’:

+----------+---------+-----------+-------------+---------+
| order_id | sale_id | client_id | city        | amount  |
+----------+---------+-----------+-------------+---------+
| 2        | 2       | 102       | New York    | 1500.50 |
| 4        | 4       | 104       | New York    | 2500.75 |
+----------+---------+-----------+-------------+---------+

1.3 ORDER BY: Sorting Results 

The ORDER BY clause sorts the result set by one or more columns. 

​​SELECT column1, column2, …
FROM table_name
ORDER BY column1 [ASC|DESC];

For example: 

SELECT order_id, sale_id, client_id, city, amount FROM ordersORDER BY amount ASC;

This query sorts the orders by the amount in ascending order:

+----------+---------+-----------+-------------+---------+
| order_id | sale_id | client_id | city        | amount  |
+----------+---------+-----------+-------------+---------+
| 1        | 1       | 101       | Los Angeles | 1000.00 |
| 2        | 2       | 102       | New York    | 1500.50 |
| 3        | 3       | 103       | Chicago     | 2000.00 |
| 4        | 4       | 104       | New York    | 2500.75 |
| 5        | NULL    | NULL      | Los Angeles | 3000.00 |
+----------+---------+-----------+-------------+---------+

1.4 LIMIT: Limiting Results 

The LIMIT clause specifies the number of records to return. 

For instance: 

SELECT order_id, sale_id, client_id, city, amount
FROM orders
ORDER BY amount ASC
LIMIT 3;

This query retrieves the first three records from the sorted results: 

+----------+---------+-----------+-------------+---------+
| order_id | sale_id | client_id | city        | amount  |
+----------+---------+-----------+-------------+---------+
| 1        | 1       | 101       | Los Angeles | 1000.00 |
| 2        | 2       | 102       | New York    | 1500.50 |
| 3        | 3       | 103       | Chicago     | 2000.00 |
+----------+---------+-----------+-------------+---------+

By mastering these commands, you can efficiently retrieve and manipulate data to gain valuable insights, preparing you for any SQL interview. 

  1. Data Manipulation 

Data manipulation in SQL involves inserting, updating, and deleting data in a database. These commands allow you to manage the data stored within your tables effectively. 

2.1 INSERT INTO: Adding New Data 

The INSERT INTO statement adds new records to a table. 

INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …);

For example:

INSERT INTO clients (client_id, client_name, city)
VALUES (106, ‘Emily Clark’, ‘Boston’);

This SQL statement inserts a new record into the clients table: 

+-----------+--------------+--------+
| client_id | client_name  | city   |
+-----------+--------------+--------+
| 106       | Emily Clark  | Boston |
+-----------+--------------+--------+

2.2 UPDATE: Modifying Existing Data 

The UPDATE statement modifies existing records in a table. 

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

For example: 

UPDATE clients
SET city = ‘San Francisco’
WHERE client_id = 105;

This statement updates the city for the client with client ID 105: 

+-----------+--------------+------------------+
| client_id | client_name  | city             |
+-----------+--------------+------------------+
| 105       | Jack Johnson | San Francisco    |
+-----------+--------------+------------------+

2.3 DELETE: Removing Data 

The DELETE statement removes records from a table. 

DELETE FROM table_name
WHERE condition;

For example:

DELETE FROM clients
WHERE client_id = 104;

This SQL statement deletes the record with client ID 104 from the clients table: 

+-----------+--------------+---------------+
| client_id | client_name  | city          |
+-----------+--------------+---------------+
| 101       | John Doe     | New York      |
| 102       | Jane Smith   | New York      |
| 103       | Jim Beam     | Los Angeles   |
| 105       | Jack Johnson | San Francisco |
+-----------+--------------+---------------+

By mastering these data manipulation commands, you can efficiently manage and update your database, ensuring it remains accurate and up-to-date. 

Advanced SQL Techniques 

In advanced SQL, you’ll employ techniques like joins and subqueries to perform more complex data analysis and manipulation. These methods allow you to combine data from multiple tables and execute multi-step operations within your queries. Mastering these techniques significantly improves your data management capabilities and prepares you for your SQL interview, ensuring that this is your go-to advanced SQL cheat sheet. 

  1. Joins

Joins combine rows from two or more tables based on a related column between them. They are essential for working with relational databases where data is distributed across multiple tables. Use this PDF as your go-to SQL joins cheat sheet to master SQL joins. By mastering joins, you can retrieve and analyze data from different tables like a single table, enabling unique or innovative insights.

Below are examples of different types of joins and how you can use them:

1.1 INNER JOIN: Combining Tables on Matching Rows

An INNER JOIN returns only the rows with matching values in both tables. 

For example: 

SELECT orders.order_id, clients.client_name, orders.amount
FROM orders
INNER JOIN clients ON orders.client_id = clients.client_id;

Result: 

+----------+--------------+---------+
| order_id | client_name  | amount  |
+----------+--------------+---------+
| 1        | John Doe     | 1000.00 |
| 2        | Jane Smith   | 1500.50 |
| 3        | Jim Beam     | 2000.00 |
| 4        | Jill Jackson | 2500.75 |
+----------+--------------+---------+

This query retrieves the order ID, client name, and amount for orders with a matching client ID in the orders and clients tables. 

1.2 LEFT JOIN: Including All Rows from the LEFT Table 

A LEFT join returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the right table’s side. 

For example: 

SELECT orders.order_id, clients.client_name, orders.amount
FROM orders
LEFT JOIN clients ON orders.client_id = clients.client_id;

Result: 

+----------+--------------+---------+
| order_id | client_name  | amount  |
+----------+--------------+---------+
| 1        | John Doe     | 1000.00 |
| 2        | Jane Smith   | 1500.50 |
| 3        | Jim Beam     | 2000.00 |
| 4        | Jill Jackson | 2500.75 |
| 5        | NULL         | 3000.00 |
+----------+--------------+---------+

This query retrieves all orders and the corresponding client names, including orders without a matching client. 

1.3 RIGHT JOIN: Including all Rows from the Right Table 


A RIGHT JOIN returns all the rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the left table’s side. 

For example: 

SELECT orders.order_id, clients.client_name, orders.amount
FROM orders
RIGHT JOIN clients ON orders.client_id = clients.client_id;

Result:

+----------+--------------+---------+
| order_id | client_name  | amount  |
+----------+--------------+---------+
| 1        | John Doe     | 1000.00 |
| 2        | Jane Smith   | 1500.50 |
| 3        | Jim Beam     | 2000.00 |
| 4        | Jill Jackson | 2500.75 |
| NULL     | Jack Johnson | NULL    |
+----------+--------------+---------+

This query retrieves all clients and their corresponding orders, including clients without orders. 

1.4 FULL OUTER JOIN: Combining all Rows from Both Tables 

A FULL OUTER JOIN returns all rows when there is a match in either left or right table records. If there is no match, the result is NULL for every column from the table with no corresponding record. 

For example:

SELECT orders.order_id, clients.client_name, orders.amount
FROM orders
FULL OUTER JOIN clients ON orders.client_id = clients.client_id;

Result:

+----------+--------------+---------+
| order_id | client_name  | amount  |
+----------+--------------+---------+
| 1        | John Doe     | 1000.00 |
| 2        | Jane Smith   | 1500.50 |
| 3        | Jim Beam     | 2000.00 |
| 4        | Jill Jackson | 2500.75 |
| 5        | NULL         | 3000.00 |
| NULL     | Jack Johnson | NULL    |
+----------+--------------+---------+

This query retrieves all orders and clients, including those without matches. 

  1. Subqueries 

Subqueries, also known as inner queries or nested queries, are queries within another SQL query. They perform operations that require multiple steps, making them powerful tools for complex data retrieval and manipulation. 

Here are examples of different types of subqueries using the orders and clients tables. 

2.1 Basic Subquery Syntax 

A basic subquery is a query within a query that performs operations requiring multiple steps. It is often placed inside another SQL statement to retrieve data that the main query will use. 

For example, find clients who have placed orders with an amount greater than 2000:

SELECT client_name
FROM clients
WHERE client_id IN (SELECT client_id FROM orders WHERE amount > 2000);

Result: 

This query retrieves the names of clients who have placed orders with amounts greater than 2000. The subquery first finds the client IDs from the orders table with an amount greater than 2000, and the outer query retrieves the corresponding client names from the clients table. 

2.2 Correlated Subqueries 

+--------------+
| client_name  |
+--------------+
| Jill Jackson |
+--------------+

A correlated subquery references columns from the outer query. It is evaluated once for each row processed by the outer query. 

For example, find the orders with amounts greater than the average amount of orders placed in the same city: 

SELECT order_id, amount
FROM orders o
WHERE amount > (SELECT AVG(amount)
                FROM orders
                WHERE city = o.city);

Result: 

+----------+---------+
| order_id | amount  |
+----------+---------+
| 4        | 2500.75 |
| 5        | 3000.00 |
+----------+---------+

This query retrieves the order ID and number of orders greater than the average number of orders placed in the same city. The subquery calculates the average number of orders for each city, and the outer query compares each order’s amount to this average. 

2.3 Nested Subqueries 

Nested subqueries are subqueries within another subquery, used for even more complex queries.

For example, find the name of the client who placed the order with the highest amount:

SELECT client_name
FROM clients
WHERE client_id = (SELECT client_id
                  FROM orders
                  WHERE amount = (SELECT MAX(amount)
                                  FROM orders));

Result: 

+--------------+
| client_name  |
+--------------+
| NULL         |
+--------------+

This query retrieves the client name of the client who placed the order with the highest amount. The innermost subquery finds the highest order amount, the middle subquery finds the client ID for that order, and the outer query retrieves the corresponding client name from the clients table. 

Mastering subqueries allows you to perform sophisticated data analysis and retrieval operations, which is essential for handling complex SQL queries in interviews and real-world applications. 

SQL Functions 

SQL functions are essential for performing calculations on data, manipulating strings, and working with dates. This document serves as your SQL cheat sheet for functions by providing examples of the different types of SQL functions using the orders and clients tables. 

  1. Aggregate Functions 

Aggregate functions perform a calculation on a set of values and return a single value. They are commonly used with the GROUP BY clause. 

1.1 COUNT(): Counting Rows

The COUNT() function returns the number of rows that match a specified criterion.

For example:

+--------------+
| total_orders |
+--------------+
| 5            |
+--------------+

This query counts the total number of rows in the orders table. 

1.2 SUM(): Summing Values 

The SUM() function adds up the values in a specified column. 

For example: 

SELECT SUM(amount) AS total_sales
FROM orders;

Result:

+-------------+
| total_sales |
+-------------+
| 10001.25    |
+-------------+

This query calculates the total amount of all orders in the orders table. 

1.3 AVG(): Calculating Averages 

The AVG() function returns the average value of a numeric column. 

For example:

SELECT AVG(amount) AS average_order_amount
FROM orders;

Result: 

+----------------------+
| average_order_amount |
+----------------------+
| 2000.25              |
+----------------------+

This query calculates the average order amount in the orders table. 

1.4 MIN() and MAX(): Finding Minimum and Maximum Values 

The MIN() function returns the smallest value in a column, and the MAX() function returns the largest value. 

For example:

SELECT MIN(amount) AS smallest_order, MAX(amount) AS largest_order
FROM orders;

Result: 

+----------------+---------------+
| smallest_order | largest_order |
+----------------+---------------+
| 1000.00        | 3000.00       |
+----------------+---------------+

This query finds the smallest and largest order amounts in the orders table. 

  1. String Functions

String functions are used to perform operations on string data. 

2.1 CONCAT(): Joining Strings 

The CONCAT() function concatenates two or more strings. 

For example: 

SELECT CONCAT(client_name, ‘ from ‘, city) AS full_description
FROM clients;

Result:

+-------------------------------+
| full_description              | 
+-------------------------------+
| John Doe from New York        |
| Jane Smith from New York      |
| Jim Beam from Los Angeles     |
| Jill Jackson from Los Angeles |
| Jack Johnson from Chicago     |
+-------------------------------+

This query concatenates the client name and city columns to form a full description. 

2.2 SUBSTRING(): Extracting Substrings

The SUBSTRING() function extracts a part of a string. 

For example:

SELECT SUBSTRING(client_name, 1, 3) AS short_name
FROM clients;

Result: 

+------------+
| short_name |
+------------+
| Joh        |
| Jan        |
| Jim        |
| Jill       |
| Jac        |
+------------+

This query extracts the first three characters of each client’s name. 

2.3 LENGTH(): Measuring String Length

The LENGTH() function returns the length of a string. 

For example: 

SELECT LENGTH(client_name) AS name_length
FROM clients;

Result: 

+-------------+
| name_length |
+-------------+
| 8           |
| 10          |
| 7           |
| 12          |
| 11          |
+-------------+

This query returns the length of each client’s name. 

2.4 REPLACE(): Replacing Substrings 

The REPLACE() function replaces all occurrences of a specified substring within a string. 

For example: 

SELECT REPLACE(client_name, ‘a’, ‘X’) AS replaced_name
FROM clients;

Result: 

+---------------+
| replaced_name |
+---------------+
| John Doe      |
| JXne Smith    |
| Jim BeXm      |
| Jill JXckson  |
| JXck Johnson  |
+---------------+

This query replaces all occurrences of the letter ‘a’ with ‘X’ in the client name column. 

2.5 TRIM(): Removing Spaces 

The TRIM() function removes leading and trailing spaces from a string. 

For example: 

SELECT TRIM(‘   John Doe   ‘) AS trimmed_name;

Result: 

+--------------+
| trimmed_name |
+--------------+
| John Doe     |
+--------------+

This query removes the leading and trailing spaces from the string ‘   John Doe   ‘.

  1. Date Functions 

Date functions perform operations on date and time data. 

3.1 NOW() function: Returns the Current Date and Time 

For example: 

SELECT NOW() AS current_datetime;

Result: 

+---------------------+
| current_datetime    |
+---------------------+
| 2024-06-11 10:45:00 |
+---------------------+

This query returns the current date and time. 

3.2 DATE(): Extracting Date Parts 

The DATE() function extracts the date part of a date or date-time expression. 

SELECT DATE(NOW()) AS current_date;

Result:

+--------------+
| current_date |
+--------------+
| 2024-06-11   |
+--------------+

This query extracts the date part from the current date and time. 

3.3 DATEDIFF(): Difference Between Dates

The DATEDIFF() function returns the difference between two dates. 

For example: 

SELECT DATEDIFF(NOW(), ‘2024-01-01’) AS days_difference;

Result: 

+------------------+
| days_difference  |
+------------------+
| 162              |
+------------------+

This query calculates the number of dates between January 1, 2024, and the current date. 

3.4 DATE_ADD(): Adding Intervals to Dates

The DATE_ADD() function adds a specified time interval to a date. 

For example: 

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week;

Result: 

+------------+
| next_week  |
+------------+
| 2024-06-18 |
+------------+

This query adds seven days to the current date, returning the date for the next week. 

Data Grouping and Filtering 

SQL provides powerful tools for grouping and filtering data, which are essential for summarizing large datasets and applying conditions to grouped data. Two key clauses for these tasks are GROUP BY and HAVING. 

  1. GROUP BY: Grouping Data 

The GROUP BY clause groups rows that have the same values in specified columns into summary rows. It is often used with aggregate functions like COUNT(), SUM(), MIN(), and MAX() to perform calculations on each group. 

For example, find the number of orders placed by each client: 

SELECT client_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY client_id;

Result: 

+-----------+-------------+
| client_id | order_count |
+-----------+-------------+
| 101       | 1           |
| 102       | 1           |
| 103       | 1           |
| 104       | 1           |
| NULL      | 1           |
+-----------+-------------+

This query groups the orders by client id and counts the number of orders placed by each client. 

  1. HAVING: Filtering Grouped Data 

The HAVING clause was introduced because the WHERE keyword could not be used with aggregate functions. It is used to filter the results after the GROUP BY clause has been applied. 

For example, find clients who have placed more than one order:

SELECT client_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY client_id
HAVING COUNT(order_id) > 1;

Result:

This query does not return any rows because, in the current dataset, no client has placed more than one order. However, if the data were different, it would show only those clients who have placed more than one order. 


A second example: Find clients with orders more than $2000.

SELECT client_id, SUM(amount) AS total_amount
FROM orders
GROUP BY client_id
HAVING SUM(amount) > 2000;

Result: 

+-----------+--------------+
| client_id | total_amount |
+-----------+--------------+
| 104       | 2500.75      |
| NULL      | 3000.00      |
+-----------+--------------+

This query groups the orders by client ID and sums the total amount for each client. The HAVING clause filters the results to include only clients whose total order exceeds $2000. 

SQL Window Functions 

SQL Window functions are a powerful feature in SQL that allows you to perform calculations across a set of table rows that are related to the current row. They are useful for tasks such as ranking, calculating running totals, and working with time-series data. 

Here are some commonly used window functions with examples using the orders and clients tables. 

  1. ROW_NUMBER(): Assigning Unique Row Numbers 

The ROW_NUMBER() function assigns a unique row number to each row within a partition of a result set. 


For example, assign unique row numbers to orders based on the order amount: 

SELECT order_id, order_date, client_id, amount,
      ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num
FROM orders;

Result: 

+----------+------------+-----------+--------+---------+
| order_id | order_date | client_id | amount | row_num |
+----------+------------+-----------+--------+---------+
| 5        | ...        | NULL      | 3000.00| 1       |
| 4        | ...        | 104       | 2500.75| 2       |
| 3        | ...        | 103       | 2000.00| 3       |
| 2        | ...        | 102       | 1500.50| 4       |
| 1        | ...        | 101       | 1000.00| 5       |
+----------+------------+-----------+--------+---------+
  1. RANK(), DENSE_RANK(): Ranking Rows 

RANK() assigns a rank to each row within the partition of a result set, with gaps in ranking for ties. 

For example, rank orders by amount:

SELECT order_id, order_date, client_id, amount,
      RANK() OVER (ORDER BY amount DESC) AS rank
FROM orders;

Result: 

+----------+------------+-----------+--------+------+
| order_id | order_date | client_id | amount | rank |
+----------+------------+-----------+--------+------+
| 5        | ...        | NULL      | 3000.00| 1    |
| 4        | ...        | 104       | 2500.75| 2    |
| 3        | ...        | 103       | 2000.00| 3    |
| 2        | ...        | 102       | 1500.50| 4    |
| 1        | ...        | 101       | 1000.00| 5    |
+----------+------------+-----------+--------+------+

DENSE_RANK() is similar to RANK() but without gaps in ranking for ties. 

For example, dense rank orders by amount:

SELECT order_id, order_date, client_id, amount,
      DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM orders;

Result: 

+----------+------------+-----------+--------+------------+
| order_id | order_date | client_id | amount | dense_rank |
+----------+------------+-----------+--------+------------+
| 5        | ...        | NULL      | 3000.00| 1          |
| 4        | ...        | 104       | 2500.75| 2          |
| 3        | ...        | 103       | 2000.00| 3          |
| 2        | ...        | 102       | 1500.50| 4          |
| 1        | ...        | 101       | 1000.00| 5          |
+----------+------------+-----------+--------+------------+
  1. NTILE(): Dividing Rows into Buckets 

The NTILE() function divides rows into a specified number of buckets and assigns a bucket number to each row. 

For example, divide orders into 3 buckets based on the amount:

SELECT order_id, order_date, client_id, amount,
      NTILE(3) OVER (ORDER BY amount DESC) AS bucket
FROM orders;


Result: 

+----------+------------+-----------+--------+--------+
| order_id | order_date | client_id | amount | bucket |
+----------+------------+-----------+--------+--------+
| 5        | ...        | NULL      | 3000.00| 1      |
| 4        | ...        | 104       | 2500.75| 1      |
| 3        | ...        | 103       | 2000.00| 2      |
| 2        | ...        | 102       | 1500.50| 2      |
| 1        | ...        | 101       | 1000.00| 3      |
+----------+------------+-----------+--------+--------+
  1. LEAD(), LAG(): Accessing Data from Subsequent/Previous Rows

LEAD() allows you to access data from the following row. 

For example, find the amount of the next order for each order: 

SELECT order_id, amount,
      LEAD(amount) OVER (ORDER BY order_date) AS next_order_amount
FROM orders;

Result: 

+----------+--------+-------------------+
| order_id | amount | next_order_amount |
+----------+--------+-------------------+
| 1        | 1000.00| 1500.50           |
| 2        | 1500.50| 2000.00           |
| 3        | 2000.00| 2500.75           |
| 4        | 2500.75| 3000.00           |
| 5        | 3000.00| NULL              |
+----------+--------+-------------------+

LAG() accesses data from the previous row. 

For example, find the amount of the previous order for each order: 

SELECT order_id, amount,
      LAG(amount) OVER (ORDER BY order_date) AS previous_order_amount
FROM orders;

Result: 

+----------+--------+-----------------------+
| order_id | amount | previous_order_amount |
+----------+--------+-----------------------+
| 1        | 1000.00| NULL                  |
| 2        | 1500.50| 1000.00               |
| 3        | 2000.00| 1500.50               |
| 4        | 2500.75| 2000.00               |
| 5        | 3000.00| 2500.75               |
+----------+--------+-----------------------+

Mastering SQL window functions allows for complex calculations and insights across datasets, enhancing the analysis capabilities for your SQL queries. These functions are vital for tasks that involve ranking, row numbering, and accessing data from different rows without using subqueries. 

Indexes and Performance Tuning 

Optimizing SQL queries and database performance is critical for efficient data retrieval and manipulation. Here are key techniques and examples for creating and using indexes, understanding query execution plans, and applying common performance optimization techniques.

Creating and Using Indexes

Indexes in SQL databases are used to speed up data retrieval by creating a smaller, more manageable copy of the data that can be searched more quickly. Proper indexing can drastically improve query performance. 

For example, creating an index: 

CREATE INDEX idx_client_id ON orders(client_id);

This SQL statement creates an index of the client ID column in the orders table, allowing for raster searches based on client IDs. 

There are a number of different types of indexes, including: 

  1. Unique indexes ensure that no two rows have the same value in the indexed column(s). 
  2. Primary indexes are a specific type of index created from the table’s primary key. 
  3. Secondary indexes are additional indexes created as required and can be non-unique. 
  4. Composite indexes are indexes that include multiple columns. 

Now that we understand the different types of indexes let’s look at the considerations for indexing: 

  1. Selectivity: The uniqueness of the column values; more unique values often make better index candidates. 
  2. Query Patterns: Index columns that are frequently used in search conditions, sorting, or joining operations. 
  3. Resource Overhead: Each index must be updated when the data is modified, which can slow down insert, update, or delete operations if overused. 

Understanding Query Execution Plans 

Query execution plans provide a roadmap of how a database server executes a query. Analyzing these plans helps identify performance bottlenecks and suggests possible optimizations. These plans are essential for optimizing SQL queries across various database systems, including MySQL, PostgreSQL, SQL Server, and Oracle.

Execution plans can be generated in different ways depending on whether you need an estimate or an actual execution analysis: 

  1. Estimated Execution Plan: This provides an estimate of the query execution process without actually executing the query. It is useful for understanding potential performance issues without affecting the database. The estimated execution plan is based on the database’s statistical information about the data and the indexes. 
  1. Actual Execution Plan: Requires the query to be executed and provides a detailed and accurate representation of the execution steps. It includes runtime metrics such as actual row counts and execution times, making it more precise than the estimated execution plan. 

Key Components of an Execution Plan 

Execution plans include several key components that detail how a query is processed. Understanding these components can help you optimize your queries effectively. 

  1. Table Access Methods: These indicate how the database retrieves rows from tables. Common methods include:
    1. Table Access Full: This method reads all rows and columns from a table (also known as a Full Table Scan). It is typically the slowest and should be avoided if possible. 
    2. Index Scan/Index Seek: Uses an index to find rows. Index seek is generally faster as it looks for specific entries in the index, which index scan reads all entries. 

By analyzing these access methods, you can identify if the query is performing unnecessary full table scans, which can be optimized by creating indexes. 

  1. Join Types: These are different methods used to join tables, such as:
    1. Nested Loops: Joins two tables by iterating through each row of the first table and finding matching rows in the second table. 
    2. Hash Join: This joins two tables by creating a hash table on the join key of one table and then probing it with rows from the other table. 
    3. Merge Join: Joins two sorted tables by merging them together based on the join key. 

Understanding the join types used in your queries helps you determine if there are more efficient ways to combine tables. 

  1. Sort and Aggregate Operations: These show how the database handles sorting and aggregating data.
    1. Sort Order By: Sorts the result set based on the specified columns. 
    2. Sort Group By: Groups the result set based on the specified columns and aggregates data. 

  Analyzing sort and aggregate operations can help you optimize queries that involve sorting and grouping, ensuring they run as efficiently as possible. 

Execution plans provide critical insights into query performance, helping database administrators and developers optimize their SQL queries for better efficiency and faster response times.


Common Performance Optimization Techniques 

Performance optimization in SQL involves various techniques to ensure queries run efficiently, minimizing resource usage and response time, including the following: 

  1. Add Missing Indexes: Analyze execution plans for missing index suggestions and create appropriate indexes to speed up queries. 
  2. Check for Unused Indexes: Remove indexes that are not being used to reduce maintenance overhead. 
  3. Avoid Using Multiple OR Conditions: Use UNION or other strategies to optimize complex WHERE clauses. 
  4. Use Wildcards Appropriately: Place wildcards at the end of search patterns to maintain index usage. 
  5. Minimize JOINs and Use INNER JOINs: Simplify JOIN operations and prefer INNER JOINs over WHERE clauses for joining tables. 
  6. Use SELECT columns instead of SELECT(*): Limit the columns retrieved to only those necessary. 
  7. Run Queries During Off-Peak Hours: Schedule heavy queries during times of low database activity to reduce impact on performance. 
  8. Regularly Update Statistics: Keep database statistics up to date to ensure the query optimizer makes informed decisions. 

Implementing these indexing and performance-tuning techniques can significantly improve the efficiency of your SQL queries and overall database performance. 

Common SQL Interview Questions 

Preparing for an SQL interview involves understanding and practicing a variety of SQL questions, including Amazon SQL interview questions. Here are some common SQL interview questions, along with sample answers and additional tips. 

Sample Questions and Answers 

  1. What is the difference between ALTER and UPDATE? 
  1. b.The ALTER statement is used to change the structure of a table, such as adding, deleting, or modifying columns. 
  2. The UPDATE statement is used to modify existing records in a table. 

For example:

— Adding a new column to a table
ALTER TABLE employees ADD COLUMN email VARCHAR(255);

— Updating a record in a table
UPDATE employees SET salary = 60000 WHERE department = ‘IT’;
  1. Explain different types of joins with examples.

2.1 INNER JOIN

Returns rows that have matching values in both tables. 

For example: 

SELECT orders.order_id, clients.client_name, orders.amount
FROM orders
INNER JOIN clients ON orders.client_id = clients.client_id;

Result: 

+----------+--------------+--------+
| order_id | client_name  | amount |
+----------+--------------+--------+
| 1        | John Doe     | 1000.00|
| 2        | Jane Smith   | 1500.50|
| 3        | Jim Beam     | 2000.00|
| 4        | Jill Jackson | 2500.75|
+----------+--------------+--------+

2.2 LEFT JOIN

Returns all rows from the left table and the matched rows from the right table. 

For example: 

SELECT
    o.order_id,
    c.client_name
FROM
    Orders o
LEFT JOIN
    Clients c ON o.client_id = c.client_id
WHERE
    o.amount > 2000 OR o.order_id IS NULL
ORDER BY
    o.amount ASC;

Result:

+----------+--------------+
| order_id | client_name  |
+----------+--------------+
| 4        | Jill Jackson |
| 5        | null         |
+----------+--------------+

2.3 RIGHT JOIN

Returns all rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the left table’s side. 

For example: 

SELECT orders.order_id, clients.client_name, orders.amount
FROM orders
RIGHT JOIN clients ON orders.client_id = clients.client_id;

Result: 

+----------+--------------+--------+
| order_id | client_name  | amount |
+----------+--------------+--------+
| 1        | John Doe     | 1000.00|
| 2        | Jane Smith   | 1500.50|
| 3        | Jim Beam     | 2000.00|
| 4        | Jill Jackson | 2500.75|
| NULL     | Jack Johnson | NULL   |
+----------+--------------+--------+

2.4 FULL OUTER JOIN

A FULL OUTER JOIN returns all rows when there is a match in either left or right table records. If there is no match, the result is NULL for every column from the table that lacks a match. 

For example: 

SELECT orders.order_id, clients.client_name, orders.amount
FROM orders
FULL OUTER JOIN clients ON orders.client_id = clients.client_id;

Result: 

+----------+--------------+--------+
| order_id | client_name  | amount |
+----------+--------------+--------+
| 1        | John Doe     | 1000.00|
| 2        | Jane Smith   | 1500.50|
| 3        | Jim Beam     | 2000.00|
| 4        | Jill Jackson | 2500.75|
| 5        | NULL         | 3000.00|
| NULL     | Jack Johnson | NULL   |
+----------+--------------+--------+
  1. What is a subquery? Provide an example. 

A subquery is a query nested inside another query, used to fetch data that will be used in the main query. 

For example: 

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
  1. How do you optimize SQL queries? 

SQL query optimization involves improving the performance of SQL queries by reducing resource usage and execution time. Strategies include using appropriate indexes, optimizing query structure, and avoiding costly operations like full table scans. 

  1. What are correlated subqueries? 

A correlated subquery is a subquery that references columns from the outer query. It is executed repeatedly, once for each row being processed by the outer query, and its execution depends on the outcomes of the outer query. 

Tips for Effective Problem-Solving 

Solving SQL problems effectively requires a structured approach and a good understanding of SQL concepts. Here are several tips to help you tackle SQL challenges efficiently:

  1. Understand the Problem: Carefully read and comprehend the problem statement. Identify the input and expected output. 
  2. Plan Your Query: Break down the problem into smaller steps and plan your SQL query accordingly. 
  3. Write Clean and Efficient Code: Use proper SQL syntax and avoid unnecessary operations. Aim for readability and efficiency. 
  4. Test your Query: Run test cases to ensure your query works correctly and handles edge cases. 
  5. Optimize: Look for ways to optimize your query, such as adding indexes, refining joins, and avoiding full table scans. 

Additional Questions 

Lastly, here are several additional questions and answers:

  1. Where can I download a SQL cheat sheet PDF? 

You can download a comprehensive SQL cheat sheet PDF from www.bigtechinterviews.com

  1. Where can I find a GitHub SQL cheat sheet? 

GitHub repositories often contain useful SQL cheat sheets. You can search for “SQL cheat sheet” on GitHub to find a variety of user-contributed cheat sheets.

  1. Can I use an SQL cheat sheet during an interview?

Typically, using a cheat sheet during an interview is not allowed. However, it’s beneficial to study cheat sheets beforehand to familiarize yourself with common SQL commands and syntax. Practicing without a cheat sheet will help reinforce your knowledge and ensure you are prepared for the interview.

  1. What are some of the common interview questions? 

When preparing for SQL interviews, practicing with questions from different companies is helpful. For example, the 3 latest Uber SQL interview questions, as well as the SQL questions found in the Meta data engineer interview guide, can give you an idea of the types of challenges you might face and help you refine your problem-solving strategies. 

Additional Resources 

In Conclusion

In this comprehensive SQL cheat sheet, we’ve covered essential SQL commands, advanced techniques, and critical components that will aid you in preparing for SQL interviews. 

Understanding these key SQL functions, from basic data retrieval and manipulation commands to advanced techniques like joins, subqueries, and window functions, will enable you to perform efficient data analysis and management.

Recapping the key points, we explored the foundational aspects of SQL vital for mastering data retrieval, manipulation, and sorting with commands like SELECT, INSERT, UPDATE, and DELETE. To handle complex data relationships and analyses, we examined advanced SQL techniques, such as using joins, subqueries, and window functions. 

We also covered performance optimization strategies, including implementing indexes and understanding execution plans to ensure efficient query performance. Finally, we highlighted the importance of SQL functions, like aggregate, string, and date functions, to perform a variety of operations on your data. 

To excel in SQL, continuous practice and exploration are crucial. Utilize the additional resources provided, including recommended books, online courses, and practice platforms, to further your understanding and stay updated with the latest SQL developments. Engaging with SQL communities and forums can also provide valuable insights and support from fellow SQL professionals. 

Remember, the key to mastering SQL is constant practice and a willingness to explore beyond the basics. Use this cheat sheet as a guide and continue to build on your knowledge to become proficient in SQL and excel in your interviews. 

Looking to ace your SQL interviews with confidence? Enhance your SQL skills and get interview-ready with tailored preparation materials designed to help you succeed. Don’t miss out on the opportunity to master SQL and secure your dream job. Visit app.bigtechinterviews.com for comprehensive resources, including detailed SQL cheat sheets, practice questions, and expert tips.

Do you want to ace your SQL interview?

Practice free and paid real SQL interview questions with step-by-step answers.

Do you want to Ace your SQL interview?

Practice free and paid SQL interview questions with step-by-step video solutions!