50 DBMS MCQs with Answers

Question 41

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

• P and R

• P and S

• Q and R

• Q and S

Question 42

An index is clustered, if

• it is on a set of fields that form a candidate key.
• it is on a set of fields that include the primary key.
• the data records of the file are organized in the same order as the data entries of the index.
• the data records of the file are organized not in the same order as the data entries of the index.

Question 43

Which of the following command is used to delete a table in SQL?

• delete
• truncate
• remove
• drop

Question 44

Which of the following related to snowflake schema is true?

• Each dimension is represented by a single dimensional table
  

• Maintenance efforts are less
  

• Dimension tables are normalised
  

• It is not an extension of star schema
  

Question 45

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

• the average salary is more than the average salary in the company
• the average salary of male employees is more than the average salary of all male employees in the company
• the average salary of male employees is more than the average salary of employees in the same department
• the average salary of male employees is more than the average salary in the company

Question 46

A database table T1 has 2000 records and occupies 80 disk blocks. Another table T2 has 400 records and occupies 20 disk blocks. These two tables have to be joined as per a specified join condition that needs to be evaluated for every pair of records from these two tables. The memory buffer space available can hold exactly one block of records for T1 and one block of records for T2 simultaneously at any point in time. No index is available on either table. If Nested-loop join algorithm is employed to perform the join, with the most appropriate choice of table to be used in outer loop, the number of block accesses required for reading the data are  

• 800000

• 40080

• 32020

• 100

Question 47

Consider a table that describes the customers :

Customers(custid, name, gender, rating)

The rating value is an integer in the range 1 to 5 and only two values (male and female) are recorded for gender. Consider the query “how many male customers have a rating of 5”? The best indexing mechanism appropriate for the query is

• Linear hashing
• Extendible hashing
• B+ Tree
• Bit-mapped hashing

Question 48

Select operation in SQL is equivalent to

• the selection operation in relational algebra
• the selection operation in relational algebra, except that select in SQL retains duplicates
• the projection operation in relational algebra
• the projection operation in relational algebra, except that select in SQL retains duplicates

Question 49

Consider the following tables : What will be the output of following SQL query ?

SELECT * FROM EMPLOYEE E    
WHERE 2 = (SELECT COUNT(DISTINCT E1.SALARY)    
FROM EMPLOYEE E1    
WHERE E1.SALARY>E.SALARY) 

• Second highest salary
• Two distinct salary of employees
• Third highest salary
• Employee with second highest salary

Question 50

In a Hierachical database, a hashing function is used to locate the ________.

• Collision
• Root
• Foreign Key
• Records