Advanced SQL Concepts: Master Subqueries, Joins, and More

Question 1

Which join type retrieves all rows from both tables, whether or not they match?

Accepted Answer

FULL JOIN

Answer

INNER JOIN

Answer

LEFT JOIN

Answer

RIGHT JOIN

Question 2

Which aggregation function calculates the average of a column's values?

Accepted Answer

AVG()

Answer

SUM()

Answer

MIN()

Answer

MAX()

Question 3

Which window function calculates the cumulative sum of a column's values over a specified range?

Accepted Answer

SUM(OVER())

Answer

ROW_NUMBER()

Answer

RANK()

Answer

LAG()

Question 4

Which join type retrieves all rows from the left table, and only matching rows from the right table?

Accepted Answer

LEFT JOIN

Answer

INNER JOIN

Answer

RIGHT JOIN

Answer

FULL JOIN

Question 5

Which aggregation function counts the number of non-null values in a column?

Accepted Answer

COUNT()

Answer

AVG()

Answer

MIN()

Answer

MAX()

Question 6

Which window function calculates the difference between the current row and the previous row for a specified column?

Accepted Answer

LAG()

Answer

ROW_NUMBER()

Answer

RANK()

Answer

SUM(OVER())

Question 7

To enhance the efficiency of a query, which optimization technique involves rearranging the sequence of operations within the query?

Accepted Answer

Query Rewriting

Answer

Normalization

Answer

Indexing

Question 8

Which aggregation function computes the average value of a numeric column?

Accepted Answer

AVG()

Answer

COUNT()

Answer

SUM()

Answer

MAX()

Question 9

Which SQL statement correctly uses the 'UNION' operator to combine the results of two queries?

Accepted Answer

SELECT * FROM Table1 UNION ALL SELECT * FROM Table2

Answer

SELECT * FROM Table1 UNION SELECT * FROM Table2

Answer

SELECT * FROM Table1, Table2

Answer

SELECT * FROM Table1 JOIN Table2

Question 10

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

Accepted Answer

To group rows with identical values for the specified column(s)

Answer

To calculate the sum of the values in the specified column(s)

Question 11

Which window function in SQL is used to calculate the cumulative sum of values over a specified range of rows?

Accepted Answer

SUM

Answer

RANK

Question 12

Which of the following is a valid use of the HAVING clause in SQL?

Accepted Answer

To filter the results of an aggregation function

Answer

To group rows with identical values for the specified column(s)

Question 13

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

Accepted Answer

To improve the performance of queries by reducing the number of disk accesses

Answer

To ensure that data is stored in a sorted order

Question 14

What is the primary purpose of a primary key in a database table?

Accepted Answer

To uniquely identify each row in the table

Answer

To enforce referential integrity between tables

Question 15

What is the primary purpose of using a JOIN clause in SQL?

Accepted Answer

To combine rows from multiple tables based on a common column value

Answer

To create a new table from the results of a query

Answer

To update rows in a table

Answer

To delete rows from a table

Question 16

Which of the following aggregation functions is used to return the count of distinct values in a column?

Accepted Answer

COUNT(DISTINCT)

Answer

COUNT()

Answer

SUM()

Answer

AVG()

Question 17

What is the purpose of the OVER clause in a window function in SQL?

Accepted Answer

To specify the range of rows over which the window function should be applied

Answer

To specify the column over which the window function should be applied

Answer

To specify the order in which the rows should be processed

Answer

To specify the type of window function that should be used

Question 18

What is the purpose of using the DISTINCT keyword in a SELECT statement in SQL?

Accepted Answer

To return only unique rows in the result set

Answer

To return all rows in the result set, including duplicates

Answer

To sort the result set in ascending order

Answer

To sort the result set in descending order

Question 19

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

Accepted Answer

ARRAY

Answer

INTEGER

Answer

VARCHAR

Answer

BOOLEAN

Question 20

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

Accepted Answer

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

Answer

To ensure data integrity by enforcing unique constraints

Answer

To store data in a compressed format

Answer

To provide a backup of the data in case of data loss

Question 21

Which subquery type returns a single value?

