SQL query for Access

Using the week 2 attachment database, I need to come up with the proper
SQL to answer these six questions: I suppose each of the visuals ids the
answer I need the SQL for each?

 

#1. Dollar amount of orders by year

YEAR SumOfcost_line

1999 1261.1

2000 954.950000000001

 

HINT: The function YEAR() will convert a date into its year value, as
in:  SELECT Year([order_date]) AS [YEAR], etc

 

#2. Sales by publisher

publisher_name SumOfcost_line

American Publishing Co. 17.95

Anchor Press 24.95

Atheneum 63.85

Ballantine Books 100.75

Clarendon Press 21.95

Columbia University Press 109.75

D. Appleton & Co. 21.95

Delacorte 71.85

Dial Press 24.95

Dodd, Mead 46.9

Doubleday 138.65

E. R. Burroughs, Inc. 18.95

Franklin Library 55.9

Gregg Press 99.8

Grosset & Dunlap 31.9

Harper 18.95

J. W. Lovell Co. 149.7

James R. Osgood and Co. 91.75

Knopf 245.5

Metheun 31.9

Overlook Press 68.85

Platt & Munk 21.95

Putnam 55.9

R. Marek Publishers 95.8

Random House 65.85

Scribner 87.8

St. Martin's Press 107.75

Tandem 81.8

University of Nebraska Press 83.85

Warner Books 158.65

 

#3 How many books on hand never ordered

SumOfnumber_on_hand

293

 

 

#4 Sum of retail price of books not ordered (the "answer" is $6386.35)

 

 

#5 Find the number of customers who have ordered

    $0-$100 in books

    $100-$200 in books

    $200-$300 in books

    $300-$400 in books

 

    HINT:  100*(Int([SumOfcost_line]/100)) AS X

 

    HINT: Query results:

X CountOfX

0 2

100 4

200 5

300 1

 

#6. Create a crosstab query to show how many copies within an order
a particular title was ordered. For example, Apache Devil was ordered
three times and in each order 1 copy was ordered Or, as another example:
The Bandit of Hell's Bend was found in 1 order, but the order involved 2
copies of the book.

 

title 1 2 5

Apache Devil 3



Aquitaine Progression, The 1



Bandit of Hell's Bend, The

1

Black Unicorn 2



Book of the Beast, The 1



Book of the Dead, The 2



Bourne Identify, The 2



Celebrated Jumping Frog of Calaveras County 1



Child's Garden of Verses, A 4



Clay's Ark 4



Complete Poems of Emily Jane Bronte, The

1

Corsican Brothers, The 2



Count of Monte Cristo, The 1



Cyteen 1



Dragonflight

1

Dragonsinger 1



East of Midnight 1



Faded Sun, Kesrith, The 1



Faded Sun, Shon'jir, The 1



Feast of All Saints, The 1



Floating Opera and The End of the Road, The 1



Gai-Jin 1



Gemini Contenders, The 1



Heavy Time 3



Hellburner 2



Holcroft Covenant, The 2



Jane Eyre 2



Kidnapped 1



Kindred 1



Lasher 4



Letters: A Novel 2



Life on the Mississippi

1

Mind of My Mind 1 1

Out of Time's Abyss 1



People that Time Forgot, The 3



Prince and the Pauper, The 1 1

Pudd'nhead Wilson 1



Rimrunners 1



Rob Roy 1



Sot-Weed Factor, The 2



Strange Case of Dr. Jekyll and Mr. Hyde 1



Survivor 1



Tai-Pan 2



Tale of the Body Thief, The 3



Taltos 3



Tarzan and the Forbidden City 1



Three Musketeers, The 2



Treasure Island 1

1

Vilette 1



Waverly Novels 3



White Dragon, The 2



HYPERLINK ""

HYPERLINK "mailto:metrics@email.phoenix.edu"