50 DBMS MCQs with Answers

A database index is clustered if physical records on disk follow the index order.

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

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.

See http://www.geeksforgeeks.org/database-management-system-set-3/

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/

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

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.

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

A clustering index as the name suggests is created when the data can be grouped in the form of clusters. 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.

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.