## SQL

 Question 1

Consider a relational database containing the following schemas.

The primary key of each table is indicated by underlying the constituent fields.

SELECT s.sno, s.sname

FROM Suppliers s, Catalogue c

WHERE s.sno = c.sno AND

Cost > (SELECT AVG (cost)

FROM Catalogue

WHERE pno = ‘P4’

GROUP BY pno);

The number of rows returned by the above SQL query is
 A 0 B 5 C 4 D 2
Database-Management-System       SQL       GATE 2020
Question 1 Explanation:
The inner query “select avg(cost) from catalogue where pno='P4' group by pno;” returns:
AVG(COST)
------------
225
The outer query “select s.sno,s.sname from suppliers s, catalogue c where s.sno=c.sno” returns:
SNO   SNAME
----------------------------------------
S1  M/s Royal furniture
S1 M/s Royal furniture
S1  M/s Royal furniture
S2       M/s Balaji furniture
S2       M/s Balaji furniture
So, the final result of the query is:
SN      SNAME
----------------------------------------
S2       M/s Balaji furniture
Therefore, 4 rows will be returned by the query.
 Question 2

Consider the following two tables and four queries in SQL.

Book (isbn, bname), Stock (isbn, copies)
```Query 1:    SELECT B.isbn, S.copies
FROM Book B INNER JOIN Stock S
ON B.isbn = S.isbn;

Query 2:    SELECT B.isbn, S.copies
FROM Book B LEFT OUTER JOIN Stock S
ON B.isbn = S.isbn;

Query 3:    SELECT B.isbn, S.copies
FROM Book B RIGHT OUTER JOIN Stock S
ON B.isbn = S.isbn;

Query 4:    SELECT B.isbn, S.copies
FROM Book B FULL OUTER JOIN Stock S
ON B.isbn = S.isbn;
```

Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?

 A Query 1 B Query 2 C Query 3 D Query 4
Database-Management-System       SQL       Gate 2018
Question 2 Explanation:
Given two tables are,
Book (isbn, bname)
Stock (isbn, copies)
isbn is a primary key of Book and isbn is a foreign key of stock referring to Book table.
For example:

Query 1:
INNER JOIN keyword selects records that have matching values in both tables (Book and Stock).

So, the result of Query 1 is,

Query 2:
The LEFT OUTER JOIN keyword returns all records from the left table (Book) and the matched records from the right table (Stock).
The result is NULL from the right side, if there is no match.

So, the result of Query 2 is,

Query 3:
The RIGHT OUTER JOIN keyword returns all records from the right table (Stock), and the matched records from the left table(BOOK).
The result is NULL from the left side, when there is no match.

Query 4:
The FULL OUTER JOIN keyword return all records when there is a match in either left (Book) or right (Stock) table records.

So, the result of Query 4 is,

Therefore, from the result of above four queries, a superset of the outputs of the Query 1, Query 2 and Query 3 is Query 4.
Note:
If we take isbn as a primary key in both the tables Book and Stock and foreign key, in one of the tables then also will get option (D) as the answer.
 Question 3

