SQL

Question 31

__________ SQL command changes one or more fields in a record.

• LOOK-UP
• INSERT
• MODIFY
• CHANGE

Question 32

Consider the schema Sailors(sid, sname, rating, age) with the following data

sid	sname	rating	age
22	Dustin	7	45
29	Borg	1	33
31	Pathy	8	55
32	Robert	8	25
58	Raghu	10	17
64	Herald	7	35
71	Vishnu	10	16
74	King	9	35
85	Archer	3	26
84	Bob	3	64
96	Flinch	3	17

For the query

SELECT S.rating, AVG(S.age) AS avgage FROM Sailors S
Where S.age >= 18
GROUP BY S.rating
HAVING 1 < (SELECT COUNT(*) FROM Sailors S2 where S.rating = S2.rating)

The number of rows returned is

• 6

• 5

• 4

• 3

Question 33

Given two relations R1(A, B) and R2(C, D), the result of following query

Select distinct A, B
from R1, R2

is guaranteed to be same as R1 provided one of the following condition is satisfied.

• R1 has no duplicates and R2 is empty.
• R1 has no duplicates and R2 is non - empty.
• Both R1 and R2 have no duplicates.
• R2 has no duplicates and R1 is non - empty.

Question 34

What is the output of the following SQL query?

select count(*) from ((select Employee, Department from Overtime_allowance) as S
natural join (select Department, OT_allowance from Overtime_allowance) as T);

• 16
• 4
• 8
• None of the above
• none

Question 35

The relation book (title,price) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list? select title from book as B where (select count(*) from book as T where T.price>B.price)<5

• Titles of the four most expensive books
• Title of the fifth most inexpensive book
• Title of the fifth most expensive book
• Titles of the five most expensive books

Question 36

Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)

Consider the following relational query on the above database:

SELECT S.sname
    FROM Suppliers S
        WHERE S.sid NOT IN (SELECT C.sid
                            FROM Catalog C
                            WHERE C.pid NOT IN (SELECT P.pid  
                                                FROM Parts P
                                                WHERE P.color<> \'blue\'))

Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?

• Find the names of all suppliers who have supplied a non-blue part.
• Find the names of all suppliers who have not supplied a non-blue part
• Find the names of all suppliers who have supplied only non blue parts.
• Find the names of all suppliers who have not supplied only non-blue parts.

Question 37

Consider the following schema:

Emp (Empcode, Name, Sex, Salary, Deptt)

A simple SQL query is executed as follows:

SELECT Deptt FROM Emp
WHERE sex = \'M\'
GROUP by Dept
Having avg (Salary) > {select avg (Salary) from Emp}

The output will be

• Average salary of male employee is the average salary of the organization
• Average salary of male employee is less than the average salary of the organization
• Average salary of male employee is equal to the average salary of the organization
• Average salary of male employee is more than the average salary of the organization

Question 38

Consider the following ORACLE relations : One (x, y) = {<2, 5>, <1, 6>, <1, 6>, <1, 6>, <4, 8>, <4, 8>} Two (x, y) = {<2, 55>, <1, 1>, <4, 4>, <1, 6>, <4, 8>, <4, 8>, <9, 9>, <1, 6>} Consider the following two SQL queries SQ1 and SQ2 :

SQ1 : SELECT * FROM One)
           EXCEPT
      (SELECT * FROM Two);
SQ2 : SELECT * FROM One)
        EXCEPT ALL
      (SELECT * FROM Two);

For each of the SQL queries, what is the cardinality (number of rows) of the result obtained when applied to the instances above ?

• 2 and 1 respectively
• 1 and 2 respectively
• 2 and 2 respectively
• 1 and 1 respectively

Question 39

In distributed databases, location transparency allows for database users, programmers and administrators to treat the data as if it is at one location. A SQL query with location transparency needs to specify:

• Inheritances
• Fragments
• Locations
• Local formats

Question 40

Consider the following ORACLE relations : R (A, B, C) = {<1, 2, 3>, <1, 2, 0>, <1, 3, 1>, <6, 2, 3>, <1, 4, 2>, <3, 1, 4> } S (B, C, D) = {<2, 3, 7>, <1, 4, 5>, <1, 2, 3>, <2, 3, 4>, <3, 1, 4>}. Consider the following two SQL queries SQ₁ and SQ₂ : SQ₁ : SELECT R⋅B, AVG (S⋅B) FROM R, S WHERE R⋅A = S⋅C AND S⋅D < 7 GROUP BY R⋅B;
SQ₂ : SELECT DISTINCT S⋅B, MIN (S⋅C) FROM S GROUP BY S⋅B HAVING COUNT (DISTINCT S⋅D) > 1; If M is the number of tuples returned by SQ₁ and N is the number of tuples returned by SQ₂ then

• M = 4, N = 2
• M = 5, N = 3
• M = 2, N = 2
• M = 3, N = 3