Q.1)Consider the following Entities and their Relationships
Department (deptno, deptname,location)
Employee (empno, empname, salary, commission, designation)
Relationship between Department and Employee is one-to-many.
Constraints :Primary Key
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL Procedure to display details of a given employee.
b)Write a Cursor to display total salary (Salary + Commission) of all employees.
Q.2)Consider the following Entities and their Relationships
Customer (cno, cname, city)
Account (ano, acc_type, balance)
Relationship between Customer and Account is one to many
Constraints:Primary key,
acc_type should not be null.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL function to return account balance of a given customer.
b)Create or replace a trigger that restricts insertion or updationof account having balance less than 100.
Q.3)Consider the following Entities and their Relationships
Movie (mvno, mvname, releaseyear)
Actor (actno, actname)
Movie and Actor are related with many to many relationships with descriptive attribute rate of actor formovie.
Constraints:Primary Key.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL Procedure to display details of all movies of actor ‘Amitabh’.
b)Write a cursor to display names of all movies which are released in year 2002.
Q.4)Consider the following Entities and Relationships
Book (bno, bname, pubname, price)
Department (dno, dname)
Relationship between Book and Department is many to one.
Constraints:Primary key,
Price should be > 0
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL function to return total expenditure on books of a given department.
b)Write a cursor to display details of all books brought for a ‘Computer’ department.
Q.5)Consider the following Entities and their Relationships
Book ( bno, bname, pubname, price)
Author (ano, aname)
The relationship between Book and Author is many-to-many.
Constraints : Primary Key,
aname and pubname should not be null.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL Procedure to display details of all books written by ‘Kanetkar’.
b)Create or replace a Trigger that restricts insertion or updation of books having price less than 0.
Q.6)Consider the following Entities and their Relationships
Employee (eno, ename, city, deptname)
Project (pno, pname, status)
Relationship betweenEmployee and project is many to manywith descriptive attribute number_of_days employee worked on that project.
Constraints:Primary key,
Status has to be C-Complete, P-Progressive,I-Incomplete
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL function to return total number of employees working on any project for more than 25 days.
b)Write a cursor to display all employee names working on a project whose status is Incomplete.
Q.7)Consider the following Entities and their Relationships
Customer (cno, cname, city)
Account (ano, acc_type, balance)
Relationship between Customer and Account is one to many
Constraints:Primary key,
Balance should be >100
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL procedure to find total balance of all customers of ‘Pune’ city.
b)Write a cursor to add interest of 3 % to the balance of all accounts whose balance is greater than 10000.
Q.8)Consider the following Entities and their Relationships
Doctor(dno, dname, dcity)
Hospital (hno, hname, hcity)
The relation between Doctor and Hospital is many-to-many.
Constraints :Primary Key,
dcity and hcity should not be null
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL Procedure to display details of all hospitals in ‘Pune’.
b)Write a cursor to list all hospitals and their doctor’s details.
Q.9)Consider the following Entities and their Relationships
Student (rollno, name, class, totalmarks)
Teacher (tno, tname)
The relation between Student and Teacher is many-to-many with subject as descriptive attribute.
Constraints :Primary Key,
Class has to be FY, SY or TY.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL Procedure to display details of all students of class ‘FY’.
b)Create or replace a Trigger that restricts insertion or updation of students having totalmarks less than 0.
Q.10)Consider the following Entities and their Relationships
Employee (eno, ename, city, deptname)
Project (pno, pname, status)
Relationship between Employee and projectismany to manywithdescriptive attribute number_of_daysemployee worked on that project.
Constraints:Primary key,
Status has to be C-Complete, P-Progressive,I-Incomplete
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL function to return number of incomplete projectsof given employee.
b)Create or Replace a trigger before update on status of project such that status of project once complete can not be changed. Display appropriate message.
Q.11)Consider the following Entities and their Relationships
Department (deptno, deptname,location)
Employee (empno, empname, salary, commission, designation)
Relationship between Department and Employee is one-to-many.
Constraints :Primary Key
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL Procedure to increase the salary of a given employee by 5% & display updated salary.
b)Write a Cursor to display details of all Employees of all Departments.
Q.12)Consider the following Entities and their Relationships
Customer (cno, cname, city)
Loan (lno, loan_amt, no_of_years)
Relationship between Customer and Loan is one to many
Constraints: Primary key,
loan_amt should be > 0.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL function to find total loan amount from ‘Mumbai’ city.
b)Write a cursor to display details of all customers who have taken loan for more than 3 years.
Q.13)Consider the following Entities and their Relationships
Movie (mvno, mvname, releaseyear)
Actor (actno, actname)
Movie and Actor are related with many to many relationships with descriptive attribute rate of actor formovie.
Constraints:Primary Key.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace PL/SQL function to return total number of movies of ‘Aishwarya’.
b)Create or replace a Trigger that restricts insertion or updation of movies released
before year 2005.
Q.14)Consider the following Entities and their Relationships
Book (bno, bname, pubname, price)
Department (dno, dname)
Relationship between Book and Department is many to one
Constraints:Primary key,
pubname should not be null
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL function to return total number of books purchased by a given department.
b)Create or Replace a trigger that restricts insertion or updation of book having price less than 0.
Q.15)Consider the following Entities and their Relationships
Book ( bno, bname, pubname)
Author (ano, aname)
The relation between Book and Author is many-to-many.
Constraints : Primary Key,
aname and pubname should not be null.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Write a cursor to display list of authors and their books details.
b)Create or Replace PL/SQL function to count the total number of books of ‘BPB’ Publication.
Q.16)Consider the following Entities and their Relationships
Customer (cno, cname, city)
Account (ano, acc_type, balance)
Relationship between Customer and Account is one to many
Constraints:Primary key,
acc_type should not be null.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Write a cursor to display details of all customers from ‘Pune’ city having A/C balance between 20000 and 40000.
b)Create or Replace a PL/SQL function to return total number of customers having ‘Saving’ account.
Q.17)Consider the following Entities and their Relationships
Doctor(dno, dname, dcity)
Hospital (hno, hname, hcity)
The relation between Doctor and Hospital is many-to-many.
Constraints :Primary Key,
dcity and hcity should not be null
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace PL/SQL function to count the total number of doctors who are visiting to ‘KEM’ hospital.
b)Write a cursor to list all doctors and hospitals visited by them.
Q.18)Consider the following Entities and their Relationships
Employee (eno, ename, city, deptname)
Project (pno, pname, status)
Relationship between Employee and projectismany to manywith descriptive attribute number_of_daysemployee worked on that project.
Constraints:Primary key,
Status has to be C-Complete, P-Progressive,I-Incomplete
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace PL/SQL procedure to increment number_of_days by 2 of all employees working on project having status progressive.
b)Create or Replace a trigger that restricts insertion or updation of table such that number_of_days should not be less than zero.
Q.19)Consider the following Entities and their Relationships
Student (rollno, name, class, totalmarks)
Teacher (tno, tname)
The relation between Student and Teacher is many-to-many with subject as descriptive attribute.
Constraints :Primary Key,
Class has to be FY, SY or TY.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL Procedure to display details of all teachers who are teaching subject ‘Data Structure’.
b)Create or replace a Trigger that restricts insertion or updation of students having totalmarks greater than 500.
Q.20)Consider the following Entities and their Relationships
Customer (cno, cname, city)
Loan (lno, loanamt, no_of_years)
Relationship between Customer and Loan is one to many
Constraints: Primary key,
loanamt should be > 0.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL procedure to display customer details who have taken maximum loan from ‘Pune’ city.
b)Create or Replace a trigger which will execute when loan amount is updated. Do not allow to update. Display appropriate message.
Q.21)Consider the following Entities and their Relationships
Department (deptno, deptname,location)
Employee (empno, empname, salary, commission, designation)
Relationship between Department and Employee is one-to-many.
Constraints :Primary Key,
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace PL/SQL function to return employee name having minimum salary in Computer Department
b)Create or replace a Trigger for an Employee table that restricts insertion or updation or deletion on Sunday.
Q.22)Consider the following Entities and their Relationships
Book (bno, bname, pubname, price)
Department (dno, dname)
Relationship between Book and Department is many to one
Constraints:Primary key,
Price should be > 0
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL procedure to increase the price of all books by 5 % for given pubname and display updated book details.
b)Write a cursor to display all department’s name having more than 2 books of ‘BPB’ publication.
Q.23)Consider the following Entities and their Relationships
Movie (mvno, mvname, releaseyear)
Actor (actno, actname)
Movie and Actor are related with many to many relationships with descriptive attribute rate of actor formovie.
Constraints:Primary Key.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL Procedure to display details of all actors acted in movie ‘Race’.
b)Write a cursor to display the list of actors and their movies for which their rate is greater than 20 Lakhs.
Q.24)Consider the following Entities and their Relationships
Customer (cno, cname, city)
Account (ano, acc_type, balance)
Relationship between Customer and Account is one to many
Constraints:Primary key,
acc_type should not be null.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL procedure that will accept any two account number and withdrawal amount as input parameter to transfer withdrawal amount from first account to second account.
b)Write a cursor to list all customers & their account details.
Q.25)Consider the following Entities and their Relationships
Student (rollno, name, class, totalmarks)
Teacher (tno, tname)
The relation between Student and Teacher is many-to-many with subject as descriptive attribute.
Constraints :Primary Key,
Class has to be FY, SY or TY.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace PL/SQL function to return student name that has scored maximum marks in ‘TY’.
b)Create or Replace PL/SQL function to return total number of teachers who are teaching subject ‘Accounts’.
Q.26)Consider the following Entities and their Relationships
Employee (eno, ename, city, deptname)
Project (pno, pname, status)
Relationship between Employee and project is many to many with descriptive attribute number_of_days employee worked on that project.
Constraints:Primary key,
Status has to be C-Complete, P-Progressive,I-Incomplete
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL procedure to find number of employees of agiven department working on project having status ‘Incomplete’.
b)Write a cursor to display details of all projects along with status and number_of_days on which a given employee works.
Q.27)Consider the following Entities and their Relationships
Department (deptno, deptname,location)
Employee (empno, empname, salary, commission, designation)
Relationship between Department and Employee is many-to-one.
Constraints :Primary Key,
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace PL/SQL function to return total number of employees working in ‘Computer’ Department
b)Create or replace a Trigger for updation of an Employee table that restricts new salary should not less than old salary.
Q.28)Consider the following Entities and their Relationships
Politician (pno, pname, description)
Party (partycode, partyname)
Relationship between Politician and Party is many to one
Constraints:Primary key,
partyname should not be null.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL function to return total number of politician of a given party.
b) Write a cursor to display details of all politicians of‘BJP’ party.
Q.29)Consider the following Entities and their Relationships
Movie (mvno, mvname, releaseyear)
Actor (actno, actname)
Movie and Actor are related with many to many relationships with descriptive attribute rate of actor formovie.
Constraints:Primary Key.
Create a RDB in 3NF & write queries in Oracle 8i for following.
a) Create or replace a Trigger that restricts insertion or updation of movies released
before year 2000.
b)Create or Replace PL/SQL function to return total number of actors acted in movie ‘Gajani’
Q.30)Consider the following Entities and their Relationships
Book (bno, bname, pubname, price)
Department (dno, dname)
Relationship between Book and Department is many to one
Constraints:Primary key,
Price should be > 0
Create a RDB in 3NF & write queries in Oracle 8i for following.
a)Create or Replace a PL/SQL procedure to display name of departmentspending maximum amount on books.
b)Write a cursor to display department wise expenditure on books.
1