Practice Program List
1. Implement SQL DDL statements which demonstrate the use of SQL objects
such as Table, View, Index, Sequence, Synonym for following relational
schema:
Account(Acc_no, branch_name,balance)
2. Implement all SQL DML opeartions with operators, functions, and set
operator for given schema:
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)
Create above tables with appropriate constraints like primary key, foreign
key, check constrains, not null etc.
Solve following query:
a. Find the names of all branches in loan relation.
b. Find all loan numbers for loans made at Akurdi Branch with loan
amount > 12000.
c. Find all customers who have a loan from bank. Find their
names,loan_no and loan amount.
3. Implement all SQL DML opeartions with operators, functions, and set
operator for given schema:
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)
Create above tables with appropriate constraints like primary key, foreign
key, check constrains, not null etc.
Solve following query:
a. Find all customers who have an account or loan or both at bank.
b. Find all customers who have both account and loan at bank.
c. Find all customer who have account but no loan at the bank.
d. Find average account balance at Akurdi branch.
4. Implement all SQL DML opeartions with operators, functions, and set
operator for given schema:
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:
a. Find the average account balance at each branch
b. Find no. of depositors at each branch.
c. Find the branches where average account balance > 12000.
d. Find number of tuples in customer relation.
5. Implement all SQL DML opeartions with operators, functions, and set
operator for given schema:
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:
a. Calculate total loan amount given by bank.
b. Delete all loans with loan amount between 1300 and 1500.
c. Delete all tuples at every branch located in Nigdi.
6. Design at least 10 SQL queries for suitable database application
using SQL DML statements: all types of Join, Sub-Query and View.
7. Create the following tables.
Deposit (actno,cname,bname,amount,adate)
Branch (bname,city)
Customers (cname, city)
Borrow(loanno,cname,bname, amount)
Add primary key and foreign key wherever applicable.Insert data into the
above created tables.
a. Display account date of customers “ABC”.
b. Modify the size of attribute of amount in deposit
c. Display names of customers living in city pune.
d. Display name of the city where branch “OBC” is located.
8.Create following tables:
Deposit (actno,cname,bname,amount,adate)
Branch (bname,city)
Customers (cname, city)
Borrow(loanno,cname,bname, amount)
Add primary key and foreign key wherever applicable.Insert data into the
above created tables.
a. Display customer name having living city Bombay and branch city
Nagpur
b. Display customer name having same living city as their branch city
c. Display customer name who are borrowers as well as depositors and
having living city Nagpur.
9. Create the following tables.
Deposit (actno,cname,bname,amount,adate)
Branch (bname,city)
Customers (cname, city)
Borrow(loanno,cname,bname, amount)
Add primary key and foreign key wherever applicable.
Insert data into the above created tables.
a. Display loan no and loan amount of borrowers having the same
branch as that of sunil.
b. Display deposit and loan details of customers in the city where
pramod is living.
c. Display borrower names having deposit amount greater than 1000 and
having the same living city as pramod.
d. Display branch and living city of ‘ABC’
10.Create the following tables.
Deposit (actno,cname,bname,amount,adate)
Branch (bname,city)
Customers (cname, city)
Borrow(loanno,cname,bname, amount)
Add primary key and foreign key wherever applicable.Insert data into the
above created tables.
a. Display amount for depositors living in the city where anil is living.
b. Display total loan and maximum loan taken from KAROLBAGH
branch.
c. Display total deposit of customers having account date later than ‘2-Nov-
2017’.
d. Display maximum deposit of customers living in bombay.
11. Create following collection and using MongoDB implement all
basic operations..
Employee(emp_id, emp_name,emp_dept,salary)
12.Implement all Aggregation operations and types of indexing with
following collection using MongoDB.
Employee(emp_id, emp_name,emp_dept,salary)
13.Implement Map reduce operation with following example using
MongoDB
Employee(emp_id, emp_name,emp_dept,salary)
AND
Write a PL/SQL code to calculate total and percentage of marks
of the students in four subjects.
14.Implement Indexing and querying with MongoDB using following
example.
Employee(emp_id, emp_name,emp_dept,salary)
15.Write a JAVA program to implement CRUD operations in given
collection:
Employee(emp_id, emp_name,emp_dept,salary)
16.Write a JAVA program to implement all DML operations in given
relational schema:
Employee(emp_id, emp_name,emp_dept,salary)
17.Design and Implement any 5 query using MongoDB
a. Create a collection called ‘games’.
b. Add 5 games to the database. Give each document the following
properties: name, gametype, score (out of 100), achievements
c. Write a query that returns all the games
d. Write a query that returns the 3 highest scored games.
e. Write a query that returns all the games that have both the ‘Game Maser’
and the ‘Speed Demon’ achievements.
18. Create PL/SQL code block: Write a PL/SQL block of code for the following
schema:
Borrower(Rollin, Name, DateofIssue, NameofBook, Status)
Fine(Roll_no,Date,Amt)
Solve following queries:
a. Accept roll_no & name of book from user.
b. Check the number of days (from date of issue), if days are between 15 to 30
then fine amount will be Rs 5per day.
c. If no. of days>30, per day fine will be Rs 50 per day & for days less than 30,
Rs. 5 per day.
d. After submitting the book, status will change from I to R.
e. If condition of fine is true, then details will be stored into fine table.
Use of Control structure and Exception handling is mandatory.
19.Write a PL/SQL block for following requirement using user defined exception
handling. The account_master table records the current balance for an account,
which is updated whenever, any deposits or withdrawals takes place. If the
withdrawal attempted is more than the current balance held in the account. The
user defined exception is raised, displaying an appropriate message.
20. Write a PL/SQL code to calculate tax for an employee of an
organization ABC and to display his/her name & tax, by creating a
table under employee database as below.
Employee_salary(emp_no,basic,HRA,DA,Total_deduction,net_salary,gross
_Salary)
21. Writ a PL/SQL procedure to find the number of students ranging from 100-
70%, 69-60%, 59-50% & below 49% in each course from the student_course
table given by the procedure as parameter.
Schema: Student (ROLL_NO ,COURSE, COURSE_COD ,SEM
,TOTAL_MARKS, PERCENTAGE)
22. Write a PL/SQL block of code using parameterized Cursor, that will merge the
data available in the newly created table N_RollCall with the data available in the
table O_RollCall. If the data in the first table already exist in the second table then
that data should be skipped.
23.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
and900 category is first class, if marks 899 and 825 category is Higher Second
Class .
Consider Schema as Stud_Marks(name, total_marks) Result(Roll,Name, Class)
24. 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.
25.Write a before trigger for Insert, update event considering following
requirement:
Emp(e_no, e_name, salary)
I) Trigger action should be initiated when salary is tried to be inserted is less than
Rs. 50,000/-
II) Trigger action should be initiated when salary is tried to be updated for value
less than Rs. 50,000/-
