Advanced SQL Concepts: Master Joins, Subqueries, and Aggregate Functions

Question 1

Which of the following is NOT a type of SQL join?

Accepted Answer

Semi Join

Answer

Inner Join

Answer

Outer Join

Answer

Cross Join

Question 2

Which of the following is a defining characteristic of outer joins in SQL?

Accepted Answer

They allow for the retrieval of all rows from one table and only matching rows from another.

Answer

They return only rows with matching values in both tables.

Answer

They always return all rows from both tables.

Answer

Outer joins are not supported in SQL.

Question 3

To calculate the average value of a numeric column, which aggregate function should you use?

Accepted Answer

AVG()

Answer

SUM()

Answer

COUNT()

Answer

MIN()

Question 4

What is the primary purpose of using a subquery?

Accepted Answer

To embed a query within another query

Answer

To create a new table

Answer

To sort data in a specific order

Answer

To remove duplicate rows

Question 5

Which aggregate function is used to calculate the average value of a column?

Accepted Answer

AVG()

Answer

SUM()

Answer

COUNT()

Answer

MAX()

Question 6

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

Accepted Answer

To improve query performance by providing a faster way to access data

Answer

To ensure that data is always stored in a specific order

Answer

To prevent duplicate rows from being inserted into a table

Answer

To reduce the size of a database

Question 7

What is the primary purpose of a foreign key constraint?

Accepted Answer

To ensure that data in one table is consistent with data in another table

Answer

To prevent duplicate rows from being inserted into a table

Answer

To create a new table

Answer

To delete rows from a table

Question 8

Which of the following is **NOT** an advantage of using a database management system (DBMS)?

Accepted Answer

Data redundancy

Answer

Improved data security

Answer

Enforced data integrity

Answer

Increased efficiency

Question 9

What is the main reason for using subqueries in SQL?

Accepted Answer

To create nested queries that retrieve specific data based on multiple conditions

Answer

To speed up complex queries

Answer

To add data to a table

Question 10

Which of the following is the correct syntax to inner join two tables, table1 and table2, on the column 'id'?

Accepted Answer

INNER JOIN table1 ON table1.id = table2.id

Answer

JOIN table1 ON table1.id = table2.id

Answer

INNER JOIN table1, table2 ON table1.id = table2.id

Answer

JOIN table1, table2 ON table1.id = table2.id

Question 11

To calculate the total sum of values in the 'sales' column of the 'orders' table, which aggregate function should be used?

Accepted Answer

SUM(sales)

Answer

COUNT(sales)

Answer

AVG(sales)

Answer

MIN(sales)

Question 12

What is the primary purpose of creating an index on a database table?

Accepted Answer

To enhance the speed of data retrieval

Answer

To protect data from unauthorized access

Answer

To reduce the physical storage space required

Answer

To maintain the integrity of the data

Question 13

Which of the following is NOT a type of data constraint in SQL?

Accepted Answer

INDEX

Answer

NOT NULL

Answer

UNIQUE

Answer

DEFAULT

Question 14

How do clustered indexes differ from non-clustered indexes?

Accepted Answer

Clustered indexes physically sort the data rows based on the index key, while non-clustered indexes maintain a separate structure for the index

Answer

Non-clustered indexes physically sort the data rows based on the index key, while clustered indexes maintain a separate structure for the index

Answer

Both clustered and non-clustered indexes physically sort the data rows based on the index key

Answer

Both clustered and non-clustered indexes maintain a separate structure for the index

Question 15

Which of the following is NOT a type of database lock?

Accepted Answer

Advisory lock

Answer

Exclusive lock

Answer

Shared lock

Answer

Range lock

Question 16

Which type of join retrieves all rows from the left table and matching rows from the right table, but returns NULL for unmatched rows from the right table?

Accepted Answer

Left join

Answer

Inner join

Answer

Outer join

Answer

Right join

Question 17

Which clause in a subquery selects rows that meet a specified condition and returns those rows to the outer query?

Accepted Answer

WHERE

Answer

SELECT

Answer

FROM

Answer

GROUP BY

Question 18

Which type of data constraint prevents duplicate values from being inserted into a column?

Accepted Answer

Unique

Answer

Primary key

Answer

Foreign key

Answer

Not null

Question 19

Which SQL operator combines the results of two subqueries into a single result set, excluding duplicates?

Accepted Answer

UNION

Answer

EXCEPT

Answer

INTERSECT

Answer

JOIN

Question 20

Which clause in a SQL statement defines the criteria for sorting the result set?

Accepted Answer

ORDER BY

Answer

GROUP BY

Answer

WHERE

Answer

HAVING

Question 21

Which SQL statement retrieves only unique values from a table, eliminating duplicates?

Accepted Answer

SELECT DISTINCT

Answer

SELECT UNIQUE

Answer

SELECT ALL

Answer

SELECT GROUP BY

Question 22

Which clause in a SQL statement filters the result set based on a condition involving a grouped value?

Accepted Answer

HAVING

