Slide 15.9: The IN predicate

Subqueries (Cont.)

Many times, there are more than one SQL statement able to answer the question.

Question IX (Multiple Forms)
Retrieve all information concerning agents based in Duluth or Dallas.

agent
aid	aname	city	percent
a01	Smith	New York	6
a02	Jones	Newark	6
a03	Brown	Tokyo	7
a04	Gray	New York	6
a05	Otasi	Duluth	5
a06	Smith	Dallas	5

⇒

aid
a05
a06

SELECT aid FROM agent
  WHERE city IN ( 'Duluth', 'Dallas' );

SELECT aid FROM agent
  WHERE city = 'Duluth' OR city = 'Dallas';

The most local scope rule is applied to the name definition. For example, both tables customer and agent include a column city. The city belongs to the table agent according to the most local scope rule in the following SQL statement (the CAP database).

Question X (The Most Local Scope Rule)
Determine the names and discounts of all customers who place orders through agents in Duluth or Dallas.

cname	discnt
TipTop	10
Basics	12
ACME	8
ACME	0

SELECT cname, discnt FROM customer
  WHERE cid IN ( SELECT cid FROM order3 WHERE aid IN (
    SELECT aid FROM agent WHERE city IN ( 'Duluth', 'Dallas' )));

The outer SELECT can provide data to an inner subquery. When a subquery involves a single table, it does not require a qualified reference to a column name.

Question XI (A Qualified Reference for Subqueries)
Find the names of customers who order product p05.

cname
Allied
TipTop

SELECT DISTINCT cname FROM customer c, order3 o
  WHERE c.cid = o.cid AND o.pid = 'p05';

SELECT DISTINCT cname FROM customer c WHERE 'p05' IN (
  SELECT pid FROM order3 WHERE cid = c.cid );

Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.

Result:

The Database includes:

Tablename	Record
Customers	91
Categories	8
Employees	10
OrderDetails	518
Orders	196
Products	77
Shippers	3
Suppliers	29

W3C WebSQL

WebSQL Browser Support