SQL Complete Study Guide
SQL
Complete Study Guide
From Beginner to Advanced — Notes & Practice Questions
Topics Covered
Databases & Tables • SELECT & Filtering • Joins • Aggregations • Subqueries • DDL • Indexes
• Transactions • Window Functions • Stored Procedures
Page 1
SQL Complete Study Guide
UNIT 1: Introduction to Databases & SQL
1. What is a Database?
A database is an organized collection of structured data stored electronically. A Database Management
System (DBMS) is software that manages databases. SQL (Structured Query Language) is the
standard language for interacting with relational databases.
Types of Databases
• Relational (RDBMS) — MySQL, PostgreSQL, SQL Server, Oracle, SQLite
• NoSQL — MongoDB, Cassandra, Redis (not covered in SQL)
• NewSQL — CockroachDB, Spanner
SQL databases store data in tables (rows and columns), similar to an Excel spreadsheet.
KEY IDEA
Each table represents a real-world entity (e.g., Customers, Orders, Products).
Common SQL Databases
Database Best For Notes
MySQL Web apps, beginners Free, widely used
PostgreSQL Complex queries, open source Advanced features
SQL Server Enterprise, Microsoft stack Paid + free edition
SQLite Mobile, embedded apps No server needed
Practice Questions — Unit 1
Q1. What does SQL stand for?
Answer: Structured Query Language.
Q2. What is the difference between a DBMS and RDBMS?
Answer: A DBMS is general software for managing databases. An RDBMS (Relational DBMS)
specifically manages relational databases using tables, rows, columns, and supports SQL.
Q3. Name four popular relational database systems.
Answer: MySQL, PostgreSQL, Microsoft SQL Server, SQLite, Oracle (any four).
Q4. What is a table in SQL?
Answer: A table is a structured collection of data organized into rows (records) and columns (fields).
Each table represents one entity like Customers or Products.
Page 2
SQL Complete Study Guide
Q5. What is a primary key?
Answer: A primary key is a column (or set of columns) that uniquely identifies each row in a table. It
cannot be NULL and must be unique.
Page 3
SQL Complete Study Guide
UNIT 2: Data Types & Table Structure
2. SQL Data Types
Every column in a SQL table has a data type that defines what kind of data it can store.
Numeric Types
• INT / INTEGER — Whole numbers (e.g., age, quantity)
• BIGINT — Large whole numbers
• DECIMAL(p, s) / NUMERIC — Exact decimal numbers (p = total digits, s = decimal places)
• FLOAT / REAL / DOUBLE — Approximate decimal numbers
• SMALLINT, TINYINT — Small integer ranges
String Types
• CHAR(n) — Fixed-length string (pads with spaces)
• VARCHAR(n) — Variable-length string (most common)
• TEXT — Long text with no length limit
Date & Time Types
• DATE — Date only (YYYY-MM-DD)
• TIME — Time only (HH:MM:SS)
• DATETIME / TIMESTAMP — Date and time combined
Other Types
• BOOLEAN / BIT — True/False (1/0)
• BLOB — Binary data (images, files)
• NULL — Represents a missing/unknown value
IMPORTA NULL is not the same as 0 or empty string ''. NULL means the value is unknown. Use IS
NT NULL or IS NOT NULL to check for it, not = NULL.
Creating a Table — DDL Basics
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
Page 4
SQL Complete Study Guide
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE,
Salary DECIMAL(10,2) DEFAULT 0.00,
HireDate DATE,
DeptID INT
);
Constraints
• PRIMARY KEY — Uniquely identifies each row
• NOT NULL — Column must have a value
• UNIQUE — All values in column must be different
• DEFAULT — Sets default value if none provided
• CHECK — Validates data with a condition
• FOREIGN KEY — Links to primary key in another table
Practice Questions — Unit 2
Q6. What is the difference between CHAR and VARCHAR?
Answer: CHAR(n) stores fixed-length strings (always uses n bytes). VARCHAR(n) stores variable-
length strings (only uses as many bytes as the actual data). VARCHAR is more storage-efficient for
varying-length data.
Q7. What constraint ensures a column cannot have duplicate values?
Answer: The UNIQUE constraint ensures all values in a column are distinct.
Q8. How do you check if a column value is NULL?
Answer: Use IS NULL (e.g., WHERE salary IS NULL). You cannot use = NULL because NULL is not
equal to anything, not even itself.
Q9. What does DECIMAL(8,2) mean?
Answer: It stores numbers with up to 8 total digits, where 2 are after the decimal point. E.g.,
123456.78 is valid.
Q10. Write a CREATE TABLE statement for a Students table with ID, name, email, and
enrollment date.
Answer: CREATE TABLE Students (StudentID INT PRIMARY KEY, Name VARCHAR(100) NOT
NULL, Email VARCHAR(100) UNIQUE, EnrollDate DATE);
Page 5
SQL Complete Study Guide
UNIT 3: SELECT — Querying Data
3. The SELECT Statement
SELECT is the most used SQL command. It retrieves data from one or more tables.
Basic Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column ASC|DESC
LIMIT n;
SELECT Examples
-- Select all columns
SELECT * FROM Employees;
-- Select specific columns
SELECT FirstName, LastName, Salary FROM Employees;
-- Select with alias
SELECT FirstName AS First, Salary * 12 AS AnnualSalary FROM Employees;
-- Remove duplicates
SELECT DISTINCT DeptID FROM Employees;
WHERE Clause — Filtering Rows
Use WHERE to filter records. Operators:
• Comparison: =, != (or <>), <, >, <=, >=
• Range: BETWEEN a AND b
• List: IN (val1, val2, ...)
• Pattern: LIKE 'pattern%' (% = any chars, _ = one char)
• Null check: IS NULL / IS NOT NULL
• Logical: AND, OR, NOT
SELECT * FROM Employees WHERE Salary > 50000;
SELECT * FROM Employees WHERE DeptID IN (1, 2, 3);
SELECT * FROM Employees WHERE LastName LIKE 'S%';
SELECT * FROM Employees WHERE Salary BETWEEN 40000 AND 80000;
SELECT * FROM Employees WHERE Email IS NOT NULL;
Page 6
SQL Complete Study Guide
SELECT * FROM Employees WHERE DeptID = 2 AND Salary > 60000;
ORDER BY & LIMIT
-- Sort ascending (default)
SELECT * FROM Employees ORDER BY LastName ASC;
-- Sort descending
SELECT * FROM Employees ORDER BY Salary DESC;
-- Sort by multiple columns
SELECT * FROM Employees ORDER BY DeptID ASC, Salary DESC;
-- Limit results
SELECT * FROM Employees ORDER BY Salary DESC LIMIT 5;
Practice Questions — Unit 3
Q11. What does SELECT * mean?
Answer: It selects all columns from the specified table. While convenient, it's better practice to name
specific columns in production queries.
Q12. How do you find all employees whose name starts with 'A'?
Answer: SELECT * FROM Employees WHERE FirstName LIKE 'A%';
Q13. How do you get the top 3 highest-paid employees?
Answer: SELECT * FROM Employees ORDER BY Salary DESC LIMIT 3;
Q14. What is the difference between WHERE and HAVING?
Answer: WHERE filters rows before grouping (used with SELECT). HAVING filters groups after
GROUP BY is applied. You cannot use aggregate functions in WHERE.
Q15. Write a query to find employees with salary between 50000 and 80000 in department
3.
Answer: SELECT * FROM Employees WHERE Salary BETWEEN 50000 AND 80000 AND DeptID =
3;
Q16. What does DISTINCT do?
Answer: DISTINCT removes duplicate rows from the result set. For example, SELECT DISTINCT
DeptID FROM Employees returns each unique department ID only once.
Page 7
SQL Complete Study Guide
UNIT 4: Aggregate Functions & GROUP BY
4. Aggregate Functions
Aggregate functions perform calculations on sets of rows and return a single value.
Function Description Example
COUNT(*) Count all rows SELECT COUNT(*) FROM Employees
COUNT(col) Count non-NULL values SELECT COUNT(Email) FROM Employees
SUM(col) Sum of values SELECT SUM(Salary) FROM Employees
AVG(col) Average of values SELECT AVG(Salary) FROM Employees
MAX(col) Highest value SELECT MAX(Salary) FROM Employees
MIN(col) Lowest value SELECT MIN(Salary) FROM Employees
GROUP BY & HAVING
GROUP BY groups rows with the same value in a column. HAVING filters the groups.
-- Count employees per department
SELECT DeptID, COUNT(*) AS EmpCount
FROM Employees
GROUP BY DeptID;
-- Average salary per department, only show groups with avg > 60000
SELECT DeptID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DeptID
HAVING AVG(Salary) > 60000;
-- Full clause order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY →
LIMIT
SELECT DeptID, COUNT(*) AS Total, MAX(Salary) AS TopPay
FROM Employees
WHERE HireDate > '2020-01-01'
GROUP BY DeptID
HAVING COUNT(*) >= 3
ORDER BY TopPay DESC;
CLAUSE Always write clauses in this order: SELECT → FROM → WHERE → GROUP BY →
ORDER HAVING → ORDER BY → LIMIT. This is also the logical execution order.
Page 8
SQL Complete Study Guide
Practice Questions — Unit 4
Q17. What is the difference between COUNT(*) and COUNT(column)?
Answer: COUNT(*) counts all rows including those with NULLs. COUNT(column) counts only non-
NULL values in that column.
Q18. Write a query to find the highest salary in each department.
Answer: SELECT DeptID, MAX(Salary) AS MaxSalary FROM Employees GROUP BY DeptID;
Q19. How do you filter groups in a GROUP BY query?
Answer: Use the HAVING clause after GROUP BY. HAVING can use aggregate functions, unlike
WHERE.
Q20. Write a query to find departments with more than 5 employees.
Answer: SELECT DeptID, COUNT(*) AS Total FROM Employees GROUP BY DeptID HAVING
COUNT(*) > 5;
Q21. What is the order of SQL clause execution?
Answer: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Note
SELECT is evaluated late, which is why you can't use aliases in WHERE.
Page 9
SQL Complete Study Guide
UNIT 5: JOINS — Combining Tables
5. SQL Joins
Joins combine rows from two or more tables based on a related column between them. Joins are one
of the most important topics in SQL.
Types of Joins
• INNER JOIN — Returns rows that have matching values in both tables
• LEFT JOIN (LEFT OUTER JOIN) — Returns all rows from the left table, plus matches from the
right
• RIGHT JOIN (RIGHT OUTER JOIN) — Returns all rows from the right table, plus matches from
the left
• FULL OUTER JOIN — Returns all rows from both tables
• CROSS JOIN — Returns all combinations (cartesian product)
• SELF JOIN — Joins a table with itself
INNER JOIN
-- Get employee names with their department names
SELECT [Link], [Link], [Link]
FROM Employees e
INNER JOIN Departments d ON [Link] = [Link];
LEFT JOIN
-- All employees, even those with no department assigned
SELECT [Link], [Link]
FROM Employees e
LEFT JOIN Departments d ON [Link] = [Link];
INNER vs INNER JOIN: only employees WITH a department. LEFT JOIN: ALL employees; those
LEFT without a department will show NULL for DeptName.
Multiple Joins
-- Join three tables: Employees, Departments, Locations
SELECT [Link], [Link], [Link]
FROM Employees e
INNER JOIN Departments d ON [Link] = [Link]
INNER JOIN Locations l ON [Link] = [Link];
Page 10
SQL Complete Study Guide
SELF JOIN
-- Find each employee and their manager
SELECT [Link] AS Employee, [Link] AS Manager
FROM Employees e
LEFT JOIN Employees m ON [Link] = [Link];
Practice Questions — Unit 5
Q22. What is the difference between INNER JOIN and LEFT JOIN?
Answer: INNER JOIN returns only matched rows from both tables. LEFT JOIN returns all rows from
the left table; unmatched rows from the right appear as NULL.
Q23. When would you use a FULL OUTER JOIN?
Answer: When you want all records from both tables regardless of whether a match exists in the
other. Unmatched rows appear as NULL on the missing side.
Q24. Write a query to get all departments and the number of employees in each (including
departments with zero employees).
Answer: SELECT [Link], COUNT([Link]) AS Total FROM Departments d LEFT JOIN
Employees e ON [Link] = [Link] GROUP BY [Link];
Q25. What is a CROSS JOIN?
Answer: A CROSS JOIN returns every combination of rows from both tables (cartesian product). If
table A has 5 rows and table B has 4 rows, result has 20 rows. Use carefully — it can produce huge
result sets.
Q26. What is a Self Join and when is it useful?
Answer: A Self Join joins a table to itself. It's useful for hierarchical data, like finding an employee's
manager when both are stored in the same Employees table.
Page 11
SQL Complete Study Guide
UNIT 6: Subqueries & CTEs
6. Subqueries
A subquery is a SELECT statement nested inside another query. It can appear in SELECT, FROM,
WHERE, or HAVING clauses.
Subquery in WHERE
-- Find employees earning more than the average salary
SELECT FirstName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
Subquery with IN
-- Find employees in departments located in 'Hyderabad'
SELECT FirstName FROM Employees
WHERE DeptID IN (
SELECT DeptID FROM Departments
WHERE City = 'Hyderabad'
);
Correlated Subquery
A correlated subquery references the outer query. It runs once for each row of the outer query.
-- Find employees earning more than their department's average
SELECT [Link], [Link]
FROM Employees e
WHERE [Link] > (
SELECT AVG(Salary) FROM Employees
WHERE DeptID = [Link]
);
Common Table Expressions (CTEs)
A CTE is a named temporary result set defined with the WITH clause. CTEs make complex queries
more readable.
WITH HighEarners AS (
SELECT * FROM Employees WHERE Salary > 70000
)
SELECT [Link], [Link]
FROM HighEarners h
JOIN Departments d ON [Link] = [Link];
Page 12
SQL Complete Study Guide
-- Multiple CTEs
WITH
Dept_Avg AS (SELECT DeptID, AVG(Salary) AS Avg FROM Employees GROUP BY
DeptID),
Top_Depts AS (SELECT DeptID FROM Dept_Avg WHERE Avg > 60000)
SELECT * FROM Employees WHERE DeptID IN (SELECT DeptID FROM Top_Depts);
Practice Questions — Unit 6
Q27. What is a subquery?
Answer: A subquery is a SQL query nested inside another query. It is enclosed in parentheses and
can be used in SELECT, FROM, WHERE, or HAVING clauses.
Q28. What is the difference between a correlated and non-correlated subquery?
Answer: A non-correlated subquery runs once independently. A correlated subquery references the
outer query and runs once per row of the outer query — it is slower but more powerful.
Q29. What is a CTE and why is it preferred over subqueries?
Answer: A CTE (WITH clause) is a named temporary result set. It makes queries more readable,
reusable within the same query, and easier to debug compared to nested subqueries.
Q30. Write a query to find the second highest salary.
Answer: SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM
Employees);
Page 13
SQL Complete Study Guide
UNIT 7: DML — Insert, Update, Delete
7. Data Manipulation Language (DML)
INSERT
-- Insert a single row
INSERT INTO Employees (FirstName, LastName, Salary, DeptID)
VALUES ('Priya', 'Kumar', 65000, 2);
-- Insert multiple rows
INSERT INTO Employees (FirstName, LastName, Salary, DeptID) VALUES
('Arjun', 'Sharma', 72000, 1),
('Meera', 'Patel', 55000, 3);
-- Insert from another table
INSERT INTO ArchiveEmployees
SELECT * FROM Employees WHERE HireDate < '2015-01-01';
UPDATE
-- Update a specific row
UPDATE Employees SET Salary = 80000 WHERE EmployeeID = 101;
-- Update multiple columns
UPDATE Employees
SET Salary = Salary * 1.10, -- 10% raise
DeptID = 3
WHERE DeptID = 2;
-- Update using subquery
UPDATE Employees
SET Salary = Salary * 1.15
WHERE DeptID = (SELECT DeptID FROM Departments WHERE DeptName =
'Engineering');
WARNIN Always include a WHERE clause in UPDATE and DELETE statements! Without it, every
G row in the table will be affected.
DELETE
-- Delete a specific row
DELETE FROM Employees WHERE EmployeeID = 101;
Page 14
SQL Complete Study Guide
-- Delete with condition
DELETE FROM Employees WHERE Salary < 30000;
-- Delete all rows (use with caution!)
DELETE FROM Employees; -- DML, can rollback
TRUNCATE TABLE Employees; -- DDL, faster, cannot rollback
Practice Questions — Unit 7
Q31. What is the difference between DELETE and TRUNCATE?
Answer: DELETE removes specific rows (with optional WHERE) and can be rolled back. TRUNCATE
removes all rows instantly, resets auto-increment, cannot use WHERE, and typically cannot be rolled
back.
Q32. How do you update the salary of all employees in department 5 by giving a 15%
raise?
Answer: UPDATE Employees SET Salary = Salary * 1.15 WHERE DeptID = 5;
Q33. Write an INSERT statement to add a new employee.
Answer: INSERT INTO Employees (FirstName, LastName, Email, Salary, DeptID) VALUES ('John',
'Doe', 'john@[Link]', 55000, 2);
Page 15
SQL Complete Study Guide
UNIT 8: DDL — Defining Structure
8. Data Definition Language (DDL)
DDL commands define and modify the structure (schema) of database objects like tables and indexes.
CREATE, ALTER, DROP
-- Create a table
CREATE TABLE Products (
ProductID INT PRIMARY KEY AUTO_INCREMENT,
ProductName VARCHAR(100) NOT NULL,
Price DECIMAL(10,2) CHECK (Price >= 0),
CategoryID INT,
FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);
-- Add a new column
ALTER TABLE Products ADD COLUMN Stock INT DEFAULT 0;
-- Modify a column
ALTER TABLE Products MODIFY COLUMN ProductName VARCHAR(200);
-- Rename a column (MySQL 8+)
ALTER TABLE Products RENAME COLUMN Price TO UnitPrice;
-- Drop a column
ALTER TABLE Products DROP COLUMN Stock;
-- Drop a table
DROP TABLE Products;
-- Drop if it exists (safe)
DROP TABLE IF EXISTS Products;
FOREIGN KEYS & Referential Integrity
CREATE TABLE Orders (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
CustomerID INT NOT NULL,
OrderDate DATE,
Total DECIMAL(12,2),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
ON DELETE CASCADE -- delete orders when customer deleted
ON UPDATE CASCADE -- update if customer ID changes
Page 16
SQL Complete Study Guide
);
Practice Questions — Unit 8
Q34. What is the difference between DDL and DML?
Answer: DDL (Data Definition Language) defines structure: CREATE, ALTER, DROP. DML (Data
Manipulation Language) manipulates data: SELECT, INSERT, UPDATE, DELETE. DDL changes are
usually auto-committed.
Q35. What does ON DELETE CASCADE do?
Answer: When a parent row is deleted, ON DELETE CASCADE automatically deletes all related child
rows. For example, deleting a customer auto-deletes all their orders.
Q36. How do you add a column to an existing table?
Answer: ALTER TABLE TableName ADD COLUMN ColumnName DataType [constraints];
Page 17
SQL Complete Study Guide
UNIT 9: Indexes & Performance
9. Indexes
An index is a data structure that speeds up data retrieval. Like a book's index — instead of reading the
whole book, you jump to the right page.
Creating Indexes
-- Create a simple index
CREATE INDEX idx_lastname ON Employees(LastName);
-- Create a unique index
CREATE UNIQUE INDEX idx_email ON Employees(Email);
-- Composite index (multiple columns)
CREATE INDEX idx_dept_salary ON Employees(DeptID, Salary);
-- Drop an index
DROP INDEX idx_lastname ON Employees;
When to Use Indexes
• Columns frequently used in WHERE, JOIN ON, ORDER BY
• High-cardinality columns (many unique values)
• Large tables where full scans are slow
When NOT to Use Indexes
• Small tables (full scan is faster)
• Columns rarely used in queries
• Tables with very frequent INSERT/UPDATE/DELETE (indexes slow writes)
TRADE- Indexes speed up reads (SELECT) but slow down writes (INSERT, UPDATE, DELETE)
OFF because the index must be updated. Use them wisely.
Practice Questions — Unit 9
Q37. What is an index and why is it used?
Answer: An index is a data structure that improves the speed of data retrieval. Without an index, SQL
scans every row (full table scan). With an index, it jumps directly to matching rows.
Page 18
SQL Complete Study Guide
Q38. What is the downside of having too many indexes?
Answer: Indexes consume disk space and slow down write operations (INSERT, UPDATE, DELETE)
because each index must be updated when data changes.
Q39. What is a composite index?
Answer: A composite index covers multiple columns, e.g., CREATE INDEX idx ON
Employees(DeptID, Salary). It's efficient for queries that filter on both columns.
Page 19
SQL Complete Study Guide
UNIT 10: Transactions & ACID Properties
10. Transactions
A transaction is a sequence of SQL operations that are treated as a single unit of work — either all
succeed or all fail.
ACID Properties
• Atomicity — All operations in a transaction succeed or all are rolled back
• Consistency — Database moves from one valid state to another
• Isolation — Concurrent transactions don't interfere with each other
• Durability — Committed changes persist even if the system crashes
Transaction Commands
BEGIN TRANSACTION; -- or: START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 5000 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 5000 WHERE AccountID = 2;
-- Check for errors; if all good:
COMMIT;
-- If something went wrong:
ROLLBACK;
-- Partial rollback using SAVEPOINT
SAVEPOINT sp1;
DELETE FROM Temp WHERE Flag = 0;
ROLLBACK TO sp1; -- Undo only back to savepoint
Practice Questions — Unit 10
Q40. What does COMMIT do?
Answer: COMMIT permanently saves all changes made during the current transaction to the
database.
Q41. What does ROLLBACK do?
Answer: ROLLBACK undoes all changes made during the current transaction, reverting the database
to its state before the transaction began.
Q42. Explain ACID with a bank transfer example.
Page 20
SQL Complete Study Guide
Answer: Atomicity: Both debit and credit happen or neither. Consistency: Total money stays the
same. Isolation: Other transactions don't see the intermediate state. Durability: After commit, the
transfer is permanent even if the system crashes.
Page 21
SQL Complete Study Guide
UNIT 11: Window Functions
11. Window Functions
Window functions perform calculations across a 'window' of related rows without collapsing them into
one row like GROUP BY does. They are powerful for ranking, running totals, and moving averages.
Syntax
function_name() OVER (
PARTITION BY column -- group rows (like GROUP BY, but doesn't collapse)
ORDER BY column -- order within each partition
ROWS/RANGE BETWEEN ...-- define the window frame
)
Ranking Functions
-- ROW_NUMBER: unique sequential number
SELECT FirstName, Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;
-- RANK: same rank for ties, gaps after ties (1,1,3)
SELECT FirstName, Salary,
RANK() OVER (ORDER BY Salary DESC) AS Rnk
FROM Employees;
-- DENSE_RANK: same rank for ties, no gaps (1,1,2)
SELECT FirstName, Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DRnk
FROM Employees;
-- Rank within each department
SELECT FirstName, DeptID, Salary,
RANK() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS DeptRank
FROM Employees;
Aggregate Window Functions
-- Running total of salary
SELECT FirstName, Salary,
SUM(Salary) OVER (ORDER BY EmployeeID) AS RunningTotal
FROM Employees;
Page 22
SQL Complete Study Guide
-- Show each employee's salary and department average side by side
SELECT FirstName, DeptID, Salary,
AVG(Salary) OVER (PARTITION BY DeptID) AS DeptAvg
FROM Employees;
LAG & LEAD
-- Compare salary with previous employee
SELECT FirstName, Salary,
LAG(Salary, 1) OVER (ORDER BY Salary) AS PrevSalary,
LEAD(Salary, 1) OVER (ORDER BY Salary) AS NextSalary
FROM Employees;
Practice Questions — Unit 11
Q43. What is the difference between RANK and DENSE_RANK?
Answer: Both assign the same rank to tied values. RANK skips the next ranks (1,1,3,4), while
DENSE_RANK does not skip (1,1,2,3).
Q44. Write a query to find the top earner in each department using window functions.
Answer: SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY DeptID ORDER BY Salary
DESC) AS Rnk FROM Employees) t WHERE Rnk = 1;
Q45. What does PARTITION BY do in a window function?
Answer: PARTITION BY divides the result set into groups (like GROUP BY), but each group's rows
are all kept in the output. The window function is computed separately for each partition.
Page 23
SQL Complete Study Guide
UNIT 12: Stored Procedures & Views
12. Views
A view is a saved SELECT query stored in the database. It acts like a virtual table and simplifies
complex queries.
-- Create a view
CREATE VIEW HighEarnerView AS
SELECT [Link], [Link], [Link], [Link]
FROM Employees e JOIN Departments d ON [Link] = [Link]
WHERE [Link] > 70000;
-- Use the view like a table
SELECT * FROM HighEarnerView WHERE DeptName = 'Engineering';
-- Update or drop a view
CREATE OR REPLACE VIEW HighEarnerView AS SELECT ...;
DROP VIEW HighEarnerView;
Stored Procedures
A stored procedure is a precompiled block of SQL that can be saved and reused. It can accept
parameters and contain logic.
DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(IN dept_id INT)
BEGIN
SELECT FirstName, LastName, Salary
FROM Employees
WHERE DeptID = dept_id
ORDER BY Salary DESC;
END //
DELIMITER ;
-- Call the procedure
CALL GetEmployeesByDept(3);
Practice Questions — Unit 12
Q46. What is a view and what are its advantages?
Answer: A view is a virtual table based on a SELECT query. Advantages: simplifies complex queries,
provides a security layer (hide sensitive columns), and ensures consistent access to data.
Page 24
SQL Complete Study Guide
Q47. What is the difference between a view and a table?
Answer: A table stores actual data on disk. A view is a stored query — no data is stored; data comes
from the underlying tables when the view is queried.
Q48. What is a stored procedure and why use it?
Answer: A stored procedure is saved SQL code that can be called with CALL. Benefits: reusability,
performance (precompiled), security (grant EXECUTE without exposing tables), and maintainability.
Page 25
SQL Complete Study Guide
SQL QUICK REFERENCE CHEAT SHEET
Complete Clause Order
SELECT columns
FROM table
JOIN other_table ON condition
WHERE row_filter
GROUP BY grouping_columns
HAVING group_filter
ORDER BY sort_column ASC|DESC
LIMIT n;
String Functions
UPPER(str) LOWER(str) LENGTH(str) TRIM(str)
CONCAT(a,b) SUBSTRING(str,start,len) REPLACE(str,find,rep)
Date Functions
NOW() CURDATE() YEAR(date) MONTH(date) DAY(date)
DATEDIFF(d1,d2) DATE_ADD(date, INTERVAL n DAY)
Conditional Logic
-- CASE WHEN
SELECT FirstName,
CASE
WHEN Salary >= 80000 THEN 'Senior'
WHEN Salary >= 50000 THEN 'Mid-level'
ELSE 'Junior'
END AS Level
FROM Employees;
-- COALESCE: returns first non-NULL value
SELECT COALESCE(PhoneNumber, Email, 'No Contact') FROM Employees;
-- NULLIF: returns NULL if two values are equal
SELECT NULLIF(Salary, 0) FROM Employees; -- avoids division by zero
50 Must-Know Practice Questions — Summary
Q49. Find all employees hired in 2023.
Answer: SELECT * FROM Employees WHERE YEAR(HireDate) = 2023;
Page 26
SQL Complete Study Guide
Q50. Find duplicate email addresses in the Employees table.
Answer: SELECT Email, COUNT(*) FROM Employees GROUP BY Email HAVING COUNT(*) > 1;
Happy Learning! Master SQL step by step.
Page 27