1> use evelyn
1> /* */
2> /* 1. Message for imported/domestic products */
3> /* Using one if-else construct twice to test each condition */
4> /* (Japan or USA) */
5> /* */
6> if exists (select s.suppnum
7> from product p, supplier s
8> where p.suppnum = s.suppnum
9> and country = 'JAPAN')
10> begin
11> select prodname, " Imported product, extra tax required"
12> from product p, supplier s
13> where p.suppnum = s.suppnum
14> and country = 'JAPAN'
15> end
16> else
17> select prodname, "Domestic product, no tax"
18> from product p, supplier s
19> where p.suppnum = s.suppnum
20> and country = 'USA'
prodname
-------------------- -------------------------------------
memory tripler Imported product, extra tax required
memory manager Imported product, extra tax required
memory8 Imported product, extra tax required
(3 rows affected)
1> /* */
2> if exists (select s.suppnum
3> from product p, supplier s
4> where p.suppnum = s.suppnum
5> and country = 'USA')
6> begin
7> select prodname, "Domestic product, no tax"
8> from product p, supplier s
9> where p.suppnum = s.suppnum
10> and country = 'USA'
11> end
12> else
13> select prodname, " Imported product, extra tax required"
14> from product p, supplier s
15> where p.suppnum = s.suppnum
16> and country = 'JAPAN'
prodname
-------------------- ------------------------
z_connector Domestic product, no tax
bugbane Domestic product, no tax
tax time Domestic product, no tax
money master Domestic product, no tax
mortgage minder Domestic product, no tax
landlord logs Domestic product, no tax
cook & book Domestic product, no tax
home poll kit Domestic product, no tax
star systems Domestic product, no tax
paper dolls Domestic product, no tax
more paper dolls Domestic product, no tax
typing test Domestic product, no tax
teach yourself greek Domestic product, no tax
blood & guts Domestic product, no tax
C++ for kids Domestic product, no tax
bug stories Domestic product, no tax
nt guru Domestic product, no tax
how multi is media? Domestic product, no tax
(18 rows affected)
1> /* */
2> /* Same question, generalized using case statement. */
3> /* Adding one more supplier and product for testing "else". */
4> /* */
5> insert into supplier
6> values (888, 'The Olde Curiosity Shoppe', 'Threadneedle Lane', 'London',
7> null, 'England', 'NW6 A3T', null, null, null)
(1 row affected)
1> insert into product
2> values (2001, 'chinese abacus', null, null, 1.0, null, null, 888)
(1 row affected)
1> select * from supplier
2> where suppnum = 888
suppnum name street city state
country zip ctrycode areacode phone
------- -------------------- -------------------- -------------------- -----
-------------------- ---------- -------- -------- -------
888 The Olde Curiosity S Threadneedle Lane London NULL
England NW6 A3T NULL NULL NULL
(1 row affected)
1> select * from product
2> where suppnum = 888
prodnum prodname type
description weight
price giftadd suppnum
----------- -------------------- ------------
-------------------------------------------------- ---------
------------------------ ------------------------ -------
2001 chinese abacus NULL
NULL 1.00
NULL NULL 888
(1 row affected)
1> /* */
2> select prodname, "TaxInfo"=
3> case
4> when country = 'USA' then "Domestic product, no tax"
5> when country = 'JAPAN' then "Imported product, extra tax required"
6> else "Product of unknown origin"
7> end
8> from product p, supplier s
9> where p.suppnum = s.suppnum
prodname TaxInfo
-------------------- ------------------------------------
z_connector Domestic product, no tax
bugbane Domestic product, no tax
memory tripler Imported product, extra tax required
memory manager Imported product, extra tax required
memory8 Imported product, extra tax required
tax time Domestic product, no tax
money master Domestic product, no tax
mortgage minder Domestic product, no tax
landlord logs Domestic product, no tax
cook & book Domestic product, no tax
home poll kit Domestic product, no tax
star systems Domestic product, no tax
paper dolls Domestic product, no tax
more paper dolls Domestic product, no tax
typing test Domestic product, no tax
teach yourself greek Domestic product, no tax
blood & guts Domestic product, no tax
C++ for kids Domestic product, no tax
bug stories Domestic product, no tax
nt guru Domestic product, no tax
how multi is media? Domestic product, no tax
chinese abacus Product of unknown origin
(22 rows affected)
/* */
/* 2. Case or isnull (not nullif) to show change in order/detail*/
/* if all orders shipped "today" (was Nov. 25th). */
/* */
1> select ordnum, prodnum, isnull(shipdate, getdate()) as AllShipped
2> from orderdetail
3> go
ordnum prodnum AllShipped
----------- ----------- --------------------------
84 1099 Nov 25 2002 7:35PM
84 1255 Nov 25 2002 7:35PM
81 1357 Nov 25 2002 7:35PM
87 1106 Nov 25 2002 7:35PM
87 2113 Jan 4 1999 12:00AM
87 1794 Nov 25 2002 7:35PM
87 1083 Nov 25 2002 7:35PM
91 2111 Jan 3 1999 12:00AM
89 1199 Jan 4 1999 12:00AM
89 2050 Jan 4 1999 12:00AM
85 1794 Jan 2 1999 12:00AM
90 2110 Jan 2 1999 12:00AM
95 1255 Nov 25 2002 7:35PM
95 1108 Nov 25 2002 7:35PM
95 1105 Nov 25 2002 7:35PM
99 2047 Nov 25 2002 7:35PM
99 2050 Nov 25 2002 7:35PM
92 2050 Nov 25 2002 7:35PM
93 1105 Nov 25 2002 7:35PM
94 1108 Nov 25 2002 7:35PM
81 2050 Jan 1 1999 12:00AM
84 2050 Jan 5 1999 12:00AM
85 2050 Jan 5 1999 12:00AM
86 1083 Jan 5 1999 12:00AM
91 1107 Jan 5 1999 12:00AM
93 2050 Jan 5 1999 12:00AM
94 1083 Jan 5 1999 12:00AM
95 1083 Jan 5 1999 12:00AM
86 1105 Jan 5 1999 12:00AM
87 1105 Jan 5 1999 12:00AM
81 1106 Jan 5 1999 12:00AM
99 2049 Nov 25 2002 7:35PM
86 2000 Nov 25 2002 7:35PM
87 2000 Jan 5 1999 12:00AM
99 2000 Nov 25 2002 7:35PM
(35 rows affected)
2> /* 3a. Average and maximum price for products selling at < $35 */
3> /* */
4> select avg(price) AvgPrice, max(price) MaxPrice
5> from product
6> where price < 35
AvgPrice MaxPrice
------------------------ ------------------------
22.63 29.99
(1 row affected)
1> /* */
2> /* 3b. Increase price in 5% increments; keep maximum =< $40. */
3> /* */
4> while (select max(price) from product
5> where price <35) <= 40
6> begin
7> select prodname, price
8> from product
9> where price < 35
10> update product
11> set price = price * 1.05
12> where price < 35
13> end
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 19.99
paper dolls 19.99
more paper dolls 19.99
memory manager 19.99
typing test 29.99
home poll kit 19.99
money master 29.00
blood & guts 29.99
bug stories 20.00
nt guru 20.00
how multi is media? 20.00
(11 rows affected)
(11 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 20.99
paper dolls 20.99
more paper dolls 20.99
memory manager 20.99
typing test 31.49
home poll kit 20.99
money master 30.45
blood & guts 31.49
bug stories 21.00
nt guru 21.00
how multi is media? 21.00
(11 rows affected)
(11 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 22.04
paper dolls 22.04
more paper dolls 22.04
memory manager 22.04
typing test 33.06
home poll kit 22.04
money master 31.97
blood & guts 33.06
bug stories 22.05
nt guru 22.05
how multi is media? 22.05
(11 rows affected)
(11 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 23.14
paper dolls 23.14
more paper dolls 23.14
memory manager 23.14
typing test 34.72
home poll kit 23.14
money master 33.57
blood & guts 34.72
bug stories 23.15
nt guru 23.15
how multi is media? 23.15
(11 rows affected)
(11 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 24.30
paper dolls 24.30
more paper dolls 24.30
memory manager 24.30
home poll kit 24.30
bug stories 24.31
nt guru 24.31
how multi is media? 24.31
(8 rows affected)
(8 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 25.51
paper dolls 25.51
more paper dolls 25.51
memory manager 25.51
home poll kit 25.51
bug stories 25.53
nt guru 25.53
how multi is media? 25.53
(8 rows affected)
(8 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 26.79
paper dolls 26.79
more paper dolls 26.79
memory manager 26.79
home poll kit 26.79
bug stories 26.80
nt guru 26.80
how multi is media? 26.80
(8 rows affected)
(8 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 28.13
paper dolls 28.13
more paper dolls 28.13
memory manager 28.13
home poll kit 28.13
bug stories 28.14
nt guru 28.14
how multi is media? 28.14
(8 rows affected)
(8 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 29.53
paper dolls 29.53
more paper dolls 29.53
memory manager 29.53
home poll kit 29.53
bug stories 29.55
nt guru 29.55
how multi is media? 29.55
(8 rows affected)
(8 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 31.01
paper dolls 31.01
more paper dolls 31.01
memory manager 31.01
home poll kit 31.01
bug stories 31.03
nt guru 31.03
how multi is media? 31.03
(8 rows affected)
(8 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 32.56
paper dolls 32.56
more paper dolls 32.56
memory manager 32.56
home poll kit 32.56
bug stories 32.58
nt guru 32.58
how multi is media? 32.58
(8 rows affected)
(8 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 34.19
paper dolls 34.19
more paper dolls 34.19
memory manager 34.19
home poll kit 34.19
bug stories 34.21
nt guru 34.21
how multi is media? 34.21
(8 rows affected)
(8 rows affected)
(0 rows affected)
3> /* Note: On second round, setting condition to $39.99, after*/
4> /* inspecting result of price increases. */
5> /* */
6> select avg(price) AvgPrice, max(price) MaxPrice
7> from product
8> where price < 39.99
AvgPrice MaxPrice
------------------------ ------------------------
35.95 36.45
(1 row affected)
1> --
2> -- Important: Will need result later!
3> -- Checked result at this point: Original MaxPrice is $29.99
4> --
5> /* */
6> /* 3b. Increase price in 5% increments; keep maximum =< $40. */
7> /* */
8> while (select max(price) from product
9> where price <35) <= 40
10> begin
11> select prodname, price
12> from product
13> where price < 35
14> update product
15> set price = price * 1.05
16> where price < 35
17> end
(0 rows affected)
1> /* */
2> /* 3c. Take the prices back down again. */
3> /* */
4> while (select max(price) from product
5> where price <39.99) >= 29.99
6> begin
7> select prodname, price
8> from product
9> where price < 39.99
10> update product
11> set price = price * 0.95
12> where price < 39.99
13> end
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 35.90
paper dolls 35.90
more paper dolls 35.90
memory manager 35.90
typing test 36.45
home poll kit 35.90
money master 35.25
blood & guts 36.45
bug stories 35.92
nt guru 35.92
how multi is media? 35.92
(11 rows affected)
(11 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 34.10
paper dolls 34.10
more paper dolls 34.10
memory manager 34.10
typing test 34.63
home poll kit 34.10
money master 33.49
blood & guts 34.63
bug stories 34.12
nt guru 34.12
how multi is media? 34.12
(11 rows affected)
(11 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 32.40
paper dolls 32.40
more paper dolls 32.40
memory manager 32.40
typing test 32.90
home poll kit 32.40
money master 31.81
blood & guts 32.90
bug stories 32.42
nt guru 32.42
how multi is media? 32.42
(11 rows affected)
(11 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
cook & book 30.78
paper dolls 30.78
more paper dolls 30.78
memory manager 30.78
typing test 31.25
home poll kit 30.78
money master 30.22
blood & guts 31.25
bug stories 30.79
nt guru 30.79
how multi is media? 30.79
(11 rows affected)
(11 rows affected)
(0 rows affected)
/* */
/* 4a. Stored proc to make product list for indiv. suppliers */
/* */
/* In case I have it already: */
/* */
1> drop procedure prod_list
1>
2> create procedure prod_list @supno integer
3> as
4> select prodnum, prodname, price
5> from product
6> where suppnum = @spno
7> go
1> prod_list 666
2> go
prodnum prodname price
----------- -------------------- ------------------------
2047 paper dolls 19.99
2049 more paper dolls 19.99
1099 typing test 29.99
1104 teach yourself greek 49.99
1108 blood & guts 29.99
1109 C++ for kids 39.99
(6 rows affected)
(return status = 0)
/* */
2> /* 4b. Stored proc to find employee birth date and age */
3> /* */
4> create procedure birthday @empno char(9)
5> as
6> select empnum, fname, lname,
7> convert(char(12), bday, 101) Birthday,
8> datediff(yy, bday, getdate()) Age
9> from employee
10> where empnum = @empno
1> /* */
2> /* Test: */
3> /* */
4> birthday '222222221'
empnum fname lname Birthday Age
--------- -------------------- -------------------- ------------ -----------
222222221 Bill Bloomfeld 09/09/1979 23
(1 row affected)
(return status = 0)
1> /* */
2> /* 4c. Stored proc. to look up customer credit card number. */
3> /* */
4> create procedure creditcheck @custid char(9)
5> as
6> select @custid = substring(@custid, 1, 3) + '%'
7> select custnum, creditcard
8> from ordermaster
9> where custnum like @custid
1> /* */
2> /* Test: */
3> /* */
4> creditcheck '777777779'
custnum creditcard
--------- --------------------
777777779 7777 7777 6663
777777778 777766661234X
777777778 777766661234X
(3 rows affected)
(return status = 0)
1> /* */
2> creditcheck '111'
custnum creditcard
--------- --------------------
111334444 7777 7777 7777 7777
111333333 00001111222233334444
111222222 1234333331114123
111444444 111112111121111
111223333 1111222233334444
111334444 X7777 7777
(6 rows affected)
(return status = 0)
1> /* */
2> dump tran evelyn with truncate_only