Consider the relation account (customer, balance) where customer is a primary key and there are no null values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1. Ties are not broke but ranks are skipped: if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned.

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 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

What is the output of the following SQL query?

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')

In an inventory management system implemented at a trading corporation, there are several tables designed to hold all the information. Amongst these, the following two tables hold information on which items are supplied by which suppliers, and which warehouse keeps which items along with the stock-level of these items.

Supply = (supplierid, itemcode)
Inventory = (itemcode, warehouse, stocklevel)

For a specific information required by the management, following SQL query has been written

Select distinct STMP.supplierid 
From Supply as STMP 
Where not unique (Select ITMP.supplierid 
    From Inventory, Supply as ITMP 
    Where STMP.supplierid = ITMP.supplierid 
    And ITMP.itemcode = Inventory.itemcode 
    And Inventory.warehouse = 'Nagpur');

For the warehouse at Nagpur, this query will find all suppliers who

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;