Teacher Name: Siv Sankar Pati Mob: 6295670050/9851888437

DBMS PRACTICAL
1. Book(Acc_no,Title, Publisher, no_of_copy, Category)
Borrower(Card_no,B_Name,B_address)
Issue(Acc_no,Card_no,Date_of_issue,Date_of_return)
Write SQL query for the folowing:
a) Implement the above database in SQL.
b) Find the total number of “computer science” category book.
c) How many students are currently taken the book namely “Fundamental of Database
system”
d) Show the list of books of publisher “Pearson”.
e) Find the names of books having less than 5 copies.

2. Hotel(H_no,H_name,H_Address)
Room(R_no,H_no,Type,Charge)
Booking(H_no,G_no,R_no,Date_from,Date_to)
Guest(G_no,G_name,G_address)
Write SQL query for the folowing:
a) Implement the above database in SQL.
b) Find number of guests at “Hotel Taj”
c) Find names of guests who were at room number 5 of “Hotel taj” on 25th April,2018.
d) Find numbers of “Deluxe” type room at “Hotel taj”
e) Find names of hotels having more than total 50 rooms.
f) List Full details of all hotels.
g) List full details of all hotels in London.
h) List the names and addresses of all guests in London aplhabetically ordered by name.
i) List all double(‘D’) or family rooms(‘F’) with a price below 40.00 per night, in ascending order
of price.
j) List the booking for which no dataTo has been specified.
k) How many hotels are there?
l) What is the average price of a room?
m) What is the total revenue per night from all double rooms?
n) List the price and type of all rooms at the Grosvenor hotel.
o) List all guests currently staying at the Grosvenor hotel.
p) What is the total income from all bookings at the Grosvenor hotel today?
q) List the number of hotels with more than 100 rooms and located in London.
r) What is the most commonly booked room type for each hotel in London?

3. Supplier(Sid,Sname,city,status)
Parts(Pid,Pname,colour,weight)
SP(Sid,Pid,quantity)

Write SQL query for the folowing:

a) Implement the above database in SQL.
b) Find the names of suppliers located at the same place and supply parts.
c) Find names of suppliers who currently do not supplying any items.
d) Find the total number of suppliers.
e) Find total number of parts irrespective of its colour.
Teacher Name: Siv Sankar Pati Mob: 6295670050/9851888437

4. Student(Name,Roll,Addr,Ph_no,Email,Subject,Standard)
Teacher(Name,Id,Subject,Experience)
Write SQL query for the folowing:
a) Implement the above database in SQL.
b) Find the total number of students for each standard.
c) Find names of the teachers who belong to “Computer Science” having more than 5 years
experienced.
d) Find ratio of teacher and student.
e) Find name of the most serior teacher.
f) Find the total number of students.
g) Arrange names o fthe teaches according to their experience in decreasing order.
5. Employee(e_id,e_name,salary,address,hiredate)
Manages(e_id,Manager_id)

Write SQL query for the folowing:

a) Implement the above database in SQL.
b) Find the different bewteen maximum and minimum salaries of employee.
c) Find names of employees who are less than 5 years in company.
d) Find names of employees who got higher salary than that of their managers.
e) Find names of employees who joined within last one year.
f) Find average salary of employees.
g) Find names of employees who are at least 3 years in company.
h) List name and id of employees along with their name of their manager.
i) Find names of employees who joined after their manager.

6. Examination (Eid,Ename,Edate,Duration,Fees,Co_ordinator_name)

Write SQL query for the folowing:

a) Implement the above database in SQL.
b) Find total number of examinations held in January, 2018.
c) Find names of examinations which are under the same co-ordinator as “Physics”
examination.
d) Find the name of examination having minimum fees.
e) Find total numbers of co-ordinators of examinations.

7. Doctor(Did,Dname,Dept_name,Joining_date,Salary,Designation)
Patient(Pid,Did,Pname,P_addr,ph_no,admitted_dept_name)

