3.5 Equijoins, self-joins, and cross-joins

Question 1

What is the result of the following equijoin query?

SELECT Class.Name, Student.Name
FROM Class
INNER JOIN Student
ON Student.Code = Class.Code;

Answer 1

The INNER JOIN returns rows where Student.Code matches Class.Code.

Question 2

What is the result of the following equijoin query?

SELECT Name, Address
FROM Buyer
LEFT JOIN Property
ON Price < MaxPrice;

Answer 2

Note

Question 3

Which join type is the following query?

Answer 3

Self-join

SELECT A.Name, B.Name
FROM EmployeeManager A
INNER JOIN EmployeeManager B
ON B.ID = A.Manager;

Question 4

What does the following cross-join query result in?

SELECT Model, Gigabytes
FROM IPhone
CROSS JOIN Storage;

Answer 4

CROSS JOIN generates all combinations of IPhone models and storage capacities.

Question 5

What is the result of the following query?

SELECT Student.Name
FROM Student
INNER JOIN Class ON Student.Code = Class.Code
WHERE Student.StudentGrade > $
    (SELECT AVG(StudentGrade) 
    FROM Student WHERE Student.Code = Class.Code);

Answer 5

Avg (3.5+3.3+2.9+3.0)/4 = 3.1

Question 6

What is the primary use of equijoins?

Answer 6

• To combine rows from tables where specified columns equal each other.
• Equijoins are typical for matching columns using the = operator.

Question 7

Which SQL command combines two tables using conditions other than the equality operator?

Answer 7

Non-equijoin.

Non-equijoins utilize comparison operators != , >, <, >= and <=

Question 8

What does it mean when we refer to a ‘Cartesian product’ in SQL?

Answer 8

• The result of a cross-join containing all possible row combinations.
• A Cartesian product pairs each row from the first table with every row from the second.

Question 9

How do we distinguish same-table columns in a self-join?

Answer 9

Aliases act as prefixes to differentiate joined columns of the same table.

Question 10

What is the result when using non-equijoin operators?

Non-equijoins utilize comparison operators != , >, <, >= and <=.

Answer 10

Returns rows where the compare columns meet the specified non-equal condition.

Question 11

Why are aliases necessary in self-joins?

Answer 11

1. When you join a table to itself, the columns from each instance of the table could have the same names, which can lead to ambiguity.
2. Using aliases allows you to create unique identifiers for each instance of the table, making it clear which set of columns belongs to which table instance.

Question 12

Identify the join clause(s) usable in non-equijoin queries.

Non-equijoins utilize comparison operators != , >, <, >= and <=

Answer 12

All JOIN clauses.

Non-equijoin conditions can apply with INNER, LEFT, RIGHT, or FULL JOINs.

Question 13

What does a cross-join do without comparing columns?

Answer 13

Combines all rows from two tables into all possible pairs.

CROSS JOIN results in the Cartesian product of two tables.

Question 14

What is an INNER JOIN?

Answer 14

Combines rows from both tables where the join condition is met.

Question 15

What SQL statement is used for an INNER JOIN?

Answer 15

SELECT <fields> 
FROM TableA A 
INNER JOIN TableB B 
ON A.key = B.key

Question 16

What is a LEFT JOIN?

Answer 16

Returns all rows from the left table and matched rows from the right table.

Question 17

What SQL statement is used for a LEFT JOIN?

Answer 17

SELECT <fields> 
FROM TableA A 
LEFT JOIN TableB B 
ON A.key = B.key

Where B.key is NULL

Question 18

What is a RIGHT JOIN?

Answer 18

Returns all rows from the right table and matched rows from the left table.

Question 19

What SQL statement is used for a RIGHT JOIN?

Answer 19

SELECT <fields> 
FROM TableA A 
RIGHT JOIN TableB B 
ON A.key = B.key

Question 20

What is a LEFT JOIN with NULL condition?

Answer 20

A LEFT JOIN that filters for rows where the right table has no match.

Question 21

What SQL statement is used for a LEFT JOIN with NULL condition?

Answer 21

SELECT <fields>
FROM TableA A 
LEFT JOIN TableB B 
ON A.key = B.key 
WHERE B.key IS NULL

