W04 Assignment: Single Table Queries
- Purpose: Create queries to retrieve specific data from a table in the database.
- Task: Code single table queries using clauses, keywords, operators and functions.
Instructions:
- Using the v_art, bike, and magazine databases, write queries in Workbench that will solve all the answers to the following scenarios. In addition to using sql clauses and functions properly, this assignment is about problem-solving; analyzing what is being asked, and breaking the problem to solve into smaller steps that lead to the solution.
- All the queries will be written on one tab in Workbench. Save the SQL script with all 15 queries and submit the single .sql file.
- Please use comments to number your queries.
Virtual Art Gallery Application Queries:
Queries 1–4 will be queries that are coded to run the back-end CMS of the virtual art gallery application. Screenshots below are from that CMS.
-
The manager wants to add the artist Johannes Vermeer as an artist in the database. He was born in 1632 and is from the Netherlands and died in 1674. He is not a local artist. What would the insert statement look like that would run as the manager adds a new artist to the system? (The following image is a screenshot of the backend of the system that the manager of the art gallery would see. We are interested only in the SQL statement that would run in the code when a new artist is added.)
Insert Artist Screen -
If the manager lists all the artists with 'List all Artist Records' the following screen renders sorted by the last name of the artist. What query would allow all seven columns of values to show up on the screen alphabetically by the last name?
Artist List -
The manager wants to edit Vermeer's information. She finds out that he died in 1675, not 1674. She selects EDIT next to Johannes Vermeer the following screen displays. The manager changes the 'Date of Death' value to be 1675 and selects 'Update Entry'. What SQL statement will run in the background to accomplish this edit? (Don't forget a WHERE clause!)
Update Artist -
The manager decides that she wants to delete Vermeer as an artist from her database. She selects the DELETE next to the Vermeer's row of information and the following screen renders confirming that she really wants to delete Vermeer as an artist. What SQL statement will run in the code that would delete Johannes Vermeer from the database when the manager selects 'Confirm Deletion'? (Don't forget a WHERE clause!)
Delete Artist
Bike Shop Queries:
Create queries that the bike shop may need for the following business purposes.
-
There is a product demonstration and bike race planned in Houston, Texas and you'd like to text each of your customers from that city to see if they will participate. You need a list of their first and last names and phone numbers.
Your result set should look like this with nine customers:
first_name last_name phone Mozelle Carter (281) 489-9656 Lolita Mosley (281) 363-3309 Dorthey Jackson (281) 926-8010 Minerva Decker (281) 271-6390 Van Peters (281) 658-7772 Ladawn Downs (281) 165-2090 Ivelisse Nixon (281) 941-4933 Crysta Velez (281) 529-3469 Nestor Haynes (281) 969-4579 -
You need to sell more of your high-end bikes. You want to take $500 off all bikes that have a list price of $5,000.00 or more. You need to have a list showing the bike name, list price and discount price with an alias of 'Discount Price'. Sort the list showing the most expensive bike first. The result set will look like this:
product_name list_price Discount Price Trek Domane SLR 9 Disc 11999.99 11499.99 Trek Domane SLR 8 Disc 7499.99 6999.99 Trek Silque SLR 8 Women's 6499.99 5999.99 Trek Domane SL Frameset 6499.99 5999.99 Trek Domane SL Frameset Women's 6499.99 5999.99 Trek Emonda SLR 8 6499.99 5999.99 Trek Silque SLR 7 Women's 5999.99 5499.99 Trek Domane SLR 6 Disc 5499.99 4999.99 Trek Domane SL 8 Disc 5499.99 4999.99 Trek Domane SLR 6 Disc Women's 5499.99 4999.99 Trek Domane SLR 6 Disc 5499.99 4999.99 Trek Fuel EX 9.8 27.5 Plus 5299.99 4799.99 Trek Remedy 9.8 5299.99 4799.99 -
An important announcement was given to all the staff at your store (store_id 1). The same announcement needs to get to all the other staff at the other stores. You don't want to email all your staff again. You need a list of all the staff and their email who are not from your store.
first_name last_name email Jannette David jannette.david@bikes.shop Marcelene Boyer marcelene.boyer@bikes.shop Venita Daniel venita.daniel@bikes.shop Kali Vargas kali.vargas@bikes.shop Layla Terrell layla.terrell@bikes.shop Bernardine Houston bernardine.houston@bikes.shop -
A customer needs more information about a specific bike, but all they know is that it has the word 'spider' in the name. You need to list the name, model year, and list price of all the bikes with the word 'spider' somewhere in the name.
product_name model_year list_price Sun Bicycles Spider 3i 2017 832.99 -
You need to list all bike names that have a range of prices from $500-$550 sorted with the lowest price first.
product_name list_price Electra Moto 1 529.99 Electra Cruiser Lux 3i 529.99 Electra Cruiser Lux 3i Ladies' 529.99 Sun Bicycles Streamway 7 533.99 Haro SR 1.1 539.99 Electra Townie Original 21D 549.99 Electra Townie Original 21D 549.99 Haro Flightline Two 26 Plus 549.99 -
Show the customer's first_name, last_name, phone, street, city, state, zip_code who:
- have a phone number listed
- and whose city has the letters 'ach' or 'och' somewhere in their name of the city
- or whose last name is William.
Limit the result set to the first five results.
first_name last_name phone street city state zip_code Theo Reese (562) 215-2907 8755 W. Wild Horse St. Long Beach NY 11561 Dacia William NULL 62 Cypress Drive Sugar Land TX 77478 Toya Pratt (562) 218-6405 485 San Pablo Drive Long Beach NY 11561 Maribel William NULL 65 Magnolia Ave. Torrance CA 90505 Janella Bright (507) 616-2958 7317 Tower Dr. Rochester NY 14606 -
We need a list of products from either Surly or Trek. Return the product_name without the preceding brand name. Order your results by product_id and limit your results to the first 10.
Model 820 Wednesday Frameset Fuel EX 8 29 Ice Cream Truck Frameset Slash 8 27.5 Remedy 29 Carbon Frameset Conduit+ Straggler Straggler 650b Big Dummy Frameset -
List the product name and then take the 2025 model year bikes and divide the price into 3 equal payments. Display the payment with a dollar sign, comma at the thousands place and two decimal places.
product_name One of 3 payments Trek Checkpoint ALR 4 Women's $566.66 Trek Checkpoint ALR 5 $666.66 Trek Checkpoint ALR 5 Women's $666.66 Trek Checkpoint SL 5 Women's $933.33 Trek Checkpoint SL 6 $1,266.66 Trek Checkpoint ALR Frameset $1,066.66
Magazine Database:
-
List the magazine name and then take 3% off the magazine price and round to 2 decimal places.
magazineName 3% off Fishing in the Mojave 13.53 Car Racing Made Easy 14.99 Pine Cone Computing 16.98 Cooking Like Mad 17.46 If Only I Could Sing 12.08 Beautiful Birds 12.08 Corn Shucking for Fun and Profit 14.60 MySQL Magic 10.62 - 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?
subscriberKey Years since subscription 1 10 2 10 3 9 5 9 3 9 5 8 4 8 3 10 4 9 3 9 3 10 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.
subscriptionstartdate subscriptionlength subscription end 03/01/2023 12 March 1, 2024 03/01/2023 14 May 1, 2024 02/01/2024 12 February 1, 2025 02/01/2024 12 February 1, 2025 09/01/2023 12 September 1, 2024 07/01/2024 24 July 1, 2026 08/01/2024 12 August 1, 2025 05/01/2023 12 May 1, 2024 09/01/2023 12 September 1, 2024 12/01/2023 12 December 1, 2024 05/01/2023 18 November 1, 2024
Submission
Upload a single .sql query file to Canvas that contains queries for each of the scenarios. Make sure to add a comment at the top of each query with the scenario number it is solving.
Useful Links:
- Go to: Week Index • Course Home • Canvas