DBMS MCQs

Question 1

What is a candidate key in a relational database?

Accepted Answer

Any minimal set of attributes that can uniquely identify a row

Answer

Any foreign key referencing another table

Answer

A key made up of exactly two columns

Answer

A key that allows duplicate values

Question 2

In a database, what does referential integrity ensure?

Accepted Answer

That a foreign key value always matches an existing primary key value in the referenced table

Answer

That every column has a unique value

Answer

That tables are automatically normalized

Answer

That queries run faster

Question 3

Which SQL statement is used to remove a table's structure along with all its data?

Accepted Answer

DROP TABLE

Answer

DELETE TABLE

Answer

TRUNCATE TABLE

Answer

REMOVE TABLE

Question 4

What is the main purpose of an index in a database table?

Accepted Answer

To speed up data retrieval at the cost of some extra storage and slower writes

Answer

To enforce that a column only accepts unique values

Answer

To automatically back up the table

Answer

To convert the table into a normalized form

Question 5

Which isolation level allows a transaction to read data that has been modified but not yet committed by another transaction?

Accepted Answer

Read Uncommitted

Answer

Read Committed

Answer

Repeatable Read

Answer

Serializable

Question 6

What is a deadlock in the context of database transactions?

Accepted Answer

A situation where two or more transactions are waiting on each other to release locks, with none able to proceed

Answer

A situation where a transaction takes too long to complete

Answer

A query that returns no rows

Answer

A table that has no primary key

Question 7

Which type of SQL join returns only the rows that have matching values in both tables?

Accepted Answer

INNER JOIN

Answer

LEFT JOIN

Answer

RIGHT JOIN

Answer

FULL OUTER JOIN

Question 8

What is denormalization typically used for?

Accepted Answer

To improve read performance by intentionally introducing redundancy

Answer

To remove all duplicate rows from a table

Answer

To convert a table into 3NF

Answer

To enforce stricter data integrity rules

Question 9

In the context of database keys, what is a composite key?

Accepted Answer

A primary key made up of two or more columns combined

Answer

A key that references another table

Answer

A key generated automatically by the database

Answer

A key that can contain NULL values

Question 10

What does the term ACID stand for in database transactions?

Accepted Answer

Atomicity, Consistency, Isolation, Durability

Answer

Accuracy, Consistency, Integrity, Dependency

Answer

Atomicity, Concurrency, Isolation, Dependency

Answer

Accuracy, Concurrency, Integrity, Durability

Question 11

What is a view in SQL?

Accepted Answer

A virtual table based on the result of a stored query

Answer

A physical copy of a table

Answer

A type of index

Answer

A backup of the database

Question 12

Which normal form deals specifically with removing transitive dependencies?

Accepted Answer

Third Normal Form (3NF)

Answer

First Normal Form (1NF)

Answer

Second Normal Form (2NF)

Answer

Boyce-Codd Normal Form (BCNF)

Question 13

What is the purpose of a foreign key in a relational database?

Accepted Answer

To link a column in one table to the primary key of another table

Answer

To automatically sort table rows

Answer

To store encrypted data

Answer

To prevent a table from being deleted

Question 14

What is the difference between a clustered and a non-clustered index?

Accepted Answer

A clustered index determines the physical storage order of rows, while a non-clustered index does not

Answer

A clustered index is faster but never used in practice

Answer

A non-clustered index can only be created on the primary key

Answer

There is no real difference between them

Question 15

What does the SQL GROUP BY clause do?

Accepted Answer

Groups rows that share values in specified columns so aggregate functions can be applied to each group

Answer

Sorts the result set in ascending order

Answer

Removes duplicate rows from the result

Answer

Filters rows before any aggregation happens

Question 16

What is a self join used for?

Accepted Answer

Joining a table to itself to compare rows within the same table

Answer

Joining two databases together

Answer

Merging two identical tables into one

Answer

Joining a table with a view

Question 17

Which of the following best describes a primary key?

Accepted Answer

A column or set of columns that uniquely identifies each row and cannot contain NULL values

Answer

Any column that contains only numeric data

