SQL

SQL

Please wait while the activity loads. If this activity does not load, try refreshing your browser. Also, this page requires javascript. Please visit using a browser with javascript enabled.

If loading fails, click here to try again

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

According to standard SQL answer should be C. Refer
If we talk about different SQL implementations like MySQL, then option B is also right. But in question they seem to be talking about standard SQL not about implementation. For example below is a
P is correct in most of the implementations. HAVING clause can also be used with aggregate function. If we use a HAVING clause without a GROUP BY clause, the HAVING condition applies to all rows that satisfy the search condition. In other words, all rows that satisfy the search condition make up a single group. See this for more details.
S is correct . To verify S, try following queries in SQL.

The meaning of “ALL” is the A.Age should be greater than all the values returned by the subquery. There is no entry with name “arun” in table B. So the subquery will return NULL. If a subquery returns NULL, then the condition becomes true for all rows of A (See this for details). So all rows of table A are selected.
Source: http://www.geeksforgeeks.org/database-management-system-set-3/

Question 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?

When a subquery uses values from outer query, the subquery is called correlated subquery. The correlated subquery is evaluated once for each row processed by the outer query.
The outer query selects 4 entries (with pids as 0, 1, 5, 3) from Reservation table. Out of these selected entries, the subquery returns Non-Null values only for 1 and 3.

Question 6

Let R and S be relational schemes such that R={a,b,c} and S={c}. Now consider the following queries on the database:

The subquery “SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’” gives pids of parts which are not blue. The bigger subquery “SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’)” gives sids of all those suppliers who have supplied blue parts. The complete query gives the names of all suppliers who have supplied a non-blue part

Question 8

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?

First note that they asked for Anyone (= All) not for Any.
Here, Everyone means all of the group.
Anyone means all or any part of the group.
Let the employee(empId, name, department, salary) have the following instance.
empId name department salary
----------------------------------

Now the actual result should contain empId : e1 , e3 and e5 ( because they have salary greater than anyone employee in the department '5')
--------------------------------------------------------
Now Q1 :
Note : EXISTS(empty set) gives FALSE, and NOT EXISTS(empty set) gives TRUE.

Select e.empId
From employee e
Where not exists
(Select * From employee s where s.department = “5” and
s.salary >=e.salary)

Q1 will result only empId e1.
---------------------------------------------------------
whereas Q2 :

Q2 will result empId e1, e3 and e5.
--------------------------------------------------------
Hence Q1 is the correct query.
Note that if we use ALL in place of Any in second query then this will be correct.
Option (A) is correct.

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)

S1: A foreign key declaration can always
be replaced by an equivalent check
assertion in SQL.

False:
Check assertions are not sufficient to replace foreign key. Foreign key declaration may have cascade delete which is not possible by just check insertion.

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)

False:
Foreign key in one table should uniquely identifies a row of 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 doesn't uniquely identify a row in table R.