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:
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.
