adplus-dvertising
21. Given relations r(w, x) and s(y, z), the result of

SELECT DISTINCT w, x
FROM r, s

is guaranteed to be same as r, provided
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
Discuss
Answer: (a).r has no duplicates and s is non-empty
22. 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 AND not(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
Discuss
Answer: (c).x < 5 AND not (x = 5)
23. Consider the following three table to store student enrollements in different courses.

Student(EnrollNo, Name)
Course(CourseID, Name)
EnrollMents(EnrollNo, CourseID)
(EnrollNo,CourseID are primary keys)

What does the following query do?

SELECT S.Name
FROM Student S, Course C, Enrollments E
WHERE S.EnrollNo = E.EnrollNo AND
C.Name = "DBMS" AND
E.CourseID = C.CourseID AND
S.EnrollNo IN
(SELECT S2.EnrollNo
FROM Student S2, Course C2, Enrollments E2
WHERE S2.EnrollNo = E2.EnrollNo AND
E2.CourseID = C2.CourseID
C2.Name = "OS")
a. Name of all students who are either enrolled in "DBMS" or "OS" courses
b. Name of all students who are enrolled in "DBMS" and "OS"
c. Name of all students who are either enrolled in "DBMS" or "OS" or both
d. None of the above
Discuss
Answer: (b).Name of all students who are enrolled in "DBMS" and "OS"
24. Consider the following Employee table

ID salary DeptName
1 10000 EC
2 40000 EC
3 30000 CS
4 40000 ME
5 50000 ME
6 60000 ME
7 70000 CS

How many rows are there in the result of following query?

SELECT E.ID
FROM Employee E
WHERE EXISTS (SELECT E2.salary
FROM Employee E2
WHERE E2.DeptName = 'CS'
AND E.salary > E2.salary)
a. 0
b. 4
c. 5
d. 6
Discuss
Answer: (c).5
25. 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
Discuss
Answer: (d).the projection operation in relational algebra, except that select in SQL retains duplicates
26. 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)
Discuss
Answer: (a).WHERE P1. Capacity> = All (select P2. Capacity from Cinema P2)
27. 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
Discuss
Answer: (d).Execute T3 followed by T2 followed by T1
28. 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
2581 Pradeep Pendse 6 92
2643 Suhas Kulkarni 5 78
2711 Nitin Kadam 8 72
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
Discuss
Answer: (b).4
29. 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
Discuss
Answer: (d).supply two or more items
30. Consider the relations r1(P, Q, R) and r2(R, S, T) with primary keys P and R respectively. The relation r1 contains 2000 tuples and r2 contains 2500 tuples. The maximum size of the join r1⋈ r2 is :
a. 2000
b. 2500
c. 4500
d. 5000
Discuss
Answer: (a).2000
Page 3 of 11

GATE CSE Resources

Questions from Previous year GATE question papers

UGC NET Computer science Resources

UGC NET Previous year questions and practice sets

NET General Paper 1

UGC NET Previous year questions and practice sets

GATE CSE Online Test

Attempt a small test to analyze your preparation level. This GATE exam includes questions from previous year GATE papers.

UGC NET practice Test

Practice test for UGC NET Computer Science Paper. The questions asked in this NET practice paper are from various previous year papers.