Advanced SQL Queries: Master Joins, Subqueries, and More

Question 1

Which aggregation function is most suitable for calculating the average salary of employees?

Accepted Answer

AVG()

Answer

MAX()

Answer

COUNT()

Answer

SUM()

Question 2

To insert a new row into the 'orders' table with specific values, which SQL statement should be used?

Accepted Answer

INSERT INTO orders (order_id, customer_name, order_date) VALUES (101, 'New Customer', '2023-03-08')

Answer

INSERT INTO orders VALUES (101, 'New Customer', '2023-03-08')

Answer

orders.insert(101, 'New Customer', '2023-03-08')

Answer

ADD ROW TO orders (order_id, customer_name, order_date) VALUES (101, 'New Customer', '2023-03-08')

Question 3

To delete all rows from the 'orders' table where 'order_id' is less than 10, which SQL statement should be used?

Accepted Answer

DELETE FROM orders WHERE order_id < 10

Answer

DELETE orders WHERE order_id < 10

Answer

REMOVE FROM orders WHERE order_id < 10

Answer

TRUNCATE orders WHERE order_id < 10

Question 4

Which SQL clause allows you to combine rows from multiple tables by matching common columns?

Accepted Answer

JOIN

Answer

SELECT

Answer

WHERE

Answer

GROUP BY

Question 5

Define a subquery. Is it a nested query used within another query?

Accepted Answer

True

Answer

False

Question 6

For grouping rows based on common values and performing calculations on grouped data, which clause is used?

Accepted Answer

GROUP BY

Answer

JOIN

Answer

HAVING

Answer

ORDER BY

Question 7

Which SQL operator is used to combine multiple strings into one?

Accepted Answer

||

Answer

+

Answer

&

Answer

*

Question 8

State whether the following statement is true or false: A common table expression (CTE) is a temporary table utilized within a single SQL statement.

Accepted Answer

True

Answer

False

Question 9

Which SQL clause arranges the query results in a specific order?

Accepted Answer

ORDER BY

Answer

SELECT

Answer

WHERE

Answer

GROUP BY

Question 10

To filter the rows returned by a query based on a specified condition, which clause is employed?

Accepted Answer

WHERE

Answer

SELECT

Answer

GROUP BY

Answer

HAVING

Question 11

Which join type retrieves all rows from both tables, regardless of matching values?

Accepted Answer

FULL JOIN

Answer

INNER JOIN

Answer

LEFT JOIN

Answer

RIGHT JOIN

Question 12

Which SQL statement is used to retrieve data from multiple tables based on relationships between their columns?

Accepted Answer

JOIN table1 ON table2.column1 = table1.column2

Answer

SELECT * FROM table1

Answer

WHERE table1.column1 = table2.column2

Answer

UNION table1, table2

Question 13

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

Accepted Answer

To retrieve data from one table based on conditions defined in a different query.

Answer

To run multiple independent queries simultaneously.

Answer

To create a new table containing data extracted from existing tables.

Answer

To update data in multiple rows of a table with a single statement.

Question 14

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

Accepted Answer

AVG()

Answer

SUM()

Answer

COUNT()

Answer

MAX()

Question 15

What is the correct syntax for using the GROUP BY clause in an SQL statement?

Accepted Answer

GROUP BY column1, column2

Answer

BY column1 GROUP

Answer

GROUP column1 BY column2

Answer

BY GROUP column1, column2

Question 16

Which operator is used to compare two values for equality in SQL?

Accepted Answer

=

Answer

==

Answer

>

Answer

<

Question 17

Describe the key distinction between an INNER JOIN and a LEFT JOIN in SQL.

Accepted Answer

An INNER JOIN returns all matching rows, while a LEFT JOIN returns all rows from the left table regardless of matches.

Answer

A LEFT JOIN returns all matching rows, while an INNER JOIN returns all rows from the left table regardless of matches.

Answer

An INNER JOIN returns all rows from both tables, while a LEFT JOIN only returns matching rows.

Answer

A LEFT JOIN returns all rows from both tables, while an INNER JOIN only returns matching rows.

Question 18

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

Accepted Answer

To optimize data retrieval speed

Answer

To enhance data security

Answer

To enforce referential integrity

Answer

To automatically generate primary keys

Question 19

Which SQL statement is used for creating a new table?

Accepted Answer

