Structured Query Language (SQL), is a programming language that is used to manage relational databases, and perform operations on the data in them.
Sample Data
1. SIMPLE QUERYING
-- Fetch / retrieve all columns from a table.
SELECT *
FROM table_name;
-- Fetch columns from the table and order the results.
SELECT col1, col2
FROM table_name
ORDER BY col1 DESC;
ALIASES
-- COLUMNS
SELECT col1 AS col_11
FROM table_name;
-- TABLES
SELECT col1, col1_1, col1_2
FROM table_name AS t1
JOIN name_table AS t2
ON t1.col1 = t2.col1_1;
2. FILTERING THE OUTPUT
Comparison
SELECT *
FROM table_name
WHERE col1 BETWEEN range1 AND range2;
-- aggregate functions
SELECT *
FROM table_name
WHERE col1 > 3;
TEXT OPERATORS
-- List with Names starting with Mar
SELECT *
FROM employees
WHERE first_name LIKE ('Mar%');
-- list with NAMES ENDING WITH 'rk'
SELECT *
FROM employees
WHERE first_name LIKE ('%rk');
-- Others
SELECT *
FROM employees
WHERE first_name
NOT IN ('John' , 'Mark', 'Jacob');
3. QUERYING MULTIPLE TABLES
-- CROSS JOIN
-- Connecting two tables
SELECT
dm.*, d.*
FROM
dept_manager dm
CROSS JOIN
departments d
ORDER BY dm.emp_no, d.dept_no;
-- INNER JOIN
-- Null values are also not displayed
SELECT m.dept_no, m.emp_no, d.dept_name
FROM dept_manager_dup m
INNER JOIN
departments_dup d on m.dept_no = d.dept_no
GROUP BY m.emp_no
ORDER BY m.dept_no;
-- LEFT JOIN (interchangeable with LEFT OUTER JOIN)
-- All matching values of the two tables + all values from the left table that matches no values
SELECT m.dept_no, m.emp_no, d.dept_name
FROM dept_manager_dup m
LEFT JOIN departments_dup d ON m.dept_no = d.dept_no
GROUP BY m.emp_no
ORDER BY m.dept_no;
-- RIGHT JOIN
-- Identical to Left JOIN, with the only difference being that the direction of the operation is inverted.
SELECT m.dept_no, m.emp_no, m.dept_name
FROM dept_manager_dup m,
departments_dup d
WHERE m.dept_no = d.dept_no
ORDER BY m.dept_no DESC;
-- SQL Self Join
-- Applied when a table must join itself (Combining rows OF A TABLE with other rows of same table)
-- From the emp_manager table, extract the record data only of those employees who are managers as well.
SELECT *
FROM emp_manager em
ORDER BY em.emp_no;
SELECT em1.*
FROM emp_manager em1
JOIN emp_manager em2 ON em1.emp_no = em2.manager_no;
4. SUBQUERIES
SQL Sub-queries with IN Nested Inside WHERE Queries inside queries
also called inner queries/nested queries/inner select/ outer select.
SELECT * FROM dept_manager;
SELECT e.first_name, e.last_name
FROM employees e
WHERE e.emp_no IN (
SELECT dmd.emp_no
FROM dept_manager_dup dmd
);
5. SET OPERATIONS
Set operations are used to combine the results of two or more queries into a single result. The combined queries must return the same number of columns and compatible data types. The names of the corresponding columns can be different.
-- UNION
SELECT
e.emp_no, e.first_name, e.last_name,
NULL AS dept_no, NULL AS from_date
FROM employees_dup e
WHERE e.emp_no = 10001
UNION -- difference
SELECT
NULL AS emp_no, NULL AS first_name,
NULL AS last_name, dmd.dept_no,
dmd.from_date
FROM dept_manager_dup dmd;
-- UNION ALL
SELECT
e.emp_no, e.first_name, e.last_name,
NULL AS dept_no, NULL AS from_date
FROM employees_dup e
WHERE e.emp_no = 10001
UNION ALL -- difference
SELECT
NULL AS emp_no, NULL AS first_name,
NULL AS last_name, dmd.dept_no,
dmd.from_date
FROM dept_manager_dup dmd;
NOTE: I highly recommend MySQL for Data Analytics and Business Intelligence tutorial for SQL beginners with focus on Data analysis.
Top comments (1)
Well done. Thanks!