Complete SQL Practice Guide

Complete SQL Practice Guide —

Dataset + 5-5 Questions on Every
Topic
Bhai, ye ek complete SQL practice kit hai — ek dataset (company_db)
bana diya hai jisme har concept practice ho sake: datetime, boolean,
constraints, joins, window functions, sab kuch. Har topic-group ke
neeche 5 questions hain. Saari answers ke queries end me ANSWER
KEY section me di hain, taaki tu pehle khud try kare phir check kare.

PART 1: DATASET (Run this first)

-- ===== CREATE DATABASE =====
CREATE DATABASE company_db;
USE company_db;

-- ===== DEPARTMENTS TABLE =====

CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL UNIQUE,
location VARCHAR(50) DEFAULT 'Not Assigned'
);

-- ===== EMPLOYEES TABLE (datetime, boolean, check, default, FK,

self-FK) =====
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(15),
hire_date DATETIME NOT NULL,
salary DECIMAL(10,2) CHECK (salary > 0),
is_active BOOLEAN DEFAULT TRUE,
department_id INT,
manager_id INT,
FOREIGN KEY (department_id) REFERENCES
departments(department_id),
FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

-- ===== PROJECTS TABLE =====

CREATE TABLE projects (
project_id INT AUTO_INCREMENT PRIMARY KEY,
project_name VARCHAR(100) NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
 budget DECIMAL(12,2) CHECK (budget >= 0),
department_id INT,
FOREIGN KEY (department_id) REFERENCES
departments(department_id)
);

-- ===== EMPLOYEE_PROJECTS (junction table, composite PK) =====

CREATE TABLE employee_projects (
employee_id INT,
project_id INT,
role_in_project VARCHAR(50),
hours_worked INT DEFAULT 0,
PRIMARY KEY (employee_id, project_id),
FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
FOREIGN KEY (project_id) REFERENCES projects(project_id)
);

-- ===== MULTIPLE INSERT: DEPARTMENTS =====

INSERT INTO departments (department_name, location) VALUES
('IT', 'Pune'),
('HR', 'Mumbai'),
('Sales', 'Delhi'),
('Finance', 'Bangalore'),
('Production', 'Chennai');

-- ===== MULTIPLE INSERT: EMPLOYEES =====

