Advanced SQL Online Test - MCQ Questions

Welcome to the Advanced SQL Online Test - Part 2!. This Quiz continues the SQL Online Test - MCQ Questions. It will present 25 Advanced SQL MCQs (Multiple-Choice Questions) to test your SQL skills.

This online test is structured to test your ability to write and understand SQL queries and deepen your understanding of how SQL can manipulate and retrieve data efficiently in real-world scenarios.

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. What does the following SQL query do?

SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 10;

a) Displays departments and the number of employees, only for departments with more than 10 employees

b) Displays all departments and the number of employees

c) Deletes departments with more than 10 employees

d) Updates the count of employees for departments with more than 10 employees

2. What is the result of using the INTERSECT keyword in SQL queries?

a) Combines rows from two queries and returns only rows that appear in both sets

b) Combines rows from two queries and returns rows that appear in either set

c) Returns all possible combinations of rows from two queries

d) Returns the difference between two sets of rows

3. In SQL, what is a subquery?

a) A query that can delete data from a table

b) A query within another SQL query

c) A secondary table created temporarily

d) A type of join used in databases

4. How would you write a SQL query to find all orders that have not been shipped, assuming you have tables Orders and Shipments where Orders.OrderID = Shipments.OrderID?

SELECT Orders.OrderID FROM Orders
LEFT JOIN Shipments ON Orders.OrderID = Shipments.OrderID
WHERE Shipments.ShipmentDate IS NULL;

a) The given query

b) Using an INNER JOIN instead of a LEFT JOIN

c) Using a RIGHT JOIN instead of a LEFT JOIN

d) All of the above are correct

5. What is a composite key?

a) A primary key made from two or more columns

b) A unique key made automatically by the database

c) A foreign key that links two tables together

d) A key used to encrypt database data

6. Which SQL command is used to add a column to an existing table?

a) UPDATE TABLE

b) MODIFY TABLE

c) ALTER TABLE ADD COLUMN

d) INSERT COLUMN

7. What is the function of the SQL EXCEPT clause?

a) It combines the result sets of two queries and returns distinct rows from the first query that are not output by the second query

b) It deletes records that are the same in two queries

c) It combines the result sets of two queries and returns all rows

d) It updates rows that are different between two result sets

8. In SQL, what is the purpose of the COALESCE function?

a) To concatenate columns

b) To return the first non-NULL value in a list of parameters

c) To count the number of non-NULL values in a column

d) To convert a text value to upper case

9. How do you create an index in SQL?

CREATE INDEX idx_lastname ON Employees(LastName);

a) The given query

b) ALTER TABLE Employees ADD INDEX (LastName);

c) UPDATE Employees SET INDEX (LastName);

d) None of the above

10. What is the SQL clause used for specifying the conditions that must be fulfilled for records to be selected?

a) SELECT

b) WHERE

c) FROM

d) CONDITIONAL

11. What does the SQL ROLLUP function do?

a) It provides a way to create subtotals and grand totals within a result set

b) It deletes data from a table in a hierarchical manner

c) It updates data based on a rolling calculation

d) It locks the database to prevent other users from accessing it while a transaction is being processed

12. What is an SQL trigger?

a) A special type of stored procedure that automatically executes when an event occurs in the database server

b) A command used to start a transaction in the database

c) A tool used to update data in real time

d) A query that automatically updates data every time a condition is met

13. What is the difference between UNION and UNION ALL?

a) UNION only selects distinct values, UNION ALL selects all values including duplicates

b) UNION selects all values, UNION ALL selects only distinct values

c) UNION is used to merge columns, UNION ALL is used to merge rows

d) There is no difference between UNION and UNION ALL

14. How do you write a query to select all records from a table named Products where the price is higher than the average price?

SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);

a) The given query

b) SELECT * FROM Products WHERE Price > AVG(Price);

c) SELECT AVG(Price) FROM Products WHERE Price > AVG(Price);

d) None of the above

15. What does the CROSS JOIN keyword in SQL do?

a) It joins every row of one table with every row of another table

b) It joins only matching rows between two tables

c) It joins two tables based on a related column between them

d) It joins two tables and places the result in a new table

16. Which SQL statement is used to modify existing records in a table?

a) ALTER

b) UPDATE

c) MODIFY

d) CHANGE

17. What is the primary purpose of the SQL CASE statement?

a) To handle exceptions in SQL procedures

b) To facilitate easier syntax for DELETE operations

c) To provide a way to perform conditional logic in SQL queries

d) To compare a set of Boolean expressions

18. How do you retrieve a list of all databases in SQL Server?

a) SELECT * FROM sys.databases;

b) SHOW DATABASES;

c) SELECT databases();

d) LIST DATABASES;

19. What is meant by the term "normalization" in the context of database design?

a) The process of organizing data to reduce redundancy and improve data integrity

b) The process of merging multiple databases into a single database

c) The process of distributing data across different databases to improve performance

d) The process of converting data into a standard format

20. What are transactions in SQL?

a) Processes that return a new value each time they are run

b) Commands that only retrieve data without changing the database

c) A sequence of operations performed as a single logical unit of work

d) Operations that cannot be rolled back once executed

21. What does the OUTER JOIN keyword do in SQL?

a) Returns only the rows that do not match between two tables

b) Returns all rows from both tables, with matched rows from one or both tables

c) Joins only the rows from the primary table

d) Excludes all common rows between two tables

22. What is the function of the GROUP BY clause in SQL?

a) It groups rows that have the same values in specified columns into summary rows

b) It sorts the input data and returns a sorted array

c) It filters the results of a query based on specific conditions

d) It updates groups of records based on a condition

23. What is the use of the HAVING clause in SQL?

a) To specify the search condition for groups created by the GROUP BY clause

b) To limit the number of rows in a result set

c) To specify search conditions for the rows returned by the query

d) To join tables based on the conditions specified

24. What does the SQL REPLACE function do?

a) Changes all occurrences of a specified string with another string within a text field

b) Replaces the value in one table with the value from another table

c) Deletes specified strings from text fields

d) Inserts new strings into text fields

25. How can you prevent SQL injection in SQL queries?

a) By using regular expressions

b) By using the LIKE operator

c) By using parameterized queries or prepared statements

d) By using the REPLACE function