Using Subqueries

Learning Outcomes

  • Use subqueries

A subquery is a query that is contained or nested within another query. The idea is to use the outcome of one query within the execution of another query. It is analogous to asking a question of the results you have after answering another question, i.e., a multi-part question. A plain English example of a subquery would be “show me all employees who have been with the company more than ten years and then show me those who live in the state of Michigan.”

The actual use of a subquery requires a basic knowledge of SQL (Structured Query Language). In essence, SQL is programming code and quite technical. Structured Query Language (SQL) is a database language designed for managing data held in a relational database management system. SQL was initially developed by IBM in the early 1970s (Date 1986). The initial version, called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM’s quasi-relational database management system, System R. Then in the late 1970s, Relational Software Inc., which is now Oracle Corporation, introduced the first commercially available implementation of SQL, Oracle V2 for VAX computers.

PRactice Question

Many of the currently available relational DBMSs, such as Oracle Database, Microsoft SQL Server, MySQL, IBM DB2, IBM Informix and Microsoft Access, use SQL. Here is an example of the SQL language being used to create a database table:

USE SW
CREATE TABLE PROJECT
(
ProjectID       Int  NOT NULL IDENTITY (1000,100),
ProjectName     Char(50) NOT NULL,
Department      Char(35) NOT NULL,
MaxHours        Numeric(8,2)  NOT NULL DEFAULT 100,
StartDate       DateTime NULL,
EndDate         DateTime NULL,
CONSTRAINT      ASSIGNMENT_PK  PRIMARY KEY(ProjectID)
);

Here’s an example of a subquery In Access using the Query Wizard in Design View (the subquery is written in SQL at the bottom of the third column):

Subquery for Dim_Transactions. The fields Customer ID from Dim_Transactions will be Grouped. The fields LineTotal from Dim_Transactions will be Summed. The fields CustomerId from Dim_Transactions will be totaled Where.