Question III (DISTINCT Keyword) Retrieve all pid values of parts for which orders are placed.
Hint: There are two SQL commands for this question: (a) pid values could be duplicate and (b) pid values are unique.
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
⇒
pid
p01
p01
p02
p03
p04
p05
p06
p07
p03
p03
p05
p05
p01
p01
p07
p01
(a)
pid
p01
p02
p03
p04
p05
p06
p07
(b)
SELECT pid FROM order3;
SELECT DISTINCT pid FROM order3;
Question IV (SQL Execution Steps) Retrieve all customer-agent name pairs, (cname, aname), where the customer places an order through the agent.
((customer[cid, cname]∞order3)∞agent)[cname, aname]
(((customer×order3)×agent) WHERE customer.cid = order3.cid
AND order3.aid = agent.aid)[cname, aname]
SELECT DISTINCT c.cname, a.aname FROM customer c, order3 o, agent a
WHERE c.cid = o.cid AND o.aid = a.aid;
The SELECT statement results in the following conceptual steps:
Compute customer×order3×agent.
Select according to customer.cid=order3.cid and order3.aid=agent.aid.
Project on cname and aname.
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.
