Test Your PostgreSQL INNER JOIN Knowledge with This Quiz

Progress

True / False

An INNER JOIN in PostgreSQL only returns rows when there is at least one match in both tables.

True False

An INNER JOIN returns only the rows that have matching values in both tables.

True / False

The INNER JOIN keyword is optional in a PostgreSQL SELECT statement.

True False

The INNER JOIN keyword is necessary to explicitly specify that the join should only include rows with matching values in both tables.

True / False

You can use multiple INNER JOINs in a single SELECT query in PostgreSQL.

True False

You can join multiple tables using multiple INNER JOIN statements in a single SELECT query.

True / False

When using INNER JOIN in PostgreSQL, column names must be unique across all joined tables.

True False

Column names do not need to be unique, but if there are duplicate column names, you must qualify them with the table name or alias.

True / False

INNER JOIN in PostgreSQL can be used with both ON and USING clauses.

True False

PostgreSQL supports both the ON and USING clauses to specify join conditions in an INNER JOIN.

True / False

In PostgreSQL, INNER JOIN operations can be optimized by using indexes on the join columns.

True False

Indexes on the join columns can significantly improve the performance of INNER JOIN operations by speeding up the lookup of matching rows.

True / False

INNER JOIN in PostgreSQL can be performed on more than two tables simultaneously.

True False

You can join multiple tables together in a single query using multiple INNER JOIN clauses.

True / False

The order of tables in an INNER JOIN clause in PostgreSQL affects the final result set.

True False

The order of tables in an INNER JOIN clause does not affect the final result set; the join condition determines the matches.

True / False

In PostgreSQL, you can use INNER JOIN with aggregate functions like COUNT, SUM, or AVG.

True False

INNER JOIN can be combined with aggregate functions to perform calculations on the joined data.

Q10

True / False

PostgreSQL INNER JOIN will return unmatched rows from the left table and NULLs for the columns of the right table.

True False

This statement describes a LEFT JOIN, not an INNER JOIN. An INNER JOIN only returns rows with matches in both tables.

Q11

Single Choice

In PostgreSQL, which of the following is the correct syntax for performing an INNER JOIN between two tables, table1 and table2, on a common column id?

SELECT * FROM table1 JOIN table2 ON table1.id = table2.id SELECT * FROM table1 INNER JOIN table2 USING id; SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

In PostgreSQL, the INNER JOIN can be written simply as JOIN. It combines rows from both tables where the id matches in both.

Q12

Single Choice

What does an INNER JOIN return in PostgreSQL?

Only rows that have matching values in both tables All rows from the left table All rows from the right tableAll rows from both tables

An INNER JOIN returns only the rows that have matching values in both joined tables

Q13

Single Choice

Which of the following PostgreSQL queries will return all columns from both employees and departments tables where employee_id is the same in both tables?

SELECT * FROM employees INNER JOIN departments ON employees.employee_id = departments.employee_id; SELECT * FROM employees LEFT JOIN departments ON employees.employee_id = departments.employee_ SELECT * FROM employees RIGHT JOIN departments ON employees.employee_id = departments.employee_id;SELECT * FROM employees CROSS JOIN departments;

The query uses an INNER JOIN to return rows where employee_id matches in both tables.

Q14

Single Choice

Given two tables, orders and customers, how can you select the order_id, customer_name, and order_date for orders where the customer_id matches in both tables using PostgreSQL?

SELECT order_id, customer_name, order_date FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id; SELECT order_id, customer_name, order_date FROM orders RIGHT JOIN customers ON orders.customer_id = customers.customer_id; SELECT order_id, customer_name, order_date FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;SELECT order_id, customer_name, order_date FROM orders FULL JOIN customers ON orders.customer_id = customers.customer_id;

The INNER JOIN ensures that only the orders where customer_id matches in both tables are selected.

Q15

Single Choice

In PostgreSQL, what will happen if you perform an INNER JOIN between two tables on a non-key column that contains duplicate values?

