Slide 15.6: Arithmetic calculation

Slide 15.5: The DISTINCT keyword
Slide 15.7: Using the same table more than once
Home Print version

SQL SELECT Examples (Cont.)

Question V (Arithmetic Calculation)
Retrieve a table based on the order3 table, with columns orderno, cid, aid, pid, where profit is calculated as
profit = 0.40×(order3.qty×product.price) – 0.01×(customer.discnt+agent.percent) × (order3.qty×product.price)
For example, the profit of orderno 1011 is

 120.00 = 0.40 × (1000×0.5)
  – 0.01 × (10+6) × (1000×0.5)

Note: Correlation names are analogous to the relational algebra aliases.

orderno	cid	aid	pid	profit
1011	c001	a01	p01	120.00
1012	c001	a01	p01	120.00
1013	c002	a03	p03	210.00
1014	c003	a03	p05	300.00
1015	c003	a03	p05	300.00
1016	c006	a01	p01	170.00
1017	c001	a06	p03	150.00
1018	c001	a03	p04	138.00
1019	c001	a02	p02	48.00
1020	c006	a03	p07	198.00
1021	c004	a06	p01	135.00
1022	c001	a05	p06	200.00
1023	c001	a04	p05	120.00
1024	c006	a06	p01	140.00
1025	c001	a05	p07	200.00
1026	c002	a05	p03	184.00

SELECT orderno, o.cid, o.aid, o.pid, 0.40*(o.qty*p.price)
  - 0.01*(c.discnt+a.percent)*(o.qty*p.price) AS profit
  FROM order3 o, customer c, agent a, product p
  WHERE c.cid = o.cid AND a.aid = o.aid AND p.pid = o.pid
  ORDER BY orderno;

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

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

product
pid	pname	city	quantity	price
p01	comb	Dallas	111400	0.50
p02	brush	Newark	203000	0.50
p03	razor	Duluth	150600	1.00
p04	pen	Duluth	125300	1.00
p05	pencil	Dallas	221400	1.00
p06	folder	Dallas	123100	2.00
p07	case	Newark	100500	1.00

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

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

◀
Previous

Slide 15.5: The DISTINCT keyword
Slide 15.7: Using the same table more than once
Home Print version

▶
Next

Tom was hands-down

W3C WebSQL

WebSQL Browser Support