Please write the SQL code in SQL Developer to complete the tasks below. Then cli
ID: 3919609 • Letter: P
Question
Please write the SQL code in SQL Developer to complete the tasks below. Then click on Write Submission and copy and paste the code into the submission box. This assignment should be completed no later than August 2nd
Create a view called INVENTORY that contains the ISBN, title, wholesale cost, discount, retail price, profit, and category from the books table. Calculate the profit as retail-cost.
Write a query that lists the ISBN and title of the books in the fitness category from the INVENTORY view.
Write a query that lists the five least expensive books, based on retail price, using the INVENTORY view. Show the title, retail price, and profit.
Create a view called ORDER_TOTALS that contains the customer first name and last name, order number, and order total for each order. Format the order total with dollars and cents.
Write a query that shows the customer name, order number, and total for the largest order in the ORDER_TOTALS view.
Explanation / Answer
'books' Table Schema DDL:-
CREATE TABLE `books` (
`ISBN` int(11) NOT NULL,
`title` varchar(256) NOT NULL,
`wholesale_cost` int(11) DEFAULT NULL,
`discount` int(11) DEFAULT NULL,
`retail_price` int(11) DEFAULT NULL,
`profit` int(11) DEFAULT NULL,
`category` varchar(256) DEFAULT NULL,
PRIMARY KEY (`ISBN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
------------------------------
INVENTORY View on books table:
CREATE
VIEW `INVENTORY` AS
SELECT
`books`.`ISBN` AS `ISBN`,
`books`.`title` AS `title`,
`books`.`wholesale_cost` AS `wholesale_cost`,
`books`.`discount` AS `discount`,
`books`.`retail_price` AS `retail_price`,
`books`.`profit` AS `profit`,
`books`.`category` AS `category`
FROM
`books`;
---------------------------------------------------------------------------------------
Query For ISBN and Title of fitness category:-
SELECT ISBN, title FROM INVENTORY WHERE category = 'fitness';
---------------------------------------------------------------------
Query for 5 least retail price book:-
SELECT title, retail_price, profit FROM INVENTORY order by retail_price ASC LIMIT 5;
-----------------------------------------------------------------------------------
'orders' table Schema (DDL) :-
CREATE TABLE `orders` (
`order_number` int(11) NOT NULL,
`customer_first_name` varchar(256) DEFAULT NULL,
`customer_last_name` varchar(256) DEFAULT NULL,
`order_total` int(11) DEFAULT NULL,
PRIMARY KEY (`order_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--------------------------------------------
Create view ORDER_TOTALS of orders table:-
CREATE
VIEW `ORDER_TOTALS` AS
SELECT
`orders`.`order_number` AS `order_number`,
`orders`.`customer_first_name` AS `customer_first_name`,
`orders`.`customer_last_name` AS `customer_last_name`,
`orders`.`order_total` AS `order_total`
FROM
`orders`;
-----------------------------------------------------------------------------------------------------
Query for Largest order:
SELECT order_number, customer_first_name, customer_last_name, order_total FROM ORDER_TOTALS order by order_total DESC LIMIT 1;