CIT 111: Introduction to Databases

Assignment: Single Table Queries

Instructions:

  1. 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 steps leading to the solution.
  2. 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.
  3. 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.

  1. 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.)

    v-artist-insert.png
    Insert Artist Screen

  2. 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? 

    v-artist-list.png
    Artist List

  3. 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!)

    v-artist-update.png
    Update Artist

  4. 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!)

    v-artist-delete.png
    Delete Artist

Bike Shop Queries:

Create queries that the bike shop may need for the following business purposes.

  1. 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:

    bike-houston-custlist.png
    Houston Races

  2. 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:

    ./images/bike-over-5000-discount.png
    Bikes over $5000

  3. 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.

    bike-staff-not23.png
    Staff

  4. A customer needs more information about a specific bike, but all they know is that is 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. 

    bike-spider.png

  5. You need to list all bikes names that have a range of prices from $500–$550 sorted with the lowest price first.

    bike-range.png
    Bike's from $500 to $550

  6. 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.

    bike-variety.png
    Bike Results

  7. 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.

    bike-product-sans-year.png
    Bike Products Without Year

  8. 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.

    Bike-2029-payments.png
    Bike Products Without Year

Magazine Database:

  1. List the magazine name and then take 3% off the magazine price and round to 2 decimal places. 

    magazine-discount.png
    Magazine Discount

  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. magazine-subscription-begin.png
    Magazine Subscription Start

  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. magazine-subscription-start-date.png
    Magazine Start Date and Length

Submission

Upload a single .sql query file to Canvas that contains queries for each of the 10 scenarios. Make sure to add a comment at the top of each query with the scenario number it is solving.

Useful Links: