20 DBMS & SQL MCQs for NTS, CSS, PMS, KPPSC, PPSC, ETEA, SPSC, BPSC, and FPSC

For any aspiring programmer, data analyst, or database administrator, proficiency in Database Management Systems (DBMS) and Structured Query Language (SQL) is absolutely essential. Competitive exams for these technical roles, such as those conducted by NTS, CSS, PMS, KPPSC, PPSC, ETEA, SPSC, BPSC, and FPSC, place a heavy emphasis on your ability to design, manage, and query databases.

This set of 20 MCQs is designed to test your core knowledge of database concepts, from the ER model and normalization to writing effective SQL queries. These questions reflect the type and difficulty you can expect in your exam, based on an analysis of past papers.

Essential DBMS & SQL MCQs

1. A Database Management System (DBMS) is a:

• Collection of data
• Set of hardware components
• Software for storing and retrieving users' data
• Programming language

Answer: Software for storing and retrieving users' data

Explanation: A DBMS is a software system that enables users to define, create, maintain, and control access to the database. It acts as an interface between the user and the database itself.

2. In the relational model, a row in a table is also known as a:

• Attribute
• Tuple
• Domain
• Relation

Answer: Tuple

Explanation: In the context of the relational database model, a table is called a relation, a column is called an attribute, and a row is called a tuple.

3. Which of the following is NOT a type of database key?

• Primary Key
• Foreign Key
• Tertiary Key
• Super Key

Answer: Tertiary Key

Explanation: Primary, Foreign, Super, and Candidate keys are all standard types of keys used in relational databases to uniquely identify records and establish relationships. "Tertiary Key" is not a recognized term in database theory.

4. The process of organizing columns and tables in a relational database to minimize data redundancy is called:

• Denormalization
• Optimization
• Normalization
• Indexing

Answer: Normalization

Explanation: Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update, and Deletion Anomalies. It involves dividing larger tables into smaller, well-structured tables and defining relationships between them. 

5. Which normal form ensures that there are no partial functional dependencies?

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce-Codd Normal Form (BCNF)

Answer: Second Normal Form (2NF)

Explanation: A relation is in 2NF if it is in 1NF and every non-primary-key attribute is fully functionally dependent on the primary key. This means it eliminates partial dependencies, where an attribute depends on only a part of a composite primary key.

6. Which SQL command is used to extract data from a database?

• GET
• OPEN
• EXTRACT
• SELECT

Answer: SELECT

Explanation: The SELECT statement is the primary command used in SQL to query a database and retrieve data that matches criteria that you specify.

7. Which SQL keyword is used to sort the result-set?

• SORT BY
• ORDER BY
• ARRANGE BY
• SORT

Answer: ORDER BY

Explanation: The ORDER BY keyword is used to sort the records in the result set of a SELECT query. You can sort the records in ascending (ASC) or descending (DESC) order.

8. The 'A' in the ACID properties of a transaction stands for:

• Atomicity
• Availability
• Accuracy
• Aggregation

Answer: Atomicity

Explanation: ACID properties guarantee that database transactions are processed reliably. Atomicity ensures that a transaction is treated as a single, indivisible unit; it either completes fully or does not happen at all.

9. Which type of SQL command is CREATE TABLE?

• DML (Data Manipulation Language)
• DCL (Data Control Language)
• TCL (Transaction Control Language)
• DDL (Data Definition Language)

Answer: DDL (Data Definition Language)

Explanation: DDL commands are used to define the database schemThis includes commands like CREATE, ALTER, and DROP to manage tables and other database objects. DML commands (INSERT, UPDATE, DELETE) are used to manipulate the data itself.

10. A constraint that ensures that a value in one table's column must match a value in another table's column is called:

• Entity Integrity Constraint
• Domain Constraint
• Referential Integrity Constraint
• Key Constraint

Answer: Referential Integrity Constraint

Explanation: The Referential Integrity Constraint is maintained using a foreign key. It ensures that a relationship between two tables remains consistent. For example, you cannot add a record to a related table unless a corresponding record exists in the primary table.

11. What does an ER (Entity-Relationship) model represent?

• The flow of data in a system
• The logical structure of a database
• The physical storage of data
• The programming logic of an application

Answer: The logical structure of a database

Explanation: An ER model is a high-level conceptual data model that describes the structure of a database. It represents entities (tables), their attributes (columns), and the relationships between them, which is essential for database design.

12. Which SQL clause is used to filter records and extract only those that fulfill a specified condition?

• FROM
• HAVING
• WHERE
• GROUP BY

Answer: WHERE

Explanation: The WHERE clause is used with SELECT, UPDATE, and DELETE statements to specify a condition. Only the records that satisfy the condition will be affected by the query.

13. Which SQL aggregate function returns the number of rows in a result set?

• SUM()
• AVG()
• COUNT()
• MAX()

Answer: COUNT()

Explanation: The COUNT() function is an aggregate function that returns the number of rows that match a specified criterion. For example, COUNT(*) returns the total number of records in a table.

14. SQL is a/an:

• Structured language
• Unstructured language
• Object-oriented language
• Procedural language

Answer: Structured language

Explanation: SQL stands for Structured Query Language. It is the standard language for relational database management systems, designed for managing data held in a structured, tabular format.

15. To combine rows from two or more tables based on a related column between them, you would use which SQL command?

• MERGE
• COMBINE
• JOIN
• ATTACH

Answer: JOIN

Explanation: The JOIN clause is used to query data from two or more tables based on a relationship between certain columns in these tables. Common types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

16. Which normal form deals with removing transitive dependencies?

• 1NF
• 2NF
• 3NF
• BCNF

Answer: 3NF

Explanation: A relation is in 3NF if it is in 2NF and there are no transitive dependencies. A transitive dependency is when a non-key attribute depends on another non-key attribute, rather than directly on the primary key.

17. The command to remove a table from the database is:

• REMOVE TABLE table_name
• DELETE TABLE table_name
• ERASE TABLE table_name
• DROP TABLE table_name

Answer: DROP TABLE table_name

Explanation: The DROP TABLE command is a DDL statement that completely removes a table's definition, data, indexes, triggers, constraints, and permission specifications. It is an irreversible action.

18. Which of the following is a valid way to write a comment in SQL?

• // This is a comment
• /* This is a comment */
• -- This is a comment
• Both B and C

Answer: Both B and C

Explanation: Most SQL dialects support two types of comments. Multi-line comments start with /* and end with */. Single-line comments typically start with -- and extend to the end of the line.

19. Which operator is used to search for a specified pattern in a column?

• MATCH
• LIKE
• SEARCH
• FIND

Answer: LIKE

Explanation: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It is used with wildcard characters, such as % (represents zero or more characters) and _ (represents a single character).

20. A database object used to retrieve data from tables is a:

• View
• Index
• Trigger
• Query

Answer: Query

Explanation: A query is a request for data or information from a database. While a view is a virtual table based on a query, the query itself is the direct command used to retrieve the data.