SQL Joins Quiz: Test Your Knowledge with SQL Join Queries

14 Questions

What is the purpose of the INNER JOIN clause in SQL?

To combine all rows from both tables where the condition is satisfied, i.e., the values of the common field are the same.

What is the purpose of the LEFT JOIN clause in SQL?

To return all rows from the left table and the matching rows from the right table.

What is the purpose of the RIGHT JOIN clause in SQL?

To return all rows from the right table and the matching rows from the left table.

Which SQL function is used to calculate the average value of a column in a table?

AVG()

Which SQL function is used to count the number of rows in a table that match a specific condition?

COUNT()

What does an SQL INNER JOIN return?

All records with matching values in both tables

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

Left join

In SQL, what does a RIGHT JOIN return?

All records from the right table and matched records from the left

When using a FULL JOIN in SQL, what does it return?

All records from both tables regardless of match

Which SQL join type would be suitable if you want to return all rows with matching values in both tables?

INNER JOIN

In an SQL LEFT JOIN, what happens to rows that don't have a match in the right table?

They are included with NULL values for columns of the right table

What is a key benefit of using SQL join statements in database queries?

Reducing data anomalies during record deletion or update

Which SQL join type returns all records when there is a match in either the left or right table?

(FULL) JOIN

What does a CROSS JOIN in SQL do?

Returns all possible combinations between the two tables

Study Notes

SQL Server

SQL Server is a relational database management system (RDBMS) that allows users to access and manipulate data in a database.

SQL JOINS

SQL join statements allow us to access information from two or more tables at once.
They also keep our database normalized, which helps to keep data redundancy low and decrease the amount of data anomalies in our application when we delete or update a record.

Types of JOINS

INNER JOIN: Returns dataset that have matching values in both tables.
LEFT (OUTER) JOIN: Returns all records from the left table and matched records from the right.
RIGHT (OUTER) JOIN: Returns all records from the right table and the matched records from the left.
FULL (OUTER) JOIN: Returns all records when there is a match in either the left table or right table.

SQL INNER JOINS

The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies.
Syntax: SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
Example: SELECT StudentCourse.COURSE_ID, Student.naw, Student.taman FROM Student INNER JOIN StudentCourse ON Student.ROLL_NO = StudentCourse.ROLL_NO;

SQL LEFT JOINS

This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join.
The rows for which there is no matching row on right side, the result-set will contain null.
Syntax: SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
Example: SELECT StudentCourse.COURSE_ID, Student.naw, Student.taman FROM Student LEFT JOIN StudentCourse ON Student.ROLL_NO = StudentCourse.ROLL_NO;

SQL RIGHT JOINS

This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join.
The rows for which there is no matching row on left side, the result-set will contain null.
Syntax: SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Example: SELECT StudentCourse.COURSE_ID, Student.naw, Student.taman FROM Student RIGHT JOIN StudentCourse ON Student.ROLL_NO = StudentCourse.ROLL_NO;

SQL FULL JOINS

This join creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN.
The result-set will contain all the rows from both the tables.
The rows for which there is no matching, the result-set will contain NULL values.
Syntax: SELECT column_name(s) FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name;
Example: SELECT StudentCourse.COURSE_ID, Student.naw, Student.taman FROM Student FULL JOIN StudentCourse ON Student.ROLL_NO = StudentCourse.ROLL_NO;

SQL CROSS JOINS

This join is a join of tables without specifying the join condition.
In this scenario, the query would return all possible combination of the tables in the SQL query.
Syntax: SELECT column1, column2,….FROM table1 CROSS JOIN table2;
Example: SELECT naw, taman FROM tbl_student CROSS JOIN student_course;

SQL SELECT Statements

SELECT with SUM(): Write SQL statement to Select with SUM().
SELECT TOP syntax: Write SELECT TOP syntax.
Select with HAVING syntax: Write Select with having syntax.

SQL Joins and Select Statements Quiz