Write SQL query for the folowing:

a) Implement the above database in SQL.
b) Find the name of patients who have admitted under “Dr.A.Roy”.
c) Find name of the doctor who has maximum number of patients of Neurology department.
d) List the names of doctors according to alphabetical order in orthopaedic department.
e) Find name of the highest paid doctor in pathology department.
f) Find the total number of patients of Neurology department.
g) List total number of doctors in pathology department.
Teacher Name: Siv Sankar Pati Mob: 6295670050/9851888437

8. Student (s_id,s_name,total_marks)
Subject (sub_id,sub_name,s_id,sub_marks)

Write SQL query for the folowing:

a) Implement the above database in SQL.
b) Find the student name who obtained highest marks.
c) List names of students who failed exactly two subject (less than 40% marks).
d) Find names of students who failed in all subjects.
e) Find the number of students who got 1st class (60 % and above).

9. Supplier(Sid,Sname,Scity,status)
Parts(Pid,Pname,Pprice,Date_of_manufactured)
SP(Sid,Pid,no_of_item)
Write SQL query for the folowing:
a) Implement the above database in SQL.
b) Find two suppliers who supply same product.
c) Find total number of suppliers who supply “Printers:.
d) Find the name of suppliers who supply printers with lowest price.
e) Find the name of suppliers located at kolkata.

10. Employee (Eno,Ename,Joining_date,Designation,Salary, Department_name)

Write SQL query for the folowing:
a) Implement the above database in SQL.
b) List names of employees working in “Research” department.
c) Find number of employees of Research department.
d) Find the names of managers who are working last 2 years.
e) Find the name of highest paid manager.

11. Project (p_no,p_name,manager_name)

Employee (e_no,e_name)
Assign_to (p_no,e_no)
Write SQL query for the folowing:
a) Implement the above database in SQL.
b) Find names of employees who are working more than one project.
c) Find names of projects where no employee is not still assigned.
d) Find the manager of the project located at kolkata.
e) Find the project which has maximum employees.
12. Account(Ano,Atype,balance,Custid,branch)
Customer (CustomerId,AccNo,CustName,Ph_no,address)
Write SQL query for the folowing:
a) Implement the above database in SQL.
b) Find the total number of customers of Kolkata branch.
c) Find total balance amount of saving accounts of Kolkata branch.
d) List branch and number of customers according to branch’s name alphabetical order.
e) Find names of customers who have both saving and recurring accounts.
Teacher Name: Siv Sankar Pati Mob: 6295670050/9851888437

13. Bus(Bid,Agency_Name,Bfrom,Bto,Fare,No_of_days_per_week)
Ticket (Tid,Tfrom,Tto,Fare,Date)
a) Implement the above database in SQL.
b) Find tghe name of customers who booked tickets from Midnapore to Kolkata on 21st
August, 2018.
c) Find the total number of buses which run from Midnapore to Kolkata .
d) Display fares of different buses from Midnapore to Kolkata.
e) Find the name of the bus agency who runs bus with minimum fare from Kolkata to
Midnapore.
14. AUTHOR(author_id,name,city,country)
CATALOG(book_id,title,author_id,publisher_id,category_id,year,price)
PUBLISHER(publisher_id,name,city,country)
CATEGORY(category_id,description)
ORDER_DETAILS(order_no,book_id,quantity)
Write SQL query for the folowing:
a) Create the above tables with properly specifying primary key and foreign key.
b) Enter atleast 5 records to each table.
c) Display the details of the authors who have teo or more books in the catelog and
publishing year is after 2000.
d) Find the author of the book that has maximum sales.
e) Demonstrate how you increase the price of books published by a specific publisher by
10%.
15. BRANCH (branch_name,branch_city,asset)
ACCOUNT(accno,branch_name,balance)
DEPOSITOR(customer_name,acc_no)
CUSTOMER(customer_name,customer_street,customer_city)
LOAN (lone_number,branch_name,amount)
BORROWER (customer_name,loan_number)
Write SQL query for the folowing:
a) Create the above tables with properly specifying primary key and foreign key.
b) Enter atleast 5 records to each table.
c) Find the customer who have atleast two accounts.
d) Find all the customer who have an accounts at all the branches located in a specific city.
e) Demonstrate how you delete all amount records at every branch located in a specific city.
16. STUDENT (regno,name,major,bdate)
COURSE(courseno,cname,dept)
ENROLL(regno,courseno,sem,mark)
BOOK-ADOPTION(courseno,sem,book_isbn)
TEXT(book-isbn,book_title,publisher,author)
Write SQL query for the folowing:
a) Create the above tables with properly specifying primary key and foreign key.
b) Enter atleast 5 records to each table.
c) Demostrate how you add new text books.
d) Display the list of text books(includes courseno,book_isbn,book_title) in the alphabetical
order for courses offered by the ‘CS’ department that use more than two books.
e) List any depart that has all its books published by a specific publisher.
Teacher Name: Siv Sankar Pati Mob: 6295670050/9851888437

