DBMS_QP_SET

1. Design an ER model for the college database with all the required keys.
Define the
relationships based on the given schema.
FACULTY(Fno, Name, Gender, Age, Salary, Dnum)

DEPARTMENT(Dno, Dname, Dphone)
COURSE(Cno, Cname, Credits, Odno)
TEACHING(Fno, Cno, Semester)
Dnum is a foreign key that identifies the department to which a faculty belongs. Odno is a
foreign key, identifying the department that offers a course.
Write SQL queries to
1. List the names and department names of faculty members.

2. List the names of faculty members not offering any course.
3. List the names of departments offering more than three courses, in alphabetic order.
4. List the Course numbers and names of 3-credit courses offered by ‘CS’ department.
5. List the names of departments along with the number of courses offered by each of them, in
the increasing order of the number of courses.
2. Consider the schema for the Movie Database. Design an ER model for the database.
Define the relationships based on the given schema.
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)
1. List the titles of all movies directed by ‘Hitchcock’.

2. Find the movie names where one or more actors acted in two or more movies.
3. List all actors who acted in a movie before 2000 and also in a movie after 2015
(use JOIN operation).
4. Create a procedure to find the titles of movies and the number of stars for each
movie that has at least one rating for the given director. Sort the result by movie
title.
5. Update rating of all movies directed by ‘Steven Spielberg’ to 5.
3. Design an ER model for the company database. Define the relationships based on
the given schema.
EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN, DNo)

DEPARTMENT (DNo, DName, MgrSSN, MgrStartDate)
DLOCATION (DNo, DLoc)
PROJECT (PNo, PName, PLocation, DNo)
WORKS_ON (SSN, PNo, Hours)
1. List the details of employees who work in a particular department located in a

particular city.
2. Retrieve the details of the person to which the given employee reports.
3. The company has a policy that the minimum salary for an employee is 15000.
Create a trigger to check if the salary entered while inserting a tuple is greater
than or equal to 15000. Else generate an error message with the text “salary
should be greater than or equal to 15000”
4. List all the employees who work a minimum of 40 hours on a particular project
5. Create a view of all projects and the total number of hours.
4. Design an ER model for the College management system database. Define the relationships based
on the given schema.
STUDENT (USN, SName, Address, Phone, Gender) SEMSEC (SSID, Sem, Sec)
CLASS (USN, SSID)
SUBJECT (Subcode, Title, Sem, Credits)
IAMARKS (USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)
Create tables based on the above schema, add constraints (PRIMARY KEY, FOREIGN KEY), and
insert relevant data.
1. List all the student details studying in the fourth semester ‘C’ section.
2. Compute the total number of male and female students in each semester and
each section.
3. Display semwise student details along with semester and section in ascending
order.
4. Create a view of Test1 marks of students for the given USN in all subjects.
5. Create a procedure to calculate the Final IA (average of 3 internals) of the given

USN and subject and print the Final IA using out parameter.
5. Design an ER diagram for a Company with the following 3 tables with all the required
keys. Define the relationships based on the given schema.
worker(wid, name, age, mob,hramount)

supervisor(sid, sname, mob)
work(sid, wid, no_hrs, totalpay)
1. List the details of all workers, supervisors, and works.

2. List the details of workers who charge the highest hourly amount.
3. List all the workers working under the supervisor ‘Ramesh”.
4. List the total payment paid by each supervisor separately.
5. Create a procedure to give all workers a Rs.500 hike in hourly amount. Update the table
accordingly.
6. Design an ER model for the library management system database. Define the
relationships based on the given schema.
BOOK (Book_id, Title, Publisher_Name, Pub_Year)
BOOK_AUTHORS (Book_id, Author_Name)
PUBLISHER (Name, Address, Phone)
BOOK_COPIES (Book_id, Branch_id, No-of_Copies)
BOOK_LENDING (Book_id, Branch_id, Card_No, Date_Out, Due_Date)
LIBRARY_BRANCH (Branch_id, Branch_Name, Address)
Create tables based on the above schema, add constraints (PRIMARY KEY, FOREIGN
KEY) and insert relevant data.

