Students Preparing for GATE(CS/IT) Exam are requested to attempt these questions. Similar types of SQL queries are also asked in Technical Interview for Software Developer post or Database Administrator Post.
PROBLEM – 1
Read the following statement and find the correct option.
P : A query in Structural Query Language can contain HAVING clause without having GROUP BY clause
Q : A query in Structural Query Language can contain a HAVING clause only when GROUP BY clause is also there in query.
R : All attributes used in the GROUP BY clause must appear in the SELECT clause
S : No It is not necessary that 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
Explanation : As per the SQL concepts option c is right but suppose if tak about execution of these sql query on different database . Let us take MYSQL then option B will also be right. What happend if
If we use a HAVING clause without a GROUP BY clause in this situation the HAVING condition applies to all rows that satisfy the search condition and in the result all rows that satisfy the search condition make up a single group.
PROBLEM – 2
A database table named as 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
EXPLANATION: Result of first subquery will be Table S having the records -Borrower Bank_Manager
————————–
Ramesh Sunderajan
Suresh Ramgqpal
Mahesh Sunderjan Result of Second sub query will be table T having the records-Bank_Manager Loan_Amount
—————————
Sunderajan 10000.00
Ramgopal 5000.00
Sunderjan 7000.00When Natural Join is performed on these two table S and T the will After Join resultant table will have the following records Borrower Bank_Manager Load_Amount
————————————
Ramesh Sunderajan 10000.00
Ramesh Sunderajan 7000.00
Suresh Ramgopal 5000.00
Mahesh Sunderajan 10000.00
Mahesh Sunderajan 7000.00
Here point to remember is that in natural join matching occur on column name with same name.
Q.3. Suppose a table T has two
columns X and Y. Type of each column is integer. Once the table is created then
a record X=1, Y=1 is inserted in the table. If MX and My represent the respective maximum values of X and Y among
all records in the table at any instant of time. Using MX and MY, some new data is inserted in the table 128 times
with X and Y values being MX+1, 2*MY+1 respectively. It must be remember that
each time values of MX and MY change
after insertion. If following SQL query after the steps mentioned above is
carried out then what will be the output?
SELECT Y FROM T WHERE X=7;
a.127
b.255
c.129
d.257
Answer: a
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)
Which of following query should be execute to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the:
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 output?
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 sub query.
Answer B
EXPLANATION :This query will generate the correct result because at first the inner query will return the last max hire date in every department located at id 1700. Then the outer query take the result of inner query and provide the correct result.
PROBLEM – 5
Consider the following relational schema for a train reservation database . Passenger (pid, pname, age) Reservation (pid, class, tid)
Table: Passenger
pidpnameage
—————–
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 are the pids 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
EXPLANATION :
The inner query SELECT *
FROM Passenger
WHERE age > 65 AND
Passenger. pid = Reservation.pid) will return 4 tuples
1 AC 8201
2 SC 8201
1 SC 8204
3 AC 8202
Now the outer query will select those tuple only which have the class AC , as a result Pid return will be 1 and 3 so Option C is correct.
PROBLEM – 6
A relational schema is given below:
Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)
If the following sql query is executed for 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’))
Suppose some records are inserted in the table. Then find out that 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
PROBLEM -7
Take a table employee(empId, name, department, salary) and the two queries Q1 ,Q2 as given below.
Suppose 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 query
Answer b
PROBLEM – 9
For the following two statements
S1: Declaration of a foreign key 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)
The find the correct option among the following.
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