Slide 15.4: SQL SELECT examples

SQL SELECT Examples

The SQL SELECT statements could be complicated. The following slides give many SELECT examples, where RA means relational algebra and the CAP database is shown above.

Question I (Simple SELECT)
Find the aid values and names of agents that are based in New York.

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	aname
a01	Smith
a04	Gray

RA: (agent WHERE city='New York')[aid, aname]
SQL: SELECT aid, aname FROM agent WHERE city='New York';

Question II (SELECT All)
Display all the values in every row of the customer table.

SELECT * FROM customer;

customer
cid	cname	city	discnt
c001	TipTop	Duluth	10.00
c002	Basics	Dallas	12.00
c003	Allied	Dallas	8.00
c004	ACME	Duluth	8.00
c006	ACME	Kyoto	0.00

⇒

cid	cname	city	discnt
c001	TipTop	Duluth	10.00
c002	Basics	Dallas	12.00
c003	Allied	Dallas	8.00
c004	ACME	Duluth	8.00
c006	ACME	Kyoto	0.00

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