SQL Basics Cheat Sheet
Structured Query Language (SQL) is the standard language for interacting with relational databases. Mastering SQL is essential for developers, data analysts, and database administrators, whether they’re managing data, running queries, or analyzing datasets. The SQL cheat sheet provides tutorial tips, explains how SQL compilers work, and covers SQL basics.
1. Setting Up Your SQL Environment
Before diving into SQL, set up an environment where you can write and test SQL commands. Online SQL compilers such as MySQL Workbench, PostgreSQL, SQLite, or online tools such as DB Fiddle and SQL Fiddle provide interactive platforms for running SQL code. These compilers interpret your SQL statements and execute them against a database.
2. Basic SQL Syntax
SQL statements are not case-sensitive, but keywords are conventionally written in uppercase, as emphasized in many SQL tutorials. Here’s the standard structure:
SELECT column1, column2 FROM table_name WHERE condition;
3. Common SQL Commands
Data Retrieval (SELECT)
The SELECT statement is used to fetch data from a table.
SELECT * FROM employees; — Retrieve all columns
SELECT name, age FROM employees WHERE age > 30; — Retrieve specific columns with a condition
Insert Data (INSERT)
Insert new records into a table.
INSERT INTO employees (name, age, department) VALUES (‘Alice’, 30, ‘HR’);
Update Data (UPDATE)
Modify existing data in a table.
UPDATE employees SET age = 31 WHERE name = ‘Alice’;
Delete Data (DELETE)
Remove records from a table.
DELETE FROM employees WHERE age < 25;
Create Table (CREATE)
Create a new table in the database.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
department VARCHAR(50)
);
Alter Table (ALTER)
Modify an existing table structure.
ALTER TABLE employees ADD salary DECIMAL(10, 2);
Drop Table (DROP)
Remove a table completely.
DROP TABLE employees;
4. SQL Functions and Aggregates
SQL provides built-in functions for data analysis and manipulation.
- Aggregate Functions
SELECT COUNT(*) FROM employees; — Count the number of rows
SELECT AVG(age) FROM employees; — Calculate average age
SELECT MAX(salary) FROM employees; — Find the highest salary
String Functions
SELECT UPPER(name) FROM employees; — Convert names to uppercase
SELECT CONCAT(name, ‘ works in ‘, department) FROM employees; — Concatenate strings
Date Functions
SELECT CURRENT_DATE; — Get the current date
SELECT DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY); — Add 7 days to the current date
5. Working with Joins
Joins combine data from multiple tables based on related columns.
- Inner Join
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
Left Join
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
Self Join
SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.id;
6. Filtering Data (WHERE, LIKE, IN, BETWEEN)
- WHERE Clause
SELECT * FROM employees WHERE age > 30;
LIKE Operator (pattern matching)
SELECT * FROM employees WHERE name LIKE ‘A%’; — Names starting with ‘A’
IN Operator
SELECT * FROM employees WHERE department IN (‘HR’, ‘Finance’);
BETWEEN Operator
SELECT * FROM employees WHERE age BETWEEN 25 AND 35;
7. SQL Compiler Debugging
SQL compilers can provide error messages to help you debug queries.
- Syntax Errors: Ensure proper use of keywords and semicolons.
- Logical Errors: Check for incorrect conditions in WHERE or JOIN.
- Run-Time Errors: Verify the existence of tables and columns.
Use features in SQL compilers like MySQL Workbench or pgAdmin to visualize query execution plans and optimize performance.
8. Advanced SQL Cheat Sheet
Window Functions
Perform calculations across rows.
SELECT name, department, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
Subqueries
Embed a query within another query.
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
Common Table Expressions (CTEs)
Simplify complex queries with temporary result sets.
WITH dept_avg AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT * FROM dept_avg WHERE avg_salary > 50000;
9. Tips for Mastering SQL
- Start with basic tutorials and practice commands using an SQL compiler.
- Use online platforms like SQLZoo or W3Schools for guided SQL exercises.
- Keep this SQL cheat sheet handy as a quick reference during coding.
- Experiment with advanced features like stored procedures and triggers.
By combining this tutorial, your SQL compiler, and this cheat sheet, you’ll gain proficiency in SQL and confidently manage databases!
