SQL
Q31.
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 whQ32.
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) < 5Q33.
A table 'student' with schema (roll, name, hostel, marks), and another table 'hobby' with schema (roll, hobbyname) contains records as shown below: \overset{\text{Table: student}}{\begin{array}{|c|c|c|c|} \hline \textbf{Roll} & \textbf {Name} & \textbf {Hostel} & \textbf{Marks} \\\hline \text{1798} & \text{Manoj Rathor} & \text{7} & \text{95} \\\hline \text{2154} & \text{Soumic Banerjee} & \text{5} & \text{68}\\\hline \text{2369} & \text{Gumma Reddy} & \text{7} & \text{86}\\\hline\text{2581} & \text{Pradeep pendse} & \text{6} & \text{92}\\\hline \text{2643} & \text{Suhas Kulkarni} & \text{5} & \text{78} \\\hline \text{2711} & \text{Nitin Kadam} & \text{8} & \text{72}\\\hline \text{2872}& \text{Kiran Vora} & \text{5} & \text{92}\\\hline\text{2926} & \text{Manoj Kunkalikar} & \text{5} & \text{94}\\\hline \text{2959}& \text{Hemant Karkhanis} & \text{7} & \text{88}\\\hline\text{3125} & \text{Rajesh Doshi} & \text{5} & \text{82}\\\hline \end{array}}\overset{\text{Table: hobby}}{}\\\begin{array}{|c|c|} \hline \textbf{Roll} & \textbf {Hobby Name} \\\hline \text{1798} & \text{chess} \\\hline \text{1798} & \text{music} \\\hline \text{2154} & \text{music} \\\hline \text{2369} & \text{swimming}\\\hline \text{2581} & \text{cricket} \\\hline \text{2643} & \text{chess}\\\hline\text{2643} & \text{hockey} \\\hline \text{2711} & \text{volleyball}\\\hline \text{2872} & \text{football} \\\hline \text{2926} & \text{cricket} \\\hline \text{2959} & \text{photography} \\\hline \text{3125} & \text{music}\\\hline \text{3125}& \text{chess}\\\hline \end{array} The following SQL query is executed on the above tables: select hostel from student natural join hobby where marks >= 75 and roll between 2000 and 3000; Relations S and H with the same schema as those of these two tables respectively contain the same information as tuples. A new relation S is obtained by the following relational algebra operation: S = \Pi_{\text{hostel}} ((\sigma_{s.roll = H.roll} (\sigma_{marks \gt 75\text{ and }roll \gt 2000\text{ and }roll \lt 3000} (S)) \times (H)) The difference between the number of rows output by the SQL statement and the number of tuples in S isQ34.
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 from enrolled where student in (select student form 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?Q37.
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. \overset{\text{D: Drivers relation}}{\begin{array}{|c|l|r|c|}\hline \textbf{did}& \textbf{dname}& \textbf{rating}& \textbf{age} \\\hline 22& \text{Karthikeyan}& 7& 25 \\ \hline 29& \text{Salman}& 1& 33 \\ \hline 31& \text{Boris}& 8& 55 \\\hline 32& \text{Amoldt}& 8& 25 \\\hline 58& \text{Schumacher}& 10& 35 \\\hline 64& \text{Sachin}& 7& 35 \\\hline 71& \text{Senna}& 10& 16 \\\hline 74& \text{Sachin}& 9& 35 \\\hline 85& \text{Rahul}& 3& 25 \\\hline 95& \text{Ralph}& 3& 53 \\\hline \end{array}} \qquad \overset{\text{R: Reserves relation}}{\begin{array}{|c|c|c|}\hline \textbf {did} & \textbf {Cid} & \textbf {day} \\\hline 22 & 101 & 10-10-06 \\ \hline 22 & 102 & 10-10-06\\ \hline 22 & 103 & 08-10-06 \\\hline 22 & 104 & 07-10-06 \\\hline 31 & 102 & 10-11-16 \\\hline 31&103 &06-11-16 \\\hline 31 & 104&12-11-16 \\\hline 64 & 101 &05-09-06 \\\hline 64& 102 & 08-09-06 \\\hline 74 & 103 & 08-09-06 \\\hline \end{array}} \overset{\text{C: Cars relation}}{\begin{array}{|c|c|c|c|}\hline \textbf {Cid} & \textbf {Cname} & \textbf{colour} \\\hline 101 & \text{Renault} & \text{blue} \\ \hline 102 & \text{Renault} & \text{red} \\ \hline 103 & \text{Ferrari} & \text{green} \\\hline 104 & \text{Jaguar} & \text{red} \\\hline \end{array}} 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.colour = 'red' intersect select R.did from Cars C, Reserves R where R.cid = C.cid and C.colour = '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:Q38.
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 (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?Q39.
Database table by name Loan_Records is given below. What is the output of the following SQL query? SELECT count(*) FROM( (SELECT Borrower. Bank_Manager FROM Loan_Records) AS S NATURAL JOIN (SELECT Bank_Manager, Loan_Amount FROM Loan_Records) AS T);Q40.
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. \overset{\text{D: Drivers relation}}{\begin{array}{|c|l|r|c|}\hline \textbf{did}& \textbf{dname}& \textbf{rating}& \textbf{age} \\\hline 22& \text{Karthikeyan}& 7& 25 \\ \hline 29& \text{Salman}& 1& 33 \\ \hline 31& \text{Boris}& 8& 55 \\\hline 32& \text{Amoldt}& 8& 25 \\\hline 58& \text{Schumacher}& 10& 35 \\\hline 64& \text{Sachin}& 7& 35 \\\hline 71& \text{Senna}& 10& 16 \\\hline 74& \text{Sachin}& 9& 35 \\\hline 85& \text{Rahul}& 3& 25 \\\hline 95& \text{Ralph}& 3& 53 \\\hline \end{array}} \qquad \overset{\text{R: Reserves relation}}{\begin{array}{|c|c|c|}\hline \textbf {did} & \textbf {Cid} & \textbf {day} \\\hline 22 & 101 & 10-10-06 \\ \hline 22 & 102 & 10-10-06\\ \hline 22 & 103 & 08-10-06 \\\hline 22 & 104 & 07-10-06 \\\hline 31 & 102 & 10-11-16 \\\hline 31&103 &06-11-16 \\\hline 31 & 104&12-11-16 \\\hline 64 & 101 &05-09-06 \\\hline 64& 102 & 08-09-06 \\\hline 74 & 103 & 08-09-06 \\\hline \end{array}} \overset{\text{C: Cars relation}}{\begin{array}{|c|c|c|c|}\hline \textbf {Cid} & \textbf {Cname} & \textbf{colour} \\\hline 101 & \text{Renault} & \text{blue} \\ \hline 102 & \text{Renault} & \text{red} \\ \hline 103 & \text{Ferrari} & \text{green} \\\hline 104 & \text{Jaguar} & \text{red} \\\hline \end{array}} 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.colour = 'red' intersect select R.did from Cars C, Reserves R where R.cid = C.cid and C.colour = 'green' )