SQL – 2 (May – 2018) [IDOL: Old Course | Question Paper]

SQL – 2 (May – 2018) [IDOL: Old Course | Question Paper]

DOWNLOAD @
☛ PDF | PDF
☛ Text
☛ Torrent
☛ G-Drive
☛ SlideShare

Time: 3 Hours
Total Marks: 100

NOTES:

(1) Question No. 1 is Compulsory.

(2) Attempt any four from Question Nos. 2 to 7.

(3) Make suitable assumptions wherever necessary and state the assumptions made.

(4) Answers to the same question must be written together.

(5) Numbers to the right indicate marks.

(6) Draw neat labeled diagrams wherever necessary.

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

(A) Differentiate between DML and DDL statements.

(B) What are views? State the difference between views and tables.

(C) Explain correlated sub queries with example.

(D) Explain the access control statements in SQL.

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

(A) Write SQL statement to create WORKERS, SECTIONS, and AREAS tables with all the constraints as given after Q. No. 7

(B) Write a short note on data types of SQL2.

(C) Explain IN, ANY and ALL in SQL2.

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

(A) Write the SQL queries for the following (refer to the tables given after Q. No. 7)

1. List all the first name and last name of workers with their managers.
2. List the entire worker id and worker name who belong to section id 25.
3. List first name, section name and area name of all employees.
4. List worker id, first name concatenated with salary and commission from the workers table. For e.g. worker_id: first_name’s salary Rs. XXXXX has commission Rs. XXX

(B) State and explain aggregate functions.

(C) Explain primary key and foreign key with an example.

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

(A) Write the SQL queries for the following (refer to the tables given after Q. No. 7)

1. List the workers whose salary is below 25000 and commission is between 2000 and 5000.
2. List the names of the employees whose first_name starts with 'A' or 'B'.
3. List the first_name of workers in the ascending order of the section and within that in the descending order of the salary.
4. List the section name, section id and area name whose area id is 7 or city is 'DELHI'.

(B) Write a short note on joins.

(C) Explain the following in-built SQL2 functions with examples:

1. SUBSTR
2. CAST
3. TRIM

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

(A) Write the SQL queries for the following (refer to the tables given after Q. No. 7)

1. List the worker id, salary, section name and area name of workers who have completed more than 3 years.
2. List the first name, last name and hire date of workers who work in the section which is in city 'BANGALORE'.

(B) What is locking and why is it required?

(C) What are the different problems that occur due to concurrency?

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

(A) Write the SQL queries for the following (refer to the tables given after Q. No. 7)

1. Write a query to display each sections name, area, number of workers and the average salary for all the workers in that section. Label the columns Name, Location, Number of People and Salary, respectively. Round the average salary to two decimal places.
2. Display the manager number and the salary of the lowest paid worker for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is 40000 or less. Sort the output in the descending order of the salary.

(B) Explain single database and multi database architecture.

(C) Explain UNION and ORDER BY with examples. Differentiate between the two.

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

(A) Write the SQL queries for the following (refer to the tables given after Q. No. 7)

 (i) Write a query that produces the following for each worker:

<worker last name> earns <salary> monthly but wants <3 times salary>. Label the column Dream Salaries.

(ii) Create a view called WORKERS_VU based on the workers id, workers last name and section id from the workers table. Change the heading for the workers name to EMP NAME.

(B) What are triggers? Explain its syntax with an example.

(C) Explain the concept of null values. How can null values be inserted in a table?