It will produce an error It will return duplicate rows for each match It will return only unique rowsIt will return NULL for all rows

When joining on a non-key column with duplicates, the INNER JOIN will return multiple rows for each matching value.

Q16

Single Choice

Given the PostgreSQL tables products and sales, how can you retrieve the product_name and total_sales where there is a match in product_id between the two tables?

SELECT product_name, total_sales FROM products RIGHT JOIN sales ON products.product_id = sales.product_id; SELECT product_name, total_sales FROM products LEFT JOIN sales ON products.product_id = sales.product_id; SELECT product_name, total_sales FROM products INNER JOIN sales ON products.product_id = sales.product_id;SELECT product_name, total_sales FROM products FULL JOIN sales ON products.product_id = sales.product_id;

The INNER JOIN ensures only matching rows where product_id is found in both tables are retrieved.

Q17

Single Choice

How can you retrieve all columns from three PostgreSQL tables, students, courses, and enrollments, where there is a matching student_id and course_id across all three tables?

SELECT * FROM students INNER JOIN enrollments ON students.student_id = enrollments.student_id INNER JOIN courses ON enrollments.course_id = courses.course_id; SELECT * FROM students LEFT JOIN enrollments ON students.student_id = enrollments.student_id LEFT JOIN courses ON enrollments.course_id = courses.course_id; SELECT * FROM students RIGHT JOIN enrollments ON students.student_id = enrollments.student_id RIGHT JOIN courses ON enrollments.course_id = courses.course_idSELECT * FROM students FULL JOIN enrollments ON students.student_id = enrollments.student_id FULL JOIN courses ON enrollments.course_id = courses.course_id;

The INNER JOIN is applied across all three tables to retrieve matching rows based on student_id and course_id.

Q18

Single Choice

When performing an INNER JOIN between two PostgreSQL tables, employees and projects, using multiple conditions, how would you select all rows where employee_id matches and the role is 'Manager'?

SELECT * FROM employees INNER JOIN projects ON employees.employee_id = projects.employee_id WHERE employees.role = 'Manager'; SELECT * FROM employees LEFT JOIN projects ON employees.employee_id = projects.employee_id WHERE employees.role = 'Manager'; SELECT * FROM employees RIGHT JOIN projects ON employees.employee_id = projects.employee_id WHERE employees.role = 'Manager';SELECT * FROM employees FULL JOIN projects ON employees.employee_id = projects.employee_id WHERE employees.role = 'Manager';

The INNER JOIN is followed by a WHERE clause to filter the rows where the role is 'Manager'.

Q19

Single Choice

How can you use an INNER JOIN in PostgreSQL to get a list of all employees who are currently assigned to at least one project, including their employee_id, name, and project_name?

SELECT employee_id, name, project_name FROM employees INNER JOIN projects ON employees.employee_id = projects.employee_id; SELECT employee_id, name, project_name FROM employees LEFT JOIN projects ON employees.employee_id = projects.employee_id; SELECT employee_id, name, project_name FROM employees RIGHT JOIN projects ON employees.employee_id = projects.employee_id;SELECT employee_id, name, project_name FROM employees FULL JOIN projects ON employees.employee_id = projects.employee_id;

The INNER JOIN is used to return employees who are assigned to a project, ensuring a match in the employee_id

Q20

Single Choice

In a complex query involving multiple INNER JOINs in PostgreSQL, which of the following will most likely cause performance issues?

SQL Code

SELECT a.name, b.department_name, c.project_name
FROM employees a
INNER JOIN departments b ON a.department_id = b.id
INNER JOIN projects c ON b.project_id = c.id
WHERE c.budget > 100000;

The use of multiple INNER JOINs The use of WHERE clause. The lack of indexes on the joining columnsThe use of aliases

The lack of indexes on the joining columns can lead to performance issues in complex queries with multiple INNER JOINs, as PostgreSQL would need to perform full table scans instead of faster index lookups.

Q21

