SQL:
SQL
“SQL is a query language used to manage data in relational databases.”
MySQL
“MySQL is an RDBMS that uses SQL to store and manage data.”
GROUP BY → used to group rows for aggregation
ORDER BY → used to sort the result
Count how many employees are in each department and show it in order.
SELECT department, COUNT(*)
FROM employees
GROUP BY department
ORDER BY department;
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
Wrong example:
SELECT department, COUNT(*)
FROM employees;
👉 This will give error (because no GROUP BY)
Count all rows
SELECT COUNT(*) FROM employees;
Count non-null values in a column
SELECT COUNT(salary) FROM employees;
Count unique values
SELECT COUNT(DISTINCT department) FROM employees;
GROUP BY is used when you want to group rows and apply functions (COUNT, SUM, AVG,
etc.)
Count employees in each department”
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
✔ Another example:
👉 “Average salary per department”
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
Select all
SELECT * FROM employees;
2. With condition
SELECT * FROM employees WHERE salary > 50000;
🔹 Aggregate Functions
3. Count employees
SELECT COUNT(*) FROM employees;
4. Average salary
SELECT AVG(salary) FROM employees;
JOIN (🔥 MOST ASKED)
Assume 2 tables:
employees(emp_id, name, dept_id)
departments(dept_id, dept_name)
6. INNER JOIN
SELECT [Link], d.dept_name
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;
👉 Say: “Returns matching records from both tables”
7. LEFT JOIN
SELECT [Link], d.dept_name
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id;
👉 Say: “Returns all employees even if no department”
🔹 Subqueries (IMPORTANT)
8. Highest salary
SELECT name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
9. Second highest salary
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
🔹 ORDER BY
10. Sort salary descending
SELECT * FROM employees
ORDER BY salary DESC;
🔹 HAVING (🔥 tricky but asked)
👉 Difference:
WHERE → before grouping
HAVING → after grouping
11. Departments with more than 5 employees
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
🔥 4. MUST-KNOW THEORY (1-line answers)
What is JOIN?
👉 Combines data from multiple tables
Types of JOIN
INNER JOIN → matching rows
LEFT JOIN → all left + matched
RIGHT JOIN → all right + matched
What is indexing?
👉 Improves query performance
What is normalization?
👉 Reduces data redundancy
What is primary key?
👉 Unique identifier for a row
What is foreign key?
👉 Links two tables
Join employee + department
SELECT [Link], d.dept_name
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;
Show all employees even without department
SELECT [Link], d.dept_name
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id;
Count employees department-wise:
SELECT d.dept_name, COUNT(e.emp_id)
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id
GROUP BY d.dept_name;
Simple formula for JOIN
SELECT columns
FROM table1 alias
JOIN table2 alias ON condition
[Link] = table alias + column
Combine rows where dept_id matches in both tables
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id
. Employees who don’t have a department
SELECT [Link]
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;
LEFT JOIN → keeps all employees
NULL → means no matching department
Departments with NO employees
SELECT d.dept_name
FROM departments d
LEFT JOIN employees e
ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;
All departments
Find ones with no employees
Count employees in each department
SELECT d.dept_name, COUNT(e.emp_id)
FROM departments d
LEFT JOIN employees e
ON d.dept_id = e.dept_id
GROUP BY d.dept_name;
Why LEFT JOIN?
So even departments with 0 employees appear
Highest salary employee in each department
SELECT [Link], [Link], e.dept_id
FROM employees e
WHERE [Link] = (
SELECT MAX(salary)
FROM employees
WHERE dept_id = e.dept_id
);
Employees and departments even if missing (FULL JOIN logic)
SELECT [Link], d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT [Link], d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
Count employees only if more than 2 in department
SELECT dept_id, COUNT(*)
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 2;
Join 3 tables:
SELECT [Link], d.dept_name, p.project_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN projects p ON e.emp_id = p.emp_id;
. Missing data → LEFT JOIN + NULL
👉 2. Matching only → INNER JOIN
👉 3. Counting → GROUP BY
👉 4. Filtering groups → HAVING
👉 5. Same table → SELF JOIN
Find employees who work in same department(SELF JOIN):
SELECT [Link], [Link], e1.dept_id
FROM employees e1
JOIN employees e2
ON e1.dept_id = e2.dept_id
AND e1.emp_id <> e2.emp_id;
e1 → first copy of employees
e2 → second copy of employees
To compare rows within the same table
Condition:
e1.dept_id = e2.dept_id
👉 Same department
Condition:
e1.emp_id <> e2.emp_id
👉 Not the same person
Self join is used to join a table with itself to compare rows within the same table.
UNION means:
Combine results of two queries and remove duplicates
FULL JOIN IS ACHIEVED BY:
SELECT [Link], d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT [Link], d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
FULL JOIN means:
Show all data from both tables (matched + unmatched)
MySQL does NOT support FULL JOIN directly
🔹 So we do this trick:
👉 LEFT JOIN → gives:
all employees
matching departments
👉 RIGHT JOIN → gives:
all departments
matching employees
👉 UNION → combines both
UNION Rules (VERY IMPORTANT)
👉 Both queries must have:
Same number of columns
Same order of columns
Compatible data types
UNION → removes duplicates
UNION ALL → keeps duplicates