[Solved] Database Laboratory Assignment # 3

1. Use the company database created in Assignment # 2.

• Write a PL/SQL program to raise the salary of employees as follows:

All managers: 20% increase

Dno % of increase

• 10
• 15
• 18

• Write a PL/SQL program to find maximum salary earners name (use cursor)
• Write a PL/SQL program to find top N/2 salary earners name

2. Consider a bank database with only one relation

Transaction (transno, acctno, date, amount)

The amount attribute value is positive for deposits and negative for withdrawals

• Define an SQL view TP containing the information (accno, T1.date, T2.amount) for every pair of transactions T1, T2 such that T1 and T2 are transactions on the same account and the date of T2 is the date of T1.

• Using only the above view TP, write a query to find for each account the minimum balance it ever reached (not including the 0 balance when the account is created). Assume there is at most one transaction per day on each account and each account has had at least one transaction since it was created. To simply your query, break it up into 2 steps by defining an intermediate view V.

3. Consider following two relations

Book_stock(Book_id, Title, No of Copies)

Book_Issue(card_no, cholder_name, book_id, issue_date, due_date)

Book_Return(card_no, cholder_name, book_id, return_date, issue_date)

Write a PL/SQL program for issuing/return of books with following conditions:

The program will take option I(issue) / R(return) along with card_no from user. Accordingly, insert records in Issue and Return relations. Date of issue/return must be populated with SYSDATE. Before issuing book, check the following constraints: A member is not allowed to issue more than 3 books. A member is not allowed to issue more than one copy of same bookid.

After issuing/return of book the no of copies must be updated. If the return date of the book is later than the due date; insert a record in a new table called Fine (card_no, amt). Assume a fixed late fine amt.

4. Given a relation graph (P,Q, cost) where P and Q attributes represent vertices associated to edges in the graph and cost represent weight.

Write SQL/PL-SQL program for the followings

• Find the vertices with max and min degree.
• List all the path of length 2 with total cost less than 10.

5. Consider the following Table in a banking database:

Account(Accno, Accname, Type, Balance)

Write a PL/SQL program for withdraw and deposit of amount on a given account with following features: The program should check conditions for insufficient fund (before withdraw) and raise error. For successful transaction, the program must update Account relation and subsequently insert records of the transactions in a new table called Transaction with following definition:

Transaction(Tr_id, Accno, Tr_type, Amt, date of Tr)

Deadline of Submission: 28.01.2020