MGM’s College of
Engineering, Nanded.Department of IT
Semester I (2017-18)
Class: TE(IT) Subject: DBMS Assignment II
1. Explain various set
operations available in SQL with examples.
2. Explain how the
GROUP BY clause works. What is the difference between WHERE and HAVING clauses?
Explain with the help of an example.
3. How are queries
based on joins expressed in SQL? Explain the various join operations.
4. Explain the concept
of nested queries with examples.
5. Discuss the
different operators in SQL, which are used with subqueries/nested queries.
6. What are assertions?
Why are they necessary?
7. What is the purpose
of view in SQL? How can you create a view?
8. What are stored
procedures? When are they beneficial? Give the syntax and example of creating a
procedure and function in SQL.
9. What are triggers?
How are they created? Explain with an example how triggers offer a powerful
mechanism for dealing with the changes to database.
10. What do you
understand by an embedded SQL? How are variables declared and used in an
embedded SQL? Explain with examples.
11. What is the purpose
of ODBC in SQL? How is it implemented? Explain with examples.
12. What is the purpose
of JDBC in SQL? Explain with examples.
13.Consider the given
tables
Loan
LoanNo BranchName Amount
L101 Mumbai 1000
L103 Pune 2000
Borrower
CustomerName LoanNo
Sonam L101
Nandy L103
Anusha L103
Depositor
CustomerName AccountNo
Anusha A102
Sonam A103
Nandy A104
(a) What are the number of tuples present in the
result of cross product of the above two tables?
(b) Write an SQL to Find
all customers who have only accounts but no loans.
(c) Write an SQL to Find
the names of all customers who have either an account or loan but not both.
14. Define functional
dependency. Explain different Normal forms.
15.The given table is
in BCNF form, convert it to the 4th normal form.
Employee Skill Language
Jones Electrical French
Jones Electrical German
Jones Mechanical French
Jones Mechnical German
Smith Plumbing Spanish
16. Compute
the closure of the following set F of functional dependencies
for
relation schema R = (A, B, C, D, E).
A→BC
CD→E
B→D
E→A
Faculty
Incharge: Hashmi S A
