SQL Advanced Joins

Last Updated :

Discuss

Comments

Question 1

You want to see a list of all employees and all departments, regardless of whether an employee is assigned to a department or a department has any employees.

A
SELECT * FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;
B
SELECT * FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
C
SELECT * FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
D
SELECT * FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Question 2

You have a table of employees where each employee has a manager, who is also an employee in the same table. How would you write a query to see a list of employees and their managers?

A
SELECT name FROM employees
WHERE manager_id IS NOT NULL;
B
SELECT e.name, m.name FROM employees e, managers m
WHERE e.manager_id = m.id;
C
SELECT e.name AS employee, m.name AS manager
FROM employees e, employees m WHERE e.manager_id = m.id;
D
SELECT e.name AS employee, m.name AS manager
FROM employees e JOIN employees m ON e.manager_id = m.id;

Question 3

You want to generate a list of all possible combinations of products and colors. Write query for the same.

A
SELECT * FROM products p
INNER JOIN colors c ON p.color_id = c.id;
B
SELECT * FROM products p
FULL OUTER JOIN colors c ON p.color_id = c.id;
C
SELECT * FROM products
CROSS JOIN colors;
D
SELECT * FROM products, colors;

Question 4

You want to see a list of all orders, with the customer's name and the product's name.

A
SELECT o.id, c.name, p.name FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id;
B
SELECT o.id, c.name, p.name
FROM orders o, customers c, products p;
C
SELECT o.id, c.name, p.name FROM orders o
JOIN customers c, products p
ON o.customer_id = c.id AND o.product_id = p.id;
D
SELECT o.id, c.name, p.name FROM orders o, customers c, products p
WHERE o.customer_id = c.id AND o.product_id = p.id;

Question 5

What is the result of a "CROSS JOIN" between a table with 5 rows and a table with 10 rows?

A
A table with 10 rows.
B
A table with 50 rows.
C
A table with 5 rows.
D
A table with 15 rows.

Question 6

You want to find all students who are not enrolled in any courses. Which of the following queries would you use?

A
SELECT s.name FROM students s
INNER JOIN courses c ON s.course_id = c.id;
B
SELECT s.name FROM students s RIGHT JOIN courses c
ON s.course_id = c.id WHERE s.id IS NULL;
C
SELECT s.name FROM students s LEFT JOIN courses c
ON s.course_id = c.id WHERE c.id IS NULL;
D
SELECT s.name FROM students s FULL OUTER JOIN courses c
ON s.course_id = c.id WHERE c.id IS NULL;

Question 7

You want to find all pairs of employees who were hired on the same day. Which of the following queries would you use?

A
SELECT name FROM employees
GROUP BY hire_date HAVING COUNT(*) > 1;
B
SELECT e1.name, e2.name FROM employees e1
JOIN employees e2 ON e1.hire_date = e2.hire_date AND e1.id < e2.id;
C
SELECT e1.name, e2.name FROM employees e1
JOIN employees e2 ON e1.hire_date = e2.hire_date;
D
SELECT e1.name, e2.name FROM employees e1, employees e2
WHERE e1.hire_date = e2.hire_date;

Question 8

You want to see a list of all customers, and for those who have placed orders, the total amount of their orders. Which of the following queries would you use?

A
SELECT c.name, SUM(o.amount) FROM customers c
INNER JOIN orders o ON c.id = o.customer_id GROUP BY c.name;
B
SELECT c.name, SUM(o.amount) FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id GROUP BY c.name;
C
SELECT c.name, o.amount FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
D
SELECT c.name, SUM(o.amount) FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.name;

Question 9

You have a table of "employees", a table of "projects", and a linking table "employee_projects". You want to see a list of all employees and the projects they are working on. What is the minimum number of joins you need to perform?

Question 10

You want all the rows from A, the matching rows from B, and the matching rows from C. Which of the following queries would you use?

A
SELECT * FROM A INNER JOIN B
ON A.id = B.a_id INNER JOIN C ON B.id = C.b_id;
B
SELECT * FROM A LEFT JOIN B
ON A.id = B.a_id LEFT JOIN C ON B.id = C.b_id;
C
SELECT * FROM A FULL OUTER JOIN B
ON A.id = B.a_id FULL OUTER JOIN C ON B.id = C.b_id;
D
SELECT * FROM A RIGHT JOIN B
ON A.id = B.a_id RIGHT JOIN C ON B.id = C.b_id;

Tags:

SQL

There are 10 questions to complete.

Take a part in the ongoing discussion