$ settings --editor

$ settings --general

$ save --settings

SQL for beginners

What is a subquery #

A subquery is a SELECT statement nested inside another SQL statement. It runs first, and its result is used by the outer (main) query. Subqueries are often used in places where a single value or a list of values is expected, such as inside a WHERE clause.

Syntax #

A subquery is simply wrapped in parentheses:

SELECT
    column_name
FROM
    table_name
WHERE
    column_name > (
        SELECT AVG(column_name)
        FROM table_name
    );

Here, (SELECT AVG(column_name) FROM table_name) is the subquery. Since it returns a single value (the average), it can be compared directly with >, just like a regular number.

Example #

Suppose we want to find products that cost more than the cheapest product. We first need to know the minimum price, then compare each product's price against it:

SELECT
    name
FROM
    products
WHERE
    price > (
        SELECT MIN(price)
        FROM products
    );

The database first runs the inner query SELECT MIN(price) FROM products, which returns a single number. That number is then substituted into the outer query, so it behaves as if we had written WHERE price > 3.50.

One value only

When a subquery is used with a comparison operator like =, >, or <, it must return exactly one row and one column. If it returns more than one row, the database will raise an error.

Exercise #

Write a command that retrieves the name of every product from the products table whose price is greater than the average price of all products. Use a subquery to calculate the average price instead of a hardcoded number.

  • The result has a name column.
  • The result contains the names of exactly the products priced above the average price of all products.
Solution 
SELECT
    name
FROM
    products
WHERE
    price > (
        SELECT AVG(price)
        FROM products
    );
Output will be displayed here