Advanced SQL Queries: Master Complex SQL for Data Analysis

Question 1

Which SQL clause is used to extract unique or distinct values from a table?

Accepted Answer

SELECT DISTINCT

Answer

SELECT UNIQUE

Answer

SELECT ALL

Answer

SELECT NOT NULL

Question 2

Which type of subquery is used to return a single value from a nested query?

Accepted Answer

Scalar subquery

Answer

Correlated subquery

Answer

Exists subquery

Answer

In subquery

Question 3

Which aggregation function is used to calculate the arithmetic mean (average) of values in a numeric column?

Accepted Answer

AVG()

Answer

COUNT()

Answer

SUM()

Answer

MAX()

Question 4

Which set operator is used to combine the results of two queries and return only the rows that are present in both queries?

Accepted Answer

INTERSECT

Answer

UNION

Answer

EXCEPT

Answer

JOIN

Question 5

Which type of join combines rows from two tables based on matching values in a shared column?

Accepted Answer

Inner join

Answer

Outer join

Answer

Cross join

Answer

Natural join

Question 6

Which SQL statement is used to establish or create a database?

Accepted Answer

CREATE DATABASE

Answer

DROP DATABASE

Answer

ALTER DATABASE

Answer

USE DATABASE

Question 7

Which SQL function is used to extract the year component from a date?

Accepted Answer

YEAR()

Answer

MONTH()

Answer

DAY()

Answer

STRFTIME()

Question 8

Which outer join type displays all records from the left table, regardless of whether they have corresponding records in the right table?

Accepted Answer

Left Outer Join

Answer

Inner Join

Answer

Right Outer Join

Answer

Full Outer Join

Question 9

Which SQL statement creates a clustered index on a specific column in a table to optimize query performance?

Accepted Answer

CREATE CLUSTERED INDEX index_name ON table_name (column_name)

Answer

ADD INDEX index_name ON table_name (column_name)

Answer

CREATE UNIQUE INDEX index_name ON table_name (column_name)

Question 10

Which of the following subqueries can be used to calculate the total sales for each product category?

Accepted Answer

SELECT SUM(Sales) FROM Products WHERE Category IN (SELECT Category FROM Products WHERE ProductID = ?)

Answer

SELECT SUM(Sales) FROM Products WHERE Category = (SELECT Category FROM Products WHERE ProductID = ?)

Answer

SELECT SUM(Sales) FROM Products, (SELECT Category FROM Products WHERE ProductID = ?) AS SubQuery WHERE Products.Category = SubQuery.Category

Answer

SELECT SUM(Sales) FROM Products WHERE Category = ANY (SELECT Category FROM Products WHERE ProductID = ?)

Question 11

Which of the following aggregation functions should be used to identify the product with the highest sales?

Accepted Answer

MAX(Sales)

Answer

MIN(Sales)

Answer

COUNT(Sales)

Answer

SUM(Sales)

Question 12

Which of the following set operators can be used to find the products that are sold in both the UK and the US?

Accepted Answer

INTERSECT

Answer

UNION

Answer

EXCEPT

Answer

JOIN

Question 13

Which of the following SQL statements can be used to create a view that displays the product name and total sales for each product?

Accepted Answer

CREATE VIEW ProductSales AS SELECT ProductName, SUM(Sales) FROM Products GROUP BY ProductName

Answer

CREATE VIEW ProductSales AS SELECT * FROM Products GROUP BY ProductName

Answer

CREATE VIEW ProductSales AS SELECT ProductName, Sales FROM Products

Answer

CREATE VIEW ProductSales AS SELECT ProductName, COUNT(Sales) FROM Products GROUP BY ProductName

Question 14

Which of the following join types should be used to find all products that have at least one order?

Accepted Answer

LEFT JOIN

Answer

INNER JOIN

Answer

RIGHT JOIN

Answer

FULL JOIN

Question 15

Which of the following SQL statements can be used to increment the quantity of a product in the database?

Accepted Answer

UPDATE Products SET Quantity = Quantity + 10 WHERE ProductID = ?

Answer

INSERT INTO Products (Quantity) VALUES (10) WHERE ProductID = ?

Answer

DELETE FROM Products WHERE Quantity < 10

Answer

