CSPC2204 DATABASE ENGINEERING LAB (0-0-3)
1. Consider the following schema for a Library Database:
BOOK (Book_id, Title, Publisher_Name, Pub_Year)
BOOK_AUTHORS (Book_id, Author_Name)
PUBLISHER (Name, Address, Phone)
BOOK_COPIES (Book_id, Programme_id, No-of_Copies)
BOOK_LENDING (Book_id, Programme_id, Card_No, Date_Out, Due_Date)
LIBRARY_PROGRAMME (Programme_id, Programme_Name, Address)
Write SQL queries to:
i) Retrieve details of all books in the library – id, title, name of publisher, authors,
number of copies in each Programme, etc.
ii) Get the particulars of borrowers who have borrowed more than 3 books, but
from Jan 2025 to Jun 2025.
iii) Delete a book in BOOK table. Update the contents of other tables to reflect this
data manipulation operation.
iv) Partition the BOOK table based on year of publication. Demonstrate its working
with a simple query.
v) Create a view of all books and its number of copies that are currently available
in the library.
2. Consider the schema for College Database:
STUDENT (REGNO, SName, Address, Phone, Gender) SEMSEC (SSID, Sem, Sec)
CLASS (REGNO, SSID)
COURSE (Subcode, Title, Sem, Credits)
IAMARKS (REGNO, Subcode, SSID, Test1, Test2, Test3, FinalIA)
Write SQL queries to:
i) List all the student details studying in fourth semester ‘C’ section.
ii) Compute the total number of male and female students in each semester and in
each section.
iii) Create a view of Test1 marks of student REGNO ‘1BP22CS101’ in all Courses.
iv) Calculate the FinalIA (average of best two test marks) and update the
corresponding table for all students.
Categorize students based on the following criterion:
If FinalIA = 17 to 20 then CAT = ‘Outstanding’
If FinalIA = 12 to 16 then CAT = ‘Average’ If FinalIA< 12 then CAT = ‘Weak’
Give these details only for 8th semester A, B, and C section students.
3. Consider the following schema for Order Database:
SALESMAN (Salesman_id, Name, City, Commission)
CUSTOMER (Customer_id, Cust_Name, City, Grade, Salesman_id)
ORDERS (Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id)
Write SQL queries to:
i) Count the customers with grades above Bangalore’s average.
ii) Find the name and numbers of all salesman who had more than one customer.
iii) List all the salesman and indicate those who have and don’t have customers in
their cities (Use UNION operation.)
iv) Create a view that finds the salesman who has the customer with the highest
order of a day.
v) Demonstrate the DELETE operation by removing salesman with id 1000. All
his orders must also be deleted.
4. Consider the following tables namely “DEPARTMENTS” and “EMPLOYEES” Their
schemas are as follows,
Departments (dept no, dept name, dept_location)
Employees (emp_id, emp_name, emp_salary, dept_no)
Write SQL Queries to:
• Display the employee details, departments that the departments are same in both the
emp and dept.
• Display the employee’s name and Department name by implementing a left outer
join.
• Display the employee’s name and Department name by implementing a right outer
join.
• Display the details of those who draw the salary greater than the average salary.
5. Create an Emp table with the following fields:(EmpNo, EmpName, Job, Basic, DA,
HRA, PF, GrossPay, NetPay) (Calculate DA as 30% of Basic and HRA as 40% of
Basic)
Write SQL Queries to:
i) Insert Five Records and calculate GrossPay and NetPay.
ii) Display the employees who’s Basic is lowest in each department.
iii) If NetPay is less than <Rs. 10,000 add Rs. 1200 as special allowances.
iv) Display the employees whose GrossPay lies between 10,000 & 20,000
v) Display all the employees who earn maximum salary.
6. Consider the schema for Election Commission:
CONSTITUENCY (cons_id, csname, csstate, no_of_voters)
PARTY (pid, pname, psymbol);
CANDIDATES (cand_id, name, age, state, phone_no); VOTER (vid, vname, vage,
vaddr)
Write SQL Queries to:
i) List the details of the candidates who are contesting from more than one constituency
which belongs to different states.
ii) Display the state name having maximum number of constituencies.
7. Consider the following relations containing airline flight information:
FLIGHTS (flno, distance, departs, arrives)
AIRCRAFT (aid, aname, cruisingrange)
CERTIFIED (eid, aid)
EMPLOYEES (eid, ename, salary)
Note that the Employees relation describes pilots and other kinds of employees as well;
every pilot is certified for some aircraft (otherwise, he or she would not qualify as a
pilot), and only pilots are certified to fly.
Write SQL queries to:
i) Find the eids of pilots certified for some Boeing aircraft.
ii) Find the names of pilots certified for some Boeing aircraft.
iii) Find the aids of all aircraft that can be used on non-stop flights from DELHI TO
MUMBAI.
iv) Find the names of pilots who can operate some plane with a range greater than
3,000 miles but are not certified on any Boeing aircraft.
v) Find the eids of employees who make the highest salary.
8. Consider a Cricket Tournment organized by an organization. In the tournament there
are many teams contesting each having a Teamid, Team_name, City, a coach. Each
team is uniquely identified by using Teamid. A team can have many players and a
captain. Each player is uniquely identified by playerid, having a Name, and multiple
phone numbers, age. A player represents only one team. There are many stadiums to
conduct matches. Each stadium is identified using Stadiumid, having a stadium_name,
Address (involves city, area_ name, pincode). A team can play many matches. Each
Match played between the two teams in the scheduled date and time in the predefined
Stadium. Each match is identified uniquely by using Matchid. Each match won by any
of the one team that also wants to record in the database. For each match man_of_the
match award given to a player.
Write SQL Queries to:
i) Display the youngest player (in terms of age) Name, Team name, age in which he
belongs of the tournament.
ii) List the details of the stadium where the maximum number of matches were
played.
iii) List the details of the player who is not a captain but got the man_of_match award
at least in two matches.
iv) Display the Team details who won the maximum matches.
9. Consider the schema for Movie Database:
ACTOR (Act_id, Act_Name, Act_Gender)
DIRECTOR (Dir_id, Dir_Name, Dir_Phone)
MOVIES (Mov_id, Mov_Title, Mov_Year, Mov_Lang, Dir_id)
MOVIE_CAST (Act_id, Mov_id, Role)
RATING (Mov_id, Rev_Stars)
Write SQL queries to:
i) Find the movie names where one or more actors acted in two or more movies.
ii) List all actors who acted in a movie before 2000 and in a movie after 2015 (use JOIN
operation).
iii) Find the title of movies and number of stars for each movie that has at least one rating
and find the highest number of stars that movie received. Sort the result by movie
title.
10. Create the following table with the following fields
Sales (Sales_No, Sales_Name, Branch, Sales_Amount, DOB)
Write SQL Queries to:
i) Insert five records
ii) Calculate total sales amount in each branch
iii) Calculate average sales amount in each branch.
iv) Display the name and DOB of salesman in alphabetical order of the month.