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:
The Database includes:
Tablename
Record
Customers
91
Categories
8
Employees
10
OrderDetails
518
Orders
196
Products
77
Shippers
3
Suppliers
29
This SQL-Statement is not supported in the WebSQL Database.
The example still works, because it uses a modified version of SQL.
Your browser does not support WebSQL.
Your are now using a light-version of the Try-SQL Editor, with a read-only Database.
If you switch to a browser with WebSQL support, you can try any SQL statement, and play with the Database as much as you like. The Database can also be restored at any time.
Our Try-SQL Editor uses WebSQL to demonstrate SQL.
A Database-object is created in your browser, for testing purposes.
You can try any SQL statement, and play with the Database as much as you like. The Database can be restored at any time, simply by clicking the "Restore Database" button.
W3C WebSQL
WebSQL stores a Database locally, on the user's computer. Each user gets their own Database object.
WebSQL Browser Support
WebSQL is supported in Chrome, Safari, and Opera.
If you use another browser you will still be able to use our Try SQL Editor, but a different version, using a server-based ASP application, with a read-only Access Database, where users are not allowed to make any changes to the data.