Accepted Answer

Scalar subquery

Answer

Inline subquery

Answer

Correlated subquery

Answer

Common Table Expression (CTE)

Question 22

Which of these is NOT a standard join type in SQL?

Accepted Answer

Hierarchical Join

Answer

Inner Join

Answer

Outer Join

Answer

Cross Join

Question 23

Which SQL join type merges rows from two tables that share a common column?

Accepted Answer

Inner join

Answer

Outer join

Answer

Cross join

Answer

Self join

Question 24

Which subquery type generates a table with rows satisfying a specific condition?

Accepted Answer

Table Subquery

Answer

Correlated Subquery

Answer

Scalar Subquery

Answer

Exists Subquery

Question 25

Which techniques help ensure data integrity in SQL databases?

Accepted Answer

Using constraints, triggers, and data validation.

Answer

Ignoring data accuracy and validity.

Answer

Relying solely on preventing hardware failures.

Answer

Depending only on database administrators' actions.

Question 26

Which SQL command is used to retrieve data from multiple tables based on a common column, establishing a relationship between them?

Accepted Answer

JOIN

Answer

SELECT

Answer

WHERE

Answer

GROUP BY

Question 27

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

Accepted Answer

To embed a nested query within another query, allowing for more complex data retrieval or manipulation.

Answer

To delete duplicate rows from a table.

Answer

To create a new table based on the results of the subquery.

Answer

To modify existing data in a table.

Question 28

Which aggregation function in SQL is used to count the number of non-null values in a specified column?

Accepted Answer

COUNT

Answer

SUM

Answer

AVG

Answer

MIN

Question 29

What is the primary purpose of using the HAVING clause in SQL?

Accepted Answer

To apply a filter condition to the groups created by a GROUP BY operation, allowing for more precise data filtering based on group-level criteria.

Answer

To filter the results of a WHERE clause based on a specific condition.

Answer

To limit the number of rows returned in a query to a specified number.

Answer

To sort the results of a query in a specified order.

Question 30

Which window function in SQL is used to calculate the running total or cumulative sum of values over a specified range of rows?

Accepted Answer

SUM

Answer

AVG

Answer

COUNT

Question 31

In SQL, what is the correct syntax for creating a correlated subquery, where the subquery references a column from the outer query?

Accepted Answer

(SELECT ... FROM table WHERE table.column = outer_table.column)

Answer

(SELECT ... FROM table WHERE table.column > outer_table.column)

Answer

(SELECT ... FROM table)

Question 32

Which type of join in SQL is used to retrieve all rows from both tables, regardless of whether they have matching rows, providing a complete picture of the data?

Accepted Answer

FULL JOIN

Answer

RIGHT JOIN

Answer

LEFT JOIN

Answer

INNER JOIN

Question 33

What is the primary purpose of using the DISTINCT keyword in SQL?

Accepted Answer

To eliminate duplicate rows from the result set, ensuring that only unique values are returned.

Answer

To sort the result set in ascending order.

Answer

To filter the result set based on a specified condition.

Question 34

Identify the join type that fetches all records from the left table and matching records from the right.

Accepted Answer

LEFT JOIN

Answer

FULL JOIN

Answer

INNER JOIN

Answer

RIGHT JOIN

Question 35

Which aggregation function is employed to calculate the sum of values?

Accepted Answer

SUM()

Answer

AVG()

Answer

COUNT()

Answer

MIN()

Question 36

What is the purpose of a window function?

Accepted Answer

To carry out calculations on a set of rows within a window specified by an OVER() clause

Answer

To categorize rows based on a specified condition

Answer

To exclude rows that fail to meet a specified criteria

Question 37

What is the primary purpose of the HAVING clause in a GROUP BY statement?

Accepted Answer

To filter grouped rows based on aggregate values

Answer

To compute aggregate values for each group

Answer

To group rows based on specific columns

Question 38

What is the primary purpose of utilizing transactions in SQL?

Accepted Answer

To ensure that a sequence of operations is either entirely executed or completely rolled back, preserving data integrity

Answer

To enhance database operations by combining multiple queries into a single transaction

