Mega Code Archive
Using calculations within aggregate functions
<![CDATA[
5>
6> CREATE TABLE CD (
7> CD_ID integer NOT NULL PRIMARY KEY,
8> CD_Title varchar(40),
9> Composer_ID integer NOT NULL,
10> Classif_ID integer NOT NULL,
11> SalesPrice money,
12> AverageCost money)
13> GO
1> INSERT into CD VALUES(2000,"John",100,1,16.99,6.99)
2> INSERT into CD VALUES(2001,"Chicago 16",107,1,14.99,5.99)
3> INSERT into CD VALUES(2002,"Chicago 17",107,1,14.99,5.99)
4> INSERT into CD VALUES(2003,"Chicago 18",107,1,14.99,5.99)
5> INSERT into CD VALUES(2004,"Greatest Hits",107,1,16.99,7.99)
6> INSERT into CD VALUES(2005,"Midnight",101,3,14.99,5.99)
7> INSERT into CD VALUES(2006,"Mode",115,3,14.99,5.99)
8> INSERT into CD VALUES(2007,"Ultra",115,3,15.99,5.99)
9> INSERT into CD VALUES(2008,"Mindcrime",102,4,14.99,5.99)
10> INSERT into CD VALUES(2009,"Empire",102,4,14.99,5.99)
11> INSERT into CD VALUES(2010,"Land",102,4,12.99,4.99)
12> INSERT into CD VALUES(2011,"Night",103,4,11.99,3.99)
13> INSERT into CD VALUES(2012,"Pyromania",103,4,14.99,5.99)
14> INSERT into CD VALUES(2013,"Hysteria",103,4,14.99,5.99)
15> INSERT into CD VALUES(2014,"Hits",103,4,13.99,4.99)
16> INSERT into CD VALUES(2015,"Hits 2",104,2,15.99,6.99)
17> INSERT into CD VALUES(2016,"Greatest",105,2,14.99,5.99)
18> INSERT into CD VALUES(2017,"Hits 3",106,1,13.99,5.99)
19> INSERT into CD VALUES(2018,"Deep",108,1,12.99,2.99)
20> INSERT into CD VALUES(2019,"Turning",109,1,14.99,5.99)
21> INSERT into CD VALUES(2020,"TheHits",109,1,16.99,7.99)
22> INSERT into CD VALUES(2021,"Cars",110,1,9.99,3.99)
23> INSERT into CD VALUES(2022,"Anthology",110,1,25.99,11.99)
24> INSERT into CD VALUES(2023,"City",110,1,14.99,5.99)
25> INSERT into CD VALUES(2024,"Rick",111,1,11.99,2.99)
26> INSERT into CD VALUES(2025,"Live",112,1,19.99,8.99)
27> INSERT into CD VALUES(2026,"Pat",113,1,16.99,6.99)
28> INSERT into CD VALUES(2027,"Big",114,1,14.99,5.99)
29> INSERT into CD VALUES(2028,"Hurting",114,1,11.99,3.99)
30> INSERT into CD VALUES(2029,"Vol 1",116,1,9.99,2.99)
31> INSERT into CD VALUES(2030,"Vol 2",116,1,9.99,2.99)
32> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> CREATE TABLE Inventory(
2> CD_ID integer NOT NULL,
3> StoreID integer NOT NULL,
4> QOH integer NULL,
5> Returns integer NULL,
6> QtyOrdered integer NULL,
7> Discontinued char(1) NOT NULL)
8> GO
1> INSERT into Inventory VALUES(2000,1330,10,0,0,"N")
2> INSERT into Inventory VALUES(2015,1330,5,0,3,"N")
3> INSERT into Inventory VALUES(2016,1330,6,0,11,"N")
4> INSERT into Inventory VALUES(2017,1330,5,2,0,"N")
5> INSERT into Inventory VALUES(2000,1040,3,0,9,"N")
6> INSERT into Inventory VALUES(2015,1040,1,0,10,"N")
7> INSERT into Inventory VALUES(2016,1040,4,0,11,"N")
8> INSERT into Inventory VALUES(2018,1040,15,8,0,"N")
9> INSERT into Inventory VALUES(2019,1040,22,3,0,"N")
10> INSERT into Inventory VALUES(2020,1040,10,0,0,"N")
11> INSERT into Inventory VALUES(2021,1040,19,0,NULL,"N")
12> INSERT into Inventory VALUES(2008,1200,11,0,NULL,"N")
13> INSERT into Inventory VALUES(2009,1200,5,0,9,"N")
14> INSERT into Inventory VALUES(2011,1210,18,0,NULL,"N")
15> INSERT into Inventory VALUES(2012,1210,5,0,NULL,"N")
16> INSERT into Inventory VALUES(2013,1210,1,0,6,"N")
17> INSERT into Inventory VALUES(2025,1220,3,0,8,"N")
18> INSERT into Inventory VALUES(2026,1220,3,0,7,"N")
19> INSERT into Inventory VALUES(2019,1300,2,0,8,"N")
20> INSERT into Inventory VALUES(2020,1300,9,0,0,"N")
21> INSERT into Inventory VALUES(2027,1310,2,0,8,"N")
22> INSERT into Inventory VALUES(2028,1310,4,0,4,"N")
23> INSERT into Inventory VALUES(2029,1320,2,0,8,"N")
24> INSERT into Inventory VALUES(2030,1320,6,0,0,"N")
25> INSERT into Inventory VALUES(2001,1099,6,0,0,"N")
26> INSERT into Inventory VALUES(2002,1099,2,0,3,"N")
27> INSERT into Inventory VALUES(2003,1099,7,0,0,"N")
28> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT SUM(CD.AverageCost*Inventory.QOH),
3> MAX(CD.AverageCost*Inventory.QOH),
4> MIN(CD.AverageCost*Inventory.QOH)
5> FROM CD,Inventory
6> WHERE CD.CD_ID = Inventory.CD_ID
7> GO
--------------------- --------------------- ---------------------
1032.1400 131.7800 5.9800
(1 rows affected)
1>
2>
3> drop table Inventory;
4> drop table CD;
5> GO
]]>