1> /* In-class exercises, 10/14/2000 */
2> /* */
3> use pubs2
1> /* */
2> /* List titles sorted by first word. */
3> /* */
4> select title
5> from titles
6> order by title
title
--------------------------------------------------------------------------------
But Is It User Friendly?
Computer Phobic and Non-Phobic Individuals: Behavior Variations
Cooking with Computers: Surreptitious Balance Sheets
Emotional Security: A New Algorithm
Fifty Years in Buckingham Palace Kitchens
Is Anger the Enemy?
Life Without Fear
Net Etiquette
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
Prolonged Data Deprivation: Four Case Studies
Secrets of Silicon Valley
Silicon Valley Gastronomic Treats
Straight Talk About Computers
Sushi, Anyone?
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking
You Can Combat Computer Stress!
(18 rows affected)
1> /* */
2> /* List authors alphabetically by last name. */
3> /* */
4> select au_fname, au_lname
5> from authors
6> order by au_lname
au_fname au_lname
-------------------- ----------------------------------------
Abraham Bennet
Reginald Blotchet-Halls
Cheryl Carson
Michel DeFrance
Ann Dull
Marjorie Green
Morningstar Greene
Burt Gringlesby
Sheryl Hunter
Livia Karsen
Chastity Locksley
Stearns MacFeather
Heather McBadden
Michael O'Leary
Sylvia Panteley
Albert Ringer
Anne Ringer
Meander Smith
Dick Straight
Dirk Stringer
Johnson White
Akiko Yokomoto
Innes del Castillo
(23 rows affected)
1> /* */
2> /* List authors by ID in descending order. */
3> /* */
4> select au_id, au_fname, au_lname
5> from authors
6> order by au_id desc
au_id au_fname au_lname
----------- -------------------- ----------------------------------------
998-72-3567 Albert Ringer
899-46-2035 Anne Ringer
893-72-1158 Heather McBadden
846-92-7186 Sheryl Hunter
807-91-6654 Sylvia Panteley
756-30-7391 Livia Karsen
724-80-9391 Stearns MacFeather
724-08-9931 Dirk Stringer
722-51-5454 Michel DeFrance
712-45-1867 Innes del Castillo
672-71-3249 Akiko Yokomoto
648-92-1872 Reginald Blotchet-Halls
527-72-3246 Morningstar Greene
486-29-1786 Chastity Locksley
472-27-2349 Burt Gringlesby
427-17-2319 Ann Dull
409-56-7008 Abraham Bennet
341-22-1782 Meander Smith
274-80-9391 Dick Straight
267-41-2394 Michael O'Leary
238-95-7766 Cheryl Carson
213-46-8915 Marjorie Green
172-32-1176 Johnson White
(23 rows affected)
1> /* */
2> /* List authors sorted by state, last name, and first name. */
3> /* */
4> select au_fname, au_lname, state
5> from authors
6> order by state, au_lname, au_fname
au_fname au_lname state
-------------------- ---------------------------------------- -----
Abraham Bennet CA
Cheryl Carson CA
Ann Dull CA
Marjorie Green CA
Burt Gringlesby CA
Sheryl Hunter CA
Livia Karsen CA
Chastity Locksley CA
Stearns MacFeather CA
Heather McBadden CA
Michael O'Leary CA
Dick Straight CA
Dirk Stringer CA
Johnson White CA
Akiko Yokomoto CA
Michel DeFrance IN
Meander Smith KS
Sylvia Panteley MD
Innes del Castillo MI
Reginald Blotchet-Halls OR
Morningstar Greene TN
Albert Ringer UT
Anne Ringer UT
(23 rows affected)
1> /* */
2> /* How many titles are there in the database? Book types? */
3> /* */
4> select count(distinct title) as "Number of titles",
5> count(distinct type) as "Number of types"
6> from titles
Number of titles Number of types
---------------- ---------------
18 6
(1 row affected)
1> /* */
2> /* Different states with stores, without and with city. */
3> /* */
4> select distinct state
5> from stores
state
-----
CA
MA
OR
WA
(4 rows affected)
1> /* */
2> select distinct state, city
3> from stores
state city
----- --------------------
CA Tustin
CA Fremont
MA Concord
WA Seattle
OR Portland
WA Remulade
CA Los Gatos
(7 rows affected)
1> /* */
2> /* How many different authors? How many author IDs? */
3> /* */
4> select count(distinct au_lname) as CountLastName,
5> count(distinct au_fname + ' ' + au_lname) as CountWholeName,
6> count(distinct au_id) as CountID
7> from authors
CountLastName CountWholeName CountID
------------- -------------- -----------
22 23 23
(1 row affected)
1> /* */
2> /* Average, minimum, and maximum price for all titles. */
3> /* */
4> select avg(price) as AvgPrice, min(price) as MinPrice,
5> max(price) as MaxPrice
6> from titles
AvgPrice MinPrice MaxPrice
------------------------ ------------------------ ------------------------
14.77 2.99 22.95
(1 row affected)
1> /* */
2> /* Total number of books sold to date in each category. */
3> /* */
4> select type, sum(total_sales) TotalSoldByGroup
5> from titles
6> group by type
type TotalSoldByGroup
------------ ----------------
UNDECIDED NULL
business 30788
mod_cook 24278
popular_comp 12875
psychology 9939
trad_cook 19566
(6 rows affected)
1> /* */
2> /* Show titles by date of publication, newest first, and */
3> /* how many years ago each was published. */
4> /* */
5> select title, convert(char(12), pubdate) as Published,
6> datediff(yy, pubdate, getdate()) as YearsAgo
7> from titles
8> order by pubdate desc
title
Published YearsAgo
--------------------------------------------------------------------------------
------------ -----------
Net Etiquette
Sep 15 1999 1
The Psychology of Computer Cooking
Sep 15 1999 1
Computer Phobic and Non-Phobic Individuals: Behavior Variations
Oct 21 1990 10
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
Oct 21 1990 10
Life Without Fear
Oct 5 1990 10
Is Anger the Enemy?
Jun 15 1989 11
Silicon Valley Gastronomic Treats
Jun 9 1989 11
Emotional Security: A New Algorithm
Jun 12 1988 12
Prolonged Data Deprivation: Four Case Studies
Jun 12 1988 12
Cooking with Computers: Surreptitious Balance Sheets
Jun 9 1988 12
Straight Talk About Computers
Jun 22 1987 13
Sushi, Anyone?
Jun 12 1987 13
Secrets of Silicon Valley
Jun 12 1987 13
But Is It User Friendly?
Jun 30 1986 14
The Busy Executive's Database Guide
Jun 12 1986 14
You Can Combat Computer Stress!
Jun 30 1985 15
The Gourmet Microwave
Jun 18 1985 15
Fifty Years in Buckingham Palace Kitchens
Jun 12 1985 15
(18 rows affected)
1> /* */
2> /* Select particular au_lname from authors table. */
3> /* */
4> select au_lname
5> from authors
6> where au_lname in ('Gringlesby', 'Locksley', 'O''Leary', 'Panteley')
au_lname
----------------------------------------
Gringlesby
Locksley
O'Leary
Panteley
(4 rows affected)
1> /* Use pattern-matching with upper() to make patterns alike.*/
2> select au_lname
3> from authors
4> where upper(au_lname) like '%LE%'
au_lname
----------------------------------------
Gringlesby
Locksley
O'Leary
Panteley
(4 rows affected)
1> /* All end in 'y' - use right() function. */
2> select au_lname
3> from authors
4> where right(au_lname, 1) = 'y'
au_lname
----------------------------------------
Gringlesby
Locksley
O'Leary
Panteley
(4 rows affected)
1> /* Use combinination of range and exclusions. */
2> select au_lname
3> from authors
4> where au_lname between 'Gri%' and 'R' and
5> substring(au_lname, 1, 1) not in ('H', 'K', 'M')
au_lname
----------------------------------------
Gringlesby
Locksley
O'Leary
Panteley
(4 rows affected)