Answer

WHERE

Answer

GROUP BY

Answer

ORDER BY

Question 23

How do foreign key constraints ensure data integrity and consistency in relational databases?

Accepted Answer

Prevent orphaned records

Answer

Define column data types

Answer

Optimize query speed

Answer

Allow duplicate records

Question 24

Which error type is most likely to result from incorrect SQL syntax?

Accepted Answer

Syntax error

Answer

Semantic error

Answer

Logical error

Answer

Execution error

Question 25

Which type of SQL join combines rows from multiple tables based on equivalent values in multiple columns?

Accepted Answer

Natural Join

Answer

Right Join

Answer

Left Join

Answer

Full Outer Join

Question 26

What is the primary purpose of utilizing a subquery in SQL?

Accepted Answer

To embed a nested query within another query for advanced data retrieval or manipulation.

Answer

To delete duplicate records.

Answer

To create a new table based on existing data.

Answer

To update multiple rows simultaneously.

Question 27

Which SQL aggregate function is used to calculate the average value of a specified numeric column?

Accepted Answer

AVG

Answer

SUM

Answer

MAX

Answer

COUNT

Question 28

Which logical operator in SQL reverses the truth value of a Boolean expression?

Accepted Answer

NOT

Answer

OR

Answer

AND

Question 29

What is the primary function of the GROUP BY clause in SQL?

Accepted Answer

To group rows that share a common value in a specified column, aggregating and summarizing data based on those groups.

Answer

To filter out duplicate rows from a dataset.

Answer

To sort rows in ascending or descending order.

Question 30

Which of the following is NOT a recognized data type in SQL?

Accepted Answer

StringArray

Answer

Boolean

Answer

Float

Answer

Date

Question 31

What is the primary benefit of using an index in SQL?

Accepted Answer

To enhance query performance by expediting data retrieval and reducing processing time.

Answer

To guarantee data integrity by enforcing data constraints.

Answer

To protect data from unauthorized access by implementing security measures.

Question 32

What is the main purpose of utilizing the ORDER BY clause in SQL?

Accepted Answer

To sort the result set of a query in ascending or descending order based on specified columns.

Answer

To combine multiple tables based on common columns.

Answer

To filter rows based on specific criteria.

Answer

To group rows that share common values.

Question 33

What's the main reason for using the 'NOT NULL' constraint in SQL?

Accepted Answer

Prevent empty or null values in a column, ensuring data integrity

Answer

Optimize database performance by reducing null values

Answer

Improve query efficiency by facilitating data indexing

Answer

Reduce database storage space by eliminating null values

Question 34

Which type of join retrieves rows only when there is a match between the left and right tables?

Accepted Answer

INNER JOIN

Answer

FULL OUTER JOIN

Answer

LEFT JOIN

Answer

RIGHT JOIN

Question 35

What is the purpose of using a subquery in an SQL statement?

Accepted Answer

To embed a query within another query and use its results in the outer query

Answer

To improve query performance

Answer

To create a new database object

Answer

To define a stored procedure

Question 36

Which aggregate function calculates the number of distinct values in a specified column?

Accepted Answer

COUNT(DISTINCT column_name)

Answer

MIN(column_name)

Answer

AVG(column_name)

Answer

COUNT(*)

Question 37

How does the HAVING clause differ from the WHERE clause in SQL?

Accepted Answer

The HAVING clause filters groups of rows based on aggregate function results, while the WHERE clause filters individual rows

Answer

The HAVING clause is used with subqueries, while the WHERE clause is used with main queries

Answer

The HAVING clause can only be used with numeric columns, while the WHERE clause can be used with any type of column

Question 38

What is a benefit of using indexes in an SQL database?

Accepted Answer

Faster query execution by reducing the number of disk accesses

Answer

Automatic data validation

Answer

Enhanced data security

Answer

Reduced data redundancy

Question 39

Which SQL statement is used to modify the structure of an existing table by adding, removing, or modifying columns?

Accepted Answer

ALTER TABLE

Answer

CREATE TABLE

Answer

INSERT INTO

Answer

DROP TABLE

Question 40

What is the primary purpose of a FOREIGN KEY in an SQL database?

Accepted Answer

To establish a relationship between two tables by referencing the PRIMARY KEY of another table

Answer

To enforce data integrity by ensuring that all rows in a table have a unique identifier

Question 41

How can you improve the efficiency of an SQL query by optimizing indexing?

Accepted Answer

Create indexes on frequently accessed columns or those used in join conditions

Answer

Increase the size of the database cache

Answer

Minimize the number of subqueries in the query

Question 42

What is the purpose of using a transaction in SQL?

Accepted Answer

To ensure data consistency by grouping multiple SQL statements into a single logical unit

Answer

To improve query performance

Answer

To create a backup of the database

Question 43

Which SQL command is used to combine rows from multiple tables based on shared values in one or more columns?

Accepted Answer

JOIN

Answer

UNION

Answer

INTERSECT

Answer

EXCEPT