1> /* Advanced SQL Programming Fall 2002 */
2> /* Quiz 1, Oct. 7th */
3> /* */
4> /* 1. Day of week for Feb. 29th 2004; months from now. */
5> /* */
6> select datename(dw, 'Feb 29 2004')
------------------------------
Sunday
(1 row affected)
1> select datediff(mm, getdate(), 'Feb 29 2004') MonthsToLeapDay
MonthsToLeapDay
---------------
16
(1 row affected)
1> /* */
2> /* 2. Employeee age at date of hire. */
3> /* Note problem in first query - need to account for not */
4> /* having at least reached birthday at date of hire. */
5> /* Two separate, corrected queries follow. */
6> /* */
7> use evelyn
1> /* */
2> select convert(char(12), lname) LastName,
3> convert(char(10), bday, 101) Birthday,
4> convert(char(10), hired, 101) HireDate,
5> datediff(yy, bday, hired), 'years and ',
6> datediff(mm, bday, hired) - datediff(yy, bday, hired)*12, 'month(s)'
7> from employee
LastName Birthday HireDate
------------ ---------- ---------- ----------- ---------- ----------- --------
Miller 01/01/1980 03/15/1998 18 years and 2 month(s)
Chang 03/15/1950 06/05/1997 47 years and 3 month(s)
archer 05/24/1961 08/25/1996 35 years and 3 month(s)
Menendez 07/16/1971 11/05/1995 24 years and 4 month(s)
Bloomfeld 09/09/1979 03/15/1998 19 years and -6 month(s)
Blake-Pipps 12/12/1965 01/20/1993 28 years and -11 month(s)
(6 rows affected)
1> /* */
2> /* Birthday reached by date of hire. */
3> /* */
4> select convert(char(12), lname) LastName,
5> convert(char(10), bday, 101) Birthday,
6> convert(char(10), hired, 101) HireDate,
7> datediff(yy, bday, hired), 'years and ',
8> datediff(mm, bday, hired) - datediff(yy, bday, hired)*12, 'month(s)'
9> from employee
10> where datepart(mm, hired) > datepart(mm, bday)
LastName Birthday HireDate
------------ ---------- ---------- ----------- ---------- ----------- --------
Miller 01/01/1980 03/15/1998 18 years and 2 month(s)
Chang 03/15/1950 06/05/1997 47 years and 3 month(s)
archer 05/24/1961 08/25/1996 35 years and 3 month(s)
Menendez 07/16/1971 11/05/1995 24 years and 4 month(s)
(4 rows affected)
1> /* */
2> /* Birthday not reached by date of hire. */
3> /* */
4> select convert(char(12), lname) LastName,
5> convert(char(10), bday, 101) Birthday,
6> convert(char(10), hired, 101) HireDate,
7> datediff(yy, bday, hired) - 1, 'years and ',
8> datediff(mm, bday, hired) - (datediff(yy, bday, hired) -1) *12, 'month(s)'
9> from employee
10> where datepart(mm, hired) < datepart(mm, bday)
LastName Birthday HireDate
------------ ---------- ---------- ----------- ---------- ----------- --------
Bloomfeld 09/09/1979 03/15/1998 18 years and 6 month(s)
Blake-Pipps 12/12/1965 01/20/1993 27 years and 1 month(s)
(2 rows affected)
1> /* */
2> /* 3. Price list with 10% increase where price less than $20.*/
3> /* */
4> select prodname, price,
5> convert(money, price * 1.1) NewPrice
6> from product
7> where price < 20
prodname price NewPrice
-------------------- ------------------------ ------------------------
cook & book 19.99 21.99
paper dolls 19.99 21.99
more paper dolls 19.99 21.99
memory manager 19.99 21.99
home poll kit 19.99 21.99
(5 rows affected)
1> /* */
2> /* 4. Customer name corrections (some possibilities) */
3> /* (Note: != or <> both work for "not equals".) */
4> /* */
5> select upper(substring(fname, 1, 1))
6> + substring(fname, 2, char_length(fname)) FirstName,
7> upper(substring(lname, 1, 1))
8> + substring(lname, 2, char_length(lname)) LastName
9> from customer
10> where charindex(" ", fname) != 1
FirstName LastName
--------------------- ---------------------
Geoff lowell McBaird
Ruby Archer
Phillip Aziz
Felipe Le blanc
Kimiko Sato
NULL Deathmask-z
Merit Mokoperto
Pete Peters
Lauren Menendez
LI-REN WONG
(10 rows affected)
1> /* */
2> /* Remove leading space; to change case, modify as above. */
3> /* */
4> select ltrim(fname) FirstName, lname LastName
5> from customer
FirstName LastName
-------------------- --------------------
geoff lowell McBaird
ruby archer
phillip aziz
felipe le blanc
kimiko sato
SAM khandasamy
NULL deathmask-z
merit mokoperto
pete pete rs
Pete Peters
lauren Menendez
LI-REN WONG
(12 rows affected)
1> /* */
2> /* Proper case for customers with first and middle name */
3> /* */
4> select upper(substring(fname, 1, 1))
5> + substring(fname, 2, charindex(" ",fname)-1)
6> + upper(substring(fname, charindex(" ", fname)+1, 1))
7> + substring(fname, charindex(" ", fname)+2, char_length(fname))
8> FirstAndMiddleNames
9> from customer
10> where charindex(" ", fname) > 1
FirstAndMiddleNames
------------------------------------------
Geoff Lowell
(1 row affected)
1> /* */
2> /* Question: Why is " pete pete rs" missing from this result? */
3> /* */
4> /* Additional Comment on Pete Peters */
5> /* Could compare concatenated, upper- or lower-cased names */
6> /* and delete _one_ (not trivial!) if duplicate found. */
7> /* Probably hard to generalize to a real database. */
8> /* */
9> /* 5. Product names and descriptions, proper case. */
10> /* */
11> select upper(substring(prodname, 1, 1)) +
12> substring(prodname, 2, char_length(prodname)) ProductName,
13> upper(substring(description, 1, 1)) +
14> substring(description, 2, char_length(description)) Description
15> from product
16> where description is not null
ProductName Description
--------------------- ---------------------------------------------------
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> /* 6. Formatted customer phone list. */
3> /* */
4> select upper(substring(lname, 1, 1)) +
5> substring(lname, 2, char_length(lname)) + ', ' +
6> upper(substring(fname, 1, 1)) +
7> substring(fname, 2, char_length(fname)) Customer,
8> '(' + areacode + ') ' + substring(phone, 1, 3) + '-' +
9> right(phone, 4) Phone
10> from customer
Customer Phone
-------------------------------------------- --------------
McBaird, Geoff lowell (510) 555-2234
Archer, Ruby (510) 555-1111
Aziz, Phillip (617) 555-1333
Le blanc, Felipe (617) 555-1111
Sato, Kimiko (206) 555-2233
Khandasamy, SAM (212) 223-1234
Deathmask-z, (617) 555-7777
Mokoperto, Merit (617) 555-7777
Rs, pete pete (512) 555-7777
Peters, Pete (800) 555-7777
Menendez, Lauren (917) 123-1235
WONG, LI-REN (301) 123-1235
(12 rows affected)
1> /* */
2> /* 7. Customers from MD or MA (assumes no other "M" states)*/
3> /* (Some formatting added to save space) */
4> /* */
5> select convert(char(20), fname + ' ' + lname),
6> convert(char(25), street),
7> convert(char(25), city + ', ' + state + ' ' + zip)
8> from customer
9> where state like 'M%'
-------------------- ------------------------- -------------------------
phillip aziz 92 Arch St. reading, MA 01867
felipe le blanc 2 Jacob Way #8 reading, MA 01867
deathmask-z Old Foundry Block 2 Boston, MA 02110
merit mokoperto Old Foundry Block 2 Boston, MA 02110
LI-REN WONG 12 Main St. Silver Spring, MD 20906
(5 rows affected)
1> /* */
2> select convert(char(20), fname + ' ' + lname),
3> convert(char(25), street),
4> convert(char(25), city + ', ' + state + ' ' + zip)
5> from customer
6> where state in ('MD', 'MA')
-------------------- ------------------------- -------------------------
phillip aziz 92 Arch St. reading, MA 01867
felipe le blanc 2 Jacob Way #8 reading, MA 01867
deathmask-z Old Foundry Block 2 Boston, MA 02110
merit mokoperto Old Foundry Block 2 Boston, MA 02110
LI-REN WONG 12 Main St. Silver Spring, MD 20906
(5 rows affected)
1> /* */
2> select convert(char(20), fname + ' ' + lname),
3> convert(char(25), street),
4> convert(char(25), city + ', ' + state + ' ' + zip)
5> from customer
6> where state = 'MD' or state = 'MA'
-------------------- ------------------------- -------------------------
phillip aziz 92 Arch St. reading, MA 01867
felipe le blanc 2 Jacob Way #8 reading, MA 01867
deathmask-z Old Foundry Block 2 Boston, MA 02110
merit mokoperto Old Foundry Block 2 Boston, MA 02110
LI-REN WONG 12 Main St. Silver Spring, MD 20906
(5 rows affected)