Which of the following can be declared once in a table double varchar LONG mcq

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

SQL    50 DBMS MCQs with Answers    
Discuss it

Question 1 Explanation: 

According to standard SQL answer should be option (C) which is answer key given by GATE authority. 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.

CREATE TABLE temp 
  ( 
     id   INT, 
     name VARCHAR(100) 
  ); 

INSERT INTO temp VALUES (1, "abc"); 
INSERT INTO temp VALUES (2, "abc"); 
INSERT INTO temp VALUES (3, "bcd"); 
INSERT INTO temp VALUES (4, "cde"); 

SELECT Count(*) 
FROM   temp 
GROUP  BY name; 

Output:

count(*)
--------
2
1
1

Alternative way - Statement (P) "An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause" is correct because Having clause is applied after the aggregation phase and must be used if you want to filter aggregate results and Having doesn't require Group By clause. A HAVING clause without a GROUP BY clause is valid and (arguably) useful syntax in Standard SQL. Consider this example, which is valid Standard SQL:

 SELECT 'T' AS result
   FROM Book
 HAVING MIN(NumberOfPages) < MAX(NumberOfPages);

Statement (S) "Not all attributes used in the GROUP BY clause need to appear in the SELECT clause" is correct but if we use Group By clause must, there are limitations on what we can put into the Select clause.

Question 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

GATE CS 2012    SQL    50 DBMS MCQs with Answers    
Discuss it

Question 2 Explanation: 

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?

SELECT Y FROM T WHERE X=7;

A

127

B

255

C

129

D

257

GATE CS 2011    SQL    
Discuss it

Question 3 Explanation: 

See Question 4 of http://www.geeksforgeeks.org/database-management-systems-set-4/ And question 30 of http://clweb.csa.iisc.ernet.in/rahulsharma/gate2011key.html

Question 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

GATE CS 2011    SQL    50 DBMS MCQs with Answers    
Discuss it

Question 4 Explanation: 

See Question 3 of http://www.geeksforgeeks.org/database-management-systems-set-4/

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

SLECT 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

GATE CS 2010    SQL    
Discuss it

Question 5 Explanation: 

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:

count(*)
--------
2
1
1

0Which of the above queries are equivalent?

A

I and II

B

I and III

C

II and IV

D

III and IV

GATE-CS-2009    SQL    
Discuss it

Question 6 Explanation: 

I and II describe the division operator in Relational Algebra and Tuple Relational Calculus respectively. See Page 3 of this and slide numbers 9,10 of this for more details.

Question 7

Consider the following relational schema: 
 

count(*)
--------
2
1
1

1

Consider the following relational query on the above database: 
 

count(*)
--------
2
1
1

2

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.
 

E

None
 

GATE-CS-2009    SQL    50 DBMS MCQs with Answers    
Discuss it

Question 7 Explanation: 

(D) option matched because given query returns suppliers who have not supplied any blue parts. That means it can include other than blue parts. 

(A): False, as this may include blue parts and may not include "null" parts. 

(B): Obviously false because it returning other than any blue part. 

(C): Obviously false because it does not return this. 

(D): Correct. Please try here:  

This explanation is contributed by Archit Garg.
 

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?

count(*)
--------
2
1
1

3

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

GATE-CS-2007    SQL    
Discuss it

Question 8 Explanation: 

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

count(*)
--------
2
1
1

4

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. 
 

count(*)
--------
2
1
1

5

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

count(*)
--------
2
1
1

6

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:

count(*)
--------
2
1
1

7Which 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.

GATE-CS-2014-(Set-1)    SQL    50 DBMS MCQs with Answers    
Discuss it

Question 9 Explanation: 

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

Using a check condition we can have the same effect as Foreign key while adding elements to the child table. But when we delete an element from the parent table the referential integrity constraint is no longer valid. So, a check constraint cannot replace a foreign key.

So, we cannot replace it with a single check.

 

count(*)
--------
2
1
1

8

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.

Question 10

Given the following schema:

count(*)
--------
2
1
1

9You 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:

 SELECT 'T' AS result
   FROM Book
 HAVING MIN(NumberOfPages) < MAX(NumberOfPages);

0What is the outcome?

A

It executes but does not give the correct result.

B

It executes and gives the correct result.

C

It 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

GATE-CS-2014-(Set-1)    SQL    
Discuss it

Question 10 Explanation: 

The given query uses below inner query.

 SELECT 'T' AS result
   FROM Book
 HAVING MIN(NumberOfPages) < MAX(NumberOfPages);

1The inner query produces last max hire-date in every department located at location id 1700. The outer query simply picks all pairs of inner query. Therefore, the query produces correct result.

 SELECT 'T' AS result
   FROM Book
 HAVING MIN(NumberOfPages) < MAX(NumberOfPages);

2

What is double data type in SQL?

Which of the following are SQL data types Mcq?

Which of the following commands in SQL is used to create a table Mcq?

Which type of data can be stored in the database Mcq?