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

Here are 20 highly relevant Multiple Choice Questions (MCQs) on Database Management Systems (DBMS) and SQL, tailored for competitive exams like NTS, CSS, PMS, KPPSC, PPSC, ETEA, SPSC, BPSC, and FPSC.

These questions cover core concepts, including database architecture, normalization, SQL commands, and transaction management.

1. Which of the following is NOT an ACID property of a database transaction?
A) Atomicity
B) Consistency
C) Isolation
D) Inconsistency
Answer: D) Inconsistency

Explanation: The ACID properties in DBMS stand for Atomicity, Consistency, Isolation, and Durability. They ensure that database transactions are processed reliably. Inconsistency is the opposite of what ACID properties aim to achieve.

2. Which SQL command is used to modify the structural definition of an existing table (e.g., adding a new column)?
A) UPDATE
B) ALTER
C) MODIFY
D) CHANGE
Answer: B) ALTER

Explanation: 'ALTER TABLE' is a Data Definition Language (DDL) command used to change the structure of a table, such as adding, deleting, or modifying columns. 'UPDATE' is used to change the actual data within the table.

3. What is the primary rule for a table to be in First Normal Form (1NF)?
A) No partial dependencies
B) No transitive dependencies
C) All columns must contain atomic (indivisible) values
D) Every non-prime attribute must depend on the primary key
Answer: C) All columns must contain atomic (indivisible) values

Explanation: 1NF dictates that a table must not contain repeating groups or multi-valued attributes. Every intersection of a row and column should hold a single, atomic value.

4. A key that consists of more than one attribute to uniquely identify rows in a table is called a:
A) Foreign key
B) Composite key
C) Alternate key
D) Super key
Answer: B) Composite key

Explanation: When a single attribute is not enough to uniquely identify a record, two or more attributes are combined to form a primary key, which is known as a composite (or compound) key.

5. Which SQL command removes all rows from a table but leaves the table structure and its columns intact, without logging individual row deletions?
A) DROP
B) DELETE
C) REMOVE
D) TRUNCATE
Answer: D) TRUNCATE

Explanation: 'TRUNCATE' is a DDL command that quickly removes all records from a table by deallocating the data pages. 'DELETE' is a DML command that removes rows one by one and logs each deletion. 'DROP' removes the entire table structure.

6. In an Entity-Relationship (ER) Diagram, an ellipse (oval) represents:
A) An Entity
B) A Relationship
C) An Attribute
D) A Weak Entity
Answer: C) An Attribute

Explanation: In standard Chen notation for ER diagrams, rectangles represent entities, diamonds represent relationships, and ellipses represent attributes of an entity.

7. Which SQL aggregate function is used to return the total number of rows in a table?
A) SUM()
B) TOTAL()
C) COUNT()
D) MAX()
Answer: C) COUNT()

Explanation: 'COUNT()' returns the number of rows that match a specified criterion. 'SUM()' adds up the numerical values in a column.

8. Which type of JOIN returns all rows from the left table, and the matched rows from the right table?
A) INNER JOIN
B) FULL OUTER JOIN
C) RIGHT JOIN
D) LEFT JOIN
Answer: D) LEFT JOIN

Explanation: A 'LEFT JOIN' (or Left Outer Join) ensures that every record from the left-hand table is included in the result set, even if there are no matching records in the right-hand table (in which case, NULLs are returned for the right table's columns).

9. In database concurrency control, a "deadlock" occurs when:
A) A transaction fails due to a syntax error
B) Two or more transactions wait indefinitely for each other to release locks
C) The database server crashes due to power failure
D) A user forgets to commit a transaction
Answer: B) Two or more transactions wait indefinitely for each other to release locks

Explanation: Deadlock is a state where two or more processes are blocked forever, each waiting for a resource that the other process holds.

10. The 'GRANT' and 'REVOKE' commands in SQL belong to which category?
A) DDL (Data Definition Language)
B) DML (Data Manipulation Language)
C) DCL (Data Control Language)
D) TCL (Transaction Control Language)
Answer: C) DCL (Data Control Language)

