26th Sep 2024 20 minutes read SQL Interview Cheat Sheet Tihomir Babic Jobs And Career SQL Interview Questions cheat sheet Table of Contents What Is SQL? SQL Table Info SQL Commands SQL Queries Basic Clauses in an SQL Query JOINs Aggregation in SQL Set Operators Subqueries SQL Operators and Expressions Comparison Operators Arithmetic Operators Logical Operators CASE WHEN SQL Functions Text Functions Numeric Functions NULL Functions DML – Modifying Data in SQL DDL – Defining Data Structure in SQL DDL Commands SQL Data Types SQL Constraints Additional SQL Interview Resources Going Beyond the SQL Interview Cheat Sheet Need to quickly reference all the fundamental knowledge for an SQL interview? Want to refresh your memory about an SQL concept? Our SQL Interview Cheat Sheet is your new best friend! Preparing for an SQL interview requires brushing up on your SQL. But it’s very easy to get lost in all the SQL concepts you must review. In such situations, you need an easily browsable resource packed with SQL knowledge. And that’s why we created this SQL Interview Cheat Sheet. If you need to quickly refer to basic SQL concepts, this cheat sheet is for you! If you need a bit more than just a quick refresher, consider taking our SQL Basics course. It teaches you the fundamentals of SQL, from data retrieval to building simple reports, using 129 coding challenges. Then you can consolidate your knowledge by practicing on the 1,190 coding challenges in our SQL Practice track. You will be prepared for any SQL interview after that! Without further ado, let’s dive straight into our SQL Interview Cheat Sheet. We’ll start with the most basic question: What Is SQL? Structured Query Language, or SQL, is a programming language designed for handling data in databases. It allows users to query, add, change, save, and delete data in a database. It can also be used to create and change database structures. SQL Table Info Term Explanation Table A basic database object. Consisting of rows and columns, it stores data about a particular subject (e.g., an “employees” table stores data about company employees). Columns (or attributes) Like columns in a spreadsheet, table columns contain details about the database subject (e.g., the “ID” column stores the employees’ ID numbers). Rows (or records) Like rows in a spreadsheet, table rows contain data about a single instance of the database subject (e.g., one row records all the data for one employee). Fields Like a cell in a spreadsheet, a field is the intersection between one column and one row. It stores one piece of data, e.g. one employee’s ID number. Data Types Defines what type of data can be stored in each column. Here’s a visualization of the above terminology. SQL Commands Term Explanation Query A SQL statement that retrieves data, i.e., a SELECT statement. Statement A command that can be executed against the database; the most basic building block of SQL. DDL Statements Data Definition Language statements create and modify database objects, such as tables or indexes. DDL is a subset of SQL. DCL Statements Data Control Language statements give or remove rights for accessing parts of the database. DCL is a subset of SQL. DML Statements Data Manipulation Language statements allow us to manipulate the information in a database. DML is a subset of SQL. Popular DML statements include the following: INSERT Inserts one or more new rows of data into a table. UPDATE Modifies existing values in a table column. DELETE Removes one or more rows from the table. DQL Statements Data Query Language statements retrieve data from a database – i.e., SELECT statements. SELECT Fetches data from one or more tables in a database. SQL Queries Basic Clauses in an SQL Query SELECT is the most commonly used statement in SQL queries. This typical SQL query may also include several clauses that allow us to refine query results. Here’s the syntax of an SQL query: SELECT column_1, column_2, aggregate_function(column_3), … FROM table_1 JOIN table_2 ON table_1.common_column = table_2.common_column WHERE column_1 <condition> GROUP BY column_1, column_2, … HAVING aggregate_function(column_3) <condition> ORDER BY column_1 [ASC|DESC]; Here’s an explanation and example of each clause: SQL Clause Explanation Code Example Code Output FROM Defines which table to extract data from. SELECT first_name, last_name FROM employees; Returns employees’ first and last names from the table employees. JOIN Joins data from two or more tables so you can query it as one table. SELECT e.first_name, e.last _name, d.name FROM employees e JOIN department d ON e.department_id = d.id; Joins the tables employees and department on the department ID. Returns employees’ first and last names and their department name. WHERE Filters the records before aggregation and returns only those that satisfy the stated condition. SELECT first_name, last_name FROM employees WHERE last_name = 'Smith'; Returns the first and last names of all the employees whose last name is Smith from the table employees. GROUP BY Groups query results based on the shared column values. Often used with aggregate functions. SELECT nationality, AVG(salary) FROM employees GROUP BY nationality; Returns the average employee salary for each nationality. HAVING Filters records after aggregation and returns only those that satisfy the stated condition. SELECT nationality, AVG(salary) FROM employees GROUP BY nationality HAVING AVG(salary) > 5000; Returns the average employee salary for each nationality – but only for the nationalities where the average salary is above 5,000. ORDER BY Sorts the output in ascending (1-10, A-Z) or descending (10-1, Z-A) order by the values in the given column(s). SELECT first_name, last_name FROM employees ORDER BY last_name ASC; Returns employees’ first and last names and sorts the output alphabetically by employees’ last names. JOINs JOIN is an SQL clause that joins two or more tables, allowing data retrieval from all the joined tables. You can find explanations and representations of different types of JOINs in the graphic below. INNER JOIN: Returns only the matching rows from joined tables. LEFT (OUTER) JOIN: Returns all the rows from the first (left) table and only the matching rows from the second (right) table. The non-matched values from the right table are shown as NULLs. RIGHT (OUTER) JOIN: Returns all the rows from the second (right) table and only the matching rows from the first (left) table. The non-matched values from the left table are shown as NULLs. FULL (OUTER) JOIN: Returns all the rows from the left and right table. The non-matched values from either table are shown as NULLs. Aggregation in SQL Aggregation is performing calculations on multiple rows and returning a single value that represents a summary or statistical result. It’s also known as aggregating rows – e.g., counting rows, computing the average or the sum per group of rows, etc. How to perform data aggregation in SQL: This operation is done using SQL’s aggregate functions; simply pass the argument in the function’s parentheses. In other words, write the name of the column you want to aggregate: SELECT aggregate_function (column_name) FROM table_name; GROUP BY: Aggregation is typically done in conjunction with the GROUP BY clause. GROUP BY organizes rows with the same column values in one group. Combining aggregate functions with GROUP BY allows you to show labels alongside the aggregated values, as in the graphic below: HAVING: The HAVING clause filters groups based on the result of an aggregate function; only the records that satisfy the condition are shown in the output. Here’s an example of how it works: Aggregate functions: The most common aggregate functions are listed below. You can find more information about aggregate functions in our SQL Aggregate Functions Cheat Sheet. Aggregate Function Explanation Code Example Code Output COUNT() Counts the number of rows. SELECT country, COUNT(id) FROM orders GROUP BY country; Returns the number of orders from each country in the table. AVG() Calculates the average value. SELECT country, AVG(value) FROM orders GROUP BY country; Returns the average order value by country. SUM() Calculates the sum of values. SELECT country, SUM(value) FROM orders GROUP BY country; Totals the value of orders by country. MIN() Returns the smallest value. SELECT country, MIN(value) FROM orders GROUP BY country; Returns the lowest order value by country. MAX() Returns the largest value. SELECT country, MAX(value) FROM orders GROUP BY country; Returns the highest order value by country. For a deep dive into GROUP BY and aggregate functions, check out our article GROUP BY and Aggregate Functions: A Complete Overview. Set Operators SQL’s set operators combine the outputs of one or more SELECT statements into one output. Set Operator Explanation UNION Shows only unique (non-duplicate) rows from all SELECT statement outputs. UNION ALL Shows all the rows from all SELECT statements outputs, including duplicates. INTERSECT Shows only rows that exist in all SELECT statements’ outputs. MINUS or EXCEPT Returns the rows from the first SELECT output that don’t exist in other SELECT output(s). Subqueries SELECT queries that are embedded in another SQL query are called subqueries. They can be used in the following main statements: SELECT INSERT UPDATE DELETE The clauses that accept subqueries are: SELECT FROM WHERE HAVING JOIN The types of subqueries are: Scalar – Returns exactly one value. Multi-row – Returns multiple values. Correlated – References the main query. The infographic below demonstrates the different types of subqueries. Example 1: Scalar subquery Example 2: Multi-row subquery Example 3: Correlated subquery Subquery operators: Subqueries are commonly used with several operators that allow you to compare data from the main query with the subquery output. Subquery Operator Explanation ANY or SOME Returns TRUE if any value from a subquery satisfies the condition in the main query. ALL Returns TRUE if all values from a subquery satisfy the condition in the main query. EXISTS Returns TRUE if a subquery returns any rows. IN Returns all values from the main query that can be found in the subquery that returns multiple values, i.e., a list of values. SQL Operators and Expressions Operators in SQL are used for comparison, arithmetic, and logical operations. Expressions consist of one or more values, operators, functions, and other query components that calculate values and create conditions. The most common expression is CASE WHEN. Comparison Operators Comparison operators are used for comparing values within a query. Comparison Operator Explanation Code Example Code Output = Both values are equal or matching. SELECT first_name, last_name FROM employees WHERE salary = 5000; Returns all employees whose salary is exactly 5,000. < Less (lower, smaller) than the compared value. SELECT first_name, last_name FROM employees WHERE salary < 5000; Returns all employees whose salary is under 5,000. <= Lower than or equal to the compared value. SELECT first_name, last_name FROM employees WHERE salary <= 5000; Returns all employees whose salary is 5,000 or less. > Greater (higher, bigger) than the compared value. SELECT first_name, last_name FROM employees WHERE salary > 5000; Returns all employees whose salary is over 5,000. >= Greater than or equal to the compared value. SELECT first_name, last_name FROM employees WHERE salary >= 5000 Returns all employees whose salary is 5,000 or greater. <> The values are not equal or do not match. SELECT first_name, last_name FROM employees WHERE salary <> 5000; Returns all employees whose salary is not 5,000. BETWEEN Checks if the value is in the specified range. The operator is inclusive, meaning the values equal to the upper and lower values of the range are included. SELECT first_name, last_name FROM employees WHERE salary BETWEEN 5000 AND 6000; Returns all employees whose salary is between 5,000 and 6,000, including those whose salary is exactly 5,000 or 6,000. LIKE Checks if the value matches the given string (text) pattern. Important: This operator is case sensitive. SELECT first_name, last_name FROM employees WHERE last_name LIKE 'van%'; Returns all employees whose last names begin with ‘van’, such as ‘van Basten’ and ‘vanBeek’. Does not include ‘Vandeley’, since the search is case sensitive. ILIKE Checks if the value matches a string pattern (case-insensitive). SELECT first_name, last_name FROM employees WHERE last_name ILIKE 'van%'; Returns all employees whose last names begin with ‘van’, such as ‘van Basten’, ‘vanBeek’, and ‘Vandeley’ – the search is not case sensitive. IS NULL hecks if the value is NULL. SELECT first_name, last_name FROM employees WHERE end_date IS NULL; Returns all employees who are still at the company, i.e., whose employment end date is NULL. IS NOT NULL Checks if the value is not NULL. SELECT first_name, last_name FROM employees WHERE end_date IS NOT NULL; Returns all ex-employees, i.e., records where the employment end date is not NULL. IN Checks if the value(s) can be found in the given list of values. SELECT first_name, last_name FROM employees WHERE city IN ( 'New York', 'Los Angeles', 'Baltimore'); Returns all employees who live in New York, Los Angeles, or Baltimore. Arithmetic Operators Arithmetic operators are used for performing mathematical operations in SQL. Arithmetic Operator Explanation Code Example Code Output + Adds values. SELECT first_name, last_name, salary + bonus FROM employees; Returns employees’ names and calculates their total compensation, i.e. the sum of their salary and bonus. - Subtracts values. SELECT first_name, last_name, vacation_days - used_vacation_day FROM employees; Returns employees’ names and calculates their remaining vacation days, i.e., the difference between their total vacation days and used vacation days. * Multiplies values. SELECT first_name, last_name, salary*1.03 FROM employees; Returns employees’ names and calculates their salary after a 3% raise, i.e., the product of their current salary and 1.03. / Divides values. SELECT first_name, last_name, salary/12 FROM employees; Returns employees’ names and calculates their monthly salary, i.e., their annual salary divided by 12. ** Raises one number to the power of another. SELECT first_name, last_name, salary * 1.05 ** 3 FROM employees; Returns employees’ names and calculates their salary after three years of 5% annual raises, i.e., a product of their current salary and 1.05 to the power of 3. % Returns the remainder of the division (modulo operator). SELECT first_name, last_name FROM employees WHERE id % 2 = 0; Returns all employees whose ID is an even number (because the remainder when ID is divided by two is zero). Logical Operators Logical operators combine multiple conditions. Logical Operator Explanation Code Example Code Output AND Checks if both conditions are satisfied. SELECT first_name, last_name FROM employees WHERE department = 'IT' AND salary > 5000; Returns all employees who work in IT and whose salary is above 5,000. OR Checks if either condition is satisfied. SELECT first_name, last_name FROM employees WHERE department= 'IT' OR salary > 5000; Returns all employees who work in IT or whose salary is above 5,000. NOT Checks if the given condition is not satisfied. SELECT first_name, last_name FROM employees WHERE NOT department = 'IT'; Returns all employees who don’t work in IT. CASE WHEN CASE WHEN is a conditional expression that returns specified values in the cases when the condition is and is not met. This is SQL’s implementation of the IF-THEN logic. The CASE WHEN syntax is: CASE WHEN condition_1 THEN value_1 WHEN condition_2 THEN value_2 ... ELSE default_value END; CASE initiates the expression. WHEN specifies the condition that has to be met. THEN specifies the value that will be returned if the condition is met. ELSE specifies the value that will be returned if the condition is not met. END terminates the expression. SQL Functions SQL functions are named and predefined pieces of code that can be used in an SQL query. These functions accept arguments (written in the function’s parentheses) that tell the function how or where to execute. The basic types of SQL functions are: Text functions Numeric functions NULL functions Text Functions Text Function Explanation Code Example Code Output CONCAT() Concatenates two or more strings into one string. SELECT CONCAT(first_name, ' ', last_name) FROM employees; Concatenates employees’ first and last names from two separate columns into a single string shown in one column. SUBSTRING() Returns part of a string. SELECT first_name, last_name, SUBSTRING(last_name, 4) FROM employees; Returns employees’ full names and the first four letters of their last names. LENGTH() Returns the length of a string (as the number of characters). SELECT first_name, last_name, LENGTH(last_name) FROM employees; Returns the employees’ full names and the number of characters in their last names. LEFT() Returns a specified number of characters from the start (left side) of a string. SELECT first_name, last_name, LEFT(last_name, 3) FROM employees; Returns the employees’ full names and the first three letters of their last names. RIGHT() Returns a specified number of characters from the end (right side) of a string. SELECT first_name, last_name, RIGHT(last_name, 3) FROM employees; Returns the employees’ full names and the last three letters of their last names. TRIM() Removes empty spaces from either side of a string. SELECT TRIM(first_name), TRIM(last_name), FROM employees; Returns employees' full names without leading and trailing spaces, e.g., the last name ‘ Jonas ‘ becomes ‘Jonas’. REPLACE() Replaces a specified substring with another substring. SELECT first_name, last_name, REPLACE(department, 'Acuonting', 'Accounting') FROM employees; Returns employees’ full names and their departments while renaming the ‘Acuonting’ department to ‘Accounting’. REVERSE() Reverses a string. SELECT REVERSE(last_name) FROM employees; Returns reversed versions of employees’ last names, e.g., ‘Jonas’ becomes ‘sanoJ’. UPPER() Converts a string into all uppercase characters. SELECT UPPER(last_name) FROM employees; Returns all-uppercase versions of employees’ last names, e.g., ‘Jonas’ becomes ‘JONAS’. LOWER() Converts a string into all lowercase characters. SELECT LOWER(last_name) FROM employees; Returns all-lowercase versions of employees’ last names, e.g., ‘Jonas’ becomes ‘jonas’. You can find an in-depth review of text functions in our article An Overview of SQL Text Functions. Numeric Functions Numeric Function Explanation Code Example Code Output ABS() Returns the absolute value of a numeric value. SELECT ABS(-169.23); -169.23 becomes 169.23. ROUND() Rounds a numeric value to a specified number of decimal places. SELECT ROUND(169.2389, 3); 169.2389 is rounded to the third decimal and becomes 169.239. CEILING() Rounds a numeric value up to the first integer greater than or equal to it. SELECT CEILING(169.2389); 169.2389 is rounded up to 170. FLOOR() Rounds a numeric value down to the first integer lower than or equal to it. SELECT FLOOR(169.2389); 169.2389 is rounded down to 169. POWER() Raises a numeric value to the power of another numeric value. SELECT POWER(5, 4); Raises 5 to the power of 4, which equals 625. SQRT() Returns the square root of a numeric value. SELECT SQRT(25); Returns the square root of 25, which equals 5. RAND() or RANDOM() Returns a random floating-point number between 0 and 1. SELECT RANDOM(); For example, returns 0.4371221377641987. LOG() Returns a numeric value’s logarithm of a specified base. SELECT LOG(12, 2); Returns logarithm base 2 of 12, which is 0.2789429456511298. LN() Returns the natural logarithm of a numeric value. SELECT LN(12); Returns the natural logarithm of 12, which is 2.4849066497880004. You can find a detailed dive into numeric functions in our article SQL Numeric Functions. NULL Functions NULL Function Explanation Code Example Code Output COALESCE() Returns the first non-NULL value from a list of arguments. SELECT first_name, last_name, COALESCE(department, 'N/A') FROM employees; Returns employees and their departments. When the value in the department column is NULL, it’s displayed as N/A in the output. NULLIF() Returns NULL if two expressions are equal; otherwise, it returns the first expression. SELECT first_name, last_name NULLIF(department, 'N/A') FROM employees; Returns employees and their departments. When the value in the department column is N/A, it’s displayed as NULL in the output. DML – Modifying Data in SQL An overview of three commands used for data modification (DML commands) is given below. DML Command Explanation Code Example Code Output INSERT Inserts one or more rows of data into a table. INSERT INTO employees (first_name, last_name, department) VALUES ('Francis', 'Ford', 'Sales'); Inserts a new employee in the table employees. The inserted employee’s first name is Francis, his last name is Ford, and he works in the Sales department. UPDATE Modifies column value for one or more rows. UPDATE employees SET department = 'Accounting' WHERE department = 'Acuonting'; Updates the misspelled department name ‘Acuonting’ with the correct ‘Accounting’. DELETE Deletes one or more rows from the table. DELETE FROM employees WHERE id = 6; Deletes all the data from the row where the employee ID value is 6. DDL – Defining Data Structure in SQL Key DDL (Data Definition Language) concepts are: DDL commands SQL data types SQL constraints DDL Commands Here’s an overview of DDL commands: DDL Command Explanation Code Example Code Output CREATE TABLE Creates a new table with specified column names, data types, and constraints. CREATE TABLE customers( id INT PRIMARY KEY, first_name VARCHAR(120), last_name VARCHAR(120) ); Creates the table customers with columns named id, first_name, and last_name. The column data types are INT, VARCHAR(120), and VARCHAR(120), respectively. Additionally, the primary key constraint is defined on the ID column. DROP TABLE Removes the whole table and its definition from a database. DROP TABLE customers; Deletes the customers table together with its columns, data, data type definitions, constraints, etc. CREATE VIEW Creates a stored query (aka a ‘view’) that can be reused and used in other queries. CREATE VIEW salary_summary AS SELECT department, AVG(salary) FROM employees GROUP BY department; Creates a view named salary_summary, which calculates the average salary by department. DROP VIEW Completely deletes a view and its definition from the database. DROP VIEW salary_summary; Deletes the salary_summary view and its definition from the database. ALTER TABLE Modifies the existing table’s structure by … … renaming the table ALTER TABLE employees RENAME TO salaries; Changes the employees table name to salaries. … renaming a column ALTER TABLE employees RENAME COLUMN last_name TO surname; Changes the column name last_name to surname. .. changing a column’s data type ALTER TABLE employees ALTER COLUMN salary DECIMAL(10,2); Changes the data type of the salary column to DECIMAL(10,2). … adding a column ALTER TABLE employees ADD date_of_birth DATE; Adds the column date_of_birth (of the DATE data type) to the table employees. … dropping (deleting) column ALTER TABLE employees DROP COLUMN end_date; Removes the column end_date from the table employees. SQL Data Types Below is an overview of the most common SQL data types. Data Type Explanation Numeric Data Types INT Represents whole numbers, i.e., integers. The range is from -2147483648 to 2147483648 and it uses 4 bytes of storage. DECIMAL(p, s) Represents fixed-point decimal numbers with the precision p (the total number of digits before and after the decimal point) and scale s (digits after the decimal point). FLOAT Represents approximate values with floating-point precision. SMALLINT Represents small integers from -32,768 to 32,767; uses 2 bytes of storage. String Data Types CHAR(n) Represents a fixed-length string of n number of characters; the maximum length is 255. VARCHAR(n) Represents a variable-length string of n characters (maximum 255). TEXT Represents a large (up to 65,535 bytes) variable-length string. Date and Time Data Types DATE A date in the YYYY-MM-DD format. TIME A time in the HH:MI:SS format. DATETIME Date and time in the YYYY-MM-DD HH:MI:SS format. INTERVAL A date or time period, e.g., YEAR, MONTH, DAY, HOUR, etc. BOOLEAN A logical value, i.e., TRUE or FALSE. SQL Constraints Here’s an overview of the most common SQL constraints: Constraint Explanation Code Example Code Output PRIMARY KEY Ensures values in the column are unique (not duplicated) and not NULL. CREATE TABLE orders ( id INT PRIMARY KEY, value DECIMAL(10,2) ); Values in the column id of the table orders will be unique and won’t accept NULLs. FOREIGN KEY Links a column from one table to the primary key of another table. This creates a relationship between the two tables. CREATE TABLE orders ( id INT PRIMARY KEY, value DECIMAL(10,2), customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers (id) ); The column customer_id from the table orders is a foreign key that references the column id of the table customers. NOT NULL Ensures a column does not accept NULL values. CREATE TABLE orders ( id INT PRIMARY KEY, value DECIMAL(10,2) NOT NULL ); Values in the value column can’t be NULL – i.e., every order must have a value. UNIQUE Ensures values in the column are unique, but allows NULL values. CREATE TABLE customer( id INT PRIMARY KEY, name VARCHAR(120), nin INT UNIQUE ); Values in the column nin (National Identification Number) have to be unique, but NULLs are allowed. DEFAULT Defines a default column value when a value is not specified for that column during INSERT. CREATE TABLE orders ( id INT PRIMARY KEY, value DECIMAL(10,2), order_date DEFAULT CURRENT_DATE ); If the order_date is not defined during INSERT, the default value will be the current date. CHECK Checks if the values in a column satisfy a stated condition ; rejects those that don’t. CREATE TABLE orders ( id INT PRIMARY KEY, value DECIMAL(10,2), check(value>0) ); Enforces a rule where an order value can't be zero if it is to be stored in the table orders. Additional SQL Interview Resources Along with this SQL interview cheat sheet, use these additional resources to review SQL interview questions and topics: Complete SQL Practice for Interviews Top 100+ SQL Interview Questions and Practice Exercises The Top 10 SQL JOIN Interview Questions with Answers Top 9 SQL GROUP BY Interview Questions Top 10 SQL Window Functions Interview Questions Top 5 SQL CTE Interview Questions Top 27 Advanced SQL Interview Questions with Answers 15 Tricky SQL Interview Questions for Experienced Users Going Beyond the SQL Interview Cheat Sheet We’ve covered all the fundamental concepts in this SQL Interview Cheat Sheet. To impress during your interview, you’ll need to show your mastery of these ideas. This cheat sheet will help you quickly look up the definition or an example of a particular SQL command. Use it whenever you get stuck on SQL terminology or concepts. However, this is not a standalone learning resource. It’s best used alongside our SQL Basics course, SQL Practice track, or other main resources for learning and practicing SQL. Combine these materials, keep practicing, and you’ll ace that SQL interview! Tags: Jobs And Career SQL Interview Questions cheat sheet