17. CUSTOMER (custno,cname,city)

ORDER(orderno,odate,custno,ord_amt)
ORDER_ITEM (orderno,itemno,quantity)
ITEM(itemno,unitprice)
SHIPMENT(orderno,warehouseno,ship_date)
WARE_HOUSE(warehouseno,city)
Write SQL query for the folowing:
a) Create the above tables with properly specifying primary key and foreign key.
b) Enter atleast 5 records to each table.
c) Display the listing: (custname,No_of_orders) where the second column is the total
number of orders.
d) List the orderno of orders that were shipped from all the werehouse in a specific city.
e) Demonstrate deletion of an item from item table.

18. CUSTOMER (cust_id,cust_name)

ITEM (item_id,item_name,price)
SALE(bill_no,bill_date,cust_id,item_id,qty_sold)
Write SQL query for the folowing:
a) Create the above tables with properly specifying primary key and foreign key.
b) Enter atleast 5 records to each table.
c) Display the list specifying details of customer who have bought product which has a price
> 200.
d) Find the total bill details with the quantity sold,price of the item and the final amount.
e) Give a count of how many product have been bought by each customer.

19. EMPLOYEE(emp_id,emp_name)
DEPARTMENT(dept_id,dept_name)
PAYDETAILS(emp_id,dept_id,basic,deduction,additions,doj)
PAYROLL(emp_id,pay_date)
Write SQL query for the folowing:
a) Create the above tables with properly specifying primary key and foreign key.
b) Enter atleast 5 records to each table.
c) List all employee details departmentwise.
d) List all the employee who joined after a specific date.
e) Give the names of employee whose basic>5000.

20. SAILOR (sid,sname,rating,age)

BOATS(bid,bname,colour)
RESERVES(sid,bid,day)
Write SQL query for the folowing:
a) Create the above tables with properly specifying primary key and foreign key.
b) Enter atleast 5 records to each table.
c) List the sailors who have reserved for both ‘RED’ and ‘GREEN’ boats.
d) List the sailors in the decending order of their rating.
e) List the details of the oldest sailor for each rating level.
Teacher Name: Siv Sankar Pati Mob: 6295670050/9851888437

21. CUSTOMER (custno,custname,city,ph_no)

ITEM(itemno,itemname,itemprice,quantity)
INVOICE(invno,invdate,custno)
INVITEM (invno,itemno,quantity)

Write SQL query for the folowing:

a) Create the above tables with properly specifying primary key and foreign key.
b) Enter atleast 5 records to each table.
c) Display all the item name along with quantity sold,
d) Find the customer name who are not from ‘Kolkata’.
e) Display the customer name who bought maximum quantity of any specific item.

