Question
Use the magazine and bike databases write queries in Workbench to solve all the answers to the following scenarios. In addition to using functions properly,
- Use the magazine and bike databases write queries in Workbench to solve all the answers to the following scenarios. In addition to using functions properly, this assignment is about problem-solving; analyzing what is being asked, and breaking the problem to solve into steps leading to the solution.
- All the queries will be written in one tab in Workbench and then save the SQL script with all five queries and submit the .sql file.
- Please use comments to number your queries. Pay attention to aliases used.
Magazine Database:
1. List the magazine name and then take 3% off the magazine price and round to 2 decimal places.
2. Show the primary key of id from the subscriber table and using the date of 2020-12-20 as if it were today's date, how long in years, ROUNDED to the nearest year, has it been since their subscription started?
3. Show the subscriptionStartDate and subscriptionLength and add the subscriptionLength to the subscriptionStartDate to see the date of how long their subscription will go. Format that date so it takes the format of Month name, number day with comma and then a 4 digit year.
4. We need a list of all the products without the year at the end of the product_name string. Notice that some have two years listed, make sure you take those off as well. Order your results by product_id and limit your results to the first 14.
5. List the product name and then take the 2019 model year bikes and divide the price into 3 equal payments. Display one of the payments with a dollar sign, comma at the thousands place and two decimal places.
3% off magazineName Fishing in the Mojave Car Racing Made Easy Pine Cone Computing Cooking Like Mad If Only I Could Sing Beautiful Birds Corn Shucking for Fun and Profit MySQL Magic 13.53 14.99 16.98 17.46 12.08 12.08 14.60 10.62 subscriberkey 1 Years since subscription 10 10 9 2 3 5 3 5 4 3 4 3 8 : 3 10 subscription StartDate 2011-03-01 2011-03-01 2012-02-01 2012-02-01 2011-09-01 2012-07-01 2012-08-01 2011-05-01 2011-09-01 2011-12-01 2011-05-01 subscriptionLength Subscription end 12 March 1, 2012 14 May 1, 2012 12 February 1, 2013 12 February 1, 2013 12 September 1, 2012 24 July 1, 2014 12 August 1, 2013 12 May 1, 2012 12 September 1, 2012 12 December 1, 2012 18 November 1, 2012 Product Name without Year Trek 820 Ritchey Timberwolf Frameset Surly Wednesday Frameset Trek Fuel EX 8 29 Heller Shagamaw Frame Surly Ice Cream Truck Frameset Trek Slash 8 27.5 Trek Remedy 29 Carbon Frameset Trek Conduit+ Surly Straggler Surly Straggler 650b Electra Townie Original 21D Electra Cruiser 1 (24-Inch) Electra Girl's Hawaii 1 (16-inch) product_name Trek Checkpoint ALR 4 Women's - 2019 Trek Checkpoint ALR 5 - 2019 Trek Checkpoint ALR 5 Women's - 2019 Trek Checkpoint SL 5 Women's - 2019 Trek Checkpoint SL 6 - 2019 Trek Checkpoint ALR Frameset - 2019 One of 3 payments $566.66 $666.66 $666.66 $933.33 $1,266.66 $1,066.66 3% off magazineName Fishing in the Mojave Car Racing Made Easy Pine Cone Computing Cooking Like Mad If Only I Could Sing Beautiful Birds Corn Shucking for Fun and Profit MySQL Magic 13.53 14.99 16.98 17.46 12.08 12.08 14.60 10.62 subscriberkey 1 Years since subscription 10 10 9 2 3 5 3 5 4 3 4 3 8 : 3 10 subscription StartDate 2011-03-01 2011-03-01 2012-02-01 2012-02-01 2011-09-01 2012-07-01 2012-08-01 2011-05-01 2011-09-01 2011-12-01 2011-05-01 subscriptionLength Subscription end 12 March 1, 2012 14 May 1, 2012 12 February 1, 2013 12 February 1, 2013 12 September 1, 2012 24 July 1, 2014 12 August 1, 2013 12 May 1, 2012 12 September 1, 2012 12 December 1, 2012 18 November 1, 2012 Product Name without Year Trek 820 Ritchey Timberwolf Frameset Surly Wednesday Frameset Trek Fuel EX 8 29 Heller Shagamaw Frame Surly Ice Cream Truck Frameset Trek Slash 8 27.5 Trek Remedy 29 Carbon Frameset Trek Conduit+ Surly Straggler Surly Straggler 650b Electra Townie Original 21D Electra Cruiser 1 (24-Inch) Electra Girl's Hawaii 1 (16-inch) product_name Trek Checkpoint ALR 4 Women's - 2019 Trek Checkpoint ALR 5 - 2019 Trek Checkpoint ALR 5 Women's - 2019 Trek Checkpoint SL 5 Women's - 2019 Trek Checkpoint SL 6 - 2019 Trek Checkpoint ALR Frameset - 2019 One of 3 payments $566.66 $666.66 $666.66 $933.33 $1,266.66 $1,066.66Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started