Develop the SQL statements to accomplish each of the following 16 queries. Enter each SQL statement...

Question

Develop the SQL statements to accomplish each of the following 16 queries. Enter each SQL statement into the SQL command box in APEX to test the results your SQL statement produces.The 15 queries: 1. List the book code and book title for every book.2. List the name of every publisher located in New York state.3. List the name of every branch that has at least 10 employees.4. List the code and title of every book whose type is HOR and that is paperback.5. List the code, title and price for each book with a price that is greater than $10 but less than $20.6. Customers who are part of a special program get a 15% discount on regular book prices. To determine the discounted prices, list the book code, title, and discounted price of every book. (your calculated column should determine 85% of the current price that is, 100% less a 15% discount.)7. List the book code and title of every book whose type is FIC, MYS, or ART. Use the IN operator in your command.8. Repeat number 7 and list the books in descending order by book code.9. Find out how many book types are available. (Do not include duplicates)10. Calculate the average price for each type of book.11. Find the name of the most expensive book.12. For every book, list the book code, book title, publisher code, and publisher name.13. List the book title for every book of type CS that is published by Best and Furrow.14. Change the number of units on hand to 5 for all books located in branch number 1.15. Insert a new book into the database. The book code is 9700, the title is “Using Microsoft Access 2000”, the publisher is Best and Furrow, the book type is computer science, the price is $19.97, and the book is available only in paperback. The author number is 07, and there are four (4) books on hand in branch number 1. The sequence number is 2.
Continue to Part “B” on the next page in this document.Part “B”. Submit your 15 SQL statement solutions to Blackboard.1. The deliverable for this assignment is a list of each of the 15 actual SQL statements you entered into the SQL command box in APEX and the results produced by the command. 2. Create a Word document. 3. For each of the 15 questions, copy both the SQL statement you entered in the APEX command box and the results produced from that command and paste into the Word document. Be sure to clearly number each SQL statement/results.4. Be sure to include your name, the course name (MMIS630), and the assignment name (SQL Exam) on your Word document.5. Submit your Word document to Blackboard for Exam1.

005_jk-w1xvihi0.docx

Robert · Accepted Answer

Q 1. List the book code and book title for every book. 
SQL Command :> 
 SELECT Book_Code, Book_Title 
FROM Book;  
Result> 
BOOK_CODE BOOK_TITLE 
0180 Shyness  
0189 Kane and Abel  
0200 Stranger  
0378 Dunwich Horror and Others  
079X Smokescreen  
0808 Knockdown  
1351 Cujo  
1382 Marcel Duchamp  
138X Death on the Nile  
2226 Ghost from the Grand Banks  
2281 Prints of the 20th Century  
2766 Prodigal Daughter  
2908 Hymns to the Night  
3350 Higher Creativity  
3743 First Among Equals  
3906 Vortex  
5163 Organ  
5790 Database Systems  
6128 Evil Under the Sun  
6328 Vixen 07  
669X A Guide to SQL  
6908 DOS Essentials  
7405 Night Probe  
7443 Carrie  
7559 Risk  
7947 dBASE Programming  
8092 Magritte  
8720 Castle  
9611 Amerika
Q 2. List the name of every publisher located in New York State. 
SQL Command :> 
SELECT Publisher_Name 
FROM Publisher 
WHERE Publisher_City='New York';
Result> 
PUBLISHER_NAME 
Arcade Publishing  
Bantam Books  
Pocket Books  
Random House  
Rizzoli  
Schoken Books  
Signet  
Thames and Hudson  
W.W. Norton and Co.
Q 3. List the name of every branch that has at least 10 employees. 
SQL Command :> 
SELECT Branch_Name 
FROM Branch 
WHERE Number_Employees >= 10;
Result> 
BRANCH_NAME 
Henrys Downtown  
HenrysBrentwoodl
Q 4. List the code and title of every book whose type is HOR and that is paperback. 
SQL Command :>
SELECT Book_Code, Book_Title 
FROM Book 
WHERE Book_Type='HOR' 
AND Paperback='Y';
Result> 
BOOK_CODE BOOK_TITLE 
1351 Cujo  
7443 Carrie
Q 5.  List the code, title and price for each book with a price that is greater than $10 but less than $20. 
SQL Command :>
SELECT Book_Code, Book_Title, Book_Price 
FROM Book 
WHERE Book_Price BETWEEN 10 and 20;
Result> 
BOOK_CODE BOOK_TITLE BOOK_PRICE 
0378 Dunwich Horror and Others  19.75 
1382 Marcel Duchamp  11.25 
2226 Ghost from the Grand Banks  19.95 
2281 Prints of the 20th Century  13.25 
5163 Organ  16.95 
8720 Castle  12.15 
9611 Amerika  10.95 
Q 6. Customers who are part of a special program get a 15% discount on regular book prices. To 
determine the discounted prices, list the book code, title, and discounted price of every book. (your 
calculated column should determine 85% of the current price that is, 100% less a 15% discount.) 
 
SQL Command :> 
 
SELECT Book_Code, Book_Title, .

Develop the SQL statements to accomplish each of the following 16 queries. Enter each SQL statement into the SQL command box in APEX to test the results your SQL statement produces.The 15 queries:...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment