DBMS MCQ (Multiple Choice Questions)

DBMS MCQs

In this section, we are going to cover some of the basic questions based on DBMS, which are frequently asked during an interview. Here we will try to cover almost all the topics related to DBMS.

1) Relational database is a collection of

  1. Table
  2. Field
  3. Record or Row
  4. Colum
Answer: a Explanation: The answer is A. In Relational Database, Field is the column of the table that keeps data vertically, and the row is used to store it horizontally.

2) What associates with all information about every entry in the table?

  1. Column
  2. Row
  3. Entry
  4. Key
Answer: b Explanation: Column is used to store a piece of unique information associated with entries that can be anything such as id, name, number, etc. Key is just a constraint to find anything in relation, and a row shows all the attributes associated with entries.

3) In relation to a table, what term is used to refer single row?

  1. Tuple
  2. Relation Instance
  3. Attributes
  4. Field
Answer: a Explanation: Its answer is A because a tuple is a single entry that shows all attributes associated with the entry in a table.

4) The attributes of the table is referred to as:

  1. Tuple
  2. Column
  3. Row
  4. Field
Answer: b Explanation: The column is referred to as an attribute of a table that contains the unique information associated with an entry.

5) Relational algebra is a ______ which takes instances of relations as input and yields instances of relations as output.

  1. Relational query language
  2. Structural query language
  3. Procedural query language
  4. Fundamental query language
Answer: c

6) Choose the correct option:

  1. Set intersection is the fundamental operation in relational algebra.
  2. An assignment is a fundamental operation in relational algebra.
  3. Select is the fundamental operation in relational algebra.
  4. Rename operation is the fundamental operation in relational algebra.
Answer: c and d Explanation: The correct answer is C and D. Because Select, Project, Union, Set different, Cartesian product, and Rename is considered as fundamental operations in relational algebra while Set intersection, Assignment, Natural join is an additional operation in relational algebra.

7) Which language is used to define the database?

  1. DML
  2. DDL
  3. DCL
  4. TCL
Answer: a Explanation: DML(Data manipulating language) is used for accessing and manipulating the data, DCL(Data control language) is used for retrieving the stored data, and TCL(Transactional control language) is used for changes made by the DML statement.

8) Which of the following can be considered as the primary key?

  1. Name
  2. Roll no.
  3. Marks
  4. Street
Answer: b Explanation: The attribute associated with the primary key should be unique. In any class, more than one student can have a common name, more than one student can get equal marks in any subject, and their street can also be common. So here, the roll number can be considered as the primary key.

9) The overall description of the database is known as:

  1. Data manipulation
  2. Database schema
  3. Data definition
  4. Data integrity
Answer: b

10) A boy name X has rupees 1000 in their bank account. His friend Y needs rupees 200, so he tries to make a transaction. He asks his bank to make the transaction to his friend's account. After filling all the details and successful transaction, 200 is deducted from X's account. But his friend Y told him that he doesn't receive the money. What property of transaction has not been maintained here?

  1. Atomicity
  2. Consistency
  3. Isolation
  4. Durability
Answer: a Explanation: The correct answer is A because atomicity means data should remain atomic. It means that whenever an operation starts, it should execute fully. The operation should not break in the middle or execute partially.

11) Which of the following SQL command mentioned below is used to create a table, delete the table, and alter the table?

  1. DML(Data manipulating language)
  2. DDL(Data definition language)
  3. DQL(Data query language)
  4. Relational Schema
Answer: b Explanation: DDL command is the right choice because the DML command is used to make any changes in the database, DQL is used to retrieve any information from the database, and relational schema is the structure of relation.

12) If we want to remove some information from the database, then which of the following SQL command should we perform?

  1. DML
  2. TCL
  3. DCL
  4. DDL
Answer: a Explanation: Whenever we need to make any changes like INSERT, DELETE, UPDATE in the database, then we need to perform the DML command.

13) SELECT * FROM student;

Which of the following option is suitable for the given statement?
  1. DML
  2. DQL
  3. DDL
  4. DDL
Answer: b Explanation: SELECT is used to fetch values from the database

14) CREATE TABLE student (name VARCHAR (10), id INTEGER)

Choose the correct option for the given statement.
  1. DDL
  2. DML
  3. DQL
  4. TCL
Answer: a Explanation: CREATE command is used to build a table in the database.

15) To delete all the rows from a table, we need to apply the...... command.

  1. Truncate
  2. Remove
  3. Drop table
  4. Delete
Answer: a Explanation: Truncate is used to delete complete data without removing the table structure.

16) Which of the following command is used to delete a table from the database?

  1. DROP
  2. DELETE
  3. TRUNCATE
  4. REMOVE
Answer: a Explanation: The DROP command is used to remove the table from the database, DELETE is used to remove one or more rows from a table in the database.

17) DELETE FROM X

What does the above statement mean?
  1. Delete table
  2. Delete row
  3. Delete fields
  4. Remove entry x
