Scalar subquery in SELECT in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the employee name and their department name using a scalar subquery.

SQL

SELECT employee_name, (SELECT [1] FROM departments WHERE departments.id = employees.department_id) AS department_name FROM employees;

Drag options to blanks, or click blank then click option'

Aemployee_name

Bid

Cname

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting the department id instead of the name.

Using a column from the employees table inside the subquery.

The scalar subquery must select the name of the department to show it alongside the employee name.

2fill in blank

medium

Complete the code to find the total number of orders for each customer using a scalar subquery.

SQL

SELECT customer_name, (SELECT [1] FROM orders WHERE orders.customer_id = customers.id) AS total_orders FROM customers;

Drag options to blanks, or click blank then click option'

Acustomer_id

BCOUNT(*)

Corders

Dcustomer_name

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting a column name instead of counting rows.

Using the wrong column in the WHERE clause.

The scalar subquery must count the number of orders, so COUNT(*) is used.

3fill in blank

hard

Fix the error in the scalar subquery to get the highest salary from the employees table.

SQL

SELECT employee_name, (SELECT MAX([1]) FROM employees) AS highest_salary FROM employees;

Drag options to blanks, or click blank then click option'

Asalary

Bid

Cdepartment_id

Demployee_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using a non-numeric column inside MAX().

Selecting employee_name inside the subquery.

The MAX function should be applied to the salary column to find the highest salary.

4fill in blank

hard

Fill both blanks to select each product's name and its category name using a scalar subquery.

SQL

SELECT product_name, (SELECT [1] FROM categories WHERE categories.id = products.[2]) AS category_name FROM products;

Drag options to blanks, or click blank then click option'

Aname

Bcategory_id

Cid

Dproduct_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using product_name instead of category_id in the WHERE clause.

Selecting the wrong column from categories.

The subquery selects the name from categories where the category's id matches the product's category_id.

5fill in blank

hard

Fill all three blanks to select each student's name, their highest test score, and the test date using scalar subqueries.

SQL

SELECT student_name, (SELECT MAX([1]) FROM test_scores WHERE test_scores.student_id = students.[2]) AS highest_score, (SELECT [3] FROM test_scores WHERE test_scores.student_id = students.id ORDER BY score DESC LIMIT 1) AS test_date FROM students;

Drag options to blanks, or click blank then click option'

Ascore

Bid

Ctest_date

Dstudent_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using student_name instead of id in the WHERE clause.

Selecting score instead of test_date for the date.

Not ordering the subquery to get the highest score's date.

The first subquery finds the maximum score for each student matching by id. The second subquery selects the test_date of the highest score.