(More aggregation with grouping) Download, save, and open the database bookstore‐2000.mdb from the website. This database is a somewhat more complicated retail database than our plumbing database. The price actually charged the customer is the cost_of_ each attribute in the ORDERLINES table, and can be lower than the retail_price attribute in the BOOKS table, reflecting occasional discounts. The database contains a small amount of redundancy, in that cost_line in the ORDERLINES table is always equal to quantity*cost_each. Using WHERE to express joins between tables, write SQL queries to for the following tasks:
A. Show the name of each author, the number of books written by the author (labeled numb_books), the most recent year of publication for such books (labeled most_recent), and their average retail price (labeled avg_price).
B. For each combination of publisher and year after 1985 for which any books were published, show the publisher name, the year, and the number of books published by that publisher in that year (labeled numb_books).
C. For each customer, show their customer number, first name, last name, and the total dollar value of discounts they have received; note that the total discounts received is equal to the sum of quantity*(retail_price – cost_each), or equivalently the sum of quantity*retail_price – cost_line. Note that the total discounts received are zero for most customers. The discounts column should have the heading discounts.
The post write SQL queries to for the following tasks: appeared first on Best Custom Essay Writing Services | EssayBureau.com.