Answer: d Explanation: The DELETE command removes the table entries.

18) The basic data type of SQL varchar(n) has ..... length Unicode characters, and char(n) has ...... length Unicode characters.

  1. Fixed, Variable
  2. Fixed, Fixed
  3. Variable, Variable
  4. Variable, Fixed
Answer: d Explanation: The storage required for the char and varchar variable is different. Char() has a static allocation of space, whereas varchar() has a dynamic allocation of space. So the length of varchar() will vary according to the specified string.

19) Which of the following command is used to display any values from the database?

  1. SELECT
  2. INSERT
  3. DELETE
  4. TRUNCATE
Answer: a Explanation: SELECT command is used to fetch values from the table. The syntax for the SELECT command is SELECT * FROM table_name;

20) _________statement returns only distinct values from the table.

  1. Different
  2. Distinct
  3. None
  4. All
Answer: b Explanation: The table column may contain duplicate values. If we need the unique value from any column, we need to apply a distinct command. The syntax for the distinct command is:
SELECT DISTINCT column name FROM TABLE;

21) SELECT * FROM class WHERE marks>90 AND roll_no = 20;

What should be the output for the above query?
  1. Marks and roll_no
  2. class
  3. Marks
  4. All the columns from the class table.
Answer: d Explanation: The "*" character in SQL is used to show all columns of the table.

22) Choose the wrong option in this question.

  1. SELECT * FROM table_name WHERE column_name;
  2. SELECT column_name FROM table_name;
  3. SELECT column_name FROM table_name WHERE condidtion;
  4. SELECT column_name WHERE condition;
Answer: d Explanation: The correct answer is option D because the SELECT command does not produce output without specifying the table name.

23) Which clause is used to extract only those records that fulfill a specified condition?

  1. SELECT
  2. WHERE
  3. FROM
  4. ALL
Answer: b

24) INSERT INTO employee ......(101,xyz,5000);

What keyword is missing in the given statement?
  1. table
  2. values
  3. column
  4. field
Answer: b Explanation: To insert value in the table, we have to use the INSERT keyword with the VALUES clause.

25) Which of the following condition allow to join relations?

  1. Set
  2. Where
  3. Using
  4. On
Answer: d Explanation: On condition is used for join expressions.

26) Which of the join operations do not preserve non matched tuples?

  1. Left join
  2. Inner join
  3. Right join
  4. Full join
Answer: b Explanation: Inner join returns all rows from both tables when there is at least one match is found.

27) What type of join is needed when you want to include rows that do not have matching values?

  1. Equi-join
  2. Natural join
  3. Outer join
  4. None of the above
Answer: d Explanation: An outer join does not require each record in the two joined tables to have a matching record.

28) Which joins refer to joining records from the right table with no matching values in the left table?

  1. Left join
  2. Right join
  3. Full join
  4. Half join
Answer: b Explanation: Right join returns all values from the right table and the matched values from the left table.

29) How many tables can be combined with a join?

  1. One
  2. Two
  3. Three
  4. Many
Answer: d Explanation: Join operation can combine many tables at a time.

30) Which join is used to return all tuples from both tables even condition doesn't satisfy?

  1. Inner join
  2. Right join
  3. Natural Join
  4. Full join
Answer: d Explanation: In a full outer join, all tuples from both relations are included in the result, irrespective of the matching condition.

31) SELECT * FROM employee JOIN takes USING (id);

The above query is equivalent to:
  1. SELECT * FROM employee LEFT JOIN takes USING (id);
  2. SELECT * FROM employee RIGHT JOIN takes USING (id);
  3. SELECT * FROM employee INNER JOIN takes USING (id);
  4. All of the mentioned above
Answer: c Explanation: We can also write JOIN instead of INNER JOIN. JOIN is the same as INNER JOIN.

32) Which set operation is used to return the common row from relation?

  1. Union
  2. Union all
  3. Intersect
  4. Minus
Answer: c Explanation: The intersect operation combines two SELECT statements and returns the common rows from both the SELECT statement.

33) Which set operation should be used with the SELECT statements to eliminate duplicate rows?

  1. Union
  2. Union all
  3. Intersect
  4. Minus
Answer: a Explanation: The union operation eliminates the duplicate rows from its result set.

34) If we want to retain all duplicates, we must write_______in place of union.

  1. Union all
  2. Intersect
  3. Minus
  4. All of the above
Answer: a Explanation: Union all returns the result without removing duplicate rows.

35) Which of the following makes the transaction permanent in the database?

  1. Commit
  2. Rollback
  3. Flashback
  4. View
Answer: a Explanation: Commit operation is used to save the work permanently in the database.

36) In which state, a transaction executes all its operations successfully?

  1. Active
  2. Committed
  3. Partially committed
  4. Aborted
Answer: b Explanation: A complete transaction is always committed.

37) Which of the following is used to preserve the order of the operation?

  1. Schedule
  2. Atomicity
  3. Active state
  4. None of the above