Question 22

What is a RIGHT JOIN with NULL condition?

Answer 22

A RIGHT JOIN that filters for rows where the left table has no match.

Question 23

What SQL statement is used for a RIGHT JOIN with NULL condition?

Answer 23

SELECT <fields> 
FROM TableA A 
RIGHT JOIN TableB B 
ON A.key = B.key 
WHERE A.key IS NULL

Question 24

What is a FULL OUTER JOIN with NULL condition?

Answer 24

Combines results from both tables, including unmatched rows from both sides.

Question 25

What SQL statement is used for a FULL OUTER JOIN with NULL condition?

Answer 25

SELECT <fields>
FROM TableA A 
FULL OUTER JOIN TableB B 
ON A.key = B.key 
WHERE A.key IS NULL 
OR B.key IS NULL.

Question 26

What is a Natural Join?

Answer 26

A join that combines tables based on the same column names and their data types

It returns all attributes of both tables along with unique duplicate columns in the result set.

Question 27

What does an Equi Join do?

Answer 27

It joins tables based on the equality or matching column values in the associated tables

Syntax: SELECT table_name1, table_name2 FROM table_name1, table_name2;

Question 28

What is the syntax for an Inner Join?

Answer 28

SELECT * 
FROM table1 
INNER JOIN table2 
ON table1.column_name = table2.column_name;

It joins tables based on the column name specified in the ON clause explicitly.

Question 29

What does an Inner Join return?

Answer 29

Only those rows that exist in both tables

It returns all the attributes of both tables along with duplicate columns that match the ON clause condition.

Question 30

How does a Natural Join work?

Answer 30

It removes duplicate columns, keeping only one instance of each common column in the result set.

Question 31

How does an Equi Join return rows and handle column values

Answer 31

1. If there are no matching column values between the tables being joined, an equi join will not return any rows.
2. It only returns rows where the condition of equality is met.

Question 32

In an Inner Join, what is required for the rows to be included in the result set?

Answer 32

Rows must exist in both tables

Question 33

The syntax for a Natural Join is

SELECT * FROM table_name1 \_\_\_\_\_\_\_ table_name2;

Answer 33

NATURAL JOIN

Question 34

What is a join in the context of databases?

Answer 34

A join is an operation that combines records from two or more tables based on a related column.

Question 35

An INNER JOIN returns only _

Answer 35

the rows that have matching values in both tables.

Question 36

What type of join returns all records from the left table and the matched records from the right table?

Answer 36

LEFT JOIN

Question 37

A RIGHT JOIN returns all records from the _

Answer 37

Returns all records from right table and the matched records from the left table.

Question 38

Which join type includes all records from both tables and fills in NULLs for unmatched records?

Answer 38

FULL OUTER JOIN

Question 39

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

Answer 39

1. INNER JOIN returns only matched records
2. OUTER JOIN returns all records from one or both tables regardless of matches.

Question 40

CROSS JOIN combines every row of the first table with _

Answer 40

Every row of the first with every row of the second table.

Question 41

What is a self join?

Answer 41

A self join is a join where a table is joined with itself.

Question 42

Which join type would you use to find records in one table that do not exist in another?

Answer 42

LEFT JOIN with a NULL check on the right table.

Question 43

What is the main characteristic of a NATURAL JOIN?

Answer 43

A NATURAL JOIN automatically matches columns with the same names in both tables.

Question 44

A INNER JOIN is used when you want ?

Answer 44

A INNER JOIN is used when you want to combine rows from two tables based on a related column and also eliminates duplicate data.

Question 45

What is the result of a LEFT JOIN if there are no matches found in the right table?

Answer 45

All records from the left table will be returned with NULLs in the columns from the right table.

Question 46

An OUTER JOIN can be further classified into __ joins.

Answer 46

An OUTER JOIN can be further classified into LEFT, RIGHT, and FULL OUTER joins.

Question 47

Which join does not compare values from the joined tables?

Answer 47

CROSS JOIN

It does not use a join condition to compare values between tables.

Question 48

What is the SQL syntax for an INNER JOIN?

Answer 48

SELECT columns
FROM table1 INNER JOIN table2
ON table1.column = table2.column;