1> /* Result file for take-home/mail-in assignment 10/14/1999 */
2> /* */
3> /* Dates and times */
4>
5> /* Complete current date and time */
6> select getdate()
--------------------------
Oct 23 1999 6:03PM
(1 row affected)
1>
2> /* Date only, year in two-digit format */
3> select convert(char(8), getdate(), 1)
--------
10/23/99
(1 row affected)
1>
2> /* Date only, year in four-digit format */
3> select convert(char(12), getdate())
------------
Oct 23 1999
(1 row affected)
1>
2> /* Time only */
3> select convert(char(8), getdate(), 8)
--------
18:03:55
(1 row affected)
1>
2> /* Date functions: Pick a date and then-- */
3>
4> /* Show complete date */
5> select convert(char(12), 'Jul 1 1971')
------------
Jul 1 1971
(1 row affected)
1>
2> /* Show only the year */
3> select datepart(yy, '7/1/1971')
-----------
1971
(1 row affected)
1>
2> /* Show only the month (datename --> name, datepart --> number)*/
3> select datename(mm, '7/1/1971')
------------------------------
July
(1 row affected)
1>
2> /* Show how many years and how many months have passed since then*/
3> select datediff(yy, '7/1/1971', getdate()) as Years
4> select datediff(mm, '7/1/1971', getdate()) as Months
Years
-----------
28
(1 row affected)
Months
-----------
339
(1 row affected)
1>
2> /* Sample calculation to show years and months in one query */
3> select convert(char(2), datediff(yy, '7/1/1971', getdate()))
4> + ' years and ' +
5> convert(char(2), datediff(mm, '7/1/1971', getdate()) -
6> datediff(yy, '7/1/1971', getdate())*12)
7> + ' months'
----------------------
28 years and 3 months
(1 row affected)
1>
2> /* Show date 100 years from that date */
3> select dateadd(yy, 100, '7/1/1971')
--------------------------
Jul 1 2071 12:00AM
(1 row affected)
1>
2> /* What day of the week is Jan. 1st, 2000? */
3> select datename(dw, 'Jan 1 2000')
------------------------------
Saturday
(1 row affected)
1>
2>
3> /* List authors whose last names are between 6 and 12 letters */
4> /* long, sorted in order of increasing length. */
5>
6> use pubs2
1>
2> select au_lname as Last_name, char_length(au_lname) as Length
3> from authors
4> where char_length(au_lname) between 6 and 12
5> order by Length
Last_name Length
---------------------------------------- -----------
Bennet 6
Carson 6
Greene 6
Hunter 6
Karsen 6
Ringer 6
Ringer 6
O'Leary 7
DeFrance 8
Locksley 8
McBadden 8
Panteley 8
Straight 8
Stringer 8
Yokomoto 8
Gringlesby 10
MacFeather 10
del Castillo 12
(18 rows affected)
1>
2>
3> /* Length of your first name, last name, full name */
4> /* Both datalength and char_length work. */
5>
6> select datalength('Evelyn')
7> select datalength('Stevens')
8> select char_length('Evelyn Stevens')
9> select char_length('Evelyn') + char_length('Stevens')
-----------
6
(1 row affected)
-----------
7
(1 row affected)
-----------
14
(1 row affected)
-----------
13
(1 row affected)
1>
2> /* Names of authors whose first name starts with A, B, or C, */
3> /* in uppercase */
4>
5> select upper(au_fname) as First_name, upper(au_lname) as Last_name
6> from authors
7> where substring(au_fname, 1, 1) in ('A', 'B', 'C')
First_name Last_name
-------------------- ----------------------------------------
ABRAHAM BENNET
CHERYL CARSON
ANN DULL
BURT GRINGLESBY
CHASTITY LOCKSLEY
ALBERT RINGER
ANNE RINGER
AKIKO YOKOMOTO
(8 rows affected)
1>
2> /* "Formatted" list of author names and phone numbers */
3>
4> select (au_fname + ' ' + au_lname + ', ' + phone) as Phone_List
5> from authors
Phone_List
---------------------------------------------------------------------------
Johnson White, 408 496-7223
Marjorie Green, 415 986-7020
Cheryl Carson, 415 548-7723
Michael O'Leary, 408 286-2428
Dick Straight, 415 834-2919
Meander Smith, 913 843-0462
Abraham Bennet, 415 658-9932
Ann Dull, 415 836-7128
Burt Gringlesby, 707 938-6445
Chastity Locksley, 415 585-4620
Morningstar Greene, 615 297-2723
Reginald Blotchet-Halls, 503 745-6402
Akiko Yokomoto, 415 935-4228
Innes del Castillo, 615 996-8275
Michel DeFrance, 219 547-9982
Dirk Stringer, 415 843-2991
Stearns MacFeather, 415 354-7128
Livia Karsen, 415 534-9219
Sylvia Panteley, 301 946-8853
Sheryl Hunter, 415 836-7128
Heather McBadden, 707 448-4982
Anne Ringer, 801 826-0752
Albert Ringer, 801 826-0752
(23 rows affected)
1>
2> /* Sort list of authors by last name and state */
3>
4> select au_lname, state
5> from authors
6> order by au_lname, state
au_lname state
---------------------------------------- -----
Bennet CA
Blotchet-Halls OR
Carson CA
DeFrance IN
Dull CA
Green CA
Greene TN
Gringlesby CA
Hunter CA
Karsen CA
Locksley CA
MacFeather CA
McBadden CA
O'Leary CA
Panteley MD
Ringer UT
Ringer UT
Smith KS
Straight CA
Stringer CA
White CA
Yokomoto CA
del Castillo MI
(23 rows affected)
1>
2> /* Sort list of titles by first word and type */
3>
4> select title, type
5> from titles
6> order by title, type
title
type
--------------------------------------------------------------------------------
------------
But Is It User Friendly?
popular_comp
Computer Phobic and Non-Phobic Individuals: Behavior Variations
psychology
Cooking with Computers: Surreptitious Balance Sheets
business
Emotional Security: A New Algorithm
psychology
Fifty Years in Buckingham Palace Kitchens
trad_cook
Is Anger the Enemy?
psychology
Life Without Fear
psychology
Net Etiquette
popular_comp
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
trad_cook
Prolonged Data Deprivation: Four Case Studies
psychology
Secrets of Silicon Valley
popular_comp
Silicon Valley Gastronomic Treats
mod_cook
Straight Talk About Computers
business
Sushi, Anyone?
trad_cook
The Busy Executive's Database Guide
business
The Gourmet Microwave
mod_cook
The Psychology of Computer Cooking
UNDECIDED
You Can Combat Computer Stress!
business
(18 rows affected)
1>
2> /* Try to show correlation between number of books sold, type */
3> /* and price - for example: */
4>
5> select type, price, total_sales
6> from titles
7> order by type, price
type price total_sales
------------ ------------------------ -----------
UNDECIDED NULL NULL
business 2.99 18722
business 11.95 3876
business 19.99 4095
business 19.99 4095
mod_cook 2.99 22246
mod_cook 19.99 2032
popular_comp NULL NULL
popular_comp 20.00 4095
popular_comp 22.95 8780
psychology 7.00 111
psychology 7.99 3336
psychology 10.95 2045
psychology 19.99 4072
psychology 21.59 375
trad_cook 11.95 15096
trad_cook 14.99 4095
trad_cook 20.95 375
(18 rows affected)
1>
2> /* Results of previous query: for some types of book, the */
3> /* higher the price, the few books sell. (Next command to */
4> /* force this comment to appear in the output file.) */
5>
6> use evelyn