Essential SQL Queries Guide
1. Basic Queries
-- Select all columns from Employees
SELECT * FROM Employees;
-- Select specific columns
SELECT first_name, last_name FROM Employees;
-- WHERE clause
SELECT * FROM Employees WHERE department_id = 3;
-- ORDER BY
SELECT * FROM Employees ORDER BY salary DESC;
-- DISTINCT
SELECT DISTINCT department_id FROM Employees;
-- LIMIT
SELECT * FROM Employees LIMIT 5;
2. Filtering and Pattern Matching
-- LIKE
SELECT * FROM Employees WHERE last_name LIKE 'S%';
-- IN
SELECT * FROM Employees WHERE department_id IN (1, 2, 3);
-- BETWEEN
SELECT * FROM Employees WHERE salary BETWEEN 50000 AND 80000;
-- IS NULL
SELECT * FROM Employees WHERE manager_id IS NULL;
3. Aggregate Functions
-- COUNT
SELECT COUNT(*) FROM Employees;
-- SUM
SELECT SUM(salary) FROM Employees;
-- AVG
SELECT AVG(salary) FROM Employees;
-- MIN and MAX
SELECT MIN(salary), MAX(salary) FROM Employees;
Essential SQL Queries Guide
-- GROUP BY
SELECT department_id, AVG(salary) FROM Employees GROUP BY department_id;
-- HAVING
SELECT department_id, AVG(salary) as avg_salary FROM Employees GROUP BY department_id
HAVING avg_salary > 60000;
4. Joins
-- INNER JOIN
SELECT e.first_name, d.department_name
FROM Employees e
INNER JOIN Departments d ON e.department_id = [Link];
-- LEFT JOIN
SELECT e.first_name, d.department_name
FROM Employees e
LEFT JOIN Departments d ON e.department_id = [Link];
-- RIGHT JOIN
SELECT e.first_name, d.department_name
FROM Employees e
RIGHT JOIN Departments d ON e.department_id = [Link];
-- FULL OUTER JOIN
SELECT e.first_name, d.department_name
FROM Employees e
FULL OUTER JOIN Departments d ON e.department_id = [Link];
-- SELF JOIN
SELECT e1.first_name, e2.first_name AS manager_name
FROM Employees e1
JOIN Employees e2 ON e1.manager_id = [Link];
5. Subqueries
-- Subquery in SELECT
SELECT first_name, (SELECT department_name FROM Departments d WHERE [Link] =
e.department_id) AS dept
FROM Employees e;
-- Subquery in FROM
SELECT dept_avg.salary_avg FROM (SELECT AVG(salary) AS salary_avg FROM Employees) AS
dept_avg;
-- Subquery in WHERE
SELECT * FROM Employees WHERE department_id = (SELECT id FROM Departments WHERE
Essential SQL Queries Guide
department_name = 'HR');
6. Set Operations
-- UNION
SELECT first_name FROM Employees
UNION
SELECT name FROM Managers;
-- INTERSECT
SELECT first_name FROM Employees
INTERSECT
SELECT name FROM Managers;
-- EXCEPT
SELECT first_name FROM Employees
EXCEPT
SELECT name FROM Managers;
7. Data Manipulation
-- INSERT
INSERT INTO Employees (first_name, last_name, department_id, salary) VALUES ('John',
'Doe', 2, 60000);
-- UPDATE
UPDATE Employees SET salary = salary * 1.1 WHERE department_id = 2;
-- DELETE
DELETE FROM Employees WHERE salary < 30000;
8. Data Definition
-- CREATE TABLE
CREATE TABLE Departments (
id INT PRIMARY KEY,
department_name VARCHAR(100)
);
-- ALTER TABLE
ALTER TABLE Employees ADD COLUMN hire_date DATE;
-- DROP TABLE
DROP TABLE OldProjects;
9. Constraints
Essential SQL Queries Guide
-- PRIMARY KEY and FOREIGN KEY
CREATE TABLE Projects (
id INT PRIMARY KEY,
project_name VARCHAR(100),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES Employees(id)
);
-- UNIQUE and NOT NULL
CREATE TABLE Users (
id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL
);
-- CHECK
CREATE TABLE Salaries (
id INT,
amount INT CHECK (amount > 0)
);
10. Views, Indexes, and Stored Procedures
-- View
CREATE VIEW HighEarners AS
SELECT first_name, last_name, salary FROM Employees WHERE salary > 80000;
-- Index
CREATE INDEX idx_salary ON Employees(salary);
-- Stored Procedure (MySQL syntax)
DELIMITER //
CREATE PROCEDURE RaiseSalary(IN emp_id INT, IN percent DECIMAL(5,2))
BEGIN
UPDATE Employees SET salary = salary * (1 + percent / 100) WHERE id = emp_id;
END;
//
DELIMITER ;