Scribd
Upload
2 views15 pages

Dbms Assignment 13

The document outlines a database management system (DBMS) assignment that includes SQL commands for creating and manipulating an 'Employees' table, along with various queries to analyze employee data based on salary, department, and joining date. It also presents a case study for a banking system with tables for 'Accounts' and 'Transactions' and includes SQL queries to find customers with transactions exceeding their average. The queries cover a range of analytical tasks, such as finding employees with the highest salaries, those who joined before a specific date, and departments with average salaries above a certain threshold.

Uploaded by

ahdsahil23
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views15 pages

Dbms Assignment 13

The document outlines a database management system (DBMS) assignment that includes SQL commands for creating and manipulating an 'Employees' table, along with various queries to analyze employee data based on salary, department, and joining date. It also presents a case study for a banking system with tables for 'Accounts' and 'Transactions' and includes SQL queries to find customers with transactions exceeding their average. The queries cover a range of analytical tasks, such as finding employees with the highest salaries, those who joined before a specific date, and departments with average salaries above a certain threshold.

Uploaded by

ahdsahil23
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

 DBMS ASSIGNMENT 13/05/2026 BY

SACHIN SIR
use section_65;

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

EmpName VARCHAR(50),

Department VARCHAR(50),

Salary INT,

ManagerID INT,

JoiningDate DATE

);

INSERT INTO Employees

(EmpID, EmpName, Department, Salary, ManagerID, JoiningDate)

VALUES

(101, 'Rahul', 'IT', 85000, 201, '2021-01-10'),

(102, 'Priya', 'HR', 65000, 202, '2020-03-15'),

(103, 'Amit', 'IT', 92000, 201, '2019-07-21'),

(104, 'Neha', 'Finance', 75000, 203, '2022-06-12'),

(105, 'Karan', 'IT', 85000, 201, '2021-11-18'),

(106, 'Simran', 'HR', 70000, 202, '2023-01-05'),

(107, 'Vivek', 'Finance', 95000, 203, '2018-09-09'),

(108, 'Anjali', 'IT', 99000, 201, '2017-12-01');

-- Q1 Find employees whose salary is greater than the average


salary of all employees

SELECT *

FROM Employees
WHERE Salary > (

SELECT AVG(Salary)

FROM Employees

);

-- Q2 Find the employee(s) with the highest salary

SELECT *

FROM Employees

WHERE Salary = (

SELECT MAX(Salary)

FROM Employees

);

-- Q3 Find employees earning more than the average salary of their


own department

SELECT *

FROM Employees e1

WHERE Salary > (

SELECT AVG(Salary)

FROM Employees e2

WHERE [Link] = [Link]

);

-- Q4 Find the second highest salary

SELECT MAX(Salary) AS SecondHighestSalary

FROM Employees

WHERE Salary < (

SELECT MAX(Salary)

FROM Employees

);
-- Q5 Find departments whose average salary is greater than 80000

SELECT Department, AVG(Salary) AS AvgSalary

FROM Employees

GROUP BY Department

HAVING AVG(Salary) > 80000;

-- Q6 Find employees who joined before the employee "Neha"

SELECT *

FROM Employees

WHERE JoiningDate < (

SELECT JoiningDate

FROM Employees

WHERE EmpName = 'Neha'

);

-- Q7 Find employees who earn the same salary as another


employee

SELECT *
FROM Employees

WHERE Salary IN (

SELECT Salary

FROM Employees

GROUP BY Salary

HAVING COUNT(*) > 1

);

-- Q8 Find employees whose salary is NOT the maximum salary in


their department

SELECT *

FROM Employees e1

WHERE Salary < (

SELECT MAX(Salary)

FROM Employees e2

WHERE [Link] = [Link]

);

-- Q9 Find customers who placed more orders than the average


number of orders per customer

SELECT CustomerID, COUNT(*) AS TotalOrders

FROM Orders

GROUP BY CustomerID

HAVING COUNT(*) > (

SELECT AVG(OrderCount)

FROM (

SELECT COUNT(*) AS OrderCount

FROM Orders

GROUP BY CustomerID
) AS AvgOrders

);

-- Q10 Find products whose total sales amount is greater than the
average product sales

SELECT Product, SUM(Amount) AS TotalSales

FROM Orders

GROUP BY Product

HAVING SUM(Amount) > (

SELECT AVG(ProductSales)

FROM (

SELECT SUM(Amount) AS ProductSales

FROM Orders

GROUP BY Product

) AS AvgSales

);

-- Q11 Find employees whose salary is greater than the average


salary of their department

SELECT *

FROM Employees e1

WHERE Salary > (

SELECT AVG(Salary)

FROM Employees e2

WHERE [Link] = [Link]

);

-- Q12 Find employees who earn the highest salary in their


department

SELECT *

FROM Employees e1
WHERE Salary = (

SELECT MAX(Salary)

FROM Employees e2

WHERE [Link] = [Link]

);