CREATE TABLE new_table (column1 data_type, column2 data_type)

Answer

SELECT * INTO new_table FROM table1

Answer

ALTER TABLE table1 ADD COLUMN column3 data_type

Answer

DROP TABLE table1

Question 20

What is the difference between a primary key and a foreign key in a database context?

Accepted Answer

A primary key uniquely identifies each row in a table, while a foreign key references a primary key in another table.

Answer

A foreign key uniquely identifies each row in a table, while a primary key references a foreign key in another table.

Answer

Both primary and foreign keys enforce referential integrity.

Answer

Both primary and foreign keys create indexes.

Question 21

What is the primary function of the COUNT aggregate function in data analysis?

Accepted Answer

Counts the number of rows or non-NULL values in a column or table

Answer

Counts the unique values in a column

Answer

Calculates the average value of a numeric column

Answer

Finds the minimum value in a numeric column

Question 22

Which of the following is an aggregate function that returns the count of distinct values in a column?

Accepted Answer

COUNT(DISTINCT column_name)

Answer

COUNT()

Answer

SUM()

Answer

AVG()

Question 23

When using the GROUP BY clause in SQL, what is the purpose of the HAVING clause?

Accepted Answer

To filter the results of the GROUP BY clause based on a condition.

Answer

To add additional columns to the results of the GROUP BY clause.

Answer

To sort the results of the GROUP BY clause.

Answer

To create a new table with the results of the GROUP BY clause.

Question 24

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

Accepted Answer

To improve the performance of queries by quickly finding data.

Answer

To maintain the integrity of the data by enforcing constraints.

Answer

To reduce the size of the database by compressing the data.

Answer

To provide a mechanism for data encryption.

Question 25

What is the distinction between a schema and a table in a relational database?

Accepted Answer

A schema defines the structure of a database, while a table contains the actual data.

Answer

A table defines the structure of a database, while a schema contains the actual data.

Answer

A schema and a table are essentially the same thing.

Answer

A schema contains multiple tables, while a table contains multiple rows.

Question 26

What is the primary benefit of using a stored procedure in SQL?

Accepted Answer

To group related SQL statements together for reuse.

Answer

To create a new table in the database.

Answer

To update the data in a table.

Answer

To delete the data from a table.

Question 27

In SQL, which JOIN operation retrieves records with matching values in both joined tables?

Accepted Answer

INNER JOIN

Answer

LEFT JOIN

Answer

RIGHT JOIN

Answer

FULL OUTER JOIN

Question 28

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

Accepted Answer

To combine rows with identical values into a single row

Answer

To sort the results of a query

Answer

To filter out duplicate rows

Answer

To create a new column in the result set

Question 29

Which type of subquery is utilized to return a single value, such as a maximum or minimum?

Accepted Answer

Scalar subquery

Answer

Inline subquery

Answer

Correlated subquery

Answer

Exists subquery

Question 30

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

Accepted Answer

To filter grouped data based on aggregate values

Answer

To sort the results of a grouped query

Answer

To create a new column in the result set

Answer

To remove duplicate rows from a grouped query

Question 31

Which SQL function is specifically designed to count the number of unique values in a column?

Accepted Answer

COUNT(DISTINCT)

Answer

COUNT()

Answer

SUM()

Answer

AVG()

Question 32

Explain the key difference between an INNER JOIN and an OUTER JOIN in SQL queries.

Accepted Answer

INNER JOIN retrieves matching rows from both tables, while OUTER JOIN retrieves all rows from one table regardless of matches.

Answer

INNER JOIN retrieves all rows from both tables, while OUTER JOIN retrieves only matching rows.

Answer

INNER JOIN combines rows with identical values, while OUTER JOIN combines rows with different values.

Answer

INNER JOIN creates a new table, while OUTER JOIN modifies existing tables.

Question 33

What is the purpose of the ORDER BY clause in SQL queries?

Accepted Answer

To sort the results of a query

Answer

To group rows with identical values

Answer

To filter out duplicate rows

Answer

To create a new column in the result set

Question 34

Which of the following is a valid SQL data type for storing dates?

Accepted Answer

DATE

Answer

VARCHAR

Answer

INTEGER

Answer

FLOAT

Question 35

What is the intended purpose of utilizing aliases in SQL queries?

Accepted Answer

To assign temporary names to tables or columns

Answer

To create new columns in the result set

Answer

To sort the results of a query

Answer

To filter out duplicate rows

Question 36

Which SQL statement correctly retrieves only the 'CustomerName' and 'TotalOrders' columns from the 'Customers' table, groups the results by 'CustomerName', and displays the grouped data?

Accepted Answer

SELECT CustomerName, TotalOrders FROM Customers GROUP BY CustomerName

Answer

SELECT * FROM Customers GROUP BY CustomerName

Answer

SELECT CustomerName, TotalOrders FROM Customers

Answer

SELECT CustomerName, SUM(TotalOrders) FROM Customers GROUP BY CustomerName

Question 37

Which SQL join type displays all rows from both tables, even if there are no matching values?

Accepted Answer

FULL OUTER JOIN

Answer

INNER JOIN

Answer

LEFT OUTER JOIN

Answer

SELF JOIN

Question 38

Which SQL statement performs a full outer join on the 'Customers' and 'Orders' tables, ensuring all rows from both tables are returned, regardless of matching rows?

Accepted Answer

SELECT * FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Answer

SELECT * FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Answer

SELECT * FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Answer

SELECT * FROM Customers INNER JOIN Orders ON Customers.CustomerID <> Orders.CustomerID

Question 39

Which database performance tuning technique significantly reduces the number of disk accesses required to retrieve data?

Accepted Answer

Indexing

Answer

Normalization

Answer

Denormalization

Answer

Vacuuming

Question 40

Which SQL statement combines rows from multiple tables based on shared column(s)?

Accepted Answer

JOIN

Answer

EXCEPT

Answer

INTERSECT

Answer

UNION

Question 41

What is the primary purpose of a subquery in SQL?

Accepted Answer

To embed a SELECT statement within another SELECT statement.

Answer

To update data in a table.

Answer

To create a new table.

Question 42

Which aggregation function calculates the average value in a set of values?

Accepted Answer

AVG

Answer

MIN

Answer

COUNT

Answer

MAX

Question 43

What is the result of the following SQL statement: SELECT * FROM table_name WHERE column_name IN (1, 2, 3)?

Accepted Answer

Returns all rows where the value of column_name matches 1, 2, or 3.

Answer

Returns all rows where the value of column_name is greater than 1.

Answer

Returns all rows where the value of column_name is less than 3.

Question 44

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

Accepted Answer

To group query results based on specified columns.

Answer

To filter query results.

Answer

To sort query results.

Question 45

Which SQL function converts a string touppercase?

Accepted Answer

UPPER

Answer

LEN

Answer

TRIM

Answer

LOWER

Question 46

How do primary keys differ from foreign keys in SQL?

Accepted Answer

A primary key uniquely identifies rows in a table, while a foreign key references a primary key in another table.

Answer

A primary key can be any data type, while a foreign key must be an integer.

Question 47

The HAVING clause in an SQL query serves what purpose?

Accepted Answer

Filters the results of a GROUP BY clause based on aggregate function evaluations.

Answer

Sorts the output in ascending or descending order.

Answer

Filters individual rows based on specified column values.

Question 48

Which SQL function is used to calculate the mean value of a numeric column?

Accepted Answer

AVG()

Answer

SUM()

Answer

MAX()

Answer

COUNT()

Question 49

A subquery that can be executed independently of the outer query is known as a:

Accepted Answer

Non-correlated subquery

Answer

Correlated subquery

Answer

Scalar subquery

Question 50

What is the primary purpose of a Common Table Expression (CTE) in SQL?

Accepted Answer

To establish a temporary named result set that can be referenced within a single query.

Answer

To permanently store a specific subset of data from one or more tables.

Answer

To create a reusable view accessible to multiple users.

Question 51

Which of the following SQL functions can be utilized for string manipulation?

Accepted Answer

All of the above

Answer

SUBSTR()

Answer

REPLACE()

Answer

UPPER()

Question 52

In the context of database relationships, what does the term 'cardinality' describe?

Accepted Answer

The potential number of rows in one table that can relate to rows in another table.

Answer

The specific data type of a column in a table.

Answer

The uniqueness of values within a particular column.

Question 53

Which type of subquery returns a single value that can be utilized in the outer query?

Accepted Answer

Scalar subquery

Answer

Derived table

Answer

Multiple-row subquery

Answer

Correlated subquery