Multiple Choice

Which of the following SQL queries correctly performs an INNER JOIN between 'employees' and 'departments' tables in PostgreSQL?

SELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id; SELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.id; SELECT employees.name, departments.department_name FROM employees JOIN departments ON employees.department_id = departments.department_id;SELECT employees.name, departments.department_name FROM employees INNER JOIN departments WHERE employees.department_id = departments.department_id;

An INNER JOIN returns rows when there is a match between the tables. The correct queries should join 'employees' and 'departments' on the appropriate key.

Q22

Multiple Choice

Identify the SQL queries that correctly use INNER JOIN to combine data from 'orders' and 'customers' tables in PostgreSQL.

SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id; SELECT orders.order_id, customers.customer_name FROM orders JOIN customers ON orders.customer_id = customers.id; SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers WHERE orders.customer_id = customers.customer_id;SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.id = customers.customer_id;

INNER JOIN is used to combine rows from different tables when there is a match in the columns specified. The correct queries should match the 'customer_id' in both 'orders' and 'customers' tables.

Q23

Multiple Choice

Which SQL queries correctly utilize INNER JOIN to retrieve data from 'products' and 'categories' tables in PostgreSQL?

SELECT products.product_name, categories.category_name FROM products INNER JOIN categories ON products.category_id = categories.category_id; SELECT products.product_name, categories.category_name FROM products JOIN categories ON products.category_id = categories.id; SELECT products.product_name, categories.category_name FROM products INNER JOIN categories ON products.id = categories.category_id;SELECT products.product_name, categories.category_name FROM products INNER JOIN categories WHERE products.category_id = categories.category_id;

The INNER JOIN should match the 'category_id' in 'products' with 'category_id' in 'categories' to retrieve data correctly.

Q24

Multiple Choice

Determine which SQL queries correctly use INNER JOIN to merge data from 'students' and 'classes' tables in PostgreSQL.

SELECT students.student_name, classes.class_name FROM students INNER JOIN classes ON students.class_id = classes.class_id; SELECT students.student_name, classes.class_name FROM students JOIN classes ON students.id = classes.class_id; SELECT students.student_name, classes.class_name FROM students INNER JOIN classes WHERE students.class_id = classes.class_id;SELECT students.student_name, classes.class_name FROM students INNER JOIN classes ON students.class_id = classes.id;

INNER JOIN should be used to combine 'students' and 'classes' based on the matching 'class_id'. The correct queries will return only students enrolled in classes.

Q25

Multiple Choice

Which SQL queries correctly apply INNER JOIN to retrieve sales data from 'sales' and 'salespersons' tables in PostgreSQL?

SELECT sales.sale_id, salespersons.salesperson_name FROM sales INNER JOIN salespersons ON sales.salesperson_id = salespersons.salesperson_id; SELECT sales.sale_id, salespersons.salesperson_name FROM sales JOIN salespersons ON sales.salesperson_id = salespersons.id; SELECT sales.sale_id, salespersons.salesperson_name FROM sales INNER JOIN salespersons WHERE sales.salesperson_id = salespersons.salesperson_id;SELECT sales.sale_id, salespersons.salesperson_name FROM sales INNER JOIN salespersons ON sales.id = salespersons.salesperson_id;

The INNER JOIN should combine rows from 'sales' and 'salespersons' where there is a matching 'salesperson_id'. The correct queries should provide sales data linked to the correct salesperson.

Q26

Multiple Choice

Identify the SQL queries that correctly use INNER JOIN with multiple tables in PostgreSQL.

SELECT orders.order_id, products.product_name FROM orders INNER JOIN order_items ON orders.order_id = order_items.order_id INNER JOIN products ON order_items.product_id = products.product_id; SELECT orders.order_id, products.product_name FROM orders JOIN order_items ON orders.id = order_items.order_id JOIN products ON order_items.product_id = products.id; SELECT orders.order_id, products.product_name FROM orders INNER JOIN order_items ON orders.order_id = order_items.order_id INNER JOIN products ON order_items.product_id = products.id;SELECT orders.order_id, products.product_name FROM orders INNER JOIN order_items ON orders.id = order_items.order_id INNER JOIN products ON order_items.product_id = products.product_id;

