Reference no: EM133272936

Question 1

Procedure
Consider table Student (Roll, Name, Attendance ,Status).Write a PL/SQL block for following requirement and handle the exceptions. Roll no. of student will be entered by user. Attendance of roll no. entered by user will be checked in Stud table. If attendance is less than 75% then display the message "Term not granted" and set the status in stud table as "Detained". Otherwise display message "Term granted" and set the status in stud table as "Not Detained"

Question 2

Account(Acc_no, branch_name,balance) branch(branch_name,branch_city,assets) customer(cust_name,cust_street,cust_city) Depositor(cust_name,acc_no) Loan(loan_no,branch_name,amount) Borrower(cust_name,loan_no)
Solve following query:
Create above tables with appropriate constraints like primary key, foreign key, check constrains, not null etc.
a) Find the names of all branches in loan relation.
b) Find all loan numbers for loans made at 'Shivaji Nagar' Branch with loan amount > 12000.
c) Find all customers who have a loan from bank. Find their names,loan_no and loan amount.
d) List all customers in alphabetical order who have loan from Akurdi branch.
e) Find all customers who have an account or loan or both at bank.
f) Find all customers who have both account and loan at bank.
g) Find all customers who have account but no loan at the bank.
h) Find average account balance at Akurdi branch.
i) Find the branches where average account balance > 12000.
j) Find number of tuples in customer relation.
k) Calculate total loan amount given by bank.
l) Delete all loans with loan amount between 1300 and 1500.
m) Delete all tuples at every branch located in Nigdi.
n) Create sequence roll_seq and use in student table for roll_no column.

Question 3 Trigger

Write a after trigger for Insert, update and delete event considering following requirement: Emp(Emp_no, Emp_name, Emp_salary)
a) Trigger should be initiated when salary tried to be inserted is less than Rs. 50,000/-
b) Trigger should be initiated when salary tried to be updated for value less than Rs. 50,000/-
c) Also the new values expected to be inserted will be stored in new table Tracking(Emp_no, Emp_salary).

Question 4
Write Unnamed PL/SQL block of code for the following requirements:- Exception handling is mandatory.
Borrower(Rollin, Name, DateofIssue, NameofBook, Status) Fine(Roll_no,Date,Amount_Fine)
Accept Roll_no and Name of book from user. Check the number of days (from date of issue), if days are between 15 to 30 then the fine amounts will be Rs 5 per day. If no. of days>30, per day fine will be Rs 50 per day & for days less than 30, Rs. 5 per day.
After submitting the book, status will change from I to R.
If condition of fine is true, then details will be stored into Fine table.

Question 5
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. Stud_Marks(Roll, Name, Total_marks)
Result(Roll, Name, Class)

Question 6

Trigger
Consider CUSTOMER (ID, Name, Age, Address, Salary) create a row level trigger for the CUSTOMERS table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values.

Question 7
Trigger Create the customer_Master table(cust_no, Cust_name, DOB, Addr). Create row level trigger when an update, insert or delete operation is performed on the Customer_Master table. Depending on the operation being performed, a variable is assigned the value update, insert or delete. The previous and new values of the modified records
should be inserted in the Customer_Audit table.

Question 8 Basic SQL queries
Create table Cust_Master(Cust_no, Cust_name, Qty_Ordered, Order_date, Cust_addr ) Cust_no is defined as primary key,
Insert ten records in the table.
List names of customers having 'a' as second letter in their name. List customers who stay in city whose first letter is 'M'
Display order from Customer no C1002,C1005,C1007 and C1008 List Clients who stay in either 'Banglore or 'Manglore'
Create view Customer_View consisting of Cust_no, Qty_ordered and Order_date

Question 9
MongoDB CRUD operations, Create collection Employee consisting of Emp_id, Emp_Name, Emp_salary, Emp_Dept.
Insert 10 Documents in the collection.
Find the employees whose salary is greater than 50000 Rs. Increase the salary of Smith by 5000 Rs
Display the information of employees working in Marketing department.with less than 45000 salary .
Display first five highest paid employees Delete Employee with Id ‘E1007'
Create an Index on Emp_Id field , compare the time require to search Emp_id ‘E10008' before and after creating an index. (Hint Add at least 10000 Documents)

Question 10
Basic SQL Create following tables with suitable constraints. Make suitable use of AUTO_INCREMENT.
Insert data and solve the following queries:
PROPERTIES(Pno, Type, Sq_Ft_Area, Rent, Address, Status, Owner No) OWNERS (OwnerNo, OwnerName, Phno)
1. Display all 1BHK apartments in Kothrud which are not rented
2. Display all properties owned by "Gopal"
3. Write a query to display the smallest property of each owner
4. Display all properties in Kothrud in Descending order of rent
5. Create a view which shows OwnerName along with his Pno, type, Address and Rent
6. Display the names of all Owners whose name has "ee"
7. Display Pno, Type, Address, Rent and status of all properties with rent greater than 15000/- and less than 22000/-
8. Display different property types registered with the real estate agency

