//
While basic SELECT, INSERT, UPDATE, and DELETE commands allow for simple data management, the true power of relational databases lies in their ability to combine data from multiple tables and perform complex mathematical aggregations across millions of rows efficiently.
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Consider two tables: Students (ID, Name, Dept_ID) and Departments (Dept_ID, Dept_Name).
Returns records that have matching values in both tables. If a student does not belong to any department, they will not appear in the result.
SELECT Students.Name, Departments.Dept_Name
FROM Students
INNER JOIN Departments ON Students.Dept_ID = Departments.Dept_ID;
Returns all records from the left table (Students), and the matched records from the right table. If a student does not have a department, they will still appear in the result, but the Dept_Name will be NULL.
SELECT Students.Name, Departments.Dept_Name
FROM Students
LEFT JOIN Departments ON Students.Dept_ID = Departments.Dept_ID;
Returns all records from the right table (Departments), and the matched records from the left table.
Returns all records when there is a match in either left or right table. It is essentially a combination of a LEFT JOIN and a RIGHT JOIN.
SQL provides built-in functions to perform calculations on a set of values and return a single value.
COUNT(): Returns the number of rows.SUM(): Returns the total sum of a numeric column.AVG(): Returns the average value of a numeric column.MIN(): Returns the smallest value.MAX(): Returns the largest value.-- Find the highest salary in the company
SELECT MAX(Salary) FROM Employees;
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of employees in each department". It is typically used with aggregate functions.
-- Count how many students belong to each department
SELECT Dept_ID, COUNT(ID) as Total_Students
FROM Students
GROUP BY Dept_ID;
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions. WHERE filters rows before aggregation, while HAVING filters groups after aggregation.
-- Find departments that have MORE than 50 students
SELECT Dept_ID, COUNT(ID) as Total_Students
FROM Students
GROUP BY Dept_ID
HAVING COUNT(ID) > 50;
A Subquery is a query nested inside another query.
-- Find the names of students who belong to the 'Computer Science' department
SELECT Name
FROM Students
WHERE Dept_ID IN (
SELECT Dept_ID
FROM Departments
WHERE Dept_Name = 'Computer Science'
);