14.18: 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.
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):
Contributors and Attributions
- Practice Question. Authored by : Robert Danielson. Provided by : Lumen Learning. License : CC BY: Attribution
- SQL Structured Query Language. Authored by : Adrienne Watt and Nelson Eng. Provided by : BCcampus. Located at : https://opentextbc.ca/dbdesign01/chapter/sql-structured-query-language/ . License : CC BY: Attribution