[Solved]Question 1 Henry Books Database Problem 3 P126 5 Points Question 2 Henry Books Database Pr Q37079808
QUESTION 1
Henry Books Database: Problem 3 (p.126)
5 points
QUESTION 2
Henry Books Database: Problem 16 (p.126)
5 points
QUESTION 3
Henry Books Database: Problem 20 (p.126)
5 points
QUESTION 4
Henry Books Database: Problem 8 (p.126)
5 points
QUESTION 5
Premiere Products Database: Problem 17 (p.125)
5 points
QUESTION 6
Henry Books Database: Problem 17 (p.126)
5 points
QUESTION 7
Alexamara Marina Group Database: Problem 2 (p.127)
5 points
QUESTION 8
Henry Books Database: Problem 4 (p.126)
5 points
QUESTION 9
Premiere Products Database: Problem 14 (p.125)
5 points
QUESTION 10
Henry Books Database: Problem 5 (p.126)
5 points
QUESTION 11
Alexamara Marina Group Database: Problem 6 (p.127)
5 points
QUESTION 12
Henry Books Database: Problem 23 (p.126)
5 points
QUESTION 13
Henry Books Database: Problem 10 (p.126)
5 points
QUESTION 14
Alexamara Marina Group Database: Problem 12 (p.127)
5 points
QUESTION 15
Premiere Products Database: Problem 19 (p.125)
5 points
QUESTION 16
Henry Books Database: Problem 21 (p.126)
5 points
QUESTION 17
Alexamara Marina Group Database: Problem 5 (p.127)
5 points
QUESTION 18
Premiere Products Database: Problem 20 (p.125)
5 points
QUESTION 19
Premiere Products Database: Problem 5 (p.125)
5 points
QUESTION 20
Henry Books Database: Problem 7 (p.126)
Exercises Premiere Products and the Premiere Products database (see Figure 1-2 in Chapter 1) to complete the MySQlse Use the notes at the end of Chapter 3 to print your output if directed to do so Use by your instructor 125 List the part number, description, and price for all parts. 1. tall rows and columns for the complete ORDERS table. 2. List List the names of customers with credit limits of $7,500 or less. 3. order number for each order placed by customer number 148 on 10/20/2007.(Hint he DATE data type in Figure 3-11 in Chapter 3.) ed by sales rep 35 or sales rep 65. number 4. List the If you need help, use the discussion of t 5. List . List the part number and part description of each part that is not in item class SG. . List the part number, description, and number of units on hand for each part that has the number and name of each customer represent between 10 and 25 units on hand, including both 10 and 25. Do this two ways. B. List the part number, part description, and on-hand value (units on hand unit price) of each part in item class AP. (On-hand value is really units on hand cost, but there is no COST col- umn in the PART table.) Assign the name ON HAND_VALUE to the computation. List the part number, part description, and on-hand value for each part whose on-hand value is at least $7,500. Assign the name ON_ HAND_VALUE to the computation. Use the IN operator to list the part number and part description of each part in item class AP or SG. 9. o. 11. Find the number and name of each customer whose name begins with the letter “K 12. List all details about all parts. Order the output by part description. 13. List all details about all parts. Order the output by part number within item class. (That is, order the output by item class and then by part number.) How many customers have balances that are more than their credit limits? Find the total of the balances for all customers represented by sales rep 65 with balances that are less than their credit limits 14. 15. 6. List the part number, part description, and on-hand value of each part whose number of units on hand is more than the average number of units on hand for all parts. (Hint: Use a subquery.) 7. What is the price of the most expensive part in the database? 8. What is the part number, description, and price of the most expensive part in the database? (Hint: Use a subquery.) List the sum of the balances of all customers for each sales rep. Order and group the results by sales rep number. List the sum of the balances of all customers for each sales rep, but restrict the output to hose sales reps for which the sum is more than $10,000. ist the part number of any part with an unknown description. Henry Books Use MySQL and the Henry Books database (Figures 1-4 through 1-7 in Ch the following exercises. Use the notes at the end of Chapter 3 to print you do so by your instructor . List the book code and book title of each book. 2. List the complete PUBLISHER table 3. List the name of each publisher located in Boston. 4. List the name of each publisher not located in Boston 5. List the name of each branch that has at least nine employees. 6. List the book code and book title of each book that has the type SFI. Chapter 1) to complete 126 List the book code and book title of each book that has the type SFI and is in List the book code and book title of each book that has the type SFI or is publis publisher with the publisher code SC. List the book code, book title, and price of each book with a price between $20 and $30 List the book code and book title of each book that has the type MYS and a price of less than $20. paperba ack 7. 8. hed by the 9. 10. Customers who are part of a special program get a 10% discount off regular book prices. List the book code, book title, and discounted price of each book. Use DISCOUNTED_PRICE as the name for the computed column, which should calculate 909 of the current price, that is, 100% less a 10% discount. 11. 12. Find the name of each publisher containing the word “and.” (Hint: Be sure that your query selects only those publishers that contain the word “and” and not those that contain the le ters “and” in the middle of a word. For example, your query should select the publisher named “Farrar Straus and Giroux,” but should not select the publisher named “Random House.”) 13. List the book code and book title of each book that has the type SFI, MYS, or A T. Use th IN operator in your command. Repeat Exercise 13, but also list the books in alphabetical order by title. 14. Repeat Exercise 13, but also include the price, and list the books in descending order price. Within a group of books having the same price, further 15. order the books by title. 16. Display the list of book types in the database. List each book type only once. 17. How many books have the type SFI? 18. For each type of book, list the type and the average price 19. Repeat Exercise 18, but consider only paperback books 20. Repeat Exercise 18, but consider only paperback books for those types for w age price is more than $10. 21. What is the most expensive book in the database? 22. What are the title(s) and price(s) of the least expensive book(s) in the 23. How many employees does Henry Books have? Mexamara Marina Group database (Figures 1-8 through 1-12 in Chapter 1) er number, last name, and first name of every boat owner. name and first name of every owner not located in Bowton. amara Marina Group Alex S. Use the notes at the end of Chapter 3 to print your output if ete 0 ed to do so by your instructor the complete MARINA table (all rows and all columns) 127 st the last name and first name of every owner located in Bowton. 4 List the last 5. List the marina firs a a number and slip number for every slip whose length is equal to or less than 30 feet ist the marina number and slip number for every boat with the type D number for every boat with the type Dolphin 28 that is located in marina 1. 7. List the slip nur 8 List the boat name for each boat located in a slip whose length is between 25 and 30 feet. the slip number for every slip in marina 1 whose rental fee is less than $3,000. List is billed at the rate of $60 per hour. List the slip ID, category number, estimated hours and estimated labor cost for every service request. To obtain the estimated labor cost, mul- the estimated hours by 60. Use the column name ESTIMATED_COST for the esti- tiply mated labor cost. 1. List the marina number and slip number for all slips containing a boat with the type Sprite 2 List the marina number, slip number, and boat name for all boats. Sort the results by boat . How many Dolphin 28 boats are stored at both marinas? 4000, Sprite 3000, or Ray 4025 name within marina number. Calculate the total rental fees Alexamara receives each year based on the length of the slip. . 4. Add the orders shown in Figure 2-1 to the ORDERS table. 5. Add the part data shown in Figure 2-1 to the PART table. 6. Add the order line information shown in Figure 2-1 to the ORDE Henry Books Use MySQL and Figures 1-4 through 1-7 in Chapter 1 to complete th 1. Use the appropriate CREATE TABLE commands to create the tabl the tables in the Henry Books database. The information you need appears in Figure 3-28 Add the branch information shown in Figure 1-4 (in Chapter 1)t 2. INSERT commands. 3. Add the publisher information shown in Figure 1-4 to the PUBLISHER table 4. Add the author information shown in Figure 1-5 to the AUTHOR table. 5. Add the book information shown in Figure 1-6 to the BOOK table. In the P umn, enter Y when the entry in Figure 1-6 is Yes and N when the entry is N 6. Add the author and book information shown in Figure 1-7 to the WROTE table 7. Add the inventory information shown in Figure 1-7 to the INVENTORY table. BRANCH Nulls allowed? Description Decimal places Length Type Column Branch number (primary key) Branch name Branch location Number of employces BRANCI NUM BRANCH NAME BRANCH LOCATION CHAR NUM EMPLOYEES DECIMAL2 DECIMAL 2 CHAR 50 50 PUBLISHER Column Type PUBLISIHER CODE CIHAR PUBLISHER-NAME |CHAR CHAR LengthDecimal Nulls allowed? Description places 0 Publisher code (primary key) Publisher name Publisher city 25 CITY 20 FIGURE 3-28 Table layouts for the Henry Books database AUTHOR Type places allowed? DECIMAL 2 No AUTHOR NUM (primary key) Author last name Author first name CIHAR AUTHOR LAST UTHOR FIRST 12 CHAR 10 BOOK Length Decimal places Nulls allowed? Description CHAR BOOK CODE Book code (primary key) Book title Publisher code CHAR TITLE PUBLISHER CODE CHAR TYPE PRICE 40 CIHAR DECIMAL 4 CHAR Book price PAPERBACK Paperback (Y. N) WROTE Column уре Decimal places Nulls allowed? Description CIHAR BOOK CODE No Book code (primary key) uthor number (primary key) Sequence number AUTHOR NUM DECIMAL 2 No DECIMAL 1 SEQUENCIE INVENTORY Length Column Type Decimal places Nulls allowed? Description BOOK CODE RANCH NUM ON HAND No Book code (primary key Branch number (primary key) Units on hand CHAR No DECIMAL 2 DECIMAL 2 FIGURE 3-28 Table layouts for the Henry Books database (continued) OWNER Decimal places Nulls allowed? No Length Description Type Column Owner number CHAR OWNER NUM (primary key) Owner last name Owner first name Owner street address Owner city Owner state Owner zip code 50 20 15 15 CHAR CHAR CHAR CHAR CHAR CHAR LAST NAME FIRST NAME ADDRESS CITY STATE ZiP MARINA SLIP Type Length Decimal places 0 Nulls allowed? Description Column DECIMAL 4 CHAR CHAR SLIP ID MARINA NUM SLIP NUM Slip ID (primary key) Marina number Slip number in 4 the marina Length of slip (in feet) Annual rental fee for the slip Name of boat currently in the slip Type of boat currently in the slip Number of boat owner renting the slip DECIMAL 4 LENGTH DECIMAL 8 RENTAL FEE BOAT NAME BOAT TYPE OWNER NUM CIHAR 50 CHAR 50 CIHAR SERVICECATEGORY – Column Type Length Decimal places Nulls allowed? Description CATEGORY NUM DECIMAL 4 Category number (primary key) CATEGORY DESCRIPTION CHAR 255 Categor description FIGURE 3-29 Table layouts for the Alexamara Marina Group database (continued) Show transcribed image text Exercises Premiere Products and the Premiere Products database (see Figure 1-2 in Chapter 1) to complete the MySQlse Use the notes at the end of Chapter 3 to print your output if directed to do so Use by your instructor 125 List the part number, description, and price for all parts. 1. tall rows and columns for the complete ORDERS table. 2. List List the names of customers with credit limits of $7,500 or less. 3. order number for each order placed by customer number 148 on 10/20/2007.(Hint he DATE data type in Figure 3-11 in Chapter 3.) ed by sales rep 35 or sales rep 65. number 4. List the If you need help, use the discussion of t 5. List . List the part number and part description of each part that is not in item class SG. . List the part number, description, and number of units on hand for each part that has the number and name of each customer represent between 10 and 25 units on hand, including both 10 and 25. Do this two ways. B. List the part number, part description, and on-hand value (units on hand unit price) of each part in item class AP. (On-hand value is really units on hand cost, but there is no COST col- umn in the PART table.) Assign the name ON HAND_VALUE to the computation. List the part number, part description, and on-hand value for each part whose on-hand value is at least $7,500. Assign the name ON_ HAND_VALUE to the computation. Use the IN operator to list the part number and part description of each part in item class AP or SG. 9. o. 11. Find the number and name of each customer whose name begins with the letter “K 12. List all details about all parts. Order the output by part description. 13. List all details about all parts. Order the output by part number within item class. (That is, order the output by item class and then by part number.) How many customers have balances that are more than their credit limits? Find the total of the balances for all customers represented by sales rep 65 with balances that are less than their credit limits 14. 15. 6. List the part number, part description, and on-hand value of each part whose number of units on hand is more than the average number of units on hand for all parts. (Hint: Use a subquery.) 7. What is the price of the most expensive part in the database? 8. What is the part number, description, and price of the most expensive part in the database? (Hint: Use a subquery.) List the sum of the balances of all customers for each sales rep. Order and group the results by sales rep number. List the sum of the balances of all customers for each sales rep, but restrict the output to hose sales reps for which the sum is more than $10,000. ist the part number of any part with an unknown description.
Henry Books Use MySQL and the Henry Books database (Figures 1-4 through 1-7 in Ch the following exercises. Use the notes at the end of Chapter 3 to print you do so by your instructor . List the book code and book title of each book. 2. List the complete PUBLISHER table 3. List the name of each publisher located in Boston. 4. List the name of each publisher not located in Boston 5. List the name of each branch that has at least nine employees. 6. List the book code and book title of each book that has the type SFI. Chapter 1) to complete 126 List the book code and book title of each book that has the type SFI and is in List the book code and book title of each book that has the type SFI or is publis publisher with the publisher code SC. List the book code, book title, and price of each book with a price between $20 and $30 List the book code and book title of each book that has the type MYS and a price of less than $20. paperba ack 7. 8. hed by the 9. 10. Customers who are part of a special program get a 10% discount off regular book prices. List the book code, book title, and discounted price of each book. Use DISCOUNTED_PRICE as the name for the computed column, which should calculate 909 of the current price, that is, 100% less a 10% discount. 11. 12. Find the name of each publisher containing the word “and.” (Hint: Be sure that your query selects only those publishers that contain the word “and” and not those that contain the le ters “and” in the middle of a word. For example, your query should select the publisher named “Farrar Straus and Giroux,” but should not select the publisher named “Random House.”) 13. List the book code and book title of each book that has the type SFI, MYS, or A T. Use th IN operator in your command. Repeat Exercise 13, but also list the books in alphabetical order by title. 14. Repeat Exercise 13, but also include the price, and list the books in descending order price. Within a group of books having the same price, further 15. order the books by title. 16. Display the list of book types in the database. List each book type only once. 17. How many books have the type SFI? 18. For each type of book, list the type and the average price 19. Repeat Exercise 18, but consider only paperback books 20. Repeat Exercise 18, but consider only paperback books for those types for w age price is more than $10. 21. What is the most expensive book in the database? 22. What are the title(s) and price(s) of the least expensive book(s) in the 23. How many employees does Henry Books have?
Mexamara Marina Group database (Figures 1-8 through 1-12 in Chapter 1) er number, last name, and first name of every boat owner. name and first name of every owner not located in Bowton. amara Marina Group Alex S. Use the notes at the end of Chapter 3 to print your output if ete 0 ed to do so by your instructor the complete MARINA table (all rows and all columns) 127 st the last name and first name of every owner located in Bowton. 4 List the last 5. List the marina firs a a number and slip number for every slip whose length is equal to or less than 30 feet ist the marina number and slip number for every boat with the type D number for every boat with the type Dolphin 28 that is located in marina 1. 7. List the slip nur 8 List the boat name for each boat located in a slip whose length is between 25 and 30 feet. the slip number for every slip in marina 1 whose rental fee is less than $3,000. List is billed at the rate of $60 per hour. List the slip ID, category number, estimated hours and estimated labor cost for every service request. To obtain the estimated labor cost, mul- the estimated hours by 60. Use the column name ESTIMATED_COST for the esti- tiply mated labor cost. 1. List the marina number and slip number for all slips containing a boat with the type Sprite 2 List the marina number, slip number, and boat name for all boats. Sort the results by boat . How many Dolphin 28 boats are stored at both marinas? 4000, Sprite 3000, or Ray 4025 name within marina number. Calculate the total rental fees Alexamara receives each year based on the length of the slip. .
4. Add the orders shown in Figure 2-1 to the ORDERS table. 5. Add the part data shown in Figure 2-1 to the PART table. 6. Add the order line information shown in Figure 2-1 to the ORDE Henry Books Use MySQL and Figures 1-4 through 1-7 in Chapter 1 to complete th 1. Use the appropriate CREATE TABLE commands to create the tabl the tables in the Henry Books database. The information you need appears in Figure 3-28 Add the branch information shown in Figure 1-4 (in Chapter 1)t 2. INSERT commands. 3. Add the publisher information shown in Figure 1-4 to the PUBLISHER table 4. Add the author information shown in Figure 1-5 to the AUTHOR table. 5. Add the book information shown in Figure 1-6 to the BOOK table. In the P umn, enter Y when the entry in Figure 1-6 is Yes and N when the entry is N 6. Add the author and book information shown in Figure 1-7 to the WROTE table 7. Add the inventory information shown in Figure 1-7 to the INVENTORY table. BRANCH Nulls allowed? Description Decimal places Length Type Column Branch number (primary key) Branch name Branch location Number of employces BRANCI NUM BRANCH NAME BRANCH LOCATION CHAR NUM EMPLOYEES DECIMAL2 DECIMAL 2 CHAR 50 50 PUBLISHER Column Type PUBLISIHER CODE CIHAR PUBLISHER-NAME |CHAR CHAR LengthDecimal Nulls allowed? Description places 0 Publisher code (primary key) Publisher name Publisher city 25 CITY 20 FIGURE 3-28 Table layouts for the Henry Books database
AUTHOR Type places allowed? DECIMAL 2 No AUTHOR NUM (primary key) Author last name Author first name CIHAR AUTHOR LAST UTHOR FIRST 12 CHAR 10 BOOK Length Decimal places Nulls allowed? Description CHAR BOOK CODE Book code (primary key) Book title Publisher code CHAR TITLE PUBLISHER CODE CHAR TYPE PRICE 40 CIHAR DECIMAL 4 CHAR Book price PAPERBACK Paperback (Y. N) WROTE Column уре Decimal places Nulls allowed? Description CIHAR BOOK CODE No Book code (primary key) uthor number (primary key) Sequence number AUTHOR NUM DECIMAL 2 No DECIMAL 1 SEQUENCIE INVENTORY Length Column Type Decimal places Nulls allowed? Description BOOK CODE RANCH NUM ON HAND No Book code (primary key Branch number (primary key) Units on hand CHAR No DECIMAL 2 DECIMAL 2 FIGURE 3-28 Table layouts for the Henry Books database (continued)
OWNER Decimal places Nulls allowed? No Length Description Type Column Owner number CHAR OWNER NUM (primary key) Owner last name Owner first name Owner street address Owner city Owner state Owner zip code 50 20 15 15 CHAR CHAR CHAR CHAR CHAR CHAR LAST NAME FIRST NAME ADDRESS CITY STATE ZiP MARINA SLIP Type Length Decimal places 0 Nulls allowed? Description Column DECIMAL 4 CHAR CHAR SLIP ID MARINA NUM SLIP NUM Slip ID (primary key) Marina number Slip number in 4 the marina Length of slip (in feet) Annual rental fee for the slip Name of boat currently in the slip Type of boat currently in the slip Number of boat owner renting the slip DECIMAL 4 LENGTH DECIMAL 8 RENTAL FEE BOAT NAME BOAT TYPE OWNER NUM CIHAR 50 CHAR 50 CIHAR SERVICECATEGORY – Column Type Length Decimal places Nulls allowed? Description CATEGORY NUM DECIMAL 4 Category number (primary key) CATEGORY DESCRIPTION CHAR 255 Categor description FIGURE 3-29 Table layouts for the Alexamara Marina Group database (continued)
Expert Answer
Answer to QUESTION 1 Henry Books Database: Problem 3 (p.126) 5 points QUESTION 2 Henry Books Database: Problem 16 (p.126) 5 point… . . .
OR
