SQL – 2 (April – 2013) [IDOL - Old Course | Question Paper]

Click Here: (PDF Download) | Click Here: (PDF View) | Click Here: (Image View)

Time: 3 Hours

Total Marks: 100

N.B.:  (1) Question No. 1 is Compulsory.
           (2) Attempt any four questions from Q.2 – Q.7.
           (3) Make suitable assumption whenever necessary and state the assumption made.
           (4) Answers to the same questions must be written together.
           (5) Numbers to the right indicate marks.
           (6) Draw a neat labelled diagram and give example whenever necessary.

Q.1 Attempt The Following Question: (20 Marks)

(A) Explain ORDER BY clause in the SELECT statement with examples.

(B) Differentiate between DDL and DML Statements.

(C) Enumerate and explain the Codd’s Rules for relational database.

(D) Write the SQL Statements to create the EMP and DEPT tables shown after Q. No. 7. With all the constraints.

Q.2 Attempt The Following Question: (20 Marks)

(A) Define and explain Primary Key and Foreign Key with examples.

(B) What is a Trigger? Discuss the advantages and disadvantages of triggers.

(C) Write SQL Statements for the following (Refer table structure given after Q7).

1. List employees without any manager.
2. List all employees sorted department-wise in ascending order and within each department salary–wise in descending order for deptno 10 and 20.
3. List the empno an Employee Number, ename as Employee Name and deptno as Department from the emp table.
4. List the empno, ename concatenated with salary from the emp table.

E.g. empno : ename’s salary is $ XXXX

Q.3 Attempt The Following Question: (20 Marks)

(A) What are correlated subquires? Explain with examples.

(B) What are the basic privileges for table and views? Explain. 

(C) Write SQL Statements for the following (Refer table structure given after Q7).

1. List the employees whose salary is between 1000 and 3500.
2. List the empno, ename and salary of the employees whose name ends with h or t.
3. List the employees in the descending order of departments and within that in ascending order of salary.
4. List the empno, ename, salary and deptno of the employees whose deptno is more than 10 and salary is more than 1500.

Q.4 Attempt The Following Question: (20 Marks)

(A) What are joins? Explain Non-equi joins with examples.

(B) Explain the Network Database Model.

(C) Write SQL Statements for the following (Refer table structure given after Q7).

1. List the empno, ename and salary of employees who are managers.
2. List the empno, ename, and the position of alphabet ‘e’ in ename for the employees.
3. List the empno, ename, sal and job for all the employees whose job contains ‘er’.
4. List the ename, mgr, comm. from emp. If the mgr is NULLM then display comm., if comm. Is also null, then display salary.

Q.5 Attempt The Following Question: (20 Marks)

(A) What is a Transaction? What are the problems that occur due to concurrency? Explain any one.

(B) State and explain various data types available in SQL2 standard.

(C) Write SQL Statements for the following (Refer table structure given after Q7).

1. List the ename, job and salary of the employees. If the job is ‘CLERK’ the salary displayed must be 1.2 times the original value. For ‘SALESMAN’ 1.3 times the original value, for ‘ANALYST’ 1.4 times the original value and original value for the remaining. Write the query using CASE.
2. List the empno, ename and grade of the employees by joining the emp and the salgrade tables.
3. List the department numbers where the sum of the salaries of employees is greater 5000.
4. List the ename and hiredate of the employees in the same department as Scott Exclude Scott.

Q.6 Attempt The Following Question: (20 Marks)

(A) Explain IN, ANY and ALL in SQL2?

(B) What are escape characters? Why are they required? Explain with exmples.

(C) Write SQL statements for the following (Refer table structure given after Q7).

1. List the ename, deptno and job of all the employees whose department is located in ‘Dallas’.
2. List the department number and the lowest salary of the department with the highest average salary.
3. List the empno and ename of all the employees who work in a department with any employee whose last name contains n.
4. List the deptno, ename, number of employees and average salary of all departments together with the names, salaries and jobs of employees working in each department.

Q.7 Attempt The Following Question: (20 Marks)

(A) State and explain the four delete and update rules in SQL2.

(B) What is Data Integrity? Explain Entity Integrity and Referential Integrity.

(C) Write SQL Statements for the following (Refer table structure given after Q7).

1. List the deptno, dname and number of employees working in each department that has lowest number of employees.
2. List the department number and the lowest salary of the department with the highest average salary.
3. List all ename, manager name, salary who have managers with salary higher than 3500.
4. List the ename, deptno and dname of the employees by joining the emp and dept tables. The extra department name (dname) from the dept table must also be listed. (Use outer join).

NOTE:

If You're a B.Sc.IT Student of Mumbai University...and you've a previous year question papers then please mail us @ kamalbscit@yahoo.in