Answer

A column used only for sorting results

Answer

A key that can be duplicated across rows

Question 18

What is the purpose of the HAVING clause in SQL?

Accepted Answer

To filter groups of rows after GROUP BY has been applied, often based on aggregate results

Answer

To filter individual rows before any grouping happens

Answer

To sort grouped results alphabetically

Answer

To rename columns in the output

Question 19

What is a stored procedure?

Accepted Answer

A precompiled set of SQL statements saved in the database and executed as a single unit

Answer

A backup copy of a table

Answer

A type of foreign key constraint

Answer

A temporary table created during a query

Question 20

In database design, what is a many-to-many relationship typically implemented with?

Accepted Answer

A junction table containing foreign keys to both related tables

Answer

A single shared primary key

Answer

A composite column storing both values

Answer

A view joining the two tables

Question 21

What is the purpose of the SQL COMMIT statement?

Accepted Answer

It permanently saves all changes made during the current transaction

Answer

It cancels all changes made during the current transaction

Answer

It locks a table so no one else can read it

Answer

It creates a new table in the database

Question 22

What does the term 'atomicity' guarantee in a database transaction?

Accepted Answer

That all operations in a transaction complete fully or none of them do

Answer

That two transactions never run at the same time

Answer

That data is stored in binary format

Answer

That every table has exactly one primary key

Question 23

Which SQL clause is used to arrange the rows of a result set in a specific order?

Accepted Answer

ORDER BY

Answer

GROUP BY

Answer

SORT BY

Answer

ARRANGE BY

Question 24

What is a surrogate key?

Accepted Answer

An artificial key with no business meaning, usually a system generated number, used to identify a row

Answer

A key borrowed temporarily from another table

Answer

A key that is always made of text values

Answer

A key that can hold duplicate values

Question 25

What is the main requirement for a table to be in First Normal Form (1NF)?

Accepted Answer

Each column must hold atomic, indivisible values with no repeating groups

Answer

Every column must be numeric

Answer

The table must not have a primary key

Answer

Every row must be unique across all tables

Question 26

What is the main purpose of an Entity-Relationship (ER) diagram?

Accepted Answer

To visually model the entities, attributes, and relationships in a database before implementation

Answer

To measure the speed of a database server

Answer

To encrypt sensitive columns in a table

Answer

To generate SQL indexes automatically

Question 27

What is data redundancy in a database, and why is it usually a problem?

Accepted Answer

It means the same piece of data is stored in multiple places, which can lead to inconsistencies

Answer

It means a table has too many columns

Answer

It means a query takes too long to execute

Answer

It means a backup was not taken recently

Question 28

What does the SQL INSERT INTO statement do?

Accepted Answer

It adds one or more new rows of data into a table

Answer

It removes rows from a table

Answer

It changes the structure of a table

Answer

It creates a new database

Question 29

What is the role of a transaction log in a DBMS?

Accepted Answer

It records every change made to the database so the system can recover after a crash

Answer

It stores the login credentials of database users

Answer

It lists all the tables present in a schema

Answer

It keeps a copy of the query results for reporting

Question 30

What is the difference between a shared lock and an exclusive lock?

Accepted Answer

A shared lock allows multiple transactions to read the same data, while an exclusive lock allows only one transaction to read or write it

Answer

A shared lock is used only for backups, while an exclusive lock is used for reads

Answer

A shared lock blocks all access, while an exclusive lock allows unlimited access

Answer

There is no real difference between the two

Question 31

What does the SQL ROLLBACK statement do?

Accepted Answer

It undoes all changes made in the current transaction, returning the data to its previous state

Answer

It saves the current transaction permanently

Answer

It deletes a table from the database

Answer

It renames a column in a table

Question 32

What is a cursor used for in SQL programming?

Accepted Answer

It allows a program to process the rows returned by a query one at a time

Answer

It automatically indexes a table

Answer

It permanently deletes duplicate rows

Answer

It converts a table into a view

Question 33

What is the main difference between OLTP and OLAP systems?

Accepted Answer

