University of Mumbai 2006 B.Sc Information Technology SQL ( Structured Query language )TY-IT--- - Question Paper
Q one is cumplosory
Solve four ques. from Q2 to Q 7
marks 100
Duration three hours
j-'ij \ /' *m.
/ , -av> V
Con. 1390-06. X6O6- S L ' MK-6570
(3 Hours) [ Total Marks : 100
N.B. (1) Question No. 1 is compulsory.
(2) Attempt any four questions from Question NOs. 2i to 7.
(3) All the queries are based on the tables with the following structures :
(a) Employee (ecode, ename, salary, status, deptno, city)
(b) Dept (deptno,' dname, city)
(c) Product (pno, sno, pname, unitsinstock, unitprice)
(d) Order (ono, pno, quantity, odate)
1. a) A table named Employee is created with the following fields. (4)
Write an ALT1:.'. TABLE statement that defines .a single CHECK constraint for th? following:
1) The CITY coumn allows cities only BOSTON, DALAS, and AUSTIN
2) Also if CITY is AUSTIN DEPTNO is D2 OR D3 and
3) if CITY s D/vLAS DEPTNO is DT and
4) if CITY is BOSTON DEPTNO is Dl
b) Explain the 12 rules suggested by E.F. Codd. (6)
c) Give.*), brief History of Structured Query Language. (6)
d) Write SQL statement for creating order table with the following (4) constraints
1) pno is foreigr. key referencing tables product
2) define onu as the primary key
3) quantity must be between 1 and 1000
4) ono should starts with OR
2. a) Explain the symbolic constants. (4)
d) Write SQL statements for the following (6)
1) To display the details of employees MARK, STEEVE and any other employee from department Dl.
2) List the employee who is getting highest salary.
3) Select the employees whose salary less than tb.e salary of the highest paying employee from department D3.
c) Discuss the evolution of ANSI/ISO standard for SQL. (5)
d) Describe the referential integrity problems. (5)
3- a) Write queries for the following:- (4)
1) List the names of the employees with their department names
2) Get the second maximum salary from the employee table.
b) Explain the various data types available in SQL2. (6)
c) Explain Self Join with a suitable example. (6)
d) Explain calculated columns in a sclcct list and where clause with the help (4) of a SELECT statement.
4- a) What do you mean by a Foreign Key? How do you set it to an existing (4)
table?
b) Write SQL statements for the following (6)
1) List all unique cities where a department locate and/or a employee live.
2) Get the unique salaries from the employee table.
3) Create a grouped view of deptno and average salaries of each department.
c) What is a View? What are the advantages of using views? (5)
d) Briefly explain ANSI/ISO transaction model. (5)
b) Write queries for the following:- (6)
1. Create a duplicate table named Dup for Employee table with all records.
2. Get department numbers of departments for which more than one employees are assigned.(Using GROUP BY and HAVING)
3. Display the total number of departments located in each city.
c) Differentiate between - (5) =ANY and =ALL
>ANY and >ALL <ANY and <ALL >=ANY and >=ALL <=ANY and <=ALL
d) What do you mean by correlated subquery? Explain with a suitable (5) example.
6. a) Write SQL queries for the following:- (4)
1) Get all employee names that starts with S
2) List the employees whose salary is equal to any of the departments average.
b) Explain non-repeatable read and dirty read problems. (6)
c) Explain various locking levels. (5)
d) What are drawbacks of File Management Systems? Explain. (5)
7 a) Write the SQL statements for the following.- (4)
1) User SAM wants to grant updat'; and select privileges on software table to another user GROFF and he Wants to let him grant these permissions to other users.
2) User SAM wants to take away all permissions on the programmer table that were formally granted to all us ?rs.
b) Discuss multi-database and multi-location database architectures. (6)
c) Differentiate between Horizontal nd Ver;i;al views. (5)
d) Explain the privileges that are defined in Hie SQL standard? (5)
|
Attachment: |
| Earning: Approval pending. |
