TA64
Assignment 2A
Q.1 Consider Following Schema
Employee-(eid, ename, address, designation, salary, manager_id, did )
Departement = (did, dname, location)
-- Department table
CREATE TABLE Department (
did INT PRIMARY KEY,
dname VARCHAR(50),
location VARCHAR(50)
);
-- Employee table
CREATE TABLE Employee (
eid INT PRIMARY KEY,
ename VARCHAR(50),
address VARCHAR(100),
designation VARCHAR(50),
salary DECIMAL(10,2),
manager_id INT,
did INT,
FOREIGN KEY (did) REFERENCES Department(did),
FOREIGN KEY (manager_id) REFERENCES Employee(eid)
);
DESCRIBE Department;
DESCRIBE Employee;
-- 1. Cartesian product
SELECT *
FROM Employee, Department;
-- 2. Employee name, designation, department
SELECT e.ename, e.designation, d.dname
FROM Employee e
JOIN Department d ON e.did = d.did;
-- 3. Employees and departments (include employees without dept)
-- 4. Departments with employees (include empty departments)
-- 5. Departments with more than 5 employees
SELECT d.dname, COUNT(e.eid) AS total_employees
FROM Department d
JOIN Employee e ON d.did = e.did
GROUP BY d.dname
HAVING COUNT(e.eid) > 5;
-- 6. Employee and their manager
SELECT e.ename AS employee, m.ename AS manager
FROM Employee e
LEFT JOIN Employee m ON e.manager_id = m.eid;
-- 7. Employees under a specific manager (manager_id = 101)
SELECT e.ename
FROM Employee e
WHERE e.manager_id = 101;
-- 8. Manager name with count of employees
SELECT m.ename AS manager, COUNT(e.eid) AS total_employees
FROM Employee e
JOIN Employee m ON e.manager_id = m.eid
GROUP BY m.ename;
Q.2 Consider Following Schema of Library and perform queries.
-- Publisher Table
CREATE TABLE Publisher (
PID INT PRIMARY KEY,
Pub_Name VARCHAR(50),
PCity VARCHAR(50),
PEmail VARCHAR(50)
);
-- Author Table
CREATE TABLE Author (
AuthorID INT PRIMARY KEY,
AName VARCHAR(50),
AEmail VARCHAR(50)
);
-- Book Table
CREATE TABLE Book (
ISBNNO INT PRIMARY KEY,
Title VARCHAR(100),
Edition INT,
Price DECIMAL(8,2),
Quantity INT,
PID INT,
AID INT,
FOREIGN KEY (PID) REFERENCES Publisher(PID),
FOREIGN KEY (AID) REFERENCES Author(AuthorID)
);
-- Student Table
CREATE TABLE Student (
PRNNO INT PRIMARY KEY,
SName VARCHAR(50),
RollNo INT,
Branch VARCHAR(50),
Year INT
);
-- BorrowedBy Table
CREATE TABLE BorrowedBy (
TRXID INT PRIMARY KEY,
IssueDate DATE,
RetDate DATE,
Fine DECIMAL(8,2),
PRN INT,
ISBN INT,
FOREIGN KEY (PRN) REFERENCES Student(PRNNO),
FOREIGN KEY (ISBN) REFERENCES Book(ISBNNO)
);
DESCRIBE Publisher;
DESCRIBE Author;
DESCRIBE Book;
DESCRIBE Student;
DESCRIBE BorrowedBy;
-- Publishers
INSERT INTO Publisher VALUES
(1, 'Pearson', 'Pune', 'pearson@gmail.com'),
(2, 'Oxford', 'Mumbai', 'oxford@gmail.com'),
(3, 'McGrawHill', 'Delhi', 'mcgraw@gmail.com');
-- Authors
INSERT INTO Author VALUES
(101, 'Ravi Kumar', 'ravi@gmail.com'),
(102, 'Anita Sharma', NULL), -- intentionally NULL for Q22
(103, 'John Doe', 'john@gmail.com');
-- Books
INSERT INTO Book VALUES
(1001, 'Introduction to SQL', 3, 500, 10, 1, 101),
(1002, 'Advanced Database', 2, 800, 5, 2, 102),
(1003, 'Data Structures', 1, 600, 8, 3, 101),
(1004, 'Operating System Concepts', 1, 700, 7, 1, 103),
(1005, 'Database Systems', 4, 800, 4, 2, 102),
(1006, 'Introduction to AI', 1, 1000, 3, 3, 103);
-- Students
INSERT INTO Student VALUES
(201, 'Amit', 10, 'IT', 2),
(202, 'Neha', 11, 'COMP', 3),
(203, 'Suresh', 12, 'IT', 2),
(204, 'Priya', 13, 'ENTC', 1),
(205, 'Rahul', 14, 'COMP', 3);
-- BorrowedBy
INSERT INTO BorrowedBy VALUES
(1, '2025-09-12', '2025-09-15', 20, 201, 1001),
(2, '2025-09-12', NULL, 0, 202, 1002),
(3, '2025-09-10', '2025-09-12', 50, 203, 1003),
(4, '2025-09-11', '2025-09-11', 0, 204, 1004), -- issued & returned same date
(5, '2025-09-12', NULL, 100, 205, 1005),
(6, '2025-09-12', '2025-09-12', 0, 201, 1006);
-- 1. Books from min price to max
SELECT Title, Price FROM Book ORDER BY Price ASC;
-- 2. Books from max price to min, if same price then alphabetical
SELECT Title, Price FROM Book ORDER BY Price DESC, Title ASC;
-- 3. Book titles with highest price
SELECT Title FROM Book WHERE Price = (SELECT MAX(Price) FROM Book);
-- 4. Book titles with lowest price
SELECT Title FROM Book WHERE Price = (SELECT MIN(Price) FROM Book);
-- 5. Total number of publishers
SELECT COUNT(*) AS Total_Publishers FROM Publisher;
-- 6. Total number of books in the library
SELECT SUM(Quantity) AS Total_Books FROM Book;
-- 7. Books written by a particular Author (example: AuthorID = 101)
SELECT Title FROM Book WHERE AID = 101;
-- 8. Books issued today
SELECT b.Title FROM Book b
JOIN BorrowedBy br ON b.ISBNNO = br.ISBN
WHERE br.IssueDate = CURDATE();
-- 9. Total fine collected
SELECT SUM(Fine) AS Total_Fine FROM BorrowedBy;
-- 10. Total books issued by every student
SELECT s.SName, COUNT(br.ISBN) AS Total_Books_Issued
FROM Student s
LEFT JOIN BorrowedBy br ON s.PRNNO = br.PRN
GROUP BY s.SName;
-- 11. Total fine paid by every student
SELECT s.SName, SUM(br.Fine) AS Total_Fine
FROM Student s
LEFT JOIN BorrowedBy br ON s.PRNNO = br.PRN
GROUP BY s.SName;
-- 12. Student names and roll numbers with fine (sorted max → min)
SELECT s.SName, s.RollNo, br.Fine
FROM Student s
JOIN BorrowedBy br ON s.PRNNO = br.PRN
ORDER BY br.Fine DESC;
-- 13. Total fine paid by student when fine > 50
SELECT s.SName, SUM(br.Fine) AS Total_Fine
FROM Student s
JOIN BorrowedBy br ON s.PRNNO = br.PRN
GROUP BY s.SName
HAVING SUM(br.Fine) > 50;
-- 14. Student name & fine paid (only IT dept, max fine first)
SELECT s.SName, br.Fine
FROM Student s
JOIN BorrowedBy br ON s.PRNNO = br.PRN
WHERE s.Branch = 'IT'
ORDER BY br.Fine DESC;
-- 15. Total fine paid by IT department students
SELECT SUM(br.Fine) AS IT_Total_Fine
FROM Student s
JOIN BorrowedBy br ON s.PRNNO = br.PRN
WHERE s.Branch = 'IT';
-- 16. Average price of books published by each publisher
SELECT p.Pub_Name, AVG(b.Price) AS Avg_Price
FROM Publisher p
JOIN Book b ON p.PID = b.PID
GROUP BY p.Pub_Name;
-- 17. Book titles starting with 'Introduction'
SELECT Title FROM Book WHERE Title LIKE 'Introduction%';
-- 18. Books with title containing 'database'
SELECT Title FROM Book WHERE Title LIKE '%database%';
-- 19. Books with title having minimum 8 characters
SELECT Title FROM Book WHERE LENGTH(Title) >= 8;
-- 20. Unique student names in student table
SELECT DISTINCT SName FROM Student;
-- 21. Number of books published by publisher 'Oxford'
SELECT COUNT(*) AS Total_Books
FROM Book b
JOIN Publisher p ON b.PID = p.PID
WHERE p.Pub_Name = 'Oxford';
-- 22. Names of Authors without email ID
SELECT AName FROM Author WHERE AEmail IS NULL;
-- 23. Names of students who have paid fine
SELECT DISTINCT s.SName
FROM Student s
JOIN BorrowedBy br ON s.PRNNO = br.PRN
WHERE br.Fine > 0;
-- 24. Names of students who have not paid fine
SELECT DISTINCT s.SName
FROM Student s
WHERE s.PRNNO NOT IN (
SELECT PRN FROM BorrowedBy WHERE Fine > 0
);
-- 25. PRNNO and book title issued & returned on same date
SELECT s.PRNNO, b.Title
FROM Student s
JOIN BorrowedBy br ON s.PRNNO = br.PRN
JOIN Book b ON b.ISBNNO = br.ISBN
WHERE br.IssueDate = br.RetDate;
-- 26. PRNNO and book title for books issued but not returned
SELECT s.PRNNO, b.Title
FROM Student s
JOIN BorrowedBy br ON s.PRNNO = br.PRN
JOIN Book b ON b.ISBNNO = br.ISBN
WHERE br.RetDate IS NULL;
