SQL must-know note the 11th Chapter uses subqueries

11.1 Sub-query

Any SQL statement that queries (query) is a query. However, this term generally refers to a SELECT statement.
SQL also allows you to create subqueries (subquery), which are queries that are nested within other queries.

11.2 Filtering with subqueries

SELECT cust_idFROM OrdersWHERE order_num IN(SELECT order_num                                   FROM OrderItems                                   WHERE prod_id = ‘RGA01‘);

In a SELECT statement, subqueries are always handled from inside out.

SELECT cust_name,cust_contactFROM CustomersWHERE cust_id IN(SELECT cust_id     FROM Orders     WHERE order_num IN(SELECT order_num          FROM OrderItems          WHERE prod_id = ‘RGA01‘));

Can only be a single column: a SELECT statement as a subquery can only query individual columns. An attempt to retrieve multiple columns will return an error.

11.3 Using subqueries as calculated fields

In order to execute count (*) for each customer, it should be used as a subquery.

SELECT cust_name,cust_state,(SELECT COUNT(*)      FROM Orders     WHERE Orders.cust_id = Customers.cust_id) AS ordersFROM CustomersORDER BY cust_name;

SQL must-know note the 11th Chapter uses subqueries