The correct INNER JOIN should combine 'orders', 'order_items', and 'products' on matching keys to retrieve complete order information with product details.

Q27

Multiple Choice

Determine which SQL queries correctly use INNER JOIN to filter results from 'employees' and 'salaries' tables in PostgreSQL.

SELECT employees.name, salaries.salary FROM employees INNER JOIN salaries ON employees.employee_id = salaries.employee_id WHERE salaries.salary > 50000; SELECT employees.name, salaries.salary FROM employees JOIN salaries ON employees.employee_id = salaries.id WHERE salaries.salary > 50000; SELECT employees.name, salaries.salary FROM employees INNER JOIN salaries WHERE employees.employee_id = salaries.employee_id AND salaries.salary > 50000;SELECT employees.name, salaries.salary FROM employees INNER JOIN salaries ON employees.id = salaries.employee_id WHERE salaries.salary > 50000;

The correct INNER JOIN should filter the results to include only those employees earning more than 50,000. The filtering condition should be correctly applied after the join.

Q28

Multiple Choice

Which SQL queries correctly use INNER JOIN to retrieve data from 'books' and 'authors' tables based on publication year in PostgreSQL?

SELECT books.title, authors.name FROM books INNER JOIN authors ON books.author_id = authors.author_id WHERE books.publication_year > 2010; SELECT books.title, authors.name FROM books JOIN authors ON books.author_id = authors.id WHERE books.publication_year > 2010; SELECT books.title, authors.name FROM books INNER JOIN authors WHERE books.author_id = authors.author_id AND books.publication_year > 2010;SELECT books.title, authors.name FROM books INNER JOIN authors ON books.id = authors.author_id WHERE books.publication_year > 2010;

The INNER JOIN should combine 'books' and 'authors' on the matching 'author_id' and filter results to include only books published after 2010.

Q29

Multiple Choice

Identify the SQL queries that correctly apply INNER JOIN to retrieve and calculate aggregated data from 'sales' and 'products' tables in PostgreSQL.

SELECT products.product_name, SUM(sales.amount) AS total_sales FROM sales INNER JOIN products ON sales.product_id = products.product_id GROUP BY products.product_name; SELECT products.product_name, SUM(sales.amount) AS total_sales FROM sales JOIN products ON sales.product_id = products.id GROUP BY products.product_name; SELECT products.product_name, SUM(sales.amount) AS total_sales FROM sales INNER JOIN products WHERE sales.product_id = products.product_id GROUP BY products.product_name;SELECT products.product_name, SUM(sales.amount) AS total_sales FROM sales INNER JOIN products ON sales.id = products.product_id GROUP BY products.product_name;

The correct queries should use INNER JOIN to combine 'sales' and 'products', and then apply aggregation functions to calculate total sales for each product.

Q30

Multiple Choice

Which SQL queries correctly use INNER JOIN to combine data from 'employees', 'departments', and 'locations' tables in PostgreSQL?

SELECT employees.name, departments.department_name, locations.location_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id INNER JOIN locations ON departments.location_id = locations.location_id; SELECT employees.name, departments.department_name, locations.location_name FROM employees JOIN departments ON employees.department_id = departments.id JOIN locations ON departments.location_id = locations.id; SELECT employees.name, departments.department_name, locations.location_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id INNER JOIN locations ON departments.location_id = locations.location_id;SELECT employees.name, departments.department_name, locations.location_name FROM employees INNER JOIN departments ON employees.id = departments.department_id INNER JOIN locations ON departments.location_id = locations.id;

The INNER JOIN should correctly combine 'employees', 'departments', and 'locations' on their respective keys to retrieve complete information for each employee.

PostgreSQL Database Quiz Questions