Answer: a Explanation: A series of operations from one transaction to another transaction is known as a schedule, which is used to preserve the order of the operation in each of the individual transactions.

38) _______will undo all statements up to commit?

  1. Rollback
  2. Commit
  3. Partially committed
  4. Abort
Answer: a Explanation: The rollback operation is used to undo the work done.

39) OLAP stands for

  1. Online analytical processing
  2. Online analysis processing
  3. Online transaction processing
  4. Online aggregate processing
Answer: a Explanation: OLAP is the manipulation of information to support decision making.

40) In which normal form multi-valued attribute, composite attribute and their combination are not allowed?

  1. First Normal Form (1NF)
  2. Second Normal Form (2NF)
  3. Third Normal Form (3NF)
  4. Fourth Normal Form (4NF)
Answer: a Explanation: The first normal form is used to eliminate duplicate values, and an attribute of a table cannot hold multiple values.

41) The characteristics of the table in second normal form (2NF) is:

  1. Eliminates any hidden dependency
  2. Have a composite key
  3. Eliminate the possibility of insertion anomalies
  4. None of the above
Answer: a Explanation: The relation in the second normal form should be in the first normal form and doesn't have any partial dependency.

42) Which of the following statement is false about the third normal form (3NF)?

  1. It is used to remove data duplication.
  2. It contains transitive partial dependency.
  3. It is used to achieve data integrity.
  4. All of the above
Answer: b Explanation: A relation will be in 3NF if it is in 2NF, and there should not be a transitive dependency for non-prime attributes.

43) Which forms are based on the concept of functional dependency?

  1. 1NF
  2. 2NF
  3. 3NF
  4. 4NF
Answer: c Explanation: A relation is in third normal form if it holds at least one of the following conditions for every non-trivial functional dependency X ? Y.
  • X is a super key.
  • Y is a prime attribute, i.e., each element of Y is part of some candidate key.

44) Which of the following is a bottom-up approach to design a database by examining the relationship between attributes?

  1. Functional dependency
  2. Database modeling
  3. Normalization
  4. Decomposition
Answer: c Explanation: Normalisation is the process of removing redundancy and unwanted data.

45) Which of the following header must be included in the java program to establish database connectivity using JDBC?

  1. Import java.sql.*;
  2. Import java.sql.odbc.jdbc.*;
  3. Import java.jdbc.*;
  4. Import java.sql.jdbc.*;
Answer: a Explanation: The Java program must import java.sql.*, which contains the interface definitions for the functionality provided by JDBC.

46) Which of the following is used to access large objects from a database?

  1. setBlob()
  2. getBlob()
  3. getClob()
  4. All of the above
Answer: d Explanation: These are the predefined functions in SQL to access the large object from a database.

47) DriverManager.getConnection(....... , ....... , ........)

Fill in the blanks with valid options.
  1. URL or machine name where the server runs, Password, User ID
  2. Password, URL or machine name where the server runs, User ID
  3. URL or machine name where the server runs, User ID, Password
  4. User ID, Password, URL or machine name where the server runs
Answer: c Explanation: This connection method is used to open the database because a database must be opened first before performing any action.

48) Which of the following statement is used to invoke the function in SQL?

  1. Connection statement
  2. Callable statements
  3. Prepared Statements
  4. All of the above
Answer: b Explanation: This interface is provided by JDBC that allows invocation of SQL stored procedures and functions.

49) Which of the following is a bottom-up approach that combines two or more low-level entities into a high-level entity?

  1. Generalization
  2. Specialization
  3. Aggregation
  4. All of the above
Answer: a Explanation: Specialization is the opposite of generalization that break high-level entities into low-level entities.

50) Which keyword is used to access records in file organization?

  1. Primary key
  2. Foreign key
  3. Candidate key
  4. Super key
Answer: a Explanation: The collection of records is known as a file.

51) ____a condition where two or more transactions are waiting indefinitely for one another to give up locks.

  1. Deadlock
  2. Waiting
  3. Idle
  4. Ready
Answer: a Explanation: When one data item is waiting for another data item in a transaction, then the system is in a deadlock state.

52) Which of the following are the ways of avoiding deadlock?

  1. Deadlock detection
  2. Deadlock avoidance
  3. Deadlock prevention
  4. All of the above
Answer: d

53)Which of the following is the transaction failure?

  1. Logical errors
  2. Boot errors
  3. Read error
  4. All of the above
Answer: a Explanation: Logical errors and syntax errors are the types of transaction failure.

54) Which kind of failure loses its data in head crash or failure during a transfer operation?

  1. Disk failure
  2. System crash
  3. Transaction failure
  4. All of the above
Answer: a Explanation: This kind of error occurs due to the formation of bad sectors, head crashes, and unreachability to the disk.

55) The log is a sequence of _____ recording all the update activities in the database.

  1. Records
  2. Log records
  3. Entries
  4. Redo
Answer: b Explanation: Log records are the most commonly used recovery method.