Ninth in a series of posts in response to Tim Ford's #EntryLevel Challenge.

Subqueries are queries within a query – one SELECT nested within another. They can take the place of column and table references, helping you to formulate queries that otherwise would be more difficult or less efficient to express using join operations. 

As Columns

Following is an example of a subquery filling a position typically occupied by a column name or expression. The goal of the query is to list the number of products per subcategory.

SELECT ps.name,
    (SELECT COUNT(*)
    FROM Production.Product p
    WHERE p.ProductSubcategoryID = ps.ProductSubcategoryID) AS sub_count
FROM production.ProductSubcategory ps

The output looks as  follows:

Once upon a time there was a very experienced database administrator who accidentally dropped a 12 TB index from a 55 TB table. The question I had was “How did he fix it?”(read more)

All issues since May 2001 (Vol. 15 No. 2)(read more)

In an interview for the NoCOUG Journal (http://www.nocoug.org/Journal/NoCOUG_Journal_200608.pdf#page=4), Steven Feuerstein was asked: “SQL is a set-oriented non-procedural language; i.e., it works on sets and does not specify access paths. PL/SQL on the other hand is a record-oriented procedural language, as is very clear from the name. What is the place of a record-oriented procedural language in the relational world?”

Steven replied: “Its place is proven: SQL is not a complete language. Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens. We need more than SQL to build our applications, whether it is the implementation of business rules or application logic.