1> /* Advanced SQL Programming Fall 2002*/
2> /* */
3> /* In-class / take-home exercises for Sept. 30th */
4> /* */
5> /* Noticed: most questions require use of my database - use it! */
6> /* */
7> use evelyn
1> /* */
2> /* String manipulation */
3> /* */
4> /* d. List product names using "proper" case. */
5> /* */
6> /* Start with one word: */
7> /* */
8> select name,
9> upper(substring(name, 1, 1)) + substring(name, 2, char_length(name)-1)
10> from product
11> where charindex(" ", name) = 0
name
-------------------- ---------------------
bugbane Bugbane
memory8 Memory8
z_connector Z_connector
(3 rows affected)
1> /* */
2> /* Two words: */
3> /* */
4> select name,
5> upper(substring(name, 1, 1))
6> + substring(name, 2, charindex(" ", name)-1)
7> + upper(substring(name, charindex(" ", name) +1, 1))
8> + substring(name, charindex(" ", name)+2, char_length(name))
9> from product
10> where charindex(" ", name) > 0
name
-------------------- ------------------------------------------
cook & book Cook & book
paper dolls Paper Dolls
more paper dolls More Paper dolls
tax time Tax Time
memory tripler Memory Tripler
memory manager Memory Manager
typing test Typing Test
home poll kit Home Poll kit
star systems Star Systems
money master Money Master
teach yourself greek Teach Yourself greek
mortgage minder Mortgage Minder
blood & guts Blood & guts
C++ for kids C++ For kids
bug stories Bug Stories
nt guru Nt Guru
how multi is media? How Multi is media?
landlord logs Landlord Logs
(18 rows affected)
1> /* */
2> /* 4. Adapted from Internet discussions */
3> /* */
4> /* a. Between with dates */
5> /* (Create a table with dates) */
6> select * from test_dates
recordnum date_test
--------- --------------------------
4 Aug 1 2001 12:00AM
7 Aug 31 2001 4:17PM
9 Aug 30 2001 4:18PM
11 Aug 1 2001 4:18PM
13 Aug 31 2001 12:00AM
(5 rows affected)
1> /* */
2> select * from test_dates
3> where date_test between '8/1/2001' and '8/31/2001'
recordnum date_test
--------- --------------------------
4 Aug 1 2001 12:00AM
9 Aug 30 2001 4:18PM
11 Aug 1 2001 4:18PM
13 Aug 31 2001 12:00AM
(4 rows affected)
1> /* */
2> /* To include all rows from 8/31, include the latest time: */
3> /* */
4> select * from test_dates
5> where date_test between '8/1/2001' and '8/31/2001 23:59:59'
recordnum date_test
--------- --------------------------
4 Aug 1 2001 12:00AM
7 Aug 31 2001 4:17PM
9 Aug 30 2001 4:18PM
11 Aug 1 2001 4:18PM
13 Aug 31 2001 12:00AM
(5 rows affected)
1> /* */
2> /* b. Sort part numbers with mixed alpha and numeric chars. */
3> /* */
4> /* (First create a parts table) */
5> /* */
6> select * from parts
7> order by part_id
part_id
--------
C1
C100
C11
C12
C2
C20
(6 rows affected)
1> /* */
2> select part_id,
3> substring(part_id, 1, 1) as alpha,
4> convert(int,substring(part_id, 2, char_length(part_id))) as numeric
5> from parts
6> order by 2, 3
part_id alpha numeric
-------- ----- -----------
C1 C 1
C2 C 2
C11 C 11
C12 C 12
C20 C 20
C100 C 100
(6 rows affected)
1> /* */
2> /* Beginning approach if alpha portion is variable length: */
3> /* (To be discussed in class - example to be posted by 10/6)
4> /* */
1> /* */
2> /* c. Pull last name from field with whole name +/- initial.*/
3> /* (First create a table with "bad" names.) */
4> /* */
5> select * from badnames
id wholename
----- -------------------------
1 Snow White
2 Prince Charming
3 Donald D. Duck
4 Mickey M. Mouse
(4 rows affected)
1> /* */
2> /* Ignore middle initial: */
3> /* */
4> select substring(wholename, charindex(" ", wholename)+1,
5> char_length(wholename))
6> from badnames
-------------------------
White
Charming
D. Duck
M. Mouse
(4 rows affected)
1> /* */
2> /* All names: */
3> /* */
4> select substring(wholename, charindex(" ", wholename)+1,
5> char_length(wholename)) LastName
6> from badnames
7> where wholename not like '%.%'
8> union
9> select substring(wholename, charindex(".", wholename)+2,
10> char_length(wholename))
11> from badnames
12> where wholename like '%.%'
LastName
-------------------------
Duck
Mouse
White
Charming
(4 rows affected)
1> /* */
2> dump tran evelyn with truncate_only