1> /* Advanced SQL Programming Fall 2002*/
2> /* */
3> /* In-class / take-home exercises for Sept. 30th */
4> /* */
5> /* Noticed: most questions use my database */
6> /* */
7> use evelyn
1> /* */
2> /* 1. Date and time manipulation */
3> /* */
4> /* a. Write today's date as mm/dd/yy and mon dd yyyy. */
5> /* */
6> select convert(char(12), getdate(), 1),
7> convert(char(12), getdate(), 109)
------------ ------------
09/23/02 Sep 23 2002
(1 row affected)
1> /* */
2> /* b. What time is it now? */
3> /* */
4> select convert(char(12), getdate(), 8) Curr_Time
Curr_Time
------------
23:53:49
(1 row affected)
1> /* */
2> /* c. How much time has passed since 1/1/2000 in years? in months? */
3> /* */
4> select datediff(yy, '1/1/2000', getdate()) Years,
5> datediff(mm, '1-Jan-2000', getdate()) Months
Years Months
----------- -----------
2 32
(1 row affected)
1> /* */
2> /* d. On what date will an 18 mo. CD come due (term starts today)? */
3> /* */
4> select dateadd(mm, 18, getdate()) DueDate
DueDate
--------------------------
Mar 30 2004 11:53PM
(1 row affected)
1> /* */
2> /* */
3> /* 2. String manipulation */
4> /* */
5> /* a. Properly sort the customers in table customer by last name. */
6> /* (Showing "incorrect" sort order first) */
7> /* */
8> select fname, lname Badly_Sorted_LName
9> from customer
10> order by lname
fname Badly_Sorted_LName
-------------------- --------------------
geoff lowell McBaird
lauren Menendez
Pete Peters
LI-REN WONG
ruby archer
phillip aziz
NULL deathmask-z
SAM khandasamy
felipe le blanc
merit mokoperto
pete pete rs
kimiko sato
(12 rows affected)
1> /* */
2> select fname, lname Good_Sort_LName
3> from customer
4> order by upper(lname)
fname Good_Sort_LName
-------------------- --------------------
ruby archer
phillip aziz
NULL deathmask-z
SAM khandasamy
felipe le blanc
geoff lowell McBaird
lauren Menendez
merit mokoperto
Pete Peters
pete pete rs
kimiko sato
LI-REN WONG
(12 rows affected)
1> /* */
2> /* b. Same as 2a, but by first name. */
3> /* */
4> select fname Badly_Sorted_FName, lname
5> from customer
6> order by fname
Badly_Sorted_FName lname
-------------------- --------------------
NULL deathmask-z
SAM khandasamy
pete pete rs
LI-REN WONG
Pete Peters
felipe le blanc
geoff lowell McBaird
kimiko sato
lauren Menendez
merit mokoperto
phillip aziz
ruby archer
(12 rows affected)
1> /* */
2> select fname Sort_Still_Bad, lname
3> from customer
4> order by lower(fname)
Sort_Still_Bad lname
-------------------- --------------------
NULL deathmask-z
pete pete rs
SAM khandasamy
felipe le blanc
geoff lowell McBaird
kimiko sato
lauren Menendez
LI-REN WONG
merit mokoperto
Pete Peters
phillip aziz
ruby archer
(12 rows affected)
1> /* */
2> select fname Good_Sort_FName, lname
3> from customer
4> order by ltrim(lower(fname))
Good_Sort_FName lname
-------------------- --------------------
NULL deathmask-z
felipe le blanc
geoff lowell McBaird
kimiko sato
lauren Menendez
LI-REN WONG
merit mokoperto
Pete Peters
pete pete rs
phillip aziz
ruby archer
SAM khandasamy
(12 rows affected)
1> /* */
2> /* c. Find hyphenated names; show where hyphen occurs. */
3> /* */
4> select fname, charindex("-", fname) f_dash,
5> lname, charindex("-", lname) l_dash
6> from customer
7> where fname like '%-%' or lname like '%-%'
fname f_dash lname l_dash
-------------------- ----------- -------------------- -----------
NULL 0 deathmask-z 10
LI-REN 3 WONG 0
(2 rows affected)
1> /* Another solution: */
2> select fname, charindex("-", fname) f_dash,
3> lname, charindex("-", lname) l_dash
4> from customer
5> where charindex("-", fname) > 0 or charindex("-", lname) > 0
fname f_dash lname l_dash
-------------------- ----------- -------------------- -----------
NULL 0 deathmask-z 10
LI-REN 3 WONG 0
(2 rows affected)
1> /* */
2> /* Note: Ex. 2d in same file as 4a-4c. */
3> /* */
4> /* 3. Nullif and coalesce functions */
5> /* */
6> /* a. Show product weights as NULL if listed as -1. */
7> /* */
8> select name, weight, nullif(weight, -1) Adj_Weight
9> from product
10> where weight = -1
name weight Adj_Weight
-------------------- --------- ----------
memory tripler -1.00 NULL
memory manager -1.00 NULL
memory8 -1.00 NULL
(3 rows affected)
1> /* */
2> /* b. Show product version as Unknown instead of NULL. */
3> /* */
4> select name, version, coalesce(version, "Unknown") Null_Replaced
5> from product
6> where version is NULL
name version Null_Replaced
-------------------- -------- -------------
tax time NULL Unknown
typing test NULL Unknown
bugbane NULL Unknown
teach yourself greek NULL Unknown
mortgage minder NULL Unknown
blood & guts NULL Unknown
(6 rows affected)
1> /* */
2> dump tran evelyn with truncate_only