Using View in Query - For this problem, need a query to address the same use case as in step 4, except you will use a view in the FROM clause in place of the subquery. The results should be the same as in step 4, except of course possibly row ordering which can vary.

 

step4 code

SELECT Store_location,store_name, Product.product_name ,
Alternate_name.name as "Alternate name",
to_char(Product.price_in_us_dollars,'$999.00') AS US_in_Price
from Store_location
JOIN Sells on sells.store_location_id = Store_location.store_location_id
JOIN Product on Product.product_id = sells.product_id
JOIN Alternate_name on Product.product_id = Alternate_name.product_id
WHERE Store_location.store_location_id IN
(SELECT Store_location.store_location_id
FROM Store_location
JOIN Offers ON Offers.store_location_id = Store_location.store_location_id
GROUP BY Store_location.store_location_id
HAVING COUNT(offers.shipping_offering_id) >1
ORDER BY store_name, product_name, name);

 

step4 result

Data Output Messages =+ 3 4 5 6 7 8 GB store_location store_location (12,"London Extension", 1) (12,"London Extension",1) (12,"London Extension", 1) (12,"London Extension", 1) (12,"London Extension",1) (12,"London Extension", 1) (12,"London Extension", 1) (13,"New York Extension",3) (13,"New York Extension",3) (13,"New York Extension",3) (13,"New York Extension",3) (13,"New York Extension",3) (13,"New York Extension",3) (13,"New York Extension",3) (13,"New York Extension",3) (13,"New York Extension",3) Total rows: 18 of 18 Query complete 00:00:00.071 9 10 11 12 13 14 15 16 17 18 No limit Notifications N store_name character varying (255) London Extension London Extension London Extension London Extension London Extension London Extension London Extension New York Extension New York Extension New York Extension New York Extension New York Extension New York Extension New York Extension New York Extension New York Extension < product_name character varying (255) Glucometer Bag Valve Mask Bag Valve Mask Digital Thermometer Electronic Stethoscope Handheld Pulse Oxime... Handheld Pulse Oxime... Glucometer Glucometer Glucometer Bag Valve Mask Bag Valve Mask Digital Thermometer Electronic Stethoscope Handheld Pulse Oxime... Handheld Pulse Oxime... 19 Ev Alternate name character varying (255) Glucose Meter Oxygen Bag Valve Mask Ambu Bag Thermometer Cardiology Stethoscope Handheld Pulse Oxime... Portable Pulse Oximeter Glucose Monitoring Sy... Blood Glucose Meter Glucose Meter Oxygen Bag Valve Mask Ambu Bag Thermometer Cardiology Stethoscope Handheld Pulse Oxime... Portable Pulse Oximeter us_in_price text $ 50.00 $ 25.00 $ 25.00 $ 250.00 $ 350.00 $ 450.00 $ 450.00 $ 50.00 $ 50.00 $ 50.00 $ 25.00 $ 25.00 $ 250.00 $ 350.00 $ 450.00 $ 450.00 Ln 12, Col 32