DBMS Online Test - MCQ Questions

Welcome to the DBMS Online Test! We will present 25 MCQs (Multiple-Choice Questions) to test your knowledge of the DBMS concepts and commands.

You can select the correct answer for each question and submit the test. You will get your online test score after finishing the complete test.

1. Which SQL command is used to remove a table and all its data from a database?

a) DELETE TABLE employees;

b) REMOVE TABLE employees;

c) DROP TABLE employees;

d) EXCLUDE TABLE employees;

2. What does the following SQL query return?

SELECT COUNT(DISTINCT country) FROM customers;

a) The total number of customers

b) The total number of unique customer names

c) The total number of transactions

d) The total number of unique countries from the customers table

3. Which clause is used in SQL to sort the result set of a query by one or more columns?

a) GROUP BY

b) SORT BY

c) ORDER BY

d) ARRANGE BY

4. What is the purpose of the FOREIGN KEY in a relational database?

ALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

a) To link tables together and enforce referential integrity

b) To provide unique identification for each row in a table

c) To speed up the queries involving these columns

d) To encrypt data

5. What is a transaction in DBMS?

START TRANSACTION;

a) A single SQL command

b) A set of SQL commands that execute as a unit

c) A method to create new databases

d) A type of SQL function

6. What does the SQL HAVING clause do?

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;

a) Filters records before the data is grouped

b) Filters groups created by the GROUP BY clause

c) Sorts the result set

d) Deletes groups that do not match the HAVING condition

7. What is normalization in database design?

a) A process of integrating data from multiple sources

b) A technique to structure data to reduce data redundancy and improve data integrity

c) A method of backing up databases regularly

d) A form of database index creation

8. Which of the following SQL statements is used to modify data in an existing table?

a) MODIFY TABLE

b) CHANGE DATA

c) UPDATE

d) ALTER TABLE

9. What is the role of the LIKE operator in SQL queries?

SELECT * FROM customers WHERE name LIKE 'A%';

a) Matches exact characters anywhere in the text

b) Searches for patterns in columns

c) Compares different tables

d) Orders the result set alphabetically

10. How do you ensure data integrity in a database system?

a) By implementing primary keys and foreign keys

b) By frequently backing up the database

c) By restricting database access

d) By using the UPDATE statement regularly

11. What is the purpose of the SQL INNER JOIN clause?

SELECT employees.name, departments.name FROM employees INNER JOIN departments ON employees.dept_id = departments.id;

a) To return rows that have matching values in both tables

b) To return all rows from both tables, regardless of match

c) To delete rows in the first table that don't exist in the second table

d) To update data based on values matched from another table

12. What is the difference between WHERE and HAVING clauses?

a) WHERE filters before data aggregation, HAVING filters after aggregation

b) WHERE is used only in the SELECT statement, HAVING is used in the UPDATE statement

c) WHERE and HAVING can be used interchangeably

d) WHERE is used to sort the result set, HAVING is used to group the result set

13. What is an index in DBMS?

CREATE INDEX idx_name ON students(name);

a) A constraint that ensures uniqueness of column data

b) A performance optimization tool that helps speed up data retrieval

c) A command to update data in a table

d) A data structure to organize tables in the database

14. How is a view different from a table in a database?

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

a) A view is a physical table that exists in the database

b) A view is the same as a table but with restricted access

c) A view is a virtual table based on the result set of an SQL statement

d) A view is a type of database index

15. What does the following SQL command do?

GRANT SELECT ON employees TO user1;

a) It restricts user1 from selecting data from the employees table

b) It allows user1 to select data from the employees table

c) It deletes the SELECT privilege from user1 on the employees table

d) It updates the SELECT privilege settings for the employees table

16. What is a schema in a DBMS?

a) A schema is the language used to write database queries

b) A schema is a collection of database objects associated with a database user

c) A schema is a tool to connect to the database

d) A schema is the interface between the database and the application

17. How do you create a backup of a database in SQL?

BACKUP DATABASE testdb TO DISK = 'C:\\backups\\testdb.bak';

a) The given query

b) SAVE DATABASE testdb TO DISK = 'C:\\backups\\testdb.bak';

c) COPY DATABASE testdb TO DISK = 'C:\\backups\\testdb.bak';

d) ARCHIVE DATABASE testdb TO DISK = 'C:\\backups\\testdb.bak';

18. What is the effect of a ROLLBACK statement in a database transaction?

ROLLBACK;

a) It saves all changes made in the current transaction

b) It undoes all changes made in the current transaction

c) It shows the changes made in the current transaction

d) It deletes the transaction log

19. Which SQL statement is used to change the structure of an existing table, such as adding a column?

ALTER TABLE students ADD COLUMN birth_date DATE;

a) The given query

b) MODIFY TABLE students ADD COLUMN birth_date DATE;

c) UPDATE TABLE students ADD COLUMN birth_date DATE;

d) CHANGE TABLE students ADD COLUMN birth_date DATE;

20. What does the UNION operator do in SQL?

SELECT name FROM students UNION SELECT name FROM teachers;

a) It merges the results of two queries into a single result set, including duplicate rows

b) It merges the results of two queries into a single result set, excluding duplicate rows

c) It selects only the matching entries between two tables

d) It creates a new table that combines the columns from two queries

21. What is the purpose of the GROUP BY statement in SQL?

SELECT department, COUNT(*) FROM employees GROUP BY department;

a) It groups the result set by one or more columns and returns the unique values for those columns

b) It sorts the entire result set by one or more columns

c) It deletes groups of data based on column values

d) It updates groups of data based on column values

22. How do you remove a column from a database table?

ALTER TABLE employees DROP COLUMN email;

a) The given query

b) DELETE COLUMN email FROM employees;

c) REMOVE COLUMN email FROM employees;

d) DROP COLUMN email FROM employees;

23. Which SQL clause is used to filter the results of a query based on a condition?

SELECT * FROM employees WHERE salary > 50000;

a) WHERE

b) FILTER

c) IF

d) CONDITION

24. What does the REVOKE command do in SQL?

REVOKE SELECT ON employees FROM user1;

a) Grants permissions to a user

b) Removes specific access rights from a user

c) Lists the permissions granted to a user

d) Denies all permissions to a user

25. What is the main advantage of using SQL Views?

CREATE VIEW active_employees AS SELECT * FROM employees WHERE status = 'Active';

a) They provide an additional layer of security by restricting direct access to the underlying data

b) They are used to back up data

c) They increase the physical storage space on the server

d) They can replace tables when tables are not necessary