Explanation: DCL commands are used to manage permissions and access controls to the database. 'GRANT' gives permissions, and 'REVOKE' takes them away.

11. Third Normal Form (3NF) strictly prohibits which of the following?
A) Partial dependency
B) Multi-valued dependency
C) Transitive dependency
D) Overlapping candidate keys
Answer: C) Transitive dependency

Explanation: A table is in 3NF if it is in 2NF and has no transitive dependencies (meaning non-prime attributes must not depend on other non-prime attributes, they must depend ONLY on the primary key).

12. Which SQL constraint ensures that a specific column cannot be left empty during a data insert?
A) UNIQUE
B) NOT NULL
C) CHECK
D) DEFAULT
Answer: B) NOT NULL

Explanation: The 'NOT NULL' constraint enforces a column to always contain a value, meaning you cannot insert a new record or update a record without adding a value to this field.

13. In the ANSI/SPARC 3-schema architecture, the conceptual level represents:
A) The physical storage structures on the disk
B) The individual user views of the database
C) The entire database structure for a community of users
D) The network layout of the database servers
Answer: C) The entire database structure for a community of users

Explanation: The conceptual schema describes the global logical structure of the whole database (entities, data types, relationships, constraints) without worrying about physical storage details.

14. What is a "View" in SQL?
A) A physical table stored permanently on the disk
B) An automated backup process
C) A virtual table based on the result-set of an SQL statement
D) A stored procedure that executes triggers
Answer: C) A virtual table based on the result-set of an SQL statement

Explanation: A view does not store data physically. It is a saved SQL query that acts like a table. The data is fetched dynamically from the underlying base tables whenever the view is queried.

15. Which command is used to permanently save all changes made during the current transaction?
A) ROLLBACK
B) COMMIT
C) SAVEPOINT
D) END
Answer: B) COMMIT

Explanation: 'COMMIT' makes all data modifications performed since the start of the transaction permanent. 'ROLLBACK' undoes those changes.

16. Which SQL operator is used to search for a specified pattern in a column?
A) IN
B) BETWEEN
C) LIKE
D) MATCH
Answer: C) LIKE

Explanation: The 'LIKE' operator is used in a 'WHERE' clause to search for a specified pattern. It is commonly used with wildcards like '%' (represents zero, one, or multiple characters) and '_' (represents a single character).

17. The Cartesian product of two tables in relational algebra is equivalent to which SQL join?
A) Equi Join
B) Natural Join
C) Self Join
D) Cross Join
Answer: D) Cross Join

Explanation: A 'CROSS JOIN' returns the Cartesian product of rows from tables in the join. If table A has 5 rows and table B has 4 rows, a Cross Join will result in 20 rows.

18. B-Trees and B+ Trees are primarily used in databases to implement:
A) Foreign key constraints
B) Database indexes
C) User authentication
D) Stored procedures
Answer: B) Database indexes

Explanation: B-Trees (Balanced Trees) are data structures that keep data sorted and allow searches, sequential access, insertions, and deletions in logarithmic time. They are widely used to build database indexes for fast data retrieval.

19. Which SQL data type is best suited to store variable-length alphanumeric character strings where lengths vary significantly?
A) CHAR
B) VARCHAR
C) BLOB
D) INT
Answer: B) VARCHAR

Explanation: 'VARCHAR' stores variable-length strings, meaning it only uses as much storage as the actual text takes up. 'CHAR' stores fixed-length strings, padding shorter texts with spaces, which wastes storage if lengths vary.

20. What is a database "Trigger"?
A) A hardware interrupt sent to the database server
B) A manually executed script to generate reports
C) A specialized stored procedure that executes automatically when a specific database event occurs
D) A constraint used to enforce referential integrity
Answer: C) A specialized stored procedure that executes automatically when a specific database event occurs

Explanation: Triggers are set to run (or "fire") automatically in response to certain events on a particular table or view, such as 'INSERT', 'UPDATE', or 'DELETE'. They are often used for auditing or enforcing complex business rules.