Chapter-1 Database Fundamentals
1. What is a database transaction?
a. A single SQL query execution
b. A sequence of operations performed as a single logical unit of work
c. A database backup process
d. A system reboot process
✅b. A sequence of operations performed as a single logical unit of work
2. Which property ensures that a transaction is executed completely or not at all?
a. Consistency
b. Durability
c. Atomicity
d. Isolation
✅ c. Atomicity
3. The ACID properties of a transaction stand for:
a. Accuracy, Consistency, Integration, Durability
b. Atomicity, Compilation, Isolation, Durability
c. Atomicity, Consistency, Isolation, Durability
d. Accuracy, Compression, Integrity, Dependency
✅c. Atomicity, Consistency, Isolation, Durability
4. What does the Isolation property of a transaction ensure?
a. Transactions execute without interference from other transactions
b. Data remains accurate after transaction completion
c. A transaction is never lost
d. Transactions are committed immediately
✅a. Transactions execute without interference from other transactions
5. A transaction that is successfully completed and permanently saved in the database is called:
a. Rolled back
b. Aborted
c. Committed
d. Canceled
✅c. Committed
6. hat happens if a transaction fails before completion?
a. It is committed
b. It is saved partially
c. It is rolled back
d. It continues execution
✅c. It is rolled back
7. The SQL command used to save all changes made by a transaction is:
a. ROLLBACK
b. COMMIT
c. DELETE
d. TRUNCATE
✅b. COMMIT
8. The SQL command used to undo changes made by a transaction is:
a. SAVEPOINT
b. DELETE
c. ROLLBACK
d. COMMIT
✅c. ROLLBACK
9. Which SQL statement is used to define a savepoint within a transaction?
a. SAVE TRANSACTION
b. SAVEPOINT
c. MARK TRANSACTION
d. CREATE SAVE
✅b. SAVEPOINT
10. Which of the following is NOT a valid state of a transaction?
a. Active
b. Failed
c. Locked
d. Partially Committed
✅c. Locked
11. A transaction that violates database constraints is said to fail which ACID property?
a. Atomicity
b. Consistency
c. Isolation
d. Durability
✅b. Consistency
12. Which of the following is an advantage of using transactions in databases?
a. Ensures data integrity
b. Speeds up query execution
c. Reduces data storage usage
d. Removes duplicate records
✅a. Ensures data integrity
13. In case of a system crash, which ACID property ensures that committed transactions remain in the database?
a. Atomicity
b. Consistency
c. Isolation
d. Durability
✅d. Durability
14. What does TCL stand for in SQL?
a. Transaction Control Language
b. Table Control Language
c. Transaction Command Language
d. Table Configuration Language
✅a. Transaction Control Language
15. Which of the following commands is NOT a part of TCL?
a. COMMIT
b. ROLLBACK
c. SAVEPOINT
d. DELETE
✅d. DELETE
16. The COMMIT command is used to:
a. Undo changes made by a transaction
b. Save all changes made by a transaction permanently
c. Save data temporarily
d. Remove data from a table
✅b. Save all changes made by a transaction permanently
17. Which SQL command is used to undo changes made by a transaction before committing?
a. DELETE
b. ROLLBACK
c. UPDATE
d. DROP
✅b. ROLLBACK
18. What is the purpose of the SAVEPOINT command?
a. To define a point in a transaction to which you can later roll back
b. To save all changes permanently
c. To delete a specific row
d. To recover deleted tables
✅a. To define a point in a transaction to which you can later roll back
19. If a transaction is not explicitly committed, when does it get committed automatically?
a. After every SELECT statement
b. At the end of each SQL session
c. When the system crashes
d. When a DDL command (like CREATE or DROP) is executed
✅d. When a DDL command (like CREATE or DROP) is executed
20. What happens if a transaction is rolled back?
a. All changes made by the transaction are saved
b. Only part of the transaction is executed
c. The transaction is ignored, and all changes are undone
d. The database shuts down
✅c. The transaction is ignored, and all changes are undone
21. Which TCL command is used to create a temporary save point within a transaction?
a. SAVEPOINT
b. COMMIT
c. ROLLBACK
d. TRUNCATE
✅a. SAVEPOINT
22. What is the default transaction mode in most relational database systems?
a. Manual commit mode
b. Deferred mode
c. Savepoint mode
d. Auto-commit mode
✅d. Auto-commit mode
23. What happens if you execute ROLLBACK TO SAVEPOINT save1?
a. The transaction will be committed
b. The transaction will be completely rolled back
c. The transaction will be rolled back only to the savepoint named "save1"
d. The database will shut down
✅c. The transaction will be rolled back only to the savepoint named "save1"
24. Which command is used to remove a specific savepoint without affecting other savepoints?
a. DELETE SAVEPOINT
b. RELEASE SAVEPOINT
c. REMOVE SAVEPOINT
d. DROP SAVEPOINT
✅b. RELEASE SAVEPOINT
25. What is the effect of issuing a ROLLBACK command in auto-commit mode?
a. It has no effect because changes are already committed
b. It rolls back all committed transactions
c. It deletes all tables
d. It creates a savepoint
✅a. It has no effect because changes are already committed
26. When should the SAVEPOINT command be used?
a. To delete a table
b. To permanently store data
c. To execute a SELECT query
d. To create a point to which a transaction can be partially rolled back
✅d. To create a point to which a transaction can be partially rolled back
27. What are aggregate functions in SQL?
a. Functions that return multiple rows
b. Functions that perform a calculation on a set of values and return a single value
c. Functions that modify the database structure
d. Functions that work only on numeric data
✅b. Functions that perform a calculation on a set of values and return a single value
28. Which of the following is NOT an aggregate function in SQL?
a. COUNT
b. SUM
c. AVG
d. WHERE
✅d. WHERE
29. The COUNT() function is used to:
a. Calculate the total sum of values
b. Count the number of rows in a result set
c. Find the average of values
d. Return the highest value in a column
✅b. Count the number of rows in a result set
30. Which aggregate function returns the highest value in a column?
a. MAX()
b. MIN()
c. SUM()
d. COUNT()
✅a. MAX()
31. Which aggregate function is used to find the lowest value in a column?
a. AVG()
b. MIN()
c. MAX()
d. SUM()
✅b. MIN()
32. The SUM() function in SQL is used to:
a. Count the number of rows
b. Add up all numeric values in a column
c. Find the highest value
d. Return the first value of a column
✅b. Add up all numeric values in a column
33. What does the AVG() function do?
a. Returns the average (mean) of a numeric column
b. Finds the median of a dataset
c. Counts the number of values
d. Returns the first value in a dataset
✅a. Returns the average (mean) of a numeric column
34. Which SQL clause is commonly used with aggregate functions to group results?
a. ORDER BY
b. GROUP BY
c. HAVING
d. DISTINCT
✅b. GROUP BY
35. What is the difference between COUNT(*) and COUNT(column_name)?
a. COUNT() counts all rows, while COUNT(column_name) counts only non-null values
b. They both count the same values
c. COUNT(column_name) counts duplicate values
d. COUNT() works only on primary key columns
✅a. COUNT() counts all rows, while COUNT(column_name) counts only non-null values
36. Which aggregate function ignores NULL values when performing calculations?
a. COUNT()
b. SUM()
c. AVG()
d. All of the above
✅d. All of the above
37. Which clause is used to filter groups after using GROUP BY?
a. WHERE
b. HAVING
c. ORDER BY
d. DISTINCT
✅b. HAVING
38. What is the output of SELECT SUM(NULL)?
a. NULL
b. 0
c. 1
d. An error
✅a. NULL
39. If a column contains only NULL values, what will COUNT(column_name) return?
a. NULL
b. 0
c. The total number of rows
d. An error
✅b. 0
40. Which SQL clause is used to combine the results of two SELECT statements?
a. JOIN
b. UNION
c. INTERSECT
d. MERGE
✅b. UNION
41. What is a Cartesian product in SQL?
a. The total number of rows in both tables
b. The sum of two tables' columns
c. The combination of every row from the first table with every row from the second table
d. A division operation between two tables
✅c. The combination of every row from the first table with every row from the second table
42. What is the primary difference between JOIN and UNION?
a. JOIN is faster than UNION
b. JOIN removes duplicate rows, while UNION does not
c. UNION works only on primary keys, while JOIN does not
d. JOIN combines columns from different tables, while UNION combines rows
✅d. JOIN combines columns from different tables, while UNION combines rows
43. What does an equi-join do?
a. Matches rows based on a range of values
b. Returns only distinct rows
c. Uses the = operator to match columns from two tables
d. Uses a subquery to filter results
✅c. Uses the = operator to match columns from two tables
44. If Table A has 5 rows and Table B has 4 rows, how many rows will the Cartesian Product (A × B) produce?
a. 4
b. 5
c. 9
d. 20
d. 20
45. How can you prevent an unintended Cartesian Product in SQL?
a. Always use SELECT DISTINCT
b. Use the ORDER BY clause
c. Use a WHERE clause
d. Avoid using GROUP BY
✅c. Use a WHERE clause
46. Which clause is used to filter results after grouping?
a. WHERE
b. ORDER BY
c. HAVING
d. GROUP
✅c. HAVING
47. Which function would you use to count distinct values?
a. COUNT(*)
b. COUNT(DISTINCT column_name)
c. COUNT(ALL)
d. SUM(DISTINCT column_name)
✅b. COUNT(DISTINCT column_name)
48. What is an EQUI JOIN?
a. A join using the equality operator (=)
b. A self join
c. A natural join
d. A full join
✅a. A join using the equality operator (=)
49. What does the UNION operator do?
a. Updates data in tables
b. Returns only matched rows
c. Performs a cross join
d. Returns rows from two queries without duplicates
✅d. Returns rows from two queries without duplicates
50. What is required for a UNION to work correctly?
a. Tables must have primary keys
b. Queries must return the same number and type of columns
c. Only one query can have a WHERE clause
d. Tables must be joined first
✅b. Queries must return the same number and type of columns
Chapter-2 Introduction to MySQL
1. Which of the following is true about NULL in SQL?
a. NULL means zero
b. NULL means an empty string
c. NULL represents an unknown or missing value
d. NULL is equivalent to 0
✅c. NULL represents an unknown or missing value
2. Which SQL keyword is used to check for NULL values?
a. = NULL
b. IS NULL
c. EQUALS NULL
d. LIKE NULL
✅b. IS NULL
3. Which of the following SQL statements correctly returns rows where the column age is NOT NULL?
a. SELECT * FROM employees WHERE age = NOT NULL;
b. SELECT * FROM employees WHERE age IS NOT NULL;
c. SELECT * FROM employees WHERE age != NULL;
d. SELECT * FROM employees WHERE age <> NULL;
✅b. SELECT * FROM employees WHERE age IS NOT NULL;
4. What is the purpose of the ORDER BY clause in SQL?
a. To filter records
b. To group records
c. To join tables
d. To sort the result set
✅d. To sort the result set
5. Which keyword is used to sort the result in descending order?
a. DOWN
b. DESC
c. DECREASE
d. LOWER
✅b. DESC
6. What is the default sorting order of the ORDER BY clause?
a. Descending
b. Random
c. No sorting
d. Ascending
✅d. Ascending
7. What is a column alias in SQL used for?
a. To rename a column temporarily in the result set
b. To permanently rename a column in a table
c. To create a new column
d. To change data type of a column
✅a. To rename a column temporarily in the result set
8. Which keyword is used to assign an alias to a column in SQL?
a. CHANGE
b. RENAME
c. ALIAS
d. AS
✅d. AS
9. Which of the following is a benefit of using column aliases?
a. Improves query speed
b. Makes result sets more readable
c. Allows for data compression
d. Automatically indexes the column
✅b. Makes result sets more readable
10. Which function returns the length of a string in MySQL?
a. LEN()
b. LENGTH()
c. STRLEN()
d. SIZE()
✅b. LENGTH()
11. What does the CONCAT() function do in MySQL?
a. Removes spaces from a string
b. Replaces characters in a string
c. Finds a substring
d. Joins two or more strings together
✅d. Joins two or more strings together
12. Which function is used to convert a string to lowercase?
a. LOW()
b. LCASE()
c. TOLOWER()
d. LOWERCASE()
✅b. LCASE()
13. Which function removes leading and trailing spaces from a string?
a. TRIM()
b. REMOVE()
c. CUT()
d. CLEAN()
✅a. TRIM()
14. What is the result of the following?
SELECT REVERSE('SQL');
a. SQL
b. SLQ
c. LQS
d. QLS
✅c. LQS
15. What does the UPPER() function do in MySQL?
a. Capitalizes only the first letter
b. Converts string to uppercase
c. Removes lowercase letters
d. Makes all text bold
✅b. Converts string to uppercase
16. What will this query return?
SELECT LEFT('database', 4);
a. 'data'
b. 'base'
c. 'tab'
d. 'atab'
✅a. 'data'
17. Which MySQL function returns the position of the first occurrence of a substring within a string?
a. POSITION()
b. INSTR()
c. SEARCH()
d. FIND()
✅b. INSTR()
18. What does the CONCAT_WS() function do in MySQL?
a. Reverses the string with a separator
b. Converts string to uppercase with a separator
c. Joins strings without a separator
d. Concatenates strings with a separator
✅d. Concatenates strings with a separator
19. Which MySQL function extracts a substring starting from a given position?
a. EXTRACT()
b. LEFT()
c. SUBSTRING()
d. RIGHT()
✅c. SUBSTRING()
20. Which function is used to round a number to the nearest integer in MySQL?
a. FLOOR()
b. ROUND()
c. TRUNCATE()
d. CEIL()
✅b. ROUND()
21. What is the result of the expression POW(3, 2) in MySQL?
a. 5
b. 6
c. 9
d. 8
✅c. 9
22. Which function in MySQL returns the current date?
a. NOW()
b. CURDATE()
c. DATE()
d. CURRENT_DATE()
✅b. CURDATE()
23. Which MySQL function returns the day of the week for a given date (1 = Sunday, 7 = Saturday)?
a. DAYOFWEEK()
b. WEEKDAY()
c. DAYOFMONTH()
d. DAYOFYEAR()
✅a. DAYOFWEEK(
24. Which function in MySQL returns the current date and time?
a. DATE()
b. TIME()
c. NOW()
d. CURRENT_TIMESTAMP()
✅c. NOW()
25. Which function in MySQL extracts the year part from a date?
a. YEAR()
b. MONTH()
c. DATE_PART()
d. EXTRACT_YEAR()
✅a. YEAR()
26. Which function in MySQL extracts the month part from a date?
a. EXTRACT_MONTH()
b. MONTH()
c. MONTHOFYEAR()
d. DATE_PART()
✅b. MONTH()
27. Which function in MySQL returns the current time?
a. CURRENT_TIME()
b. NOW()
c. CURTIME()
d. TIME()
✅c. CURTIME()
28. Which function is used to extract the day part from a date in MySQL?
a. EXTRACT(DAY)
b. DAYOFMONTH()
c. DAY()
d. DATE_PART()
✅c. DAY()
29. Which of the following commands is used to rename a table?
a. ALTER TABLE old_table_name RENAME TO new_table_name;
b. RENAME TABLE old_table_name TO new_table_name;
c. ALTER TABLE old_table_name CHANGE TO new_table_name;
d. ALTER TABLE old_table_name MODIFY TABLE TO new_table_name;
✅RENAME TABLE old_table_name TO new_table_name;
30. How do you remove a column from a table using ALTER TABLE?
a. ALTER TABLE table_name DROP COLUMN column_name;
b. ALTER TABLE table_name REMOVE COLUMN column_name;
c. ALTER TABLE table_name DELETE COLUMN column_name;
d. ALTER TABLE table_name DROP column_name;
✅a. ALTER TABLE table_name DROP COLUMN column_name;