INSERT INTO employees (first_name, last_name, email, phone,
hire_date, salary, is_active, department_id, manager_id) VALUES
('Rohan','Sharma','[Link]@[Link]','9000000001','2018-01-15
09:00:00',150000.00,TRUE,1,NULL),
('Priya','Singh','[Link]@[Link]','9000000002','2019-03-10
09:30:00',95000.00,TRUE,1,1),
('Aman','Verma','[Link]@[Link]','9000000003','2020-07-22
10:00:00',88000.00,FALSE,1,1),
('Sneha','Patel','[Link]@[Link]','9000000004','2018-11-05
09:15:00',70000.00,TRUE,2,1),
('Vikram','Rao','[Link]@[Link]','9000000005','2021-02-18
09:45:00',55000.00,TRUE,2,4),
('Kavita','Joshi','[Link]@[Link]','9000000006','2019-09-01
09:00:00',80000.00,TRUE,3,1),
('Arjun','Mehta','[Link]@[Link]','9000000007','2022-04-12
10:30:00',60000.00,TRUE,3,6),
('Neha','Gupta','[Link]@[Link]','9000000008','2021-12-01
09:00:00',62000.00,FALSE,3,6),
('Suresh','Iyer','[Link]@[Link]','9000000009','2017-06-30
09:00:00',90000.00,TRUE,4,1),
('Anjali','Desai','[Link]@[Link]','9000000010','2020-01-20
09:00:00',58000.00,TRUE,4,9),
('Manoj','Kumar','[Link]@[Link]','9000000011','2016-08-15
09:00:00',75000.00,TRUE,5,1),
('Pooja','Nair','[Link]@[Link]','9000000012','2022-10-10
09:00:00',52000.00,TRUE,5,11),
('Rahul','Bose','[Link]@[Link]','9000000013','2023-01-05
09:00:00',53000.00,TRUE,5,11),
('Divya','Menon','[Link]@[Link]','9000000014','2023-05-19
09:00:00',48000.00,TRUE,1,2),
('Karan','Malhotra','[Link]@[Link]','9000000015','2023-08-01
09:00:00',40000.00,TRUE,NULL,NULL);

-- ===== MULTIPLE INSERT: PROJECTS =====

 INSERT INTO projects (project_name, start_date, end_date, budget,
department_id) VALUES
('ERP Implementation','2022-01-01','2022-12-31',500000.00,1),
('Recruitment Drive','2023-01-01','2023-06-30',100000.00,2),
('Market Expansion','2023-02-01',NULL,300000.00,3),
('Audit 2023','2023-03-01','2023-09-30',150000.00,4),
('New Product Line','2022-06-01','2023-06-01',800000.00,5),
('Website Revamp','2023-07-01',NULL,200000.00,1);

-- ===== MULTIPLE INSERT: EMPLOYEE_PROJECTS =====

INSERT INTO employee_projects (employee_id, project_id,
role_in_project, hours_worked) VALUES
(1,1,'Sponsor',20),
(2,1,'Lead Developer',180),
(3,1,'Developer',150),
(14,1,'Junior Developer',90),
(4,2,'Coordinator',60),
(5,2,'Support',40),
(6,3,'Lead',100),
(7,3,'Executive',80),
(8,3,'Executive',70),
(9,4,'Lead Auditor',120),
(10,4,'Auditor',95),
(11,5,'Project Head',200),
(12,5,'Engineer',160),
(13,5,'Engineer',140),
(2,6,'Lead Developer',50),
(14,6,'Developer',30);

PART 2: TOPIC GROUPS — 5 QUESTIONS

EACH

Group A — Database/Table creation: DATETIME,

BOOLEAN, NOT NULL, UNIQUE, DEFAULT, CHECK,
PRIMARY KEY, AUTO INCREMENT, FOREIGN KEY,
CREATE TABLE

1. Create a new database school_db.

2. Create a table interns with: intern_id auto-increment PK, name
NOT NULL, email UNIQUE, joining_date DATETIME NOT NULL,
is_paid BOOLEAN DEFAULT FALSE, stipend with a CHECK that it
must be >= 0.
3. Create a table attendance with a FOREIGN KEY referencing
employees(employee_id).
4. In employees, which column has a CHECK constraint, and what
does it enforce?
5. Write a CREATE TABLE for departments_history that references
departments(department_id) as a foreign key, with a changed_on
DATETIME column defaulting to the current timestamp.

Group B — ALTER TABLE: MODIFY COLUMN,

RENAME COLUMN, DROP COLUMN, RENAME TABLE

1. Add a new column bonus (DECIMAL(10,2), default 0) to employees.

2. Modify the phone column in employees to VARCHAR(20).
3. Rename the column location in departments to office_location.
4. Drop the column bonus you added in Q1.
5. Rename the table employee_projects to project_assignments.

Group C — INSERT / Multiple Insert

1. Insert one new department: 'Marketing', location 'Hyderabad'.

2. Insert a new employee (any valid values) into the Marketing
department.
3. Insert 3 new employees in a single INSERT statement.
4. Insert a new project with end_date as NULL (ongoing project).
5. Insert 2 rows into employee_projects assigning existing employees
to existing projects.

Group D — UPDATE Query

1. Give a 10% raise to all employees in the IT department.

2. Set is_active = FALSE for employee 'Neha Gupta'.
3. Update the budget of 'Website Revamp' project to 250000.
4. Set manager_id = 1 for all employees whose manager_id is currently
NULL (except Rohan himself).
5. Update hours_worked to hours_worked + 10 for employee_id 2 on
project_id 1.

Group E — DELETE Query

1. Delete the employee named 'Karan Malhotra'.

2. Delete all rows from employee_projects where hours_worked = 0.
3. Delete all projects that have no end_date and budget less than
100000.
4. Delete all departments that have no employees (without breaking
FK).
5. Delete duplicate-looking entries (if any) from employee_projects for
the same employee & project.

Group F — SELECT, WHERE, =, <>, >, <, >=, <=

1. Select all columns from employees.

2. Select employees where salary = 60000.
3. Select employees where department_id <> 1.
4. Select employees with salary >= 70000.
5. Select projects where budget < 200000.

Group G — BETWEEN AND, IN, NOT IN, LIKE, NOT

LIKE

1. Select employees with salary BETWEEN 50000 AND 90000.

2. Select employees whose department_id IN (1, 3, 5).
3. Select employees whose department_id NOT IN (2, 4).
4. Select employees whose first name starts with 'A' (LIKE).
5. Select employees whose email does NOT LIKE '%gupta%'.

Group H — ORDER BY, LIMIT, LIMIT+OFFSET,

LIMIT+sorting, COUNT

1. List all employees ordered by salary descending.

2. Get the top 3 highest-paid employees (ORDER BY + LIMIT).
3. Get employees ranked 4th to 6th by salary (LIMIT with OFFSET).
4. Count the total number of employees.
5. Get the 2 most recently hired employees (ORDER BY hire_date
DESC LIMIT).

Group I — String functions: UPPER, LOWER,

CONCAT, SUBSTRING, LENGTH, TRIM

1. Display all employee first names in UPPERCASE.

2. Display all department names in lowercase.
3. Create a full name column by CONCAT(first_name, ’ ’, last_name).
4. Get the first 3 characters of each employee’s last_name using
SUBSTRING.
5. Find the LENGTH of each department name, and also show what
TRIM(’ IT ’) would return.

Group J — Numeric functions: ABS, ROUND, FLOOR,

CEIL/CEILING, MOD

1. Show salary divided by 3, rounded to 2 decimal places (ROUND).

2. Show FLOOR and CEILING of salary / 7 for each employee.
3. Show ABS(budget - 250000) for each project.
4. Find employees whose employee_id MOD 2 = 0 (even IDs).
5. Round each project’s budget to the nearest 10,000 (hint: combine
ROUND with division/multiplication).

Group K — Aggregate functions: COUNT, SUM, AVG,

MAX, MIN

1. Find the total number of active employees (COUNT with WHERE).

2. Find the SUM of all employee salaries.
3. Find the AVG salary of employees in the Sales department.
4. Find the MAX and MIN budget among all projects.
5. Find how many employees report to each manager (COUNT with
GROUP BY — preview for Group L).

Group L — GROUP BY, HAVING

1. Find the total salary paid per department (GROUP BY).

2. Find the number of employees in each department.
3. Find departments having more than 3 employees (HAVING).
4. Find managers who manage more than 2 employees (GROUP BY
manager_id HAVING COUNT > 2).
5. Find the average hours_worked per project, only for projects with
average hours > 100.

Group M — JOINS: INNER, LEFT, RIGHT, SELF,

CROSS

1. INNER JOIN employees with departments to show employee name +

department name.
2. LEFT JOIN employees with departments to show ALL employees,
even those with no department (Karan Malhotra).
3. RIGHT JOIN departments with employees to show ALL departments,
even those with zero employees (if any exist after Group E
deletions, otherwise reason about it).
4. SELF JOIN employees to show each employee alongside their
 manager’s name.
5. CROSS JOIN departments and projects to show every possible
department-project pairing (and explain how many rows it
produces).

Group N — UNION, UNION ALL

1. Get a combined list of distinct cities from [Link] and

a hypothetical second list ('Pune'),('Goa') using UNION.
2. Use UNION ALL on the same two lists and explain the row-count
difference vs Q1.
3. Combine first_name of employees earning > 100000 UNION
first_name of employees in department 5.
4. Explain why UNION removes duplicates but UNION ALL does not,
using IT department employees appearing in two different filtered
SELECTs.
5. Write a UNION query combining project names ending before
2023 and project names with budget > 400000.

Group O — SUBQUERY

1. Find employees who earn more than the average salary (subquery
in WHERE).
2. Find the department(s) with the highest total salary (subquery +
aggregate).
3. Find employees who are NOT assigned to any project (subquery
with NOT IN).
4. Find the name of the employee with the maximum salary using a
subquery.
5. Find all projects whose budget is above the average budget of all
projects.

Group P — VIEW

1. Create a view active_employees_view showing only active

employees.
2. Create a view dept_salary_summary showing department_name and
total salary (using GROUP BY + JOIN).
3. Query the view created in Q2 to find the department with the
highest total salary.
4. Create a view employee_manager_view showing employee name and
their manager’s name (self join).
5. Drop the view active_employees_view.

Group Q — INDEXES: Clustered, Non-Clustered,

Unique Index

1. Explain (in your own words) the difference between a clustered

index and a non-clustered index.
2. Create a non-clustered (regular) index on employees.department_id.
3. Create a UNIQUE index on [Link] (note: it’s already
UNIQUE via constraint — explain why this is redundant here).
4. Which column in employees already effectively has a clustered
index, and why?
5. Create a non-clustered index on [Link] to speed up range
queries like budget BETWEEN.
Group R — CASE statement

1. Show each employee’s salary with a label: 'High' if salary > 90000,
'Medium' if 60000–90000, else 'Low'.
2. Show 'Active' or 'Inactive' text instead of TRUE/FALSE for
is_active using CASE.
3. Categorize projects as 'Completed', 'Ongoing' (NULL end_date), or
'Closed without info'.
4. Use CASE inside an ORDER BY to sort employees by department,
but put NULL department employees last.
5. Use CASE to assign a performance bonus % based on salary range,
then SELECT name, salary, bonus%.

Group S — FUNCTION (User-Defined)

1. Write a function get_annual_salary(monthly_salary) that returns

monthly_salary * 12.
2. Write a function get_full_name(fname, lname) that returns
concatenated full name.
3. Write a function years_of_service(hire_date) that returns number
of years since hiring.
4. Use the function from Q1 in a SELECT query on employees.
5. Write a function that returns 'Senior' if years_of_service > 5, else
'Junior'.

Group T — STORED PROCEDURE

1. Write a stored procedure get_employees_by_dept(dept_id) that

returns all employees of that department.
2. Write a stored procedure give_raise(emp_id, raise_amount) that
increases an employee’s salary.
3. Write a stored procedure add_department(dept_name, dept_location)
that inserts a new department.
4. Write a stored procedure deactivate_employee(emp_id) that sets
is_active = FALSE.
5. Call all 4 procedures above with sample values.

Group U — TRIGGER

1. Write a trigger that prevents inserting an employee with salary <=

0 (even though CHECK exists, do it via trigger too — BEFORE
INSERT).
2. Write a trigger that logs every salary UPDATE into an salary_audit
table (employee_id, old_salary, new_salary, changed_on).
3. Write a trigger that automatically sets is_active = TRUE whenever
a new employee is inserted, if not specified.
4. Write a trigger that prevents deleting a department if it still has
employees.
5. Write a trigger that updates a last_modified timestamp column
whenever a project row is updated.

Group V — Date/Time functions: NOW,

CURRENT_DATE, CURRENT_TIME, DATE PART /
EXTRACT, AGE

1. Select the current date, current time, and current datetime using
NOW(), CURRENT_DATE, CURRENT_TIME.
2. Extract the YEAR from each employee’s hire_date (EXTRACT or
DATE PART).
3. Find each employee’s age in service (AGE-style calculation) — how
many years since hire_date till today.
4. Find all employees hired in the year 2023 using EXTRACT/YEAR().
5. Find employees hired more than 5 years ago compared to today’s
date.

Group W — WINDOW Functions

1. Use ROW_NUMBER() to rank employees by salary within each

department.
2. Use RANK() to rank all employees company-wide by salary
(highest first).
3. Use SUM(salary) OVER (PARTITION BY department_id) to show
running department salary total next to each employee.
4. Use LAG() to show each employee’s salary alongside the salary of
the previously hired employee (ordered by hire_date).
5. Use AVG(hours_worked) OVER (PARTITION BY project_id) to
compare each employee’s hours_worked against their project’s
average.

PART 3: ANSWER KEY

Group A

-- 1
CREATE DATABASE school_db;

-- 2
CREATE TABLE interns (
intern_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
joining_date DATETIME NOT NULL,
is_paid BOOLEAN DEFAULT FALSE,
stipend DECIMAL(10,2) CHECK (stipend >= 0)
);

-- 3
CREATE TABLE attendance (
attendance_id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
attendance_date DATE,
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

-- 4: 'salary' column -> CHECK (salary > 0) ensures salary can never
be zero or negative.

-- 5
CREATE TABLE departments_history (
history_id INT AUTO_INCREMENT PRIMARY KEY,
department_id INT,
changed_on DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (department_id) REFERENCES
departments(department_id)
 );

Group B

-- 1
ALTER TABLE employees ADD COLUMN bonus DECIMAL(10,2) DEFAULT 0;

-- 2
ALTER TABLE employees MODIFY COLUMN phone VARCHAR(20);

-- 3
ALTER TABLE departments RENAME COLUMN location TO office_location;

-- 4
ALTER TABLE employees DROP COLUMN bonus;

-- 5
ALTER TABLE employee_projects RENAME TO project_assignments;

Group C

-- 1
INSERT INTO departments (department_name, location) VALUES
('Marketing','Hyderabad');

-- 2
INSERT INTO employees
(first_name,last_name,email,hire_date,salary,department_id)
VALUES ('Tina','Roy','[Link]@[Link]','2023-09-01
09:00:00',45000,6);

-- 3
INSERT INTO employees
(first_name,last_name,email,hire_date,salary,department_id) VALUES
('Aditi','Shah','[Link]@[Link]','2023-10-01 09:00:00',47000,6),
('Farhan','Khan','[Link]@[Link]','2023-10-05
09:00:00',49000,6),
('Meera','Iyer','[Link]@[Link]','2023-10-10 09:00:00',46000,6);

-- 4
INSERT INTO projects
(project_name,start_date,end_date,budget,department_id)
VALUES ('Brand Campaign','2023-11-01',NULL,120000,6);

-- 5
INSERT INTO employee_projects
(employee_id,project_id,role_in_project,hours_worked) VALUES
(9,4,'Reviewer',30),
(12,5,'Tester',45);

Group D

-- 1
UPDATE employees SET salary = salary * 1.10 WHERE department_id = 1;

-- 2
UPDATE employees SET is_active = FALSE WHERE first_name='Neha' AND
last_name='Gupta';
 -- 3
UPDATE projects SET budget = 250000 WHERE project_name = 'Website
Revamp';

-- 4
UPDATE employees SET manager_id = 1 WHERE manager_id IS NULL AND
employee_id <> 1;

-- 5
UPDATE employee_projects SET hours_worked = hours_worked + 10
WHERE employee_id = 2 AND project_id = 1;

Group E

-- 1
DELETE FROM employees WHERE first_name='Karan' AND
last_name='Malhotra';

-- 2
DELETE FROM employee_projects WHERE hours_worked = 0;

-- 3
DELETE FROM projects WHERE end_date IS NULL AND budget < 100000;

-- 4
DELETE FROM departments
WHERE department_id NOT IN (SELECT DISTINCT department_id FROM
employees WHERE department_id IS NOT NULL);

-- 5
DELETE FROM employee_projects
WHERE (employee_id, project_id) IN (
SELECT employee_id, project_id FROM (
SELECT employee_id, project_id, COUNT(*) c
FROM employee_projects GROUP BY employee_id, project_id HAVING c
> 1
) t
);

Group F

SELECT * FROM employees; -- 1

SELECT * FROM employees WHERE salary = 60000; -- 2
SELECT * FROM employees WHERE department_id <> 1; -- 3
SELECT * FROM employees WHERE salary >= 70000; -- 4
SELECT * FROM projects WHERE budget < 200000; -- 5

Group G

SELECT * FROM employees WHERE salary BETWEEN 50000 AND 90000;

-- 1
SELECT * FROM employees WHERE department_id IN (1,3,5);
-- 2
SELECT * FROM employees WHERE department_id NOT IN (2,4);
-- 3
SELECT * FROM employees WHERE first_name LIKE 'A%';
-- 4
SELECT * FROM employees WHERE email NOT LIKE '%gupta%';
-- 5
Group H

SELECT * FROM employees ORDER BY salary DESC;

-- 1
SELECT * FROM employees ORDER BY salary DESC LIMIT 3;
-- 2
SELECT * FROM employees ORDER BY salary DESC LIMIT 3 OFFSET 3;
-- 3
SELECT COUNT(*) FROM employees;
-- 4
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2;
-- 5

Group I

SELECT UPPER(first_name) FROM employees;

-- 1
SELECT LOWER(department_name) FROM departments;
-- 2
SELECT CONCAT(first_name,' ',last_name) AS full_name FROM employees;
-- 3
SELECT SUBSTRING(last_name,1,3) FROM employees;
-- 4
SELECT department_name, LENGTH(department_name) FROM departments;
SELECT TRIM(' IT '); -- returns 'IT'
-- 5

Group J

SELECT employee_id, ROUND(salary/3, 2) FROM employees;

-- 1
SELECT employee_id, FLOOR(salary/7), CEIL(salary/7) FROM employees;
-- 2
SELECT project_id, ABS(budget - 250000) FROM projects;
-- 3
SELECT * FROM employees WHERE employee_id % 2 = 0;
-- 4 (MOD via %)
SELECT project_id, ROUND(budget/10000)*10000 FROM projects;
-- 5

Group K

SELECT COUNT(*) FROM employees WHERE is_active = TRUE;

-- 1
SELECT SUM(salary) FROM employees;
-- 2
SELECT AVG(salary) FROM employees WHERE department_id = 3;
-- 3
SELECT MAX(budget), MIN(budget) FROM projects;
-- 4
SELECT manager_id, COUNT(*) FROM employees GROUP BY manager_id;
-- 5

Group L

SELECT department_id, SUM(salary) FROM employees GROUP BY

department_id; -- 1
SELECT department_id, COUNT(*) FROM employees GROUP BY
department_id; -- 2
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id
HAVING COUNT(*) > 3; -- 3
SELECT manager_id, COUNT(*) FROM employees GROUP BY manager_id
HAVING COUNT(*) > 2; -- 4
SELECT project_id, AVG(hours_worked) FROM employee_projects
GROUP BY project_id HAVING AVG(hours_worked) > 100;
-- 5

Group M

-- 1 INNER JOIN
SELECT e.first_name, d.department_name
FROM employees e INNER JOIN departments d ON e.department_id =
d.department_id;

-- 2 LEFT JOIN
SELECT e.first_name, d.department_name
FROM employees e LEFT JOIN departments d ON e.department_id =
d.department_id;

-- 3 RIGHT JOIN
SELECT d.department_name, e.first_name
FROM departments d RIGHT JOIN employees e ON d.department_id =
e.department_id;
-- (to truly see depts with 0 employees, swap to: employees e RIGHT
JOIN departments d ON e.department_id = d.department_id)

-- 4 SELF JOIN
SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e LEFT JOIN employees m ON e.manager_id =
m.employee_id;

-- 5 CROSS JOIN
SELECT d.department_name, p.project_name
FROM departments d CROSS JOIN projects p;
-- Row count = (number of departments) x (number of projects)

Group N

-- 1
SELECT location FROM departments
UNION
SELECT x FROM (SELECT 'Pune' AS x UNION SELECT 'Goa') t;

-- 2 (UNION ALL keeps duplicates like 'Pune' appearing twice, UNION

removes the duplicate)
SELECT location FROM departments
UNION ALL
SELECT x FROM (SELECT 'Pune' AS x UNION SELECT 'Goa') t;

-- 3
SELECT first_name FROM employees WHERE salary > 100000
UNION
SELECT first_name FROM employees WHERE department_id = 5;

-- 4: Explanation only — UNION applies an implicit DISTINCT, UNION

ALL does not.
 -- 5
SELECT project_name FROM projects WHERE end_date < '2023-01-01'
UNION
SELECT project_name FROM projects WHERE budget > 400000;

Group O

-- 1
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM
employees);

-- 2
SELECT department_id, SUM(salary) AS total_sal FROM employees
GROUP BY department_id
HAVING SUM(salary) = (
SELECT MAX(total) FROM (SELECT SUM(salary) AS total FROM employees
GROUP BY department_id) t
);

-- 3
SELECT * FROM employees
WHERE employee_id NOT IN (SELECT employee_id FROM
employee_projects);

-- 4
SELECT first_name, last_name FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- 5
SELECT * FROM projects WHERE budget > (SELECT AVG(budget) FROM
projects);

Group P

-- 1
CREATE VIEW active_employees_view AS
SELECT * FROM employees WHERE is_active = TRUE;

-- 2
CREATE VIEW dept_salary_summary AS
SELECT d.department_name, SUM([Link]) AS total_salary
FROM employees e JOIN departments d ON e.department_id =
d.department_id
GROUP BY d.department_name;

-- 3
SELECT * FROM dept_salary_summary ORDER BY total_salary DESC LIMIT
1;

-- 4
CREATE VIEW employee_manager_view AS
SELECT e.first_name AS employee_name, m.first_name AS manager_name
FROM employees e LEFT JOIN employees m ON e.manager_id =
m.employee_id;

-- 5
DROP VIEW active_employees_view;

Group Q
 -- 1: Clustered index physically sorts/stores table data in index
order (only one per table,
-- usually the primary key). Non-clustered index is a separate
structure with pointers back
-- to the table rows — a table can have many of these.

-- 2
CREATE INDEX idx_emp_dept ON employees(department_id);

-- 3
CREATE UNIQUE INDEX idx_emp_email ON employees(email);
-- Redundant because UNIQUE constraint on email already auto-creates
a unique index internally.

-- 4: 'employee_id' (the PRIMARY KEY) — most databases (e.g., MySQL

InnoDB) automatically
-- cluster the table's data by the primary key.

-- 5
CREATE INDEX idx_proj_budget ON projects(budget);

Group R

-- 1
SELECT first_name, salary,
CASE
WHEN salary > 90000 THEN 'High'
WHEN salary BETWEEN 60000 AND 90000 THEN 'Medium'
ELSE 'Low'
END AS salary_band
FROM employees;

-- 2
SELECT first_name, CASE WHEN is_active THEN 'Active' ELSE 'Inactive'
END AS status
FROM employees;

-- 3
SELECT project_name,
CASE
WHEN end_date IS NOT NULL AND end_date < CURRENT_DATE THEN
'Completed'
WHEN end_date IS NULL THEN 'Ongoing'
ELSE 'Closed without info'
END AS project_status
FROM projects;

-- 4
SELECT * FROM employees
ORDER BY CASE WHEN department_id IS NULL THEN 1 ELSE 0 END,
department_id;

-- 5
SELECT first_name, salary,
CASE
WHEN salary > 90000 THEN 15
WHEN salary BETWEEN 60000 AND 90000 THEN 10
ELSE 5
END AS bonus_percent
FROM employees;
Group S

-- 1
CREATE FUNCTION get_annual_salary(monthly_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
RETURN monthly_salary * 12;

-- 2
CREATE FUNCTION get_full_name(fname VARCHAR(50), lname VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
RETURN CONCAT(fname,' ',lname);

-- 3
CREATE FUNCTION years_of_service(hire_date DATETIME)
RETURNS INT
DETERMINISTIC
RETURN TIMESTAMPDIFF(YEAR, hire_date, CURDATE());

-- 4
SELECT first_name, get_annual_salary(salary) AS annual_salary FROM
employees;

-- 5
CREATE FUNCTION seniority_label(hire_date DATETIME)
RETURNS VARCHAR(10)
DETERMINISTIC
RETURN IF(TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) > 5, 'Senior',
'Junior');

Group T

-- 1
DELIMITER //
CREATE PROCEDURE get_employees_by_dept(IN dept_id INT)
BEGIN
SELECT * FROM employees WHERE department_id = dept_id;
END //
DELIMITER ;

-- 2
DELIMITER //
CREATE PROCEDURE give_raise(IN emp_id INT, IN raise_amount
DECIMAL(10,2))
BEGIN
UPDATE employees SET salary = salary + raise_amount WHERE
employee_id = emp_id;
END //
DELIMITER ;

-- 3
DELIMITER //
CREATE PROCEDURE add_department(IN dept_name VARCHAR(50), IN
dept_location VARCHAR(50))
BEGIN
INSERT INTO departments (department_name, location) VALUES
(dept_name, dept_location);
END //
DELIMITER ;
 -- 4
DELIMITER //
CREATE PROCEDURE deactivate_employee(IN emp_id INT)
BEGIN
UPDATE employees SET is_active = FALSE WHERE employee_id = emp_id;
END //
DELIMITER ;

-- 5
CALL get_employees_by_dept(1);
CALL give_raise(5, 2000);
CALL add_department('Legal','Kolkata');
CALL deactivate_employee(8);

Group U

-- 1
DELIMITER //
CREATE TRIGGER trg_check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF [Link] <= 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary must be
positive';
END IF;
END //
DELIMITER ;

-- 2
CREATE TABLE salary_audit (
audit_id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
changed_on DATETIME DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF [Link] <> [Link] THEN
INSERT INTO salary_audit (employee_id, old_salary, new_salary)
VALUES (OLD.employee_id, [Link], [Link]);
END IF;
END //
DELIMITER ;

-- 3
DELIMITER //
CREATE TRIGGER trg_default_active
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NEW.is_active IS NULL THEN
SET NEW.is_active = TRUE;
END IF;
 END //
DELIMITER ;

-- 4
DELIMITER //
CREATE TRIGGER trg_prevent_dept_delete
BEFORE DELETE ON departments
FOR EACH ROW
BEGIN
IF (SELECT COUNT(*) FROM employees WHERE department_id =
OLD.department_id) > 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete
department with employees';
END IF;
END //
DELIMITER ;

-- 5
ALTER TABLE projects ADD COLUMN last_modified DATETIME;

DELIMITER //
CREATE TRIGGER trg_project_update_time
BEFORE UPDATE ON projects
FOR EACH ROW
BEGIN
SET NEW.last_modified = NOW();
END //
DELIMITER ;

Group V

SELECT NOW(), CURRENT_DATE, CURRENT_TIME;

-- 1

SELECT employee_id, EXTRACT(YEAR FROM hire_date) AS hire_year FROM

employees; -- 2

SELECT employee_id,
TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years_in_service
FROM employees;
-- 3

SELECT * FROM employees WHERE EXTRACT(YEAR FROM hire_date) = 2023;

-- 4

SELECT * FROM employees

WHERE TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) > 5;
-- 5

(In PostgreSQL: use EXTRACT(YEAR FROM hire_date) and AGE(hire_date) /

AGE(CURRENT_DATE, hire_date) instead of TIMESTAMPDIFF.)

Group W

-- 1
SELECT first_name, department_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary
DESC) AS rn
FROM employees;
 -- 2
SELECT first_name, salary,
RANK() OVER (ORDER BY salary DESC) AS company_rank
FROM employees;

-- 3
SELECT first_name, department_id, salary,
SUM(salary) OVER (PARTITION BY department_id) AS dept_total_salary
FROM employees;

-- 4
SELECT first_name, hire_date, salary,
LAG(salary) OVER (ORDER BY hire_date) AS prev_hired_salary
FROM employees;

-- 5
SELECT ep.employee_id, ep.project_id, ep.hours_worked,
AVG(ep.hours_worked) OVER (PARTITION BY ep.project_id) AS
avg_project_hours
FROM employee_projects ep;

Tip: Pehle Part 2 ke questions khud likh, run kar, fir Part 3 se match
kar. Agar koi query error de (especially trigger/procedure syntax —
MySQL vs PostgreSQL me thoda farak hota hai), bata dena, main us
specific RDBMS ke hisaab se fix kar dunga.