Question 39

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

Accepted Answer

Union join

Answer

Cross join

Answer

Inner join

Answer

Outer join

Question 40

What is the primary purpose of a subquery in SQL?

Accepted Answer

To return a set of rows as a result, which can be used as input to the main query.

Answer

To delete rows from a table.

Answer

To create a new table.

Question 41

Which SQL aggregation function is used to calculate the average of a set of values?

Accepted Answer

AVG()

Answer

COUNT()

Answer

SUM()

Question 42

What is the key difference between a window function and an aggregate function in SQL?

Accepted Answer

Aggregate functions operate on the entire set of rows in a table, while window functions operate on a set of rows defined by a window clause.

Answer

Window functions are more efficient than aggregate functions.

Answer

Aggregate functions are simpler to use than window functions.

Question 43

Which of the following is an invalid window clause in SQL?

Accepted Answer

BETWEEN CURRENT ROW AND 3 PRECEDING ROWS

Answer

ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING

Answer

OVER (PARTITION BY department ORDER BY salary)

Answer

RANGE BETWEEN INTERVAL '1 HOUR' PRECEDING AND CURRENT ROW

Question 44

What is the purpose of using the DISTINCT keyword in a SELECT statement?

Accepted Answer

To return only unique rows in the result set, eliminating duplicates.

Answer

To sort the rows in the result set.

Answer

To limit the number of rows returned in the result set.

Answer

To group the rows in the result set.

Question 45

What is the primary purpose of using the ALTER TABLE statement in SQL?

Accepted Answer

To modify the structure or definition of an existing table.

Answer

To create a new table.

Answer

To insert data into a table.

Answer

To delete a table.

Question 46

What is the primary purpose of creating an INDEX in SQL?

Accepted Answer

To improve the speed of data retrieval from a table by optimizing data access.

Answer

To delete a table.

Answer

To insert data into a table.

Answer

To create a new table.

Question 47

Which SQL statement retrieves the department names and the maximum and minimum number of employees in each department?

Accepted Answer

SELECT department_name, MAX(num_employees) AS max_employees, MIN(num_employees) AS min_employees FROM departments GROUP BY department_name;

Answer

SELECT department_name, SUM(num_employees) AS total_employees FROM departments GROUP BY department_name;

Question 48

Which SQL statement uses a window function to calculate the cumulative sum of sales for each product category over time?

Accepted Answer

SELECT product_category, SUM(sales) OVER (PARTITION BY product_category ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales FROM sales;

Answer

SELECT product_category, SUM(sales) OVER (PARTITION BY product_category ORDER BY date) AS cumulative_sales FROM sales;

Question 49

Which type of join combines rows from two tables based on a common column, returning all rows from both tables?

Accepted Answer

FULL JOIN

Answer

RIGHT JOIN

Answer

LEFT JOIN

Answer

INNER JOIN

Question 50

Which SQL statement retrieves the distinct email addresses of duplicate customers?

Accepted Answer

SELECT DISTINCT email FROM customers GROUP BY email HAVING COUNT(*) > 1;

Answer

SELECT email FROM customers GROUP BY email;

Answer

SELECT email FROM customers HAVING COUNT(*) > 1;

Question 51

Which SQL statement uses a correlated subquery to find employees whose salary is greater than the average salary in their department?

Accepted Answer

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);

Answer

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Question 52

Which of the following is a valid numeric data type in SQL?

Accepted Answer

DECIMAL

Answer

STRING

Answer

BOOLEAN

Answer

DATE

Question 53

Which SQL statement retrieves the top 3 customers with the highest total purchases?

Accepted Answer

SELECT customer_id, customer_name, SUM(purchase_amount) AS total_purchases FROM purchases GROUP BY customer_id ORDER BY total_purchases DESC LIMIT 3;

Answer

SELECT customer_id, customer_name, SUM(purchase_amount) AS total_purchases FROM purchases GROUP BY customer_id ORDER BY customer_name;

Question 54

Which SQL statement creates a table with a foreign key constraint referencing another table?

Accepted Answer

CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT REFERENCES customers(customer_id));

Answer

CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT);