بِسْمِ اللَّهِ الرَّحْمَنِ الرَّحِيم
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?
Explanation
Correct Answer: Option A
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)?
Explanation
Correct Answer: Option A
'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)?
Explanation
Correct Answer: Option A
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:
Explanation
Correct Answer: Option A
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?
Explanation
Correct Answer: Option A
'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:
Explanation
Correct Answer: Option A
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?
Explanation
Correct Answer: Option A
'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?
Explanation
Correct Answer: Option A
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:
Explanation
Correct Answer: Option A
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?
Explanation
Correct Answer: Option A
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?
Explanation
Correct Answer: Option A
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?
Explanation
Correct Answer: Option A
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:
Explanation
Correct Answer: Option A
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?
Explanation
Correct Answer: Option A
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?
Explanation
Correct Answer: Option A
'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?
Explanation
Correct Answer: Option A
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?
Explanation
Correct Answer: Option A
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:
Explanation
Correct Answer: Option A
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?
Explanation
Correct Answer: Option A
'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"?
Explanation
Correct Answer: Option A
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.
%20for%20NTS,%20CSS,%20PMS,%20KPPSC,%20PPSC,%20ETEA,%20SPSC,%20BPSC%20and%20FPSC%20Exams.jpg)
No comments yet
Be the first to share your thoughts!