Databases Reference
In-Depth Information
simply write the SQL command to complete each step. Check with your instructor if you are uncertain about which
approach to take.
1.
List the author number and last name for every author.
2.
List the complete Branch table (all rows and all columns).
114
3.
List the name of every publisher located in Boston.
4.
List the name of every publisher not located in Boston.
5.
List the name of every branch that has at least nine employees.
6.
List the book code and title of every book that has the type SFI.
7.
List the book code and title of every book that has the type SFI and that is a paperback.
8.
List the book code and title of every book that has the type SFI or that has the publisher code PE.
9.
List the book code, title, and price for each book with a price that is greater than or equal to $5 but less than or
equal to $10.
10.
List the book code and title of every book that has the type FIC and a price of less than $10.
11.
Customers that are part of a special program get a 15% discount off regular book prices. To determine the dis-
counted prices, list the book code, title, and discounted price of every book. (Your calculated column should cal-
culate 85% of the current price, which is 100% less a 15% discount.)
12.
List the book code and title of every book that has the type SFI, HOR, or ART.
13.
List the book code, title, and publisher code for all books. Sort the results by title within publisher code.
14.
How many books have the type SFI?
15.
Calculate the average price for each type of book.
16.
For every book, list the book code, book title, publisher code, and publisher name.
17.
For every book published by Taunton Press, list the book title and book price.
18.
List the book title and book code for every book published by Putnam Publishing Group that has a book price
greater than $15.
19.
Create a new table named Fiction using the data in the BookCode, Title, PublisherCode, and Price columns in
the Book table for those books that have the type FIC.
20.
Use an update query to change the price of any book in the Fiction table with a current price of $14.00 to $14.50.
21.
Use a delete query to delete all books in the Fiction table that have the publisher code VB.
In the following exercises, you will use the data in the Alexamara Marina Group database shown in Figures 1-20
through 1-24 in Chapter 1. (If you use a computer to complete these exercises, use a copy of the Alexamara Marina
Group database so you will still have the original data when you complete Chapter 4.) In each step, use SQL to obtain
the desired results. You can use the query feature in a DBMS to complete the exercises using a computer, or you can
simply write the SQL command to complete each step. Check with your instructor if you are uncertain about which
approach to take.
1.
List the owner number, last name, and first name of every boat owner.
2.
List the complete Marina table (all rows and all columns).
3.
List the last name and first name of every owner located in Bowton.
4.
List the last name and first name of every owner not located in Bowton.
5.
List the marina number and slip number for every slip whose length is equal to or less than 30 feet.
6.
List the marina number and slip number for every boat with the type Dolphin 28.
7.
List the slip number for every boat with the type Dolphin 28 that is located in marina 1.
8.
List the boat name for each boat located in a slip whose length is between 25 and 30 feet.
9.
List the slip number for every slip in marina 1 whose annual rental fee is less than $3,000.
