SQL EXAM Question [PDF]

Zitiervorschau

SQL EXAM QUESTION

Q1.

Write SQL command for a) to f) and write the output of g) on the basis of table MOV.

No 1 2 3 4 5 6 7 8 9 10

Name Gone with wind Friday the 13th Top Gun Splash Independent day Risk Business Cocoon Crocodile 101 Dalmatians Tootsie

Type Drama Horror Drama Comedy Drama Comedy Sci-fi Comedy Comedy Comedy

Rating G R PG PG13 R R PG PG13 G PG

Stars Gable Jason Cruise Hanks Turner Cruise Ameche Harris Hoffman Croft

Qty 4 2 7 3 3 2 2 2 3 1

a) b) c) d)

price 39.95 60.95 49.95 29.95 19.95 44.95 31.95 69.95 59.95 29.95

Find the total value of the movie cassettes available in the library. Display a list of all movies with price over 20 and sorted by price Display all the movies sorted by QTY in decreasing order. Display a report listing a movie number, current value and replacement value for each movie in the above table. Calculate the replacement value for all movies as QTY * Price * 1.15 e) Count the number of movies where Rating is not “G” f) Insert a new movie in the MOV where Price > 30. g) Give the output of the following SQL statement i. SELECT MAX(price) FROM MOV WHERE PRICE > 30 ii. SELECT SUM ( price * qty) FROM MOV WHERE QTY < 4 iii. SELECT COUNT ( DISTINT TYPE) Q2 (.JANUARY 2006) 7. a) Consider the insurance database given below: person (driver-id, name, address) car (license, model, year) accident (report-number, date, location) owns (driver-id, license) participated (driver-id, car, report-number, damage-amount) Construct the following SQL queries for this relational database. i) Find the total number of people who owned cars that were involved in accidents in 2004. ii) Find the number of accidents in which the cars belonging to “Thakre” were involved. iii) Delete the Mazda belonging to “S Khan”. a) How does SQL allow implementation of entity and integrity constraints?

1

SQL EXAM QUESTION Q3. (JANUARY 2007) 9.

a) A schema describing theatres, cities where they are located and shows is defined as

followsCITY (Name, State, Country) THEATRE (Name, City, State, Capacity) SHOW (Title, Artist, Hall, Attendance) Write the following queries in both 1) SQL and 2) Relational Algebra i)Find names of artists who performed before at least 5000 people, together with cities where those performances took place. ii) Find all states in India where Mr. X has performed. iii) List all artists who never played in Delhi. iv) Find the name of theatres in Bombay whose capacity exceeds 5000. Q4.( JANUARY 2008) Consider the following tables which give details of customers, trucks and packets booked by customers, which are carried by trucks and write SQL commands to do the following: Tables: CUSTOMER(c_no, c_name, c_address) TRUCK(t_no, driver_name) PACKET(p_no, c_no, t_no, date_of_booking, weight, destination) Queries: i) Destinations which have received more than 10 packets. ii) Name of Customers who have sent at least one packet of weight more than one kg to ‘BOMBAY’. iii) Name of all Customers whose packets were delivered a driver whose name is ‘RAJA’. iv) Three top customers (names) in terms of total packet weight sent by them. (list is to be in descending order of total weight.) v) Name of all Customers whose individual shipments are less than one kg. Q5. ( JULY 2006) b) How cursors addresses the impedance mismatch between host languages and SQL. c) In what way dynamic SQL is different from embedded SQL? Q6. (JULY 2007) c) In respect of project, we are given the following record types for the relational model of the

database: EMPLOYEE : with attributes (“Emp#” and “Name”) ASSIGNED_To: with attributes (‘Project#’, ‘Emp#’) PROJECT : with attributes (‘Project#’, ‘Project_Name’, ‘Chief_architect’) Express the following queries in relational algebra: i) Get Emp# of employees working on project Comp 353 ii) Give details of employees (both number and name) working on project Comp 353 iii) Gather details of employees working on both Comp 353 and Comp 354 2

SQL EXAM QUESTION

Q7.( JULY 2007) a) What restrictions apply to the use of aggregate functions within the select?

b) Describe how the process of view resolution works and what restrictions are necessary to ensure that a view is updatable? c) Consider the following Relation Schema. An employee can work in more than one department: Emp (E-id, E_name, Salary) Dept (d_id, d_name, manager_id, floor_number) Write the following queries in SQL: i) Print the name of all employees, who work on the 10th floor and earn salary less than Rs.50,000. ii) Print the names of the departments that employee Santa work in. iii) Print the names of all managers who manage three or more departments on the same floor. iv) Print the names of all employees who work on floors where Jane Donald works. v) Give every employee who works in the toys dept. at 10% raise in the salary. Q8. (JULY 2008) a) Consider the following “Sailors” and “Reserves” relations R (sid, bid, day) S (sid, sname, rating, age) I) Formulate the following queries using relational algebra:

i) Find names of sailors who’ve reserved boat #XXX. ii) Find sailors who’ve reserved a red or a green boat. iii) Find the names of sailors who’ve reserved all boats. II) Formulate the following queries using SQL: i) Find sid’s of sailors who’ve reserved a red or a green boat. ii) Find names of sailors who’ve reserved boat #XXX. iii) Find sailors whose rating is greater than that of some sailor called XYZ. b) Explain, how referential integrity is enforced in SQL. Q9. ( JULY 2008) a) What is the purpose of ‘VIEW’ operation in SQL? Explain how is it created. List its advantages. b) What restrictions apply to the use of aggregate functions within the select? c) Describe how the process of view resolution works and what restrictions are necessary to ensure that a view is updatable? Q10. b) What is meant by Heuristic Optimization? Discuss the main heuristics that are applied to query optimization?

3