1> /* SQL/Sybase Programming - In-Class Practice - Oct 7th, 2000 */
2> /* */
3> use pubs2
1> /* */
2> /* List all the stores with ID numbers and addresses. */
3> /* */
4> select stor_id, stor_name, stor_address, city, state, postalcode, country
5> from stores
stor_id stor_name
stor_address city state
postalcode country
------- ----------------------------------------
---------------------------------------- -------------------- -----
---------- ------------
7066 Barnum's
567 Pasadena Ave. Tustin CA
92789 USA
7067 News & Brews
577 First St. Los Gatos CA
96745 USA
7131 Doc-U-Mat: Quality Laundry and Books
24-A Avrogado Way Remulade WA
98014 USA
8042 Bookbeat
679 Carson St. Portland OR
89076 USA
6380 Eric the Read Books
788 Catamaugus Ave. Seattle WA
98056 USA
7896 Fricative Bookshop
89 Madison St. Fremont CA
90019 USA
5023 Thoreau Reading Discount Chain
20435 Walden Expressway Concord MA
01776 USA
(7 rows affected)
1> /* */
2> /* List titles, type, and price of books selling for under $10 */
3> /* */
4> select title, type, price
5> from titles
6> where price < 10
title
type price
--------------------------------------------------------------------------------
------------ ------------------------
You Can Combat Computer Stress!
business 2.99
The Gourmet Microwave
mod_cook 2.99
Life Without Fear
psychology 7.00
Emotional Security: A New Algorithm
psychology 7.99
(4 rows affected)
1> /* */
2> /* Which books have sold 5000 - 20000 copies? How many sold? */
3> /* */
4> select title, type, total_sales
5> from titles
6> where total_sales between 5000 and 20000
title
type total_sales
--------------------------------------------------------------------------------
------------ -----------
You Can Combat Computer Stress!
business 18722
But Is It User Friendly?
popular_comp 8780
Fifty Years in Buckingham Palace Kitchens
trad_cook 15096
(3 rows affected)
1> /* */
2> /* List the publishers using specific column headers. */
3> /* */
4> select pub_id ID, pub_name as "Publisher's name", city as City,
5> State = state
6> from publishers
ID Publisher's name City State
---- ---------------------------------------- -------------------- -----
0736 New Age Books Boston MA
0877 Binnet & Hardley Washington DC
1389 Algodata Infosystems Berkeley CA
(3 rows affected)
1> /* */
2> /* Different statements to find authors not living in MD or MI.*/
3> /* */
4> select au_fname, au_lname, state
5> from authors
6> where state not in ('MD', 'MI')
au_fname au_lname state
-------------------- ---------------------------------------- -----
Johnson White CA
Marjorie Green CA
Cheryl Carson CA
Michael O'Leary CA
Dick Straight CA
Meander Smith KS
Abraham Bennet CA
Ann Dull CA
Burt Gringlesby CA
Chastity Locksley CA
Morningstar Greene TN
Reginald Blotchet-Halls OR
Akiko Yokomoto CA
Michel DeFrance IN
Dirk Stringer CA
Stearns MacFeather CA
Livia Karsen CA
Sheryl Hunter CA
Heather McBadden CA
Anne Ringer UT
Albert Ringer UT
(21 rows affected)
1> /* */
2> select au_fname, au_lname, state
3> from authors
4> where state < 'MD' or state > 'MI'
au_fname au_lname state
-------------------- ---------------------------------------- -----
Johnson White CA
Marjorie Green CA
Cheryl Carson CA
Michael O'Leary CA
Dick Straight CA
Meander Smith KS
Abraham Bennet CA
Ann Dull CA
Burt Gringlesby CA
Chastity Locksley CA
Morningstar Greene TN
Reginald Blotchet-Halls OR
Akiko Yokomoto CA
Michel DeFrance IN
Dirk Stringer CA
Stearns MacFeather CA
Livia Karsen CA
Sheryl Hunter CA
Heather McBadden CA
Anne Ringer UT
Albert Ringer UT
(21 rows affected)
1> /* */
2> select au_fname, au_lname, state
3> from authors
4> where substring(state, 1, 1) != 'M'
au_fname au_lname state
-------------------- ---------------------------------------- -----
Johnson White CA
Marjorie Green CA
Cheryl Carson CA
Michael O'Leary CA
Dick Straight CA
Meander Smith KS
Abraham Bennet CA
Ann Dull CA
Burt Gringlesby CA
Chastity Locksley CA
Morningstar Greene TN
Reginald Blotchet-Halls OR
Akiko Yokomoto CA
Michel DeFrance IN
Dirk Stringer CA
Stearns MacFeather CA
Livia Karsen CA
Sheryl Hunter CA
Heather McBadden CA
Anne Ringer UT
Albert Ringer UT
(21 rows affected)
1> /* */
2> /* Increase advances under $5000 by 50%. Add $ and headers. */
3> /* */
4> select title as Title, '$' + convert(char(7), advance) as "Original advance",
5> '$' + convert(char(7), advance * 1.5) as "Adjusted advance"
6> from titles
7> where advance < 5000
Title
Original advance Adjusted advance
--------------------------------------------------------------------------------
---------------- ----------------
Silicon Valley Gastronomic Treats
$ 0.00 $0.00000
Is Anger the Enemy?
$2275.00 $3412.50
Prolonged Data Deprivation: Four Case Studies
$2000.00 $3000.00
Emotional Security: A New Algorithm
$4000.00 $6000.00
Fifty Years in Buckingham Palace Kitchens
$4000.00 $6000.00
(5 rows affected)
1> /* */
2> /* Create an authors' phone list, formatted, with headers. */
3> /* */
4> select au_lname + ', ' + au_fname as Name, phone as Telephone
5> from authors
Name Telephone
-------------------------------------------------------------- ------------
White, Johnson 408 496-7223
Green, Marjorie 415 986-7020
Carson, Cheryl 415 548-7723
O'Leary, Michael 408 286-2428
Straight, Dick 415 834-2919
Smith, Meander 913 843-0462
Bennet, Abraham 415 658-9932
Dull, Ann 415 836-7128
Gringlesby, Burt 707 938-6445
Locksley, Chastity 415 585-4620
Greene, Morningstar 615 297-2723
Blotchet-Halls, Reginald 503 745-6402
Yokomoto, Akiko 415 935-4228
del Castillo, Innes 615 996-8275
DeFrance, Michel 219 547-9982
Stringer, Dirk 415 843-2991
MacFeather, Stearns 415 354-7128
Karsen, Livia 415 534-9219
Panteley, Sylvia 301 946-8853
Hunter, Sheryl 415 836-7128
McBadden, Heather 707 448-4982
Ringer, Anne 801 826-0752
Ringer, Albert 801 826-0752
(23 rows affected)
1> /* */
2> /* Books (by title and publication date) published in 1986. */
3> /* */
4> select title as Title, pubdate as "Date of Publication"
5> from titles
6> where pubdate like '%1986%'
Title
Date of Publication
--------------------------------------------------------------------------------
--------------------------
The Busy Executive's Database Guide
Jun 12 1986 12:00AM
But Is It User Friendly?
Jun 30 1986 12:00AM
(2 rows affected)