1> /* In-class assignment 2, due 10/14/1999 */
2>
3> use pubs2
1>
2> /* List all authors names (first and last) and phone numbers. */
3>
4> select au_fname, au_lname, phone
5> from authors
au_fname au_lname phone
-------------------- ---------------------------------------- ------------
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> /* Last name and phone number of authors not living in CA, */
3> /* with column headers. */
4>
5> select au_lname as "Last name", phone as "Telephone number", state
6> from authors
7> where state != 'CA'
Last name Telephone number state
---------------------------------------- ---------------- -----
Smith 913 843-0462 KS
Greene 615 297-2723 TN
Blotchet-Halls 503 745-6402 OR
del Castillo 615 996-8275 MI
DeFrance 219 547-9982 IN
Panteley 301 946-8853 MD
Ringer 801 826-0752 UT
Ringer 801 826-0752 UT
(8 rows affected)
1>
2> /* Book titles in popular computing category */
3>
4> select title, type
5> from titles
6> where type = 'popular_comp'
title
type
--------------------------------------------------------------------------------
------------
But Is It User Friendly?
popular_comp
Secrets of Silicon Valley
popular_comp
Net Etiquette
popular_comp
(3 rows affected)
1>
2> /* Book titles in business category selling for less than $12 */
3>
4> select title, type, price
5> from titles
6> where type = 'business'
7> and price < 12
title
type price
--------------------------------------------------------------------------------
------------ ------------------------
Cooking with Computers: Surreptitious Balance Sheets
business 11.95
You Can Combat Computer Stress!
business 2.99
(2 rows affected)
1>
2> /* Book titles in $5-20 range */
3>
4> select title, price
5> from titles
6> where price between 5 and 20
title
price
--------------------------------------------------------------------------------
------------------------
The Busy Executive's Database Guide
19.99
Cooking with Computers: Surreptitious Balance Sheets
11.95
Straight Talk About Computers
19.99
Silicon Valley Gastronomic Treats
19.99
Secrets of Silicon Valley
20.00
Is Anger the Enemy?
10.95
Life Without Fear
7.00
Prolonged Data Deprivation: Four Case Studies
19.99
Emotional Security: A New Algorithm
7.99
Fifty Years in Buckingham Palace Kitchens
11.95
Sushi, Anyone?
14.99
(11 rows affected)
1>
2> /* Book titles in same range using greater than, less than */
3>
4> select title, price
5> from titles
6> where price > 5 and price < 20
title
price
--------------------------------------------------------------------------------
------------------------
The Busy Executive's Database Guide
19.99
Cooking with Computers: Surreptitious Balance Sheets
11.95
Straight Talk About Computers
19.99
Silicon Valley Gastronomic Treats
19.99
Is Anger the Enemy?
10.95
Life Without Fear
7.00
Prolonged Data Deprivation: Four Case Studies
19.99
Emotional Security: A New Algorithm
7.99
Fifty Years in Buckingham Palace Kitchens
11.95
Sushi, Anyone?
14.99
(10 rows affected)
1>
2> /* Names and addresses of stores located in CA */
3>
4> select stor_name, stor_address, city,state, postalcode
5> from stores
6> where state = 'CA'
stor_name
stor_address city state
postalcode
----------------------------------------
---------------------------------------- -------------------- -----
----------
Barnum's
567 Pasadena Ave. Tustin CA
92789
News & Brews
577 First St. Los Gatos CA
96745
Fricative Bookshop
89 Madison St. Fremont CA
90019
(3 rows affected)
1>
2> /* Names and addresses of authors in KS, MD, or MI */
3>
4> select au_fname, au_lname, address, city, state, postalcode
5> from authors
6> where state in ('KS', 'MD', 'MI')
au_fname au_lname
address city state
postalcode
-------------------- ----------------------------------------
---------------------------------------- -------------------- -----
----------
Meander Smith
10 Mississippi Dr. Lawrence KS
66044
Innes del Castillo
2286 Cram Pl. #86 Ann Arbor MI
48105
Sylvia Panteley
1956 Arlington Pl. Rockville MD
20853
(3 rows affected)
1>
2> /* Find books that have something to do with cooking. */
3> /* Comprehensive statement based on all possibly relevant cols.*/
4> /* Assignment asked for three separate statements - any */
5> /* individual part of this one, or statements using '=' and */
6> /* a suitable word, are acceptable. Results may vary. */
7>
8> select title, type, notes
9> from titles
10> where
11> (title like '%cook%' or type like '%cook%' or notes like '%cook%')
title
type
notes
--------------------------------------------------------------------------------
------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Silicon Valley Gastronomic Treats
mod_cook
Favorite recipes for quick, easy, and elegant meals, tried and tested by people who never have time to eat, let alone cook.
The Gourmet Microwave
mod_cook
Traditional French gourmet recipes adapted for modern microwave cooking.
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
trad_cook
Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend.
Fifty Years in Buckingham Palace Kitchens
trad_cook
More anecdotes from the Queen's favorite cook describing life among English royalty. Recipes, techniques, tender vignettes.
Sushi, Anyone?
trad_cook
Detailed instructions on improving your position in life by learning how to make authentic Japanese sushi in your spare time. 5-10% increase in number of friends per recipe reported from beta test.
(5 rows affected)
1>
2> /* Note that the search is case-sensitive: */
3>
4> select title
5> from titles
6> where title like '%cook%'
title
--------------------------------------------------------------------------------
(0 rows affected)
1>
2> /* in contrast to: */
3>
4> select title
5> from titles
6> where title like '%Cook%'
title
--------------------------------------------------------------------------------
Cooking with Computers: Surreptitious Balance Sheets
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
The Psychology of Computer Cooking
(3 rows affected)