SELECT * FROM Products WHERE Quantity > 10

Question 16

Which of the following data types should be used to store a customer's address in a database?

Accepted Answer

VARCHAR(255)

Answer

INT

Answer

FLOAT

Answer

DATE

Question 17

Which of the following SQL statements can be used to set the ProductID column as the primary key in the Products table?

Accepted Answer

ALTER TABLE Products ADD PRIMARY KEY (ProductID)

Answer

CREATE PRIMARY KEY ON Products (ProductID)

Answer

SET PRIMARY KEY = ProductID ON Products

Answer

DECLARE PRIMARY KEY ProductID ON Products

Question 18

What is the purpose of the GROUP BY clause in SQL?

Accepted Answer

To combine rows with identical values into a single row, optionally applying aggregate functions to generate summary data

Answer

To sort the results of a query

Answer

To filter out duplicate rows

Answer

To create a new table

Question 19

What is the difference between an INNER JOIN and an OUTER JOIN?

Accepted Answer

An INNER JOIN returns only matching rows from both tables, while an OUTER JOIN includes all rows from one table regardless of matching.

Answer

An OUTER JOIN returns only matching rows from both tables, while an INNER JOIN includes all rows from one table regardless of matching.

Answer

Both INNER JOIN and OUTER JOIN return all rows from both tables unconditionally.

Answer

There is no difference between an INNER JOIN and an OUTER JOIN.

Question 20

What is the purpose of the HAVING clause in SQL?

Accepted Answer

To filter the results of a grouped query, applying conditions to aggregate values

Answer

To sort the results of a query

Answer

To combine rows with identical values into a single row

Answer

To create a new table

Question 21

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

Accepted Answer

To establish a relationship between two tables, typically enforcing referential integrity

Answer

To enforce data integrity only

Answer

To improve query performance

Answer

To create a unique identifier for each row

Question 22

Which SQL keyword is used to combine the results of multiple SELECT statements, returning distinct rows?

Accepted Answer

UNION

Answer

INTERSECT

Answer

UNION ALL

Answer

JOIN

Question 23

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

Accepted Answer

To apply a condition to groups of rows, filtering the grouped data.

Answer

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

Answer

To apply a condition to individual rows, filtering the entire dataset.

Answer

To join two or more tables based on a common column.

Question 24

Which SQL function is used to calculate the count of unique values in a specified column?

Accepted Answer

COUNT(DISTINCT column_name)

Answer

AVG(column_name)

Answer

SUM(column_name)

Answer

COUNT(*)

Question 25

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

Accepted Answer

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

Answer

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

Answer

To apply a filter condition to a group of rows.

Answer

To join two or more tables based on a common column.

Question 26

Which SQL keyword is used to specify a condition that filters rows in a query?

Accepted Answer

WHERE

Answer

ORDER BY

Answer

HAVING

Answer

GROUP BY

Question 27

What is the primary purpose of using the GROUP BY clause in an SQL query?

Accepted Answer

To group the results of a query by one or more specified columns, aggregating data based on those groupings.

Answer

To join two or more tables based on a common column.

Answer

To apply a condition to groups of rows, filtering the grouped data.

Question 28

Which SQL keyword is used to sort the results of a query in a specified order?

Accepted Answer

ORDER BY

Answer

GROUP BY

Answer

WHERE

Answer

HAVING

Question 29

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

Accepted Answer

To enhance the performance of queries by providing a fast and efficient way to locate data.

Answer

To store data in a specific order, making it easier to retrieve.

Answer

To secure data from unauthorized access, protecting sensitive information.

Answer

To prevent duplicate data from being entered, ensuring data integrity.

Question 30

Which SQL keyword is used to create a temporary table that exists only for the duration of the current session?

Accepted Answer

CREATE TEMP TABLE

Answer

CREATE INDEX

Answer

CREATE TABLE

Answer

CREATE VIEW

Question 31

What is the main purpose of using a stored procedure in an SQL database?

Accepted Answer

To store and execute frequently used SQL statements, enhancing code reusability and reducing redundancy.

Answer

To delete data from a table in the database, removing existing records.

Answer

To insert data into a table in the database, adding new records.

Answer

To create a new table in the database, defining its structure and constraints.