Comprehensive SQL Notes for Placement Preparation
Of course! Here are comprehensive SQL notes tailored for placement preparation, covering everything from
the basics to advanced topics frequently asked in interviews.
1. Introduction to SQL & RDBMS
SQL (Structured Query Language) is the standard language for managing and manipulating relational
databases. It's a must-have skill for roles like Software Developer, Data Analyst, Data Scientist, and Business
Analyst.
* RDBMS (Relational Database Management System): A system for managing relational databases. Data is
stored in tables (relations) with rows and columns. Examples: MySQL, PostgreSQL, Oracle, SQL Server.
* DBMS vs RDBMS: All RDBMS are DBMS, but not all DBMS are RDBMS. An RDBMS specifically uses a
relational model (tables), while a DBMS is a broader term for any database management software.
2. Types of SQL Commands
SQL commands are broadly categorized into four types.
DDL (Data Definition Language)
Used to define or modify the database schema (structure).
* CREATE: To create databases, tables, views, etc.
CREATE TABLE Employees (
ID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary INT,
DepartmentID INT
);
* ALTER: To modify the structure of an existing table (add, delete, or modify columns).
ALTER TABLE Employees ADD Email VARCHAR(100);
* DROP: To permanently delete an entire database or table.
DROP TABLE Employees;
* TRUNCATE: To delete all data inside a table, but not the table itself. It's faster than DELETE and cannot be
rolled back.
TRUNCATE TABLE Employees;
Comprehensive SQL Notes for Placement Preparation
DML (Data Manipulation Language)
Used for managing data within the tables.
* INSERT: To add new rows of data.
INSERT INTO Employees (ID, FirstName, LastName, Salary, DepartmentID)
VALUES (1, 'Virat', 'Kohli', 90000, 101);
* UPDATE: To modify existing records.
UPDATE Employees SET Salary = 95000 WHERE ID = 1;
* DELETE: To remove existing records.
DELETE FROM Employees WHERE ID = 1;
[Interview Hotspot] Interview Hotspot: DELETE vs. TRUNCATE vs. DROP
| Feature | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| Action | Removes rows one by one. | Deletes all rows at once. | Removes the entire table structure and
data. |
| WHERE clause | Can be used. | Cannot be used. | Cannot be used. |
| Speed | Slower. | Faster. | Fastest. |
| Rollback | Can be rolled back. | Cannot be rolled back. | Cannot be rolled back. |
| Triggers | Fires triggers for each row. | Does not fire triggers. | Does not fire triggers. |
| Space Freed | Does not reset identity. | Resets identity counter. | Frees all table space. |
DQL (Data Query Language)
Used to fetch data from the database. This is the most frequently used command.
* SELECT: To retrieve data from one or more tables.
SELECT FirstName, Salary FROM Employees WHERE Salary > 50000;
DCL (Data Control Language)
Used to manage permissions and access rights.
* GRANT: Gives a user access privileges to the database.
* REVOKE: Takes back permissions from a user.
TCL (Transaction Control Language)
Used to manage transactions in the database.
Comprehensive SQL Notes for Placement Preparation
* COMMIT: Saves all the transactions to the database.
* ROLLBACK: Undoes transactions that have not been saved.
* SAVEPOINT: Sets a point within a transaction to which you can later roll back.
3. The SELECT Statement in Detail
The SELECT statement is the workhorse of SQL. Mastering its clauses is essential.
Core Clauses
* SELECT: Specifies the columns to be returned.
* FROM: Specifies the table to query.
* WHERE: Filters records based on a condition.
* GROUP BY: Groups rows that have the same values into summary rows.
* HAVING: Filters groups based on a condition (used after GROUP BY).
* ORDER BY: Sorts the result set in ascending (ASC) or descending (DESC) order.
* LIMIT / TOP: Restricts the number of rows returned.
Order of Execution: [Note]
SQL does not execute clauses in the order they are written. The logical processing order is:
* FROM & JOINs
* WHERE
* GROUP BY
* HAVING
* SELECT
* ORDER BY
* LIMIT / TOP
Filtering and Operators
* Comparison: =, != or <>, >, <, >=, <=
* Logical: AND, OR, NOT
* Range/List: BETWEEN, IN, NOT IN
* Pattern Matching: LIKE (uses wildcards: % for zero or more characters, _ for a single character).
-- Find employees whose first name starts with 'A'
SELECT * FROM Employees WHERE FirstName LIKE 'A%';
* Null Values: IS NULL, IS NOT NULL
Comprehensive SQL Notes for Placement Preparation
Aggregate Functions
These functions perform a calculation on a set of values and return a single value.
* COUNT(): Counts the number of rows. COUNT(*) counts all rows, COUNT(column_name) counts
non-NULL values in that column.
* SUM(): Calculates the sum of values.
* AVG(): Calculates the average of values.
* MIN(): Returns the minimum value.
* MAX(): Returns the maximum value.
-- Get the number of employees and their average salary per department
SELECT
DepartmentID,
COUNT(*) AS NumberOfEmployees,
AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID;
[Interview Hotspot] Interview Hotspot: WHERE vs. HAVING
* WHERE is used to filter individual rows before any aggregation (GROUP BY) happens.
* HAVING is used to filter groups after aggregation has already occurred.
-- Get departments with more than 5 employees and an average salary over 60000
SELECT
DepartmentID,
COUNT(*) AS NumberOfEmployees,
AVG(Salary) AS AverageSalary
FROM Employees
WHERE Salary > 30000
GROUP BY DepartmentID
HAVING COUNT(*) > 5 AND AVG(Salary) > 60000;
4. Joins [Joins]
Joins are used to combine rows from two or more tables based on a related column between them.
Departments table:
DepartmentID | DepartmentName
Comprehensive SQL Notes for Placement Preparation
------------------------------
101 | Engineering
102 | HR
103 | Sales
* INNER JOIN:
SELECT [Link], [Link]
FROM Employees E
INNER JOIN Departments D ON [Link] = [Link];
* LEFT JOIN:
SELECT [Link], [Link]
FROM Employees E
LEFT JOIN Departments D ON [Link] = [Link];
* RIGHT JOIN, FULL OUTER JOIN, SELF JOIN, CROSS JOIN definitions and use cases.
5. Advanced SQL Topics
Subqueries:
-- Find employees who have a salary greater than the average salary
SELECT FirstName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
CTEs:
WITH DepartmentCounts AS (
SELECT DepartmentID, COUNT(*) as EmployeeCount
FROM Employees
GROUP BY DepartmentID
)
SELECT [Link], [Link]
FROM Departments D
JOIN DepartmentCounts DC ON [Link] = [Link]
WHERE [Link] > 3;
Comprehensive SQL Notes for Placement Preparation
Window Functions:
-- Find the 3rd highest salary using DENSE_RANK
WITH RankedSalaries AS (
SELECT
FirstName,
Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) as SalaryRank
FROM Employees
)
SELECT FirstName, Salary
FROM RankedSalaries
WHERE SalaryRank = 3;
Indexing: Speeds up retrieval, slows down modification.
ACID Properties: Atomicity, Consistency, Isolation, Durability
Normalization: 1NF, 2NF, 3NF
6. Final Tips for Placements [Tips]
* Practice on LeetCode, HackerRank, StrataScratch.
* Explain logic during interviews.
* Focus on JOINs, GROUP BY, HAVING.
* Think about NULLs, edge cases.
* Learn basics of optimization, indexing.