Assignment 1


CS451/551, Spring 2024
due Friday, April 19

The queries you write should be able to run on MySQL - we will test them. You do not need to provide the output. You can use your own MySQL instance or the instructor's:

UPDATE: there is an alternate server running that you can use on port 3398. It runs MySQL version 8, while port 3128 is version 5. The other connection parameters are the same.

To turn in: Put your queries in a single plain text (.txt) file and submit that via Canvas. Include your name in the text along with the queries.

    Write SQL for the queries below on the stores7 database.

  1. List all customers who represent a company that contains the word "Sports" as the last word in the company name. That is, the company field starts with some words, then a space, and then the word "Sports" at the end. Include the first name, last name, and company name.

  2. Give the first and last names of every person (and the city and state they live in) who has ordered something made by the company Husky between May 22 and June 25 in 1998. Include the order date and description (FROM the stock table).

  3. For each state, list the full state name and all customers who live in that state. If no one lives in that state, it should still be listed but with a blank (null) next to the state name.

    Write the queries below on the company database.

  4. Retrieve the names of all employees who work between 8 and 25 hours (presumably per week) on any single project that starts with "Product" (The names of the projects start with “Product”, then may or may not be a space, and then end with some words). If there is someone who works on multiple projects start with "Product" that satisfy the condition, the name should appear just once.

  5. For each department, list the department name and the names of the dependents for all employees who work for that department (determined by the employee's dno). Include departments with no such dependents. Sort the results by department.

  6. List all employees (by name) who work in the Administration department and the the names of anyone that person directly supervises. You do not need to list a name if they supervise no one.

    Write SQL for the queries below on the sakila database.

  7. List the customers (first and last name) and the names of the movies they rented in July 2005. Use the functions month(attribute), monthname(attribute), year(attribute), etc.

  8. Show the first name, last name, and email of all customers who have spent more than $6 ON any rental of a film in the 'Drama' category. (The $6 is for the price of a single rental, not the total spent, so no SUM required.)

  9. Count the number of times any movie with 'MOTHER' in it's title has been rented. (Use COUNT(*).)

Databases used: