Inner Join Basics Quiz

1. What does an INNER JOIN return?

Only rows that match in both tables

All rows from the left table

All rows from both tables

Rows that don't match in either table

An INNER JOIN combines rows from two or more tables based on a related column between them. It returns only the rows where there is a match in both tables, effectively filtering out any records that do not meet this criterion. This ensures that only relevant data from both tables is included in the result.

Explanation

An INNER JOIN combines rows from two or more tables based on a related column between them. It returns only the rows where there is a match in both tables, effectively filtering out any records that do not meet this criterion. This ensures that only relevant data from both tables is included in the result.

2. Which keyword is used to combine rows from two tables based on a common column?

MERGE

JOIN

UNION

COMBINE

JOIN is the SQL keyword used to combine rows from two or more tables based on a related column between them. It allows for the retrieval of data that is interconnected, enabling more complex queries and analyses by linking tables through their common attributes.

Explanation

JOIN is the SQL keyword used to combine rows from two or more tables based on a related column between them. It allows for the retrieval of data that is interconnected, enabling more complex queries and analyses by linking tables through their common attributes.

3. In the query SELECT * FROM students INNER JOIN courses ON students.id = courses.student_id, what does the ON clause do?

Specifies which rows to display

Defines the join condition between tables

Orders the results

Limits the number of rows returned

The ON clause in the SQL query specifies the condition that links the two tables, students and courses, by matching the student IDs. This ensures that only related records from both tables are combined in the result set, establishing a clear relationship between the data being queried.

Explanation

The ON clause in the SQL query specifies the condition that links the two tables, students and courses, by matching the student IDs. This ensures that only related records from both tables are combined in the result set, establishing a clear relationship between the data being queried.

4. True or False: An INNER JOIN will include rows from the left table that have no match in the right table.

True

False

An INNER JOIN only returns rows where there is a match between the two tables based on the specified join condition. If a row in the left table does not have a corresponding match in the right table, it will be excluded from the result set, making the statement false.

Explanation

An INNER JOIN only returns rows where there is a match between the two tables based on the specified join condition. If a row in the left table does not have a corresponding match in the right table, it will be excluded from the result set, making the statement false.

5. What is a subquery?

A query nested inside another query

A query that joins three tables

A query that sorts results

A query that groups data

A subquery is a query embedded within another SQL query, allowing for more complex data retrieval. It can be used to filter results, calculate values, or provide data for the outer query, making it a powerful tool for handling intricate database operations efficiently.

Explanation

A subquery is a query embedded within another SQL query, allowing for more complex data retrieval. It can be used to filter results, calculate values, or provide data for the outer query, making it a powerful tool for handling intricate database operations efficiently.

6. In a subquery, which query executes first?

The outer query

The inner query (subquery)

Both execute simultaneously

Whichever is shortest

In SQL, when a subquery is used, the inner query (subquery) executes first to provide the necessary data for the outer query. This sequential execution ensures that the outer query can utilize the results from the inner query to perform its operations effectively.

Explanation

In SQL, when a subquery is used, the inner query (subquery) executes first to provide the necessary data for the outer query. This sequential execution ensures that the outer query can utilize the results from the inner query to perform its operations effectively.

7. SELECT name FROM employees WHERE dept_id IN (SELECT id FROM departments WHERE budget > 50000). What type of subquery is this?

A scalar subquery

A correlated subquery

A non-correlated (independent) subquery

A multiple-row subquery with aggregation

This query contains a subquery that retrieves department IDs based on a budget condition, which is executed independently of the outer query. Since the inner subquery does not reference any columns from the outer query, it is classified as a non-correlated (independent) subquery, allowing it to run separately and return multiple results.

Explanation

This query contains a subquery that retrieves department IDs based on a budget condition, which is executed independently of the outer query. Since the inner subquery does not reference any columns from the outer query, it is classified as a non-correlated (independent) subquery, allowing it to run separately and return multiple results.

8. When joining two tables, the column used to match rows is called the ____.

A foreign key is a column in one table that uniquely identifies a row in another table, establishing a relationship between the two. It allows for the linking of data across tables, ensuring referential integrity by enforcing that the value in the foreign key column corresponds to a valid entry in the referenced table.

Explanation

A foreign key is a column in one table that uniquely identifies a row in another table, establishing a relationship between the two. It allows for the linking of data across tables, ensuring referential integrity by enforcing that the value in the foreign key column corresponds to a valid entry in the referenced table.

Submit

9. True or False: A subquery can return multiple rows and columns.

True

False

A subquery can indeed return multiple rows and columns, functioning like a separate query within a larger SQL statement. This allows for complex data retrieval and manipulation, enabling users to extract detailed information from one or more tables based on specific criteria. Subqueries enhance the flexibility and power of SQL queries.

Explanation

A subquery can indeed return multiple rows and columns, functioning like a separate query within a larger SQL statement. This allows for complex data retrieval and manipulation, enabling users to extract detailed information from one or more tables based on specific criteria. Subqueries enhance the flexibility and power of SQL queries.

10. Which statement best describes the relationship in INNER JOIN?

Preserves all unmatched rows from both tables

Returns only matching rows from both tables

Returns all rows from the first table only

Removes duplicate rows automatically

INNER JOIN is a type of SQL join that retrieves records from two tables where there is a match based on a specified condition. It excludes any rows that do not have corresponding matches in both tables, ensuring that only the records that satisfy the join condition are included in the result set.

Explanation

INNER JOIN is a type of SQL join that retrieves records from two tables where there is a match based on a specified condition. It excludes any rows that do not have corresponding matches in both tables, ensuring that only the records that satisfy the join condition are included in the result set.

11. In a subquery within a WHERE clause, what does the subquery typically return?

A single value or list of values to filter rows

The entire result set

Column names only

A sorted list of tables

In a WHERE clause, a subquery is used to refine the main query's results by returning a single value or a list of values. This allows the main query to filter rows based on specific criteria derived from the subquery's output, ensuring only relevant data is selected.

Explanation

In a WHERE clause, a subquery is used to refine the main query's results by returning a single value or a list of values. This allows the main query to filter rows based on specific criteria derived from the subquery's output, ensuring only relevant data is selected.

12. A correlated subquery references columns from the ____.

A correlated subquery is a type of subquery that depends on the outer query for its values. It references columns from the outer query to perform its operations, allowing it to return results that are specific to each row processed by the outer query. This relationship creates a dynamic link between the two queries.

Explanation

A correlated subquery is a type of subquery that depends on the outer query for its values. It references columns from the outer query to perform its operations, allowing it to return results that are specific to each row processed by the outer query. This relationship creates a dynamic link between the two queries.

Submit

13. True or False: INNER JOIN and INNER SUBQUERY produce identical results in all cases.

True

False

14. Which operator is commonly used to compare a value with the results of a subquery that returns multiple rows?

=

IN

>

Submit

Inner Join Basics Quiz

1. What does an INNER JOIN return?

2.

What first name or nickname would you like us to use?

2. Which keyword is used to combine rows from two tables based on a common column?

3. In the query SELECT * FROM students INNER JOIN courses ON students.id = courses.student_id, what does the ON clause do?

4. True or False: An INNER JOIN will include rows from the left table that have no match in the right table.

5. What is a subquery?

6. In a subquery, which query executes first?

7. SELECT name FROM employees WHERE dept_id IN (SELECT id FROM departments WHERE budget > 50000). What type of subquery is this?

8. When joining two tables, the column used to match rows is called the ____.

9. True or False: A subquery can return multiple rows and columns.

10. Which statement best describes the relationship in INNER JOIN?

11. In a subquery within a WHERE clause, what does the subquery typically return?

12. A correlated subquery references columns from the ____.

13. True or False: INNER JOIN and INNER SUBQUERY produce identical results in all cases.

14. Which operator is commonly used to compare a value with the results of a subquery that returns multiple rows?

15. When writing an INNER JOIN, the syntax typically uses the keyword ____.