Q1:
Select e.empId
From employee e
Where not exists
(Select * From employee s
where s.department = "5" and
s.salary >=e.salary);
Q2:
Select e.empId
From employee e
Where e.salary > Any
( Select distinct salary
From employee s
Where s.department = "5");
Query1:
Select A.customer, count(B.customer)
From account A, account B
Where A.balance <=B.balance
Group by A.customer
Query2:
Select A.customer, 1+count(B.customer)
From account A, account B
Where A.balance < B.balance
Group by A.customer
Consider these statements about Query1 and Query2.
1. Query1 will produce the same row set as Query2 for some but not all databases.
2. Both Query1 and Query2 are correct implementation of the specification
3. Query1 is a correct implementation of the specification but Query2 is not
4. Neither Query1 nor Query2 is a correct implementation of the specification
5. Assigning rank with a pure relational query takes less time than scanning in decreasing balance order assigning ranks using ODBC.
Which two of the above statements are correct?Consider the relation "enrolled (student, course)" in which (student, course) is the primary key, and the relation "paid (student, amount)" where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Given the following four queries:
Query 1:
Select student
from enrolled
where student in (select student from paid)
Query 2:
Select student
from paid
where student in (select student from enrolled)
Query 3:
Select E.student
from enrolled E, paid P
where E.student = P.student
Query 4:
Select student
from paid
where exists (Select *
from enrolled
where enrolled.student = paid.student)
Which one of the following statements is correct?
Consider a database with three relation instances shown below. The primary keys for the Drivers and Cars relation are Did and cid respectively and the records are stored in ascending order of these primary keys as given in the tables. No indexing is available in the database.
D: Drivers Relation
Did | Dname | rating | Age |
---|---|---|---|
22 | Karthikeyan | 7 | 25 |
29 | Salman | 1 | 33 |
31 | Boris | 8 | 55 |
32 | Amoldt | 8 | 25 |
58 | Schumacher | 10 | 35 |
64 | Sachin | 7 | 35 |
71 | Senna | 10 | 16 |
74 | Sachin | 9 | 35 |
85 | Rahul | 3 | 25 |
95 | Ralph | 3 | 53 |
R: Reserves Relation
Did | cid | Day |
---|---|---|
22 | 101 | 10/10/06 |
22 | 102 | 10/10/06 |
22 | 103 | 8/10/06 |
22 | 104 | 7/10/06 |
31 | 102 | 10/11/06 |
31 | 103 | 6/11/06 |
31 | 104 | 12/11/06 |
64 | 101 | 5/9/06 |
64 | 102 | 8/9/06 |
74 | 103 | 8/9/06 |
C: Cars relation
cid | Cname | Color |
---|---|---|
101 | Renault |
Blue |
102 | Renault |
Red |
103 | Ferrari | Green |
104 | Jaguar | Red |
Select D.dname
From Drivers D
Where D.did in (SELECT R.did
From Cars C,Reserves R
WHERE R.cid = C.cid and C.color = 'green')
Let n be the number of comparisons performed when the above SQL query is optimally executed. If linear search is used to locate a tuple in a relation using primary key, then n lies in the range