Consider a database that has the relation schema EMP (EmpId, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are given below.

The output of executing the SQL query is ___________.

 A 2.6 B 2.7 C 2.8 D 2.9
Database-Management-System       SQL       Gate 2017 set-01
Question 3 Explanation:
The given query is

⇾ We start evaluating from the inner query.
The inner query forms DeptName wise groups and counts the DeptName wise EmpIds.
⇾ In inner query DeptName, Count(EmpId) is the alias name DeptName, Num.
So, the output of the inner query is,

The outer query will find the
Avg(Num) = (4+3+3+2+1)/5 = 2.6
 Question 4

Consider the following database table named top_scorer.

```SELECT ta.player FROM top_scorer AS ta
WHERE ta.goals > ALL (SELECT tb.goals
FROM top_scorer AS tb
WHERE tb.country = 'Spain')
AND ta.goals > ANY (SELECT tc.goals
FROM top_scorer AS tc
WHERE tc.country = 'Germany')```

The number of tuples returned by the above SQL query is _____.

 A 7 B 8 C 9 D 10
Database-Management-System       SQL       GATE 2017(set-02)
Question 4 Explanation:

In the given database table top_scorer no players are there from ‘Spain’.
So, the query (1) results 0 and ALL (empty) is always TRUE.
The query (2) selects the goals of the players those who are belongs to ‘Germany’.
So, it results in ANY (16, 14, 11, 10).
So, the outer most query results the player names from top_scorer, who have more goals.
Since, the minimum goal by the ‘Germany’ player is 10, it returns the following 7 rows.
 Question 5

Consider the following database table named water_schemes :

The number of tuples returned by the following SQL query is _________.

```with total(name, capacity) as
select district_name, sum(capacity)
from water_schemes
group by district_name
with total_avg(capacity) as
select avg(capacity)
from total
select name
from total, total_avg
where total.capacity ≥ total_avg.capacity
```
 A 2 B 3 C 4 D 5
Database-Management-System       SQL       GATE 2016 set-2
Question 5 Explanation:
• The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query.
The name assigned to the sub-query is treated as though it was an inline view or table.
• First group by district name is performed and total capacities are obtained as following:

• Then average capacity is computed, Average Capacity = (20 + 40 + 30 + 10)/4 = 100/4 = 25
• Finally, 3rd query will be executed and it's tuples will be considered as output, where name of district and its total capacity should be more than or equal to 25.
• Then average capacity is computed,
Average Capacity = (20 + 40 + 30 + 10)/4 = 100/4 = 25
• Finally, 3rd query will be executed and it's tuples will be considered as output, where name of district and its total capacity should be more than or equal to 25.
 Question 6
SELECT operation in SQL is equivalent to
 A the selection operation in relational algebra B the selection operation in relational algebra, except that SELECT in SQL retains duplicates C the projection operation in relational algebra D the projection operation in relational algebra, except that SELECT in SQL retains duplicates
Database-Management-System       SQL       GATE 2015 (Set-01)
Question 6 Explanation:
SELECT operation in SQL perform vertical partitioning which is performed by projection operation in relational calculus but SQL is multi sets; hence (D).
 Question 7
Consider the following relations:
```SELECT S. Student_Name, sum(P.Marks)
FROM Student S, Performance P
WHERE S.Roll_No = P.Roll_No
GROUP BY S.Student_Name```
The number of rows that will be returned by the SQL query is _________
 A 2 B 3 C 4 D 5
Database-Management-System       SQL       GATE 2015 (Set-01)
Question 7 Explanation:
Output table is
 Question 8
Consider the following relation
`  Cinema (theater, address, capacity)`
Which of the following options will be needed at the end of the SQL query
```SELECT P1. address
FROM Cinema P1```
Such that it always finds the addresses of theaters with maximum capacity?
 A WHERE P1.capacity >= All (select P2.capacity from Cinema P2) B WHERE P1.capacity >= Any (select P2.capacity from Cinema P2) C WHERE P1.capacity > All (select max(P2.capacity) from Cinema P2) D WHERE P1.capacity > Any (select max(P2.capacity) from Cinema P2)
Database-Management-System       SQL       GATE 2015(Set-03)
Question 8 Explanation:
Inner query collects capacities of all the theatres and in outer query we are filtering the tuples with the condition “capacity >= All”.
So the theatres which are having maximum capacity will satisfy the condition.
 Question 9
Given the following statements:
```    S1: A foreign key declaration can always
be replaced by an equivalent check
assertion in SQL.
S2: Given the table R(a,b,c) where a and
b together form the primary key, the
following is a valid table definition.
CREATE TABLE S (
a INTEGER,
d INTEGER,
e INTEGER,
PRIMARY KEY (d),
FOREIGN KEY (a) references R)```
Which one of the following statements is CORRECT?
 A S1 is TRUE and S2 is FALSE. B Both S1 and S2 are TRUE. C S1 is FALSE and S2 is TRUE. D Both S1 and S2 are FALSE.
Database-Management-System       SQL       GATE 2014(Set-01)
Question 9 Explanation:
S1: False
Using a check constraint, we can have the same effect as foreign key while adding elements to the child table. But while deleting elements from the parent table the referential integrity constraint is no longer valid. So, a check constraint cannot replace a foreign key.
S2: False:
Foreign key in one table should be defined as a primary key in other table. In above table definition, table S has a foreign key that refers to field ‘a’ of R. The field ‘a’ in table S is part of the primary key and part of the key cannot be declared as a foreign key.
 Question 10
Given the following schema:
```     employees(emp-id, first-name, last-name, hire-date, dept-id, salary)
departments(dept-id, dept-name, manager-id, location-id)```
You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:
```SQL> SELECT last-name, hire-date
FROM employees
WHERE (dept-id, hire-date) IN ( SELECT dept-id, MAX(hire-date)
FROM employees JOIN departments USING(dept-id)
WHERE location-id = 1700
GROUP BY dept-id);
What is the outcome?```
 A It executes but does not give the correct result. B It executes and gives the correct result. C It generates an error because of pairwise comparison. D It generates an error because the GROUP BY clause cannot be used with table joins in a subquery.
Database-Management-System       SQL       GATE 2014(Set-01)
Question 10 Explanation:
The given SQL query will display the last names and hire-dates of all latest hires in their respective departments in the location ID 1700. So, correct option is (B).
 Question 11
SQL allows tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below:
`    select * from R where a in (select S.a from S)`
 A select R.* from R,S where R.a=S.a B select distinct R.* from R,S where R.a=S.a C select R.* from R,(select distinct a from S) as S1 where R.a=S1.a D select R.* from R,S where R.a=S.a and is unique R
Database-Management-System       SQL       Gate 2014 Set -02
Question 11 Explanation:
Multiplicity of duplicate tuples will be distributed when there is a match between R.a and S.a; and for that match, S.a's value is repeated in each cases except the third case. So, the output of query given in the question matches with the output of (C).
 Question 12
Consider the following relational schema: employee(empId,empName,empDept) customer(custId,custName,salesRepId,rating) salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return? SELECT empName FROM employee E WHERE NOT EXISTS (SELECT custId FROM customer C WHERE C.salesRepId = E.empId AND(GATEC.rating<>’GOOD’);
 A Names of all the employees with at least one of their customers having a ‘GOOD’ rating. B Names of all the employees with at most one of their customers having a ‘GOOD’ rating. C Names of all the employees with none of their customers having a ‘GOOD’ rating. D Names of all the employees with all their customers having a ‘GOOD’ rating.
Database-Management-System       SQL       Gate 2014 Set -03
Question 12 Explanation:

The inner query i.e., ② represents all customers having other than ‘GOOD’ while the entire query represents name of all employees with all their customers having a ‘good rating’.
 Question 13
which of the  following statements are TRUE about an SQL query? P:An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause Q:An SQL query can contain a HAVING clause even if it has a GROUP BY clause R: All attributes used in the GROUP BY clause must appear in the SELECT clause S: Not all attributes used in the GROUP BY clause need to appear in the SELECT clause
 A P and R B P and S C Q and R D Q and S
Database-Management-System       SQL       Gate 2012
Question 13 Explanation:
The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. The attributes used in GROUP BY clause must present in SELECT statement.
The HAVING Clause enables you to specify conditions that filter which group results appear in the results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. So, we cannot use HAVING clause without GROUP BY clause.
 Question 14
Consider the above tables A, B and C. How many tuples does the result of the following SQL query contains?
```SELECT A.id
FROM   A
WHERE  A.age > ALL (SELECT B.age
FROM   B
WHERE  B. name = "arun")```

 A 4 B 3 C 0 D 1
Database-Management-System       SQL       Gate 2012
Question 14 Explanation:

First query (2) will be executed and 0 (no) rows will be selected because in relation B there is no Name ‘Arun’.
The outer query (1) results the follow and that will be the result of entire query now. (Because inner query returns 0 rows).
 Question 15
Database table by name Loan_Records is given below.
```Borrower    Bank_Manager   Loan_Amount
Ramesh      Sunderajan     10000.00
Suresh      Ramgopal       5000.00
Mahesh      Sunderajan     7000.00```
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 );```

 A 3 B 9 C 5 D 6
Database-Management-System       SQL       Gate 2011
Question 15 Explanation:
 Question 16
Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table. Let MX and My denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out?
`SELECT Y FROM T WHERE X=7;`
`
 A 127 B 255 C 129 D 257
Database-Management-System       SQL       Gate 2011
Question 16 Explanation:
 Question 17
A relational schema for a train reservation database is given below. Passenger (pid, pname, age) Reservation (pid, class, tid)
```Table: Passenger
pid   pname   age
-----------------
0    Sachin   65
1    Rahul    66
2    Sourav   67
3    Anil     69

Table : Reservation
pid  class  tid
---------------
0    AC   8200
1    AC   8201
2    SC   8201
5    AC   8203
1    SC   8204
3    AC   8202```
What pids are returned by the following SQL query for the above instance of the tables?
```SLECT pid
FROM Reservation ,
WHERE class ‘AC’ AND
EXISTS (SELECT *
FROM Passenger
WHERE age > 65 AND
Passenger. pid = Reservation.pid)```

 A 1, 0 B 1, 2 C 1, 3 D 1, 5
Database-Management-System       SQL       2010
Question 17 Explanation:
Passenger:

― 1, 3 Pids are returned
 Question 18
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?
 A Find the names of all suppliers who have supplied a non-blue part. B Find the names of all suppliers who have not supplied a non-blue part. C Find the names of all suppliers who have supplied only blue parts. D Find the names of all suppliers who have not supplied only blue parts.
Database-Management-System       SQL       2009
Question 18 Explanation:

If we execute the given query the output will be S3 and S4 i.e., names of all suppliers who didn’t supply blue parts which is option (A).
Option (D) says names of suppliers who didn’t supply only blue parts that means, supplier should supply all other parts for sure and shouldn’t supply blue part.
 Question 19
Student (school-id, sch-roll-no, sname, saddress) School (school-id, sch-name, sch-address, sch-phone) Enrolment(school-id sch-roll-no, erollno, examname) ExamResult(erollno, examname, marks) What does the following SQL query output?
 `SELECT  sch-name, COUNT (*)` `FROM    School C, Enrolment E, ExamResult R` `WHERE   E.school-id = C.school-id` `AND` `E.examname = R.examname AND E.erollno = R.erollno` `AND` `R.marks = 100 AND S.school-id IN (SELECT school-id` `                                ``FROM student` `                                ``GROUP BY school-id` `                                 ``HAVING COUNT (*) > 200)` `GROUP By school-id` ` ``/* Add code here. Remove these lines if not writing code */`
 A for each school with more than 200 students appearing in exams, the name of the school and the number of 100s scored by its students B for each school with more than 200 students in it, the name of the school and the number of 100s scored by its students C for each school with more than 200 students in it, the name of the school and the number of its students scoring 100 in at least one exam D nothing; the query has a syntax error
Database-Management-System       SQL       Gate 2008-IT
Question 19 Explanation:
If select clause consist of aggregate and non-aggregate columns, all non-aggregate columns in the select clause must appear in Group By clause. But in this Group By clause consists school-id instaed of school-name.
 Question 20
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?
 A 2 and 5 B 1 and 3 C 1 and 4 D 3 and 5
Database-Management-System       SQL       Gate-2006
Question 20 Explanation:
Query 1 & 2 gives the same output for all not all data based its true because the salaries may be distinct variables. Statement 1 is true.
The customer with largest balance gets rank 1. Ties are broken with ranks are skipped.
So, both queries may doesn’t give same output. Statement 4 is correct.
 Question 21
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:
```Query1: select student from enrolled where
student in (select student from paid)
Query2: select student from paid where
student in (select student from enrolled)
Query3: select E.student from enrolled E, paid P
where E.student = P.student
Query4:  select student from paid where exists
(select * from enrolled where enrolled.student
= paid.student)```
Which one of the following statements is correct?
 A All queries return identical row sets for any database. B Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets. C There exist databases for which Query3 returns strictly fewer rows than Query2. D There exist databases for which Query4 will encounter an integrity violation at runtime.
Database-Management-System       SQL       Gate-2006
Question 21 Explanation:
Consider Table examples as:

Query1 : Output
abcd
abcd
PQRS
Query 2 : Output
abcd
PQRS
Query 3 : Output
abcd
PQRS
Query 4 : Output
abcd
PQRS
Query 2 & Query 4 gives same results but Query 1 & Query 3 gives different results.
 Question 22
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
 A Karthikeyan, Boris B Sachin, Salman C Karthikeyan, Boris, Sachin D Schumacher, Senna
Database-Management-System       SQL       Gate 2006-IT
Question 22 Explanation:
For colour = "Red"
did = {22, 22, 31, 31, 64}
For colour = "Green"
did = {22, 31, 74}
Intersection of Red and Green will be = {22, 31}, which is Karthikeyan and Boris.
 Question 23
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 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
 A 36 - 40 B 44 - 48 C 60 - 64 D 100 - 104
Database-Management-System       SQL       Gate 2006-IT
Question 23 Explanation:
(4) for taking red cars with (20) comparisions for did and (4) for finding green cars with (10) for did.
red did : 22, 31, 64
green did : 22, 31, 74
(6) for intersection
(1) for searching 22 in driver relation, and (3) for searching 31.
Total: 38 + 6 + 4 = 48
 Question 24
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```
 A Titles of the four most expensive books B Title of the fifth most inexpensive book C Title of the fifth most expensive book D Titles of the five most expensive books
Database-Management-System       SQL       Gate-2005
Question 24 Explanation:
Which results titles of the five most expensive books.
The where clause of outer query will be true for 5 most expensive books.
 Question 25
A company maintains records of sales made by its salespersons and pays them commission based on each individual's total sales made in a year. This data is maintained in a table with following schema:
salesinfo = (salespersonid, totalsales, commission)
In a certain year, due to better business results, the company decides to further reward its salespersons by enhancing the commission paid to them as per the following formula:
If commission < = 50000, enhance it by 2% If 50000 < commission < = 100000, enhance it by 4% If commission > 100000, enhance it by 6%
The IT staff has written three different SQL scripts to calculate enhancement for each slab, each of these scripts is to run as a separate transaction as follows:
 T1 ```Update salesinfo Set commission = commission * 1.02 Where commission < = 50000;``` T2 ```Update salesinfo Set commission = commission * 1.04 Where commission > 50000 and commission is < = 100000;``` T3 ```Update salesinfo Set commission = commission * 1.06 Where commission > 100000;```
Which of the following options of running these transactions will update the commission of all salespersons correctly
 A Execute T1 followed by T2 followed by T3 B Execute T2, followed by T3; T1 running concurrently throughout C Execute T3 followed by T2; T1 running concurrently throughout D Execute T3 followed by T2 followed by T1
Database-Management-System       SQL       Gate 2005-IT
Question 25 Explanation:
T3 followed by T2 followed by T1 will be the correct execution sequence.
In other cases some people will get two times increment, for example,
if we have T1 followed by T2 and if initial commission is 49500, then he is belonging to <50000.
Hence, 49500 * 1.02 = 50490.
Now again he is eligible for second category. So, he will get again increment as,
50490 * 1.04 = 52509.6
So he will get increment two times, but he is eligible for only one slab of commission.
 Question 26
A table 'student' with schema (roll, name, hostel, marks), and another table 'hobby' with schema (roll, hobbyname) contains records as shown below:
Table: Student
ROLL NAME HOSTEL MARKS
1798 Manoj Rathod 7 95
2154 Soumic Banerjee 5 68
2369 Gumma Reddy 7 86
2643 Suhas Kulkarni 5 78
2872 Kiran Vora 5 92
2926 Manoj Kunkalikar 5 94
2959 Hemant Karkhanis 7 88
3125 Rajesh Doshi 5 82

Table: hobby
ROLL HOBBYNAME
1798 chess
1798 music
2154 music
2369 swimming
2581 cricket
2643 chess
2643 hockey
2711 volleyball
2872 football
2926 cricket
2959 photography
3125 music
3125 chess
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’ = ∏hostel ((σs.roll = H.roll (σmarks > 75 and roll > 2000 and roll < 3000 (S)) X (H)) The difference between the number of rows output by the SQL statement and the number of tuples in S’ is
 A 6 B 4 C 2 D 0
Database-Management-System       SQL       Gate 2005-IT
Question 26 Explanation:
SQL query will return:

Total 7 rows are selected.
Where in relational algebra only distinct values of hostels are selected,i.e., 5, 6, 7 (3 rows).
∴ Answer is 7 - 3 =4
 Question 27
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
 A do not supply any item B supply exactly one item C supply one or more items D supply two or more items
Database-Management-System       SQl       Gate 2005-IT
Question 27 Explanation:
Here (not unique) in nested query ensures that only for those suppliers it return True which supplies more than 1 item in which case supplier id in inner query will be repeated for that supplier. Hence, the answer is (D) which supply two or more items.
 Question 28

 A the average salary is more than the average salary in the company B the average salary of male employees is more than the average salary of all male employees in the company C the average salary of male employees is more than the average salary of employees in the same department D the average salary of male employees is more than the average salary in the company
Database-Management-System       SQL       Gate-2004
Question 28 Explanation:
Group by (avg(salary) > (select avg (salary) from employee))
This results the employees who having the salary more than the average salary.
Sex = M
Selects the Male employees whose salary is more than the average salary in the company.
 Question 29
A relational database contains two tables student and department in which student table has columns roll_no, name and dept_id and department table has columns dept_id and dept_name. The following insert statements were executed successfully to populate the empty tables:
```Insert into department values (1, 'Mathematics')
Insert into department values (2, 'Physics')
Insert into student values (l, 'Navin', 1)
Insert into student values (2, 'Mukesh', 2)
Insert into student values (3, 'Gita', 1)```
How many rows and columns will be retrieved by the following SQL statement?
`Select * from student, department`
 A 0 row and 4 columns B 3 rows and 4 columns C 3 rows and 5 columns D 6 rows and 5 columns
Database-Management-System       SQL       Gate 2004-IT
Question 29 Explanation:
Simply, cartesian product of two tables will result
rows = 3 * 2 = 6
Columns = 3 + 2 = 5
 Question 30
A table T1 in a relational database has the following rows and columns:
 roll no. marks 1 10 2 20 3 30 4 Null
The following sequence of SQL statements was successfully executed on table T1.
```Update T1 set marks = marks + 5
Select avg(marks) from T1```
What is the output of the select statement?
 A 18.75 B 20 C 25 D NULL
Database-Management-System       SQL       Gate 2004-IT
Question 30 Explanation:
Update on null values gives null. Now, avg function ignores null values. So, here avg will be
(15+25+35)/3 = 25
 Question 31
Consider the following SQL query
```select distinct al, a2,........., an
from r1, r2,........, rm
where P```
For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions ?
 A B C D
Database-Management-System       SQL       Gate-2003
Question 31 Explanation:
If we want to get distinct elements then we need to perform cross product in between the relations r1, r2, .... rm.
 Question 32
Consider the set of relations shown below and the SQL query that follows.
```Students: (Roll_number, Name, Date_of_birth)
Courses: (Course number, Course_name, Instructor)
``` select distinct Name
and Courses.Instructor = Korth
Which of the following sets is computed by the above query?
 A Names of students who have got an A grade in all courses taught by Korth B Names of students who have got an A grade in all courses C Names of students who have got an A grade in at least one of the courses taught by Korth D in none of the above
Database-Management-System       SQL       Gate-2003
Question 32 Explanation:
The query results a names of students who got an A grade in at least one of the courses taught by korth.
 Question 33

 A r has no duplicates and s is non-empty B r and s have no duplicates C s has no duplicates and r is non-empty D r and s have the same number of tuples
Database-Management-System       SQL       Gate-2000
Question 33 Explanation:
r has no duplicate, if r can have duplicates it can be remove in the final state. s in non-empty if s is empty then r*s becomes empty.
 Question 34

In SQL, relations can contain null values, and comparisons with null values are treated as unknown. Suppose all comparisons with a null value are treated as false. Which of the following pairs is not equivalent?

 A x = 5 not AND (not (x = 5) B x = 5 AND x > 4 and x < 6, where x is an integer C x ≠ 5 AND not (x = 5) D None of the above
Database-Management-System       SQL       Gate-2000
Question 34 Explanation:
For all values less than five, x<5 is true and if x=5 then it is false.
 Question 35

 A Theory Explanation is given below.
Database-Management-System       SQL       Gate-2000
 Question 36
Which of the following is/are correct?
 A An SQL query automatically eliminates duplicates B An SQL query will not work if there are no indexes on the relations C SQL permits attribute names to be repeated in the same relation D None of the above
Database-Management-System       SQL       Gate-1999
Question 36 Explanation:
→ SQL won't remove duplicates like relational algebra projection, we have to remove it explicitly by distinct.
→ If there are no indexes on the relation SQL, then also it works.
→ SQL does not permit 2 attributes to have same name in a relation.
 Question 37

 A A tuple (z,w) with z > y is deleted B A tuple (z,w) with z > x is deleted C A tuple (z,w) with w < x is deleted D The deletion of (x,y) is prohibited
Database-Management-System       SQL       Gate-2001
There are 37 questions to complete.