Slide 15.10: The SQL scoping rule

Subqueries (Cont.)

The SQL Scoping Rule: A local variable of a subquery is undefined until the subquery has been entered.

Question XII (SQL Scoping Rule)
Get the names of customers who order product p07 from agent a03. (the CAP database)

Note: The SQL statement below is NOT correct:

order3
orderno	month	cid	aid	pid	qty	dollar
1011	jan	c001	a01	p01	1000	450.00
1012	jan	c001	a01	p01	1000	450.00
1019	feb	c001	a02	p02	400	180.00
1017	feb	c001	a06	p03	600	540.00
1018	feb	c001	a03	p04	600	540.00
1023	mar	c001	a04	p05	500	450.00
1022	mar	c001	a05	p06	400	720.00
1025	apr	c001	a05	p07	800	720.00
1013	jan	c002	a03	p03	1000	880.00
1026	may	c002	a05	p03	800	704.00
1015	jan	c003	a03	p05	1200	1104.00
1014	jan	c003	a03	p05	1200	1104.00
1021	feb	c004	a06	p01	1000	460.00
1016	jan	c006	a01	p01	1000	500.00
1020	feb	c006	a03	p07	600	600.00
1024	mar	c006	a06	p01	800	400.00

⇒

cid
c006

⇓

customer

⇓

cname
ACME

☠: SELECT cname FROM customer c WHERE order3.aid = 'a03'
      AND 'p07' IN ( SELECT pid FROM order3 WHERE cid = c.cid );

👍: SELECT cname FROM customer WHERE cid IN (
      SELECT cid FROM order3 WHERE aid = 'a03' AND pid = 'p07' );

A comma-separated list of expressions can appear before the IN operator.

Question XIII (Expressions Before the IN Operator)
Retrieve orderno values for all orders placed by customers in Duluth throgh agents in New York. (the CAP database)

orderno
1011
1012
1023

SELECT orderno FROM order3 WHERE ( cid, aid ) IN (
  SELECT cid, aid FROM customer c, agent a
    WHERE c.city = 'Duluth' AND a.city = 'New York' );

SELECT orderno FROM order3 o WHERE EXISTS ( 
  SELECT cid, aid FROM customer c, agent a WHERE c.cid = o.cid AND
    a.aid = o.aid AND c.city = 'Duluth' AND a.city = 'New York' );

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