Hi , I want help to solve these tasks: 1- Create a view called \'retail_price\'
ID: 3741731 • Letter: H
Question
Hi , I want help to solve these tasks:
1- Create a view called 'retail_price' that returns the ISBN , retail price and a final column that contains the retail price increased
by 35%.
2. Create a view called 'value_quote' that returns the total retail value (retail*stock) and total cost value (cost*stock) across all
stock.
3. Create a view called 'profits_by_isbn' that returns the book title, isbn for each book along with the difference between the sum of
the cost and retail values across all shipments for each book. The results should be grouped by book title and isbn.
Thanks
TABLE1: books book id| title | author_id | subject_id 7808 |The Shining 4513Dune 4267 2001 A Space Odvssey 4156 | 1866 2001 9 15 15 TABLE2: editions isbn book_id | edition publisher_id | publication type 591957-03-01 h 75 1981-08-01 p 59 1949-03-01 p 039480001x| 1608 | 451160916 | 7808 | 03948007531590 TABLE3: stock isbn costretailstock 0385121679 29.00|36.9565 039480001x | 30.00 | 32.95 I 31 0394900014 | 23.00 23.95 |Explanation / Answer
1) CREATE VIEW retail_price
AS SELECT ISBN, retail, retail*0.35
FROM stock;
2) CREATE VIEW value_quote
AS SELECT retail*stock, cost*stock
FROM stock;
3) CREATE VIEW profits_by_isbn
AS SELECT b.title, s.isbn, diff(sum(s.cost,s.retail))
FROM books b, stocks s GROUPBY title,isbn;