Problem Statement: - 1
Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table,
View, Index, Sequence, Synonym.
PEOs ,POs, PSOs and COs satisfied
PEOs : 1 POs : 1,2,5 PSOs : 1 COs :1
Consider the following database where the primary keys are underlined. Create the
following tables in oracle/MySQL
1)
Person (driver_id, name, address)
Car (license, model, year)
Accident (report_no, date_acc, location)
Owns (driver_id, license)
Participated (driver_id, model, report_no, damage_amount)
2)
Employee (employee_name,street, city)
Works (employee_name,company_name,salary)
Company (company_name,city)
Manages (employee_name,manager_name)
1) Create view with the employee_name, company_name by using above tables.
2) Create index for employee & participated table.
3) Create sequence for person & insert 4 records using sequence.
4) Create the synonym for table participated & company. Display the record using this
table. Update the record using the synonym tables.
Problem Statement: - 02
Design at least 10 SQL queries for suitable database application using SQL DML statements:
Insert, Select, Update, Delete with operators, functions, and set operator.
PEOs ,POs, PSOs and COs satisfied
PEOs : 1 POs : 1,2,5 PSOs : 1 COs :1
Note : Use Primary key, foreign keys, unique, not null, null constraints whenever
necessary.
Create table Department & Insert the following records by using any one method
Deptno Dname Location
10 Accounting Mumbai
20 Research Pune
30 Sales Nashik
40 Operations Nagpur
Create table employee as shown below.
Empno Ename Job Mgr Joined_date Salary Commission Deptno Address
1001 Nilesh joshi Clerk 1005 17-dec-95 2800 600 20 Nashik
1002 Avinash pawar Salesma 1003 20-feb-96 5000 1200 30 Nagpur
n
1003 Amit kumar Manager 1004 2-apr-86 2000 ---- 30 Pune
1004 Nitin kulkarni Presiden -- 19-apr-86 50000 ---- 10 Mumbai
t
1005 Niraj Sharma Analyst 1003 3-dec-98 12000 ---- 20 Satara
1006 Pushkar Salesma 1003 1-sep-96 6500 1500 30 Pune
deshpande n
1007 Sumit patil Manager 1004 1-may-91 25000 ---- 20 Mumbai
1008 Ravi sawant Analyst 1007 17-nov-95 10000 ---- --- Amaravati
1) Write a query to display employee information. Write a name of column explicitly.
2) Create a query to display unique jobs from the table.
3) Change the location of dept 40 to Banglore instead of Nagpur.
4) Change the name of the employees 1003 to Nikhil Gosavi.
5) Delete Pushkar deshpande from employee table.
6)
using OR & IN operator).
7) Display the employee name & department number of all employees in dept 10,20,30
& 40.
8)
9) Find al
10) Find the department number, maximum salary where the maximum salary is
greater than 5000.
Problem Statement: - 03
Design at least 10 SQL queries for suitable database application using SQL DML statements: all
types of Join, Sub-Query and View
PEOs ,POs, PSOs and COs satisfied
PEOs : 1 POs : 1,2,5 PSOs : 1 COs :1
Note : Use Primary key, foreign keys, unique, not null, null constraints whenever necessary.
Design the employee database with all constraints. Construct the following SQL queries for this
relational database.
Employee(employee_name,street,city)
Works(employee_name,company_name,salary)
Company(company_name,city)
Manages(employee_name,manager_name)
1. Find the names of employees who work for First Bank Coorporation.
2. . Find the names and cities of residence of all employees who work for First Bank Coorporation
3. Find the names, street addresses, and cities of residence of all employees who work for First Bank
Coorporation and earn more than $10000.
4. Find all employees in the database who earn more than each employee of Small Bank
Coorporation
5. Find all employees who earn more than the average salary of all employees of their companies.
6. Find the company that has the smallest payroll.
7. Find those companies whose employees earn a higher salary, on average, than the average salary at
First Bank Coorporation.
8.
9. Insert the names and salaries of employees who earn more than the average salary into a new table
called HighEarners
10. Delete employees from the Employee table who work for a company in the Company table that
is located in Gotham.
Problem Statement: - 04
Unnamed PL/SQL code block: Use of Control structure and Exception handling is mandatory.
PEOs ,POs, PSOs and COs satisfied
PEOs : 1 POs : 1,2,5 PSOs : 1 COs :1
Write a PL/SQL block of code for the following requirements:-
Schema:
1. Borrower(Rollin, Name, DateofIssue, NameofBook, Status)
2. Fine(Roll_no,Date,Amt)
1. Accept roll_no & name of book from user.
2. Check the number of days (from date of issue), if days are between 15 to 30 then fine
amount will be Rs 5per day.
[Link] no. of days>30, per day fine will be Rs 50 per day & for days less than 30, Rs. 5 per
day.
[Link] submitting the book, status will change from I to R.
[Link] condition of fine is true, then details will be stored into fine table.
Frame the problem statement for writing PL/SQL block inline with above statement
Problem Statement: - 05
Cursors: (All types: Implicit, Explicit, Cursor FOR Loop, Parameterized Cursor)
PEOs ,POs, PSOs and COs satisfied
PEOs : 1 POs : 1,2,5 PSOs : 1 COs :1
Write a PL/SQL block of code using parameterized Cursor, that will
1. merge the data available in the newly created table N_RollCall with the data available
in the table O_RollCall.
2. If the data in the first table already exist in the second table then that data should be
skipped.
3. Frame the separate problem statement for writing PL/SQL block to implement all
types
Problem Statement: - 06
PEOs ,POs, PSOs and COs satisfied
PEOs : 1 POs : 1,2,5 PSOs : 1 COs :1
Write a Stored Procedure namely proc_Grade for the categorization of student.
If marks scored by students in examination is <=1500 and marks>=990
then student will be placed in Distinction category
if marks scored are between 989 and 900 category is First Class,
if marks 899 and 825 category is Higher Second Class.
Write a PL/SQL block for using procedure created with above requirement.
1. Stud_Marks (Roll, Name, Total_marks)
2. Result (Roll, Name, Class)
Frame the separate problem statement for writing PL/SQL Stored Procedure and function,
inline with above statement.
Problem Statement: - 07
Database Trigger (All Types: Row level and Statement level triggers, Before and
After Triggers).
PEOs ,POs, PSOs and COs satisfied
PEOs : 1 POs : 3,4,5 PSOs : 1 COs :2
Write a database trigger on Library table.
The System should keep track of the records that are being updated or deleted.
The old value of updated or deleted records should be added in Library_ Audit table.
Frame the problem statement for writing Database Triggers of all types, in-line with above
statement.