results:

ASSIGNMENT1 NOTE: Run the sporting_goods.sql script to create database. Use cs31_sporting_goods database for the entire Assignment. Submission 1) Upload a single sql script that contains all the queries for the below questions. 2) Upload video explaining your query to below questions and run the script and show the result. 3) Video duration should be 10minutes approximately. 1. Write a SELECT statement that returns these columns from the sportinggoods_inventory table: sportinggoods_name, list_price, date_added Return only the rows with a list price that's greater than 500 and less than 2000. Sort the result set by the date_added column in descending sequence. This should return 2 rows. 2. Write a SELECT statement that returns these column names and data from the athlete_order_items table: item id The item_id column item_price The item_price column discount_amount The discount_amount column quantity The quantity column price_total A column that's calculated by multiplying the item price by the quantity discount_total A column that's calculated by multiplying the discount amount by the quantity item_total A column that's calculated by subtracting the discount amount from the item price and then multiplying by the quantity Only return rows where the item_total is greater than 500. Sort the result set by the item_total column in descending sequence. 3. Write a SELECT statement without a FROM clause that uses the NOW function to create a row with these columns today_unformatted The NOW function unformatted today_formatted The NOW function in this format: DD-Mon-YYYY This displays a number for the day, an abbreviation for the month, and a four-digit year. Display today's date in different formats like : '01/2022' - mm/yyyy 30th January 2022 doth month yyyy ASSIGNMENT1 4. Write a SELECT statement that joins the athletes table to the athlete_addresses table and returns these columns: first_name, last_name, linel, city, state, zip_code. Return one row for each address for the musician with an email address of david.goldstein@hotmail.- com 5. Write a SELECT statement that joins the athletes table to the athlete_addresses table and returns these columns: first_name, last_name, linel, city, state, zip_code. Return one row for each athlete, but only return addresses that are the billing address for an athlete. 6. Write a SELECT statement that joins the athletes, athlete_orders, athlete_order_items, and sport- inggoods_inventory tables. This statement should return these columns: last_name, first_name, or- der_date, sportinggoods_name, item_price, discount_amount, and quantity. Use aliases for the table Sort the final result set by the last_name, order_date, and sportinggoods_name columns. 7. Write a SELECT statement that returns the sportinggoods_name and list price columns from the In- struments table. Return one row for each instrument that has the same list price as another sportinggoods inventory. Hint: Use a self-join to check that the sportinggoods_id columns aren't equal but the list price col- umns are equal. Sort the result set by the sportinggoods_name column. 8. Use the UNION operator to generate a result set consisting of three columns from the athlete_orders table: ship_status A calculated column that contains a value of SHIPPED or NOT SHIPPED order_id The order id column order_date The order_date column If the order has a value in the ship_date column, the ship status column should contain a value of SHIPPED. Otherwise, it should contain a value of NOT SHIPPED. Sort the final result set by the order_date column. Query 1 sportinggoods_nam list_price Sole E25 date_added Elliptical, 1199.00 , 2017-12-05 Trainer , 699.00 2017-10-30 09:3 Saris H3