OLTP handles many short transactions for day to day operations, while OLAP is designed for complex analytical queries over large volumes of historical data

Answer

OLTP is used only for backups, while OLAP is used only for security

Answer

OLTP stores data in files, while OLAP stores data only in memory

Answer

There is no meaningful difference between them

Question 34

What is a database schema?

Accepted Answer

The overall structure or blueprint that defines the tables, columns, and relationships within a database

Answer

A single row of data in a table

Answer

A backup copy of the entire database

Answer

A tool used to encrypt passwords

Question 35

What does the SQL UPDATE statement do?

Accepted Answer

It modifies existing values in one or more rows of a table

Answer

It creates a new row in a table

Answer

It removes a table entirely

Answer

It changes the name of a database

Question 36

What issue does Boyce-Codd Normal Form (BCNF) address that 3NF does not fully cover?

Accepted Answer

Certain anomalies caused by overlapping candidate keys with functional dependencies not covered by 3NF

Answer

Duplicate rows in a table

Answer

Missing primary keys in a table

Answer

Slow query performance on large tables

Question 37

What is the main responsibility of a database administrator (DBA)?

Accepted Answer

Managing database performance, security, backups, and overall availability

Answer

Writing the front end of a web application

Answer

Designing the company logo

Answer

Managing the office network cables

Question 38

What is the difference between a logical schema and a physical schema?

Accepted Answer

A logical schema describes the structure of data independent of storage details, while a physical schema describes how data is actually stored on disk

Answer

They are two names for the exact same thing

Answer

A logical schema is used only for reports, while a physical schema is used only for backups

Answer

A physical schema is created before any data exists, while a logical schema is created after

Question 39

What does 'data independence' mean in the context of a DBMS?

Accepted Answer

The ability to change the storage structure of data without needing to rewrite applications that use it

Answer

The ability to run a database without any internet connection

Answer

The requirement that every table must be independent of every other table

Answer

The rule that data must never be backed up

Question 40

What is a weak entity in an Entity-Relationship model?

Accepted Answer

An entity that cannot be uniquely identified by its own attributes alone and depends on a related strong entity

Answer

An entity that has no attributes at all

Answer

An entity that appears in every table of the database

Answer

An entity that is deleted automatically after use

Question 41

What is the purpose of an aggregate function such as SUM or COUNT in SQL?

Accepted Answer

To perform a calculation across multiple rows and return a single summary value

Answer

To rename a column in the output

Answer

To join two tables together

Answer

To create a new table from scratch

Question 42

What is a materialized view in a database?

Accepted Answer

A view whose result set is physically stored and periodically refreshed, rather than recalculated on every query

Answer

A view that can never be updated

Answer

A temporary table created only for the duration of a single query

Answer

A view that exists only in memory and disappears when the session ends

Question 43

What is the difference between horizontal and vertical partitioning of a table?

Accepted Answer

Horizontal partitioning splits rows across separate tables, while vertical partitioning splits columns across separate tables

Answer

Horizontal partitioning splits columns, while vertical partitioning splits rows

Answer

Both terms refer to the exact same operation

Answer

Partitioning always means deleting old data permanently

Question 44

What is the purpose of a CHECK constraint in SQL?

Accepted Answer

It restricts the values that can be entered into a column based on a specified condition

Answer

It automatically backs up a table every night

Answer

It creates a foreign key relationship between two tables

Answer

It renames a table

Question 45

In database modeling, what does a many-to-one relationship typically mean?

Accepted Answer

Many rows in one table can be associated with a single row in another table

Answer

One row in a table can be associated with many rows in the same table only

Answer

Every row in one table must match exactly one row in every other table

Answer

Two tables can never be related in this way

Question 46

A relation is in 2NF if it is in 1NF and contains no:

Accepted Answer

Partial dependency

Answer

Transitive dependency

Answer

Multivalued dependency

Answer

Join dependency

Question 47

Which ACID property guarantees that all operations within a transaction either succeed completely or are rolled back?

Accepted Answer

Atomicity

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 48

What does the "I" in the ACID properties of database transactions stand for?

Accepted Answer