adplus-dvertising
1. 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 only
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
Answer: (c).Q and R
2. Table A
Id Name Age
----------------
12 Arun 60
15 Shreya 24
99 Rohit 11

Table B
Id Name Age
----------------
15 Shreya 24
25 Hari 40
98 Rohit 20
99 Rohit 11

Table C
Id Phone Area
-----------------
10 2200 02
99 2100 01
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
Answer: (b).3
3. 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
Answer: (a).127
4. 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
Answer: (c).5
5. 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?
SELECT 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
Answer: (c).1, 3
6. 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 IN (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.
Answer: (a).Find the names of all suppliers who have supplied a non-blue part.
7. Consider the table employee(empId, name, department, salary) and the two queries Q1 ,Q2 below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary than anyone in the department 5, which one of the statements is TRUE for any arbitrary employee table?
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”)
a. Q1 is the correct query
b. Q2 is the correct query
c. Both Q1 and Q2 produce the same answer
d. Neither Q1 nor Q2 is the correct quer
Answer: (b).Q2 is the correct query
8. 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
Answer: (d).Both S1 and S2 are FALSE
9. 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. 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
Answer: (b).It executes and gives the correct result
10. 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 (D)
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
Answer: (c).select R.* from R,(select distinct a from S) as S1 where R.a=S1.a
Page 1 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.