Question 11
Basic SQL Create following tables with suitable constraints. Make suitable use of AUTO_INCREMENT.
Insert data and solve the following queries:
PROPERTIES(Pno, Type, Sq_Ft_Area, Rent, Address, Status, Owner No) CLIENTS(ClientNo, ClientName, Phno, Requirement)
RENTAL(Pno, ClientNo, FromDate, ToDate)

1. Write a query that produces a list of all available properties
2. Write a query to list all properties which will be available in December 2017
3. Write a query to count the number of properties area wise
4. Display the names of all Owners whose name starts with 'R'
5. Create a view to show ClientName along with the Pno, Type, Address and Rent of the property which he has rented
6. Display the alphabetical list of Clients
7. Increase rent of Pno 1006 by 2000/-
8. Delete record of Pno=1007

Question 12
(Java-Mysql 2 Tier connectivity)
Create Books table in Mysql and Write a Java program to perform Insert, Update, Delete, and Display All operations on the table

Question 13
(Java-Mongo 2 Tier connectivity)
Create collection Student in MongoDB and Write a Java program to perform Insert, Update, Delete, and DisplayAll operations on the collection

Question 14
MapReduce
Create a customer collection consisting of fields like name, email ID, profession, gender, bill amount
1. Write a MapReduce query for finding the count of male and female customers in the collection
2. Write a MapReduce query for finding the count of each profession in the collection
3. Display list of all customers with bill amounts greater than 5000/-
4. Update the bill amount of any one customer
5. Display all customers with name starting with 'B'
6. Display list of all customers with profession = "Business"
7. Display all customers in Descending order of Bill amount
8. Create an index on name field of customer collection. Also use the explain() function

Question 15 Mongo Aggregation

Create a student collection consisting of fields like Roll No, name, class, marks, sports etc
1. Create an index on name field of employee collection. Also use the explain() function
2. Display the first 5 toppers of TE
3. Display marks of topper of each division
4. Display the average marks of each division
5. Display the rollcall of TEComp A
6. Display list of fail students from TE Comp A
7. Display Name, Class and Marks of all students
8. Display list of students who play football

Question 16 PL/SQL procedure
Create the following tables with suitable constraints MEMBERS(mem_id, mname, addr, phno) BOOKS(Bno, bname, publisher, cost, DOP,status)
ISSUE_RETURN(mem_id, Bno, issue_date, return_date, fine)
Write a PL/SQL procedure which requires mem_id, Bno and issue _date as arguments. The procedure calculates the fine and performs the book returning operation
Assume suitable conditions for calculating fine.

Question 17 PL/SQL procedure
Create the following table with suitable constraints. Put NULL value in the Class field while entering the data.
STUDENT( Rno, Name, TotMarks, Class)
Write a PL/SQL procedure to find the class of every student and update the Class field of the STUDENT table accordingly
if marks<=1500 and marks >=990 then Class
= Distinction if marks<=989 and marks
>=900 then Class = First Class
if marks<=899 and marks >=825 then Class = Higher Second Class if marks<=824 and marks >=600 then Class = Second Class if marks<600 then class = Fail

Question 18 Stored procedure
Create tables Old_Roll_Call and New_Roll_Call with fields Rno, Name and Address
Write a stored procedure to copy all records from Old_RollCall table to New_RollCall table which are already not present in the New_RollCall table.

Question 19 PL/SQL function and trigger
Create the following table with suitable constraints. STUDENT( Rno, Name, TotMarks, Class)
Write a PL/SQL function to count the number of Distinction, FirstClass, HigherSecondClass, Second Class and Fail students
Create table BOOKS and Write an after delete trigger on the table which will store the old record in the Books_LOG table

Question 20 Basic SQL
Create following tables with suitable constraints. Make suitable use of AUTO_INCREMENT.
Insert data and solve the following queries:
CUSTOMERS(CNo, Cname, Ccity, CMobile) ITEMS(INo, Iname, Itype, Iprice, Icount) PURCHASE(PNo, Pdate, Pquantity, Cno, INo)
1. List all stationary items with price between 400/- to 1000/-
2. Change the mobile number of customer "Gopal"
3. Display the item with maximum price
4. Display all purchases sorted from the most recent to the oldest
5. Count the number of customers in every city
6. Display all purchased quantity of Customer Maya
7. Display list of customers whose name ends with 'a'
8. Create view which shows Iname, Price and Count of all stationary items in descending order of price