50 DBMS MCQs with Answers

  • Last Updated : 23 Feb, 2022


Question 1
An index is clustered, if
A
it is on a set of fields that form a candidate key.
B
it is on a set of fields that include the primary key.
C
the data records of the file are organized in the same order as the data entries of the index.
D
the data records of the file are organized not in the same order as the data entries of the index.
File structures (sequential files, indexing, B and B+ trees)    50 DBMS MCQs with Answers    
Discuss it


Question 1 Explanation: 
A database index is clustered if physical records on disk follow the index order.
Question 2
Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F = {CH -> G, A -> BC, B -> CFH, E -> A, F -> EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R. How many candidate keys does the relation R have?
A
3
B
4
C
5
D
6
Database Design(Normal Forms)    50 DBMS MCQs with Answers    
Discuss it


Question 2 Explanation: 
A+ is ABCEFGH which is all attributes except D. B+ is also ABCEFGH which is all attributes except D. E+ is also ABCEFGH which is all attributes except D. F+ is also ABCEFGH which is all attributes except D. So there are total 4 candidate keys AD, BD, ED and FD
Question 3

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 3 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 4
Consider the following transactions with data items P and Q initialized to zero:
T1: read (P) ;
    read (Q) ;
    if P = 0 then Q : = Q + 1 ;
    write (Q) ;
T2: read (Q) ;
    read (P) ;
    if Q = 0 then P : = P + 1 ;
    write (P) ;
Any non-serial interleaving of T1 and T2 for concurrent execution leads to
A
A serializable schedule
B
A schedule that is not conflict serializable
C
A conflict serializable schedule
D
A schedule for which a precedence graph cannot be drawn
GATE CS 2012    Transactions and concurrency control    50 DBMS MCQs with Answers    
Discuss it


Question 5
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 5 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 6
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 6 Explanation: 
Question 7

Which of the following concurrency control protocols ensure both conflict serializability and freedom from deadlock? I. 2-phase locking II. Time-stamp ordering

A

I only

B

II only

C

Both I and II

D

Neither I nor II

Transactions and concurrency control    50 DBMS MCQs with Answers    
Discuss it


Question 7 Explanation: 

2 Phase Locking (2PL) is a concurrency control method that guarantees serializability. The protocol utilizes locks, applied by a transaction to data, which may block (interpreted as signals to stop) other transactions from accessing the same data during the transaction’s life. 2PL may be lead to deadlocks that result from the mutual blocking of two or more transactions. See the following situation, neither T3 nor T4 can make progress.
""
Timestamp-based concurrency control algorithm is a non-lock concurrency control method. In Timestamp based method, deadlock cannot occur as no transaction ever waits.

Question 8

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.
 

E

None
 

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


Question 8 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: http://sqlfiddle.com/#!9/9ae12d/1/0 

This explanation is contributed by Archit Garg.
 

Question 9
A clustering index is defined on the fields which are of type
A
non-key and ordering
B
non-key and non-ordering
C
key and ordering
D
key and non-ordering
GATE CS 2008     File structures (sequential files, indexing, B and B+ trees)    50 DBMS MCQs with Answers    
Discuss it


Question 9 Explanation: 
A clustering index as the name suggests is created when the data can be grouped in the form of clusters. yashika For example: A small database file storing roll no and subjects enrolled for a particular student. Here data can be grouped on the basis of Roll No.s. Hence to create such kind of index files, fields could be non-key attributes and which are in ordered form so as to form clusters easily. Hence option (A) – non key and ordering. Additional points for Clustered Indexing: 1. The number of entries in the index file are the unique values of the attribute on which indexing is done. 2. The pointer in the index file will give the base address of the block in which the value appear for the first time. http://quiz.geeksforgeeks.org/indexing-in-databases-set-1/ This solution is contributed by Yashika Arora.
Question 10
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.
GATE-CS-2014-(Set-1)    SQL    50 DBMS MCQs with Answers    
Discuss it


Question 10 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.

 

    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.

There are 50 questions to complete.
My Personal Notes arrow_drop_up