SQL Handbook
By Aditya Tyagi
LinkedIn | Aditya Tyagi
Table of Contents
Placeholder for table of contents 0
SQL Home
SQL is a standard language for accessing and manipulating databases.
SELECT version();
SELECT current_date;
SQL Intro
SQL stands for Structured Query Language. It is used to communicate with databases like PostgreSQL,
MySQL, SQL Server, etc.
SELECT 'Hello, SQL' AS message;
SQL Syntax
A SQL query typically starts with a command such as SELECT, followed by the columns and the table name.
SELECT column1, column2 FROM table_name;
SQL Select
The SELECT statement is used to fetch data from a database.
SELECT * FROM employees2;
SELECT firstname, salary FROM employees2;
SQL Select Distinct
DISTINCT removes duplicate rows from the result.
SELECT DISTINCT department FROM employees2;
SQL Where
WHERE filters records based on a condition.
SELECT * FROM employees2 WHERE salary > 50000;
SQL Order By
ORDER BY sorts the results in ascending or descending order.
SELECT * FROM employees2 ORDER BY salary DESC;
SQL And
AND is used to filter records based on multiple conditions.
SELECT * FROM employees2 WHERE department='Finance' AND salary > 50000;
SQL Or
OR is used when any one of the conditions must be true.
SELECT * FROM employees2 WHERE department='Finance' OR salary > 50000;
SQL Not
NOT is used to negate a condition.
SELECT * FROM employees2 WHERE NOT department='Sales';
SQL Insert Into
INSERT is used to add new rows into a table.
INSERT INTO employees2(empid, firstname, lastname, department, salary) VALUES
('E101','Aman','Tyagi','Finance',60000);
SQL Null Values
NULL represents missing or undefined data.
SELECT * FROM employees2 WHERE email IS NULL;
SQL Update
UPDATE modifies existing records.
UPDATE employees2 SET salary=70000 WHERE empid='E005';
SQL Delete
DELETE removes rows from a table.
DELETE FROM employees2 WHERE empid='E010';
SQL Select Top
PostgreSQL uses LIMIT instead of TOP to restrict results.
SELECT * FROM employees2 LIMIT 5;
SQL Aggregate Functions
Aggregate functions perform calculations on multiple values and return a single value.
SELECT AVG(salary) FROM employees2;
SELECT COUNT(*) FROM employees2;
SQL Min and Max
MIN returns the smallest value, MAX returns the largest value.
SELECT MIN(salary) FROM employees2;
SELECT MAX(salary) FROM employees2;
SQL Count
COUNT returns the number of rows that match a condition.
SELECT COUNT(*) FROM employees2;
SELECT COUNT(DISTINCT department) FROM employees2;
SQL Sum
SUM adds up numeric values in a column.
SELECT SUM(salary) FROM employees2;
SQL Avg
AVG calculates the average of numeric values.
SELECT AVG(salary) FROM employees2;
SQL Like
LIKE is used for pattern matching.
SELECT * FROM employees2 WHERE firstname LIKE 'A%';
SELECT * FROM employees2 WHERE lastname LIKE '%a';
SQL Wildcards
Wildcards are used with LIKE to search patterns.
SELECT * FROM employees2 WHERE firstname LIKE '_a%';
SQL In
IN allows multiple values in a WHERE clause.
SELECT * FROM employees2 WHERE department IN ('HR', 'Finance');
SQL Between
BETWEEN filters results within a given range.
SELECT * FROM employees2 WHERE salary BETWEEN 40000 AND 70000;
SQL Aliases
Aliases rename a table or column temporarily.
SELECT firstname AS emp_name, salary AS emp_salary FROM employees2;
SQL Joins
Joins combine rows from two or more tables based on related columns.
SELECT e.firstname, d.department_name FROM employees2 e JOIN departments d ON
e.department_id=d.id;
SQL Inner Join
INNER JOIN returns rows with matching values in both tables.
SELECT e.firstname, d.department_name FROM employees2 e INNER JOIN departments d ON
e.department_id=d.id;
SQL Left Join
LEFT JOIN returns all rows from the left table and matching rows from the right table.
SELECT e.firstname, d.department_name FROM employees2 e LEFT JOIN departments d ON
e.department_id=d.id;
SQL Right Join
RIGHT JOIN returns all rows from the right table and matching rows from the left table.
SELECT e.firstname, d.department_name FROM employees2 e RIGHT JOIN departments d ON
e.department_id=d.id;
SQL Full Join
FULL JOIN returns all rows when there is a match in one of the tables.
SELECT e.firstname, d.department_name FROM employees2 e FULL JOIN departments d ON
e.department_id=d.id;
