DBMS MCQ Questions and Answer Set-7

1. Consider a Payment table with attributes PAY_ID (Primary Key), PAY_DT, PAY_AMT, BANK_ACCT, INIT_NAME, and PAY_MODE. Except for PAY_ID, no columns are unique. The table has three indexes as follows:

IDX1 – PAY_ID

IDX2 – BANK_ACCT, PAY_AMT

IDX3 – INIT_NAME, PAY_DT

Which of the following queries will result in INDEX RANGE SCAN

a) WHERE INIT_NAME LIKE ‘%ven’

b) WHERE PAY_ID <> 200

c) WHERE PAY_MODE = ‘online’

d) WHERE INIT_NAME = ‘Alice’ and PAY_DT > ‘1-Jan-2015’

Answer-D

2. Consider the following record of the customer table:

LASTNAME-Desouza

What will be the output of the following query?

SELECT LENGTH (SUBSTR (LASTNAME, 3, 7)) FROM CUSTOMER

a) 7

b) 5

c) 8

d) 3

Answer-B

3. ACCDT

  8-JAN-13

Which of the following query displays the data in the following format:

DAY        MON

08           01

a) SELECT TO_CHAR(Accdt,’dy’)”DAY”, TO_CHAR(Accdt,’mon’)”MON” FROM Customer

b) SELECT TO_DATE(Accdt,’dy’)”DAY”, TO_CHAR(Accdt,’mon’)”MON” FROM Customer

c) SELECT TO_CHAR (Accdt,’dd’)”DAY”, TO_CHAR (Accdt,’mm’)”MON” FROM Customer

d) SELECT TO_DATE(Accdt,’dd’)”DAY”, TO_DATE(Accdt,’mm’)”MON” FROM Customer

Answer-C

DBMS-MCQ

4. SELECT ABS (MONTHS_BETWEEN (ADD_MONTHS (’09-FEB-2014′, 3),’09-Mar-2015′))”MONTHS” FROM DUAL

a)-10

b) 13

c) 10

d)-13

Answer-C

5. A collection and a document in Mongo DB is equivalent to…………. concepts respectively.

a)Table and Column

b) Table and Row

c) Column and Row

d)Database and Table

ANSWER-B

6. In which format Mongo DB represents document structure?

a)BSON

b).txt

c).Docx

d) None of these

Answer-A

7. Which field is always the first field in the document?

a) _id

b) Ob_id

c) Id

d) None of these

Answer-A

8. Which functional dependency types is/are present in the following dependencies?

StaffNo, BranchNo -> StaffName, BranchName, Position, DOB

StaffNo -> StaffName, Position, DOB

BranchNo -> BranchName

a)Trivial functional dependency

b) Partial functional dependency

c) Transitive functional dependency

d )None

Answer-b

9. The concatenation of the collection name and database name is called a –

a)Namespace

b)MongoDB

c)sharding

d)replica

 Answer-A

10. The select list of a subquery introduced by EXISTS almost always consists of ………. There is no reason to list column names as you are just verifying whether rows that meet the conditions specified in the subquery exist.

a) Percent (%)

b) Asterisk (*)

c) Comma (,)

d) None of the above

View Answer-B

11. Consider the below table.

Table tab_1

ID            Column_2           Name

11           F1           Fardeen

22           G1          Gautam

33           H1           Harish

44           I1            Fardeen

55           E1           Eklavya

NULL     NULL     NULL

What will be the result of the following query?

SELECT ID, column_2, Name

FROM dbo.tab_1

WHERE EXISTS (SELECT NULL)

a)

ID            Column_2           Name

11           F1           Fardeen

22           G1          Gautam

33           H1           Harish

44           I1            Fardeen

55           E1           Eklavya

b) It will show an error message.

c)

ID            Column_2           Name

NULL     NULL            NULL

d)

ID            Column_2           Name

11           F1           Fardeen

22           G1          Gautam

33           H1           Harish

44           I1            Fardeen

55           E1           Eklavya

NULL     NULL     NULL

View Answer-D

12. Using GROUP BY ………… has the effect of removing duplicates from the data.

a) With aggregates

b) With order by

c) Without order by

d) Without aggregates

View Answer-D

13. With ORDER BY clause……….. Is the default sort order.

a) Descending

b) Ascending

c) No order

d) None of the above

View Answer-B

14. Consider a relation R (A, B, C, D, E) with the following functional dependencies:

ABC -> DE and

D -> AB

The number of super keys of R is:

a) 2

b) 7

c) 10

d) 12

Answer: C

15. Suppose we wish to find the ID’s of the employees that are managed by people who are managed by the employee with ID 123. Here are two possible queries:

I.SELECT ee.empID FROM Emps ee, Emps ff

  WHERE ee.mgrID = ff.empID AND ff.mgrID = 123;

II.SELECT empID FROM Emps

  WHERE mgrID IN (SELECT empID FROM Emps WHERE mgrID = 123);

Which, if any, of the two queries above will correctly (in SQL2) get the desired set of employee ID’s?

a) Both I and II

b) I only

c) II only

d) Neither I nor I

Answer:A

Leave a Reply

Your email address will not be published. Required fields are marked *