22. EMPLOYEE (eno,name,dob,doj,designation,basicpay,deptno)

DEPARTMENT(deptno,name)
PROJECT(projno,name,deptno)
WORKSFOR(eno,projno,hours)

Write SQL query for the folowing:

a) Create the above tables with properly specifying primary key and foreign key.
b) Enter atleast 5 records to each table.
c) List the department no. and number of employees in each department.
d) List the details of employee who worked in more than three projects.
e) Find the name of department which is engaged in maximum number of projects.

23. BUS (routno,source,destination)

PASSENGER( pid,pname,dob,gender)
BOOK_TICKET(pid,routno,journey_date,seat_no)

Write SQL query for the folowing:

a) Create the above tables with properly specifying primary key and foreign key.
b) Enter atleast 5 records to each table.
c) Display the passenger who had booked the journey from Kolkata to Medinipur on 03
Nov,2018
d) List the details of passenger who had travelled more than three times on the same route.
e) Include constraint that DOB of passenger should be before 2008.

24. Hotel(hno,hname,type[boys/girls])
Menu(hno,day,breakfast,lunch,dinner)
Warden(wname,qual,hno)
Student(sid,sname,gender,year,hno)
Write SQL query for the folowing:
a) Display the total number of girls and boys hostel in the college.
b) Display he menu in the hostel ‘X’ on Tuesday.
c) Display the number of wardens for each hostel.
d) Find the capacity(in term of no. of students) of each hostel.
e) Find the name of hotel which has highest capacity.
Teacher Name: Siv Sankar Pati Mob: 6295670050/9851888437

25. Department (dept_id,dept_name)

Student(rollno,name,gender,mark1,mark2,mark3,total, average,dept_id)
Staff(staff_id,name,designation,qualification,dept_id)
Tutor(rollno,staff_id)
a) Display the student details who come under the tutor ship of the given staff name’X’.
b) Dipaly the student details who got greater than overall average marks of their department.
c) How many students are there in CSE department.
d) Count the total number of staffs for each department.
e) Find the name of the students who have maximum tutors.
f) Display the staff details who work in CSE department.
g) How many differents designations and departments are there?
h) Display the student details whose name start with ‘R’.
i) Arrange names of the departments according to their average marks.
j) Find name of the student who got overall highest marks.

26. Employee(ssn,first_name,last_name,gender,designation,doj,address)
Employee_salary(ssn,basic_pay,DA,TA,pay)
Department(did,dname,mgrssn)
Employee_department(ssn,deptid)
Employee_dependency(ssn,depname,dpgender,deprelationship)
a) Retrieve the names of employees who have no dependents.
b) Retrieve all the information about employees working in ‘Research’ department including
the department information .
c) Display the department having employee count > 5.
d) Find names of employees who have more than two dependents.
e) Find the second highest paid employee.
f) Retrieve the doj,address of employees who work for ‘Research’ department.
g) For each employee,retrieve the employee’s first name and last name.
h) Retrieve the names of each employee who has a dependency with same first name and
gende rof that empoyee.
i) Display names of employees with total salary according to alphabetical order.
j) Find name of the highest paid male employee.

27. Customer(Cust_id,Cust_name,Addr,ph_no,pan_no)
Loan(Loan_id,Amont,Interest,Cust_id)
a) Display the cust_namehaving Loan and Account.
b) Display number of Loans, the sum of Loan Amount of a particular Customer(“LEENA”).
c) Display the Custname doesn’t hold any account nor taken any loan.
d) Add a column no1(number of loans).

28. stu_details(reg_no,stu_name,DOB,address,city)
marks_details(reg_no,mark1,mark2,mark3,total)
a) Display only those rows whose total ranges between 250 and 300.
b) Drop the table mark_details.
c) Delete the row whose reg_no= 161.
d) Display all details whose names begins with ‘a’.
Teacher Name: Siv Sankar Pati Mob: 6295670050/9851888437

