The Ultimate SQL Cheatsheet for Developers, Analysts, and Architects

By /

Listen to this Post

Featured Image
SQL is the backbone of data manipulation and retrieval. Mastering it is essential for developers, analysts, and architects. Below is a comprehensive breakdown of SQL commands with practical examples.

SQL Basics

-- Retrieve all data from a table 
SELECT  FROM employees;

-- Filter data using WHERE 
SELECT name, salary FROM employees WHERE salary > 50000;

-- Sort results with ORDER BY 
SELECT  FROM employees ORDER BY hire_date DESC;

-- Group data with GROUP BY 
SELECT department, AVG(salary) FROM employees GROUP BY department;

-- Filter grouped data with HAVING 
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000;

-- Limit results 
SELECT  FROM employees LIMIT 10;

SQL Joins

-- INNER JOIN (only matching records) 
SELECT e.name, d.department_name 
FROM employees e 
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN (all left + matching right) 
SELECT e.name, d.department_name 
FROM employees e 
LEFT JOIN departments d ON e.dept_id = d.id;

-- RIGHT JOIN (all right + matching left) 
SELECT e.name, d.department_name 
FROM employees e 
RIGHT JOIN departments d ON e.dept_id = d.id;

-- FULL JOIN (all records from both tables) 
SELECT e.name, d.department_name 
FROM employees e 
FULL JOIN departments d ON e.dept_id = d.id;

Table Operations

-- Create a table 
CREATE TABLE employees ( 
id INT PRIMARY KEY, 
name VARCHAR(100), 
salary DECIMAL(10, 2), 
hire_date DATE 
);

-- Delete a table 
DROP TABLE employees;

-- Modify a table 
ALTER TABLE employees ADD COLUMN department VARCHAR(50);

-- Remove all data but keep structure 
TRUNCATE TABLE employees;

Data Manipulation

-- Insert data 
INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 75000);

-- Update data 
UPDATE employees SET salary = 80000 WHERE id = 1;

-- Delete data 
DELETE FROM employees WHERE id = 1;

Constraints

-- Primary Key 
CREATE TABLE users ( 
user_id INT PRIMARY KEY, 
username VARCHAR(50) 
);

-- Foreign Key 
CREATE TABLE orders ( 
order_id INT PRIMARY KEY, 
user_id INT, 
FOREIGN KEY (user_id) REFERENCES users(user_id) 
);

-- Unique Constraint 
ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

-- Check Constraint 
ALTER TABLE employees ADD CONSTRAINT salary_check CHECK (salary > 0);

Common Functions

-- String functions 
SELECT UPPER(name), LENGTH(name) FROM employees;

-- Numeric functions 
SELECT ROUND(AVG(salary), 2) FROM employees;

-- Date functions 
SELECT NOW(), EXTRACT(YEAR FROM hire_date) FROM employees;

Performance Optimization

-- Create an index 
CREATE INDEX idx_employee_name ON employees(name);

-- Analyze query performance 
EXPLAIN SELECT  FROM employees WHERE salary > 50000;

Views & Triggers

-- Create a view 
CREATE VIEW high_paid_employees AS 
SELECT  FROM employees WHERE salary > 100000;

-- Create a trigger 
CREATE TRIGGER log_salary_changes 
AFTER UPDATE ON employees 
FOR EACH ROW 
INSERT INTO salary_logs (emp_id, old_salary, new_salary) 
VALUES (OLD.id, OLD.salary, NEW.salary);

Aggregate Functions

SELECT 
COUNT() AS total_employees, 
SUM(salary) AS total_salary, 
AVG(salary) AS avg_salary, 
MIN(salary) AS min_salary, 
MAX(salary) AS max_salary 
FROM employees;

You Should Know:

Related Posts: