1> /* Advanced SQL Programming Fall 2001*/
2> /* Quiz1, Oct. 20th, 2001 */
3> /* */
4> use evelyn
1> /* */
2> /* List products in the $20-50 range with their suppliers. */
3> /* */
4> select p.name, price, s.name
5> from product p, supplier s
6> where p.suplrnum = s.suplrnum
7> and price between 20 and 50
name price name
-------------------- ------------------------ --------------------
tax time 49.99 Hi Finance!
typing test 29.99 Above Average Arts
star systems 39.99 TrendMaster
bugbane 49.00 Soft Stuff
money master 29.00 Hi Finance!
teach yourself greek 49.99 Above Average Arts
mortgage minder 39.99 Hi Finance!
blood & guts 29.99 Above Average Arts
C++ for kids 39.99 Above Average Arts
bug stories 20.00 Emu Sister Prdctns
nt guru 20.00 Emu Sister Prdctns
how multi is media? 20.00 Emu Sister Prdctns
(12 rows affected)
1> /* */
2> /* Shipping dates for products ordered, as mm/dd/yyyy, or 'TBS'. */
3> /* */
4> /* With order number: */
5> /* */
6> select ordnum, name,
7> coalesce(convert(char(15), shipdate, 101), 'To be shipped')
8> from orderdetail d, product p
9> where d.prodnum = p.prodnum
10> order by name
ordnum name
----------- -------------------- ---------------
94 blood & guts To be shipped
95 blood & guts To be shipped
84 bug stories To be shipped
95 bug stories To be shipped
87 bugbane 01/04/1999
86 cook & book To be shipped
87 cook & book 01/05/1999
86 home poll kit 01/05/1999
87 home poll kit 01/05/1999
93 home poll kit To be shipped
95 home poll kit To be shipped
90 landlord logs 01/02/1999
91 memory tripler 01/03/1999
85 memory8 01/02/1999
87 memory8 To be shipped
86 money master 01/05/1999
87 money master To be shipped
94 money master 01/05/1999
95 money master 01/05/1999
91 mortgage minder 01/05/1999
81 nt guru To be shipped
99 paper dolls To be shipped
81 tax time 01/01/1999
84 tax time 01/05/1999
85 tax time 01/05/1999
89 tax time 01/04/1999
92 tax time To be shipped
93 tax time 01/05/1999
99 tax time To be shipped
84 typing test To be shipped
81 z_connector 01/05/1999
87 z_connector To be shipped
(32 rows affected)
1> /* */
2> /* Without order number: */
3> /* */
4> select distinct name,
5> coalesce(convert(char(15), shipdate, 101), 'To be shipped')
6> from orderdetail d, product p
7> where d.prodnum = p.prodnum
8> order by name
name
-------------------- ---------------
blood & guts To be shipped
bug stories To be shipped
bugbane 01/04/1999
cook & book 01/05/1999
cook & book To be shipped
home poll kit 01/05/1999
home poll kit To be shipped
landlord logs 01/02/1999
memory tripler 01/03/1999
memory8 01/02/1999
memory8 To be shipped
money master 01/05/1999
money master To be shipped
mortgage minder 01/05/1999
nt guru To be shipped
paper dolls To be shipped
tax time 01/01/1999
tax time 01/04/1999
tax time 01/05/1999
tax time To be shipped
typing test To be shipped
z_connector 01/05/1999
z_connector To be shipped
(23 rows affected)
1> /* */
2> /* Product names and desc. if any; 1st letter of desc. uppercase.*/
3> /* */
4> select name, upper(substring(description, 1, 1)) +
5> substring(description, 2, char_length(description))
6> from product
7> where description is not null
name
-------------------- ---------------------------------------------------
cook & book Record your recipes
paper dolls Create & dress dolls
more paper dolls Create & dress dolls
tax time 1995 edition
memory tripler 50% or more
home poll kit Take the pulse of america
star systems Scientific horoscopes
memory8 8 Meg mem
money master Pers checking
mortgage minder Know where you stand
(10 rows affected)
1> /* */
2> /* Feb. 29, 2004: day of week, months from now. */
3> /* */
4> select datename(dw, '2/29/2004')
------------------------------
Sunday
(1 row affected)
1> /* */
2> select datediff(mm, getdate(), '2/29/2004')
-----------
28
(1 row affected)
1> /* */
2> /* Suppliers whose names start with the same letter. */
3> /* */
4> select s1.name
5> from supplier s1, supplier s2
6> where substring(s1.name, 1, 1) = substring(s2.name, 1, 1)
7> and s1.name <> s2.name
name
--------------------
Total Recall
TrendMaster
(2 rows affected)
1> /* */
2> dump tran evelyn with no_log