SQL Cheat Sheet - A Must-Have for Data Professionals!
- Debapritam Mishra
- 2 days ago
- 3 min read
Updated: 1 day ago
Basic SQL Commands
Start with the core commands that form the foundation of SQL queries. These are the building blocks for retrieving and manipulating data.
SELECT
Retrieve data from one or more tables.
```sql
SELECT column1, column2 FROM table_name;
```
WHERE
Filter records based on conditions.
```sql
SELECT * FROM employees WHERE department = 'Sales';
```
ORDER BY
Sort results by one or more columns.
```sql
SELECT * FROM products ORDER BY price DESC;
```
LIMIT
Restrict the number of rows returned.
```sql
SELECT * FROM customers LIMIT 10;
```
INSERT INTO
Add new records to a table.
```sql
INSERT INTO orders (order_id, customer_id, amount) VALUES (101, 5, 250);
```
UPDATE
Modify existing records.
```sql
UPDATE employees SET salary = salary * 1.1 WHERE performance = 'Excellent';
```
DELETE
Remove records from a table.
```sql
DELETE FROM sessions WHERE last_active < '2023-01-01';
```
Understanding Joins
Joins combine rows from two or more tables based on related columns. Mastering joins is crucial for working with relational databases.
INNER JOIN
Returns rows with matching values in both tables.
```sql
SELECT a.name, b.order_date
FROM customers a
INNER JOIN orders b ON a.customer_id = b.customer_id;
```
LEFT JOIN
Returns all rows from the left table and matched rows from the right table.
```sql
SELECT a.name, b.order_date
FROM customers a
LEFT JOIN orders b ON a.customer_id = b.customer_id;
```
RIGHT JOIN
Returns all rows from the right table and matched rows from the left table.
```sql
SELECT a.name, b.order_date
FROM customers a
RIGHT JOIN orders b ON a.customer_id = b.customer_id;
```
FULL OUTER JOIN
Returns rows when there is a match in one of the tables.
```sql
SELECT a.name, b.order_date
FROM customers a
FULL OUTER JOIN orders b ON a.customer_id = b.customer_id;
```
Essential SQL Functions
Functions help you manipulate data and perform calculations within queries.
Aggregate Functions
COUNT()
Counts rows or non-null values.
```sql
SELECT COUNT(*) FROM employees;
```
SUM()
Adds up numeric values.
```sql
SELECT SUM(sales) FROM orders WHERE year = 2023;
```
AVG()
Calculates average value.
```sql
SELECT AVG(salary) FROM employees;
```
MIN() and MAX()
Find minimum and maximum values.
```sql
SELECT MIN(price), MAX(price) FROM products;
```
String Functions
CONCAT()
Combine strings.
```sql
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
```
SUBSTRING()
Extract part of a string.
```sql
SELECT SUBSTRING(email, 1, 5) FROM users;
```
UPPER() and LOWER()
Change case of text.
```sql
SELECT UPPER(city) FROM locations;
```
Date Functions
CURRENT_DATE
Returns today’s date.
```sql
SELECT CURRENT_DATE;
```
DATE_ADD()
Add interval to a date.
```sql
SELECT DATE_ADD(order_date, INTERVAL 7 DAY) FROM orders;
```
DATEDIFF()
Calculate difference between two dates.
```sql
SELECT DATEDIFF(delivery_date, order_date) FROM orders;
```
Using Subqueries
Subqueries are queries nested inside other queries. They help break complex problems into smaller parts.
In SELECT statement
```sql
SELECT name, (SELECT MAX(salary) FROM employees) AS max_salary FROM employees;
```
In WHERE clause
```sql
SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');
```
In FROM clause
```sql
SELECT avg_salary FROM (SELECT AVG(salary) AS avg_salary FROM employees) AS sub;
```
Practice writing queries regularly to build familiarity.
Use the cheat sheet as a quick reminder but try to understand the logic behind each command.
Review common interview questions involving SQL.
SQL is a powerful tool for data professionals. This cheat sheet condenses key concepts and commands into one place to support your learning and daily work. Save it, refer to it, and practice consistently to improve your skills.
Your next step could be to apply these commands on real datasets or explore advanced topics like window functions and query optimization. With steady effort, you will gain confidence and efficiency in SQL.
Comments