-- Q13 Find customers who made purchases on more than one


distinct date

SELECT CustomerID

FROM Orders

GROUP BY CustomerID

HAVING COUNT(DISTINCT OrderDate) > 1;

-- Q14 Find orders where the order amount is greater than the
customer’s average order amount

SELECT *

FROM Orders o1

WHERE Amount > (

SELECT AVG(Amount)

FROM Orders o2

WHERE [Link] = [Link]

);
-- Q15 Find departments having at least 2 employees earning more
than 80000

SELECT Department, COUNT(*) AS EmployeeCount

FROM Employees

WHERE Salary > 80000

GROUP BY Department

HAVING COUNT(*) >= 2;

-- Q16 Find employees who report to managers managing more than


2 employees

SELECT *

FROM Employees

WHERE ManagerID IN (

SELECT ManagerID

FROM Employees

GROUP BY ManagerID

HAVING COUNT(*) > 2

);
-- Q17 Find the department with the highest average salary

SELECT Department, AVG(Salary) AS AvgSalary

FROM Employees

GROUP BY Department

ORDER BY AvgSalary DESC

LIMIT 1;

-- Q18 Find employees earning more than the salary of every HR


employee

SELECT *

FROM Employees

WHERE Salary > ALL (

SELECT Salary

FROM Employees

WHERE Department = 'HR'

);

-- Q19 Find customers whose total purchase amount is the highest

SELECT CustomerID, SUM(Amount) AS TotalPurchase


FROM Orders

GROUP BY CustomerID

HAVING SUM(Amount) = (

SELECT MAX(TotalAmount)

FROM (

SELECT SUM(Amount) AS TotalAmount

FROM Orders

GROUP BY CustomerID

) AS CustomerTotals

);

-- Q20 Find products ordered by customers who also ordered laptops

SELECT DISTINCT Product

FROM Orders

WHERE CustomerID IN (

SELECT CustomerID

FROM Orders

WHERE Product = 'Laptop'

);

-- Q21 Find departments where at least one employee earns more


than 90000

SELECT DISTINCT Department

FROM Employees

WHERE Salary > 90000;

-- Q22 Find customers who never ordered a laptop

SELECT DISTINCT CustomerID

FROM Orders

WHERE CustomerID NOT IN (


SELECT CustomerID

FROM Orders

WHERE Product = 'Laptop'

);

-- Q23 Find employees who do not manage anyone

SELECT *

FROM Employees

WHERE EmpID NOT IN (

SELECT DISTINCT ManagerID

FROM Employees

WHERE ManagerID IS NOT NULL

);

Q27 Assign row numbers department-wise ordered by salary

SELECT
ROW_NUMBER() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS RowNum,
EmpName,
Department,
Salary
FROM Employees;

Q24 Find products that were ordered at least twice

SELECT Product, COUNT(*) AS OrderCount


FROM Orders
GROUP BY Product
HAVING COUNT(*) >= 2;

Q25 Find employees whose department has no employee earning


below 70000
SELECT *
FROM Employees
WHERE Department NOT IN (
SELECT Department
FROM Employees
WHERE Salary < 70000
);

Q26 Assign row numbers to employees based on salary in


descending order

SELECT
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
EmpName,
Department,
Salary
FROM Employees;

Q27 Assign row numbers department-wise ordered by salary

SELECT
ROW_NUMBER() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS RowNum,
EmpName,
Department,
Salary
FROM Employees;
Q28 Find top 2 highest-paid employees in each department

SELECT *
FROM (
SELECT
EmpID,
EmpName,
Department,
Salary,
ROW_NUMBER() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS rn
FROM Employees
) AS RankedEmployees
WHERE rn <= 2;
Q29 Rank employees based on salary

SELECT
EmpName,
Department,
Salary,
RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

Q30 Find the employee with second highest salary using RANK()

SELECT *
FROM (
SELECT
EmpID,
EmpName,
Department,
Salary,
RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees
) AS RankedEmployees
WHERE SalaryRank = 2;

 ADVANCED INTERVIEW CASE STUDY QUESTIONS

Case Study 1 — Banking System

Accounts(AccountID, CustomerID, Balance)


Transactions(TransactionID, AccountID, Amount, TransactionDate)

CREATE TABLE Accounts (


AccountID INT PRIMARY KEY,
CustomerID INT,
Balance DECIMAL(10,2)
);

CREATE TABLE Transactions (


TransactionID INT PRIMARY KEY,
AccountID INT,
Amount DECIMAL(10,2),
TransactionDate DATE
);

Questions

Q1 Find customers with transactions greater than their average


transaction

SELECT
[Link],
[Link],
[Link]
FROM Transactions t
JOIN Accounts a
ON [Link] = [Link]
WHERE [Link] > (
SELECT AVG([Link])
FROM Transactions t2
WHERE [Link] = [Link]
);

-- Q1 Find customers with transactions greater than their average


transaction

You might also like