The following
AUTHOR(AuthorName)
BOOK(ISBN, AuthorName, Title, PublisherName, PublicationYear, RetailPrice)
AuthorName foreign key to AUTHOR
PublisherName foreign key to PUBLISHER
PUBLISHER(PublisherName)
CUSTOMER(CustomerNumber, Name, Address, Phone, Email, CreditCardNumber, ExpiryDate)
ORDER(OrderNumber, CustomerNumber, OrderDate)
CustomerNumber foreign key to CUSTOMER
ORDERDETAIL(OrderNumber, ISBN, QuantitySold, RetailPrice, TotalCost)
OrderNumber foreign key to ORDER
Write SQL code to implement the following separate queries numbered 1 to 4:
1. Find all orders placed in 2018-2019.
2. Report the following information: ISBN, AuthorName, Title, RetailPrice for all books written by
Alexandre Dumas or by Mark Twain, published by ClassicsPublishingCo, with a retail price at least
$24.99.
3. Create a table which shows the value of orders by each customer who actually placed an order
in 2019. Group in order by customer names. (Hint: use an appropriate outer join structure.)
4. Create a query which shows the total book sales revenues received from all orders for each
author. Show the results for each author, starting from the largest total value of books sold to
the smallest. (Hint: use an appropriate outer join structure.)
Step by stepSolved in 2 steps with 2 images
- What does a field represent in a database? Why is it important?arrow_forwardWhy does Access require that you enter a descriptive name when the database is created?arrow_forwardhow to build a bookstore database with Entities for Tables Books This entity will store information about the books in the bookstore, such as title, author, publisher, and publication date. The table could also include additional fields such as genre, language, and the number of pages. Authors This entity will store information about the authors of the books in the bookstore, such as name, date of birth, nationality, and biography. The table could also include additional fields such as gender, education, and awards. Bookstore Members This entity will store information about the bookstore members, such as name, address, phone number, email address, and membership status. The table could also include additional fields such as date of birth, occupation, and membership type.arrow_forward
- The term "Network Database System" is used to describe a kind of database management software that centers on the idea of ownership.arrow_forwardA table schema field record is a record in a database that contains a single piece of information. O keyarrow_forwardWhy should derived attributes not be stored in a database?arrow_forward
- Topic: Database Design What are some concerns about using a date (or date time) as a primary key?arrow_forwardPart A - Database design case study In this part, you need to provide a short description of the application/website for which you will be designing the database (The overview of your own/chosen case study that is related to the assignment topic), outline the basic functions that your database needs to support, identify the data requirements, and business rules.arrow_forwardThere are three types of relationships in a database: identifying, non-identifying, and generic.arrow_forward
- How do you understand the content of a database field? This is significant in the following ways:arrow_forwardIn the context of database transactions, is a property that ensures that once a transaction is committed, its effects are permanent.arrow_forwardIn a relational database, a select operation may reduce the size of a table by removing columns that don't meet a set of criteria.arrow_forward
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill EducationStarting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSONDigital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
- C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSONDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningProgrammable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education