Time Left - 20:00 mins

GATE CS : Databases Champion Quiz 3

Attempt now to get your rank among 527 students!

Question 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

Question 2

Consider the following SQL query
select distinct a1, 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?

Question 3

A relational schema for a train reservation database is given below
Passenger (pid, pname, age)
Reservation (pid, cass, tid)
Table : Passenger

Table: Reservation

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)

Question 4

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?

Question 5

Highlight the correct sequence as per SQL working?
1) Making connection to MySql server
2) Selecting database
3) Fetching of data from query
4) Executing of SQL query

Question 6

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 );

Question 7

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;

Question 8

The relation scheme Student Performance (name, courseNo, rollNo, grade) has the following functional dependencies:
name, courseNo, grade
rollNo, courseNo grade
name roll
rollNo name
The highest normal form of this relation scheme is

Question 9

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 

Question 10

The employee information in a company is stored in the relation
Employee (name, sex, salary, deptName)
Consider the following SQL query:
Select deptName
From Employee
where sex = “M’
Group by deptName
Having avg(salary) > (select avg (salary) from Employee)
It returns the names of the department in which
  • 527 attempts
  • 3 upvotes
  • 9 comments
Mar 30GATE & PSU CS