1. Retrieve details of all books in the library – id, title, name of publisher, authors,
number of copies in each branch, etc.
2. Get the particulars of borrowers who have borrowed more than 3 books, but from
Jan 2021 to Jun 2021.
3. Retrieve all books published by an author.
4. Delete a book in the BOOK table. Update the contents of other tables to reflect
this data manipulation operation. (Use trigger to update the contents of other
tables).
5. Create a view of all books and the number of copies that are currently available in
the Library.
7. Design an ER model for the company database. Define the relationships based on
the given schema.
EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN, DNo)
DEPARTMENT (DNo, DName, MgrSSN, MgrStartDate)
DLOCATION (DNo, DLoc)
PROJECT (PNo, PName, PLocation, DNo)
WORKS_ON (SSN, PNo, Hours)
1. Make a list of all project numbers for projects that involve an employee whose
last name is ‘Scott’, either as a worker or as a manager of the department that
controls the project.
2. Show the resulting salaries of every employee working on the ‘IoT’ project is
given a 10 percent raise.
3. Find the sum of the salaries of all employees of the ‘Accounts’ department, as
well as the maximum salary, the minimum salary, and the average salary in this
department.
4. Retrieve the name of each employee who works on all the projects controlled by
department number 5 (use NOT EXISTS operator). For each department that has
more than five employees, retrieve the department number and the number of its
employees who are making more than Rs. 6,00,000
8. Design an ER diagram for the bus management system database. Define

relationships based on the given schema
BUS (B_NO, CAPACITY, DEPOT_NAME)

ROUTE(ROUTE_NO, SOURCE, DESTINATION, NO_OF_STATIONS)
DRIVER(D_NO, D_NAME, LICENSE_NO, ADDRESS, AGE, SALARY)
BUS_ROUTE(BNO, ROUTE_NO)
BUS_DRIVER(B_NO,D_NO, DUTY_DATE, SHIFT). The shift can be
1(Morning)or 2(Evening ).
Create tables based on the above schema, add constraints (PRIMARY KEY, FOREIGN
KEY) and insert relevant data.

1. Find out the route details on which buses of capacity 20.
2. Print the names of drivers working in both shifts.
3. Increase the salary of all drivers by 5% if the driver’s age is greater than 45.
4. Define a trigger after insert or update the record of the driver if the age is between
18 and 50 give the message “valid entry” otherwise give the appropriate message.
5. Write a function to accept the bus_no, date & print its allotted driver.
9. Design an ER diagram for an Order Processing Database application in a Company with all the
required key. Define the relationships based on the given schema.
Customer(Customerno, Cname)
CUST_ORDER (Orderno, Odate, Customerno, Ord_amt)
ITEM (Itemno, Item_name, Unit_price)
ORDER_ITEM (Orderno, Itemno, Qty)
1. List the details of customers who have placed more than 3 orders in a day.
2. List details of items whose price is less than the average price of all items in each order.
3. List the details of items that are present in 25% of the orders.
4. Create a view that keeps track of the detail of each customer and the number of orders placed.
5. Create a procedure to update the unit price of an item by passing a percentage increase of price
to the procedure.
10. Design an ER diagram for a Hospital management system Database application with all the
required keys. Define the relationships based on the given schema.
PATIENT(PatientID, Name, Age, Gender, Phone)

DOCTOR(DoctorID, Name, Specialty, Department, Salary)
APPOINTMENT(AppointmentID, PatientID, DoctorID, Date, Time)
Practice Questions:
1. List the names of doctors along with their specialty.

2. Find the number of appointments scheduled with each doctor.
3. List the names of patients who have visited multiple doctors.
4. Display the department names along with the total number of doctors in each department.
5. List the names of doctors who earn more than the average salary of doctors in the same
department.

DBMS_QP_SET

Uploaded by

Copyright:

Available Formats

DBMS_QP_SET

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DBMS_QP_SET

Uploaded by

Copyright:

Available Formats

1. Design an ER model for the college database with all the required keys.

FACULTY(Fno, Name, Gender, Age, Salary, Dnum)

Write SQL queries to

1. List the names and department names of faculty members.

ACTOR (Act_id, Act_Name, Act_Gender)

Write SQL queries to

1. List the titles of all movies directed by ‘Hitchcock’.

EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN, DNo)

Write SQL queries to

1. List the details of employees who work in a particular department located in a

Write SQL queries to

5. Create a procedure to calculate the Final IA (average of 3 internals) of the given

worker(wid, name, age, mob,hramount)

Write SQL queries to

1. List the details of all workers, supervisors, and works.

Write SQL queries to

8. Design an ER diagram for the bus management system database. Define

BUS (B_NO, CAPACITY, DEPOT_NAME)

Write SQL queries to

Write SQL queries to

PATIENT(PatientID, Name, Age, Gender, Phone)

1. List the names of doctors along with their specialty.

You might also like