29. Inventory(item,level,cost)
Minilevel(item,level)
Reorder(item,quantity)
Purchase(item,quantity,cost,customer_name,date_of_purchase)
a) Display the number of customers for the shop on a particular day.
b) Write a query to display the item purchased by a given customer name.
c) Display the overall income for the shop on a given date.
d) Find highest cost item name.
e) Find frequently selling item name.
30. Party(pid,pname,leader)
Constituency(cid,cname)
Contestant(ctid,ctname,ctaddr)
Election(ctid,number_of_votes,pname,cname)
a) Display the contestant details if they secured greater than 10000 votes.
b) Find the number of contestants,constituency wise.
c) Display the winner details in each constituency.
d) Find the name of winner who got maximum votes.
e) Find the winner party name.

31. Branch(bname,bcity,assets)
Account(ano,starting_date,balance)
Customer(cusid,name,address)
Deposit(ano,cusid,bname)
Transection(ano,amount,mode,date_of_trans)
a) Find the average account balance at each branch and display only if it is greater than
10000.
b) Display the branch details located in a city starting with the letter ‘S’.
c) Find the number of depositors in each branch.
d) Find total of last 5 deposit amount.
e) Find name of the customer who has highest balance out of all branches.

32. Flights(fno,from,to,distance,departs)
Aircrapt(aid,aname,range)
Certified(eid,aid)
Employees(eid,ename,salary)
a) Find names of pilots who are certifies on Boeing.
b) Find aid’s of aircrapt that can fly non stop from LA to NY,
c) Find eid of employees with the second highest salary.
d) Find names of pilots who can operate planes with a range greater than 3000 miles.
e) Find eid’s of employee certified on exactly three aircraft.

33.Employee (I'd, name , designation, sallary)

Company (type, manufactur, location)
a)Display the name of the employee who's salary in between 10k-20k
b)Display the I'd and salary who's designation and manager
c)Display the manufacturer product where location in Kolkata
Teacher Name: Siv Sankar Pati Mob: 6295670050/9851888437

34. EMP717(eno,ename,dname,sal)
PROJECT(pno,pname)
WORK(eno,pno)
a) Display the name of the employee who are working on a project name ’Banking system’.
b) Find the number of employee who are working on a given project.
c) Increase salary by 10% who get lowest salary .
d) Find the employee’s information and pno who are working on some project where
pno>13.
e) Display the name of the employee who are not working in any project.

35. Supplier3(sid,sname,saddress)
Parts3(pid,pname,color)
Catalog3(pid,sid,cost)
a) Find the name of the supplier who supply green parts.
b) Find the name of the supplier who supply both blue and green parts.
c) Find the name of the supplier who Supply all parts.
d) Find the name of the parts that has low cost.
e) Increase cost by 20% for 11,12,15.

36. EMP101(eno,ename,esal,jobstatus)
a) Increase salary by 30% for every employee.
b) Increase salary by 20% for the employee whose eno is 2.
c) Find the total salary for jobstatus ‘Assistant professor’
d) Find the total number of Employee.
e) Find the average and total salary.
f) Display the name of the employee whose salary is greater than the average salary.
g) Display the name of the employee who get highest salary.
h) Display the name of the employee who get second highest salary.
i) Delete the record from employee table whose salary is less than 5000.

37. Student11(roll,name,address,mark)
a) Display the address and mark of the student where roll is 2
b) Display all student’s result by ascending order.
c) Display all student’s result by descending order.
d) Display student details by order of name.
f) Find the average mark.
g) Display the name of the student who get heighest mark.
h) Display the name of the student who get lowest mark.

38. Employee0(eno,ename,dname,sal,jobstatus)
a) Find the total salary where job status is Teacher.
b) Find the department total salary.
c) Find the department total salary for same job status.
d) Find the total number of Lacturer.
e) Find the department wise total Employee0 for same job status.