Skip to content

HW4: CRUD Operations

Due: Monday, Feb 19th

MySQL Workbench SQL Editor
Image source: MySQL Workbench


  • Write an SQL script (file) that creates a table and imports data.
  • Modify data using INSERT, UPDATE, and DELETE statements.


This assignment must be completed individually. Your work must comply with the JMU Honor Code. Authorized help is limited to asking questions in class, during office hours, or via email. Copying code from someone else, including the use of generative AI, is prohibited and will be grounds for a reduced or failing grade in the course.

Part 0: Getting the Data

For this assignment, you will create a small database about cars. The data you will use comes from the CORGIS Datasets Project.

Visit the Cars CSV File page on the CORGIS website. Download cars.csv and open the file using a spreadsheet app (like Microsoft Excel or LibreOffice Calc) to become familiar with the data. The data has 18 columns and 5076 rows (not counting the header row).

Read the "Key Descriptions" on the download page to interpret the data. Notice that some of the comments are incorrect. For example, the comments for height, length, and width all say: "Unknown values are stored as 0. Unfortunately, many cars do not report this data." However, none of the rows in cars.csv have a value of 0 for height, length, or width.

Part 1: Create and Import

Complete the following steps using MySQL Workbench. Save your work in a file named hw4-create.sql.

  1. Begin the file with the following comment. (Replace Ada's name with your own.)

    -- CS 374 HW 4 Part 1
    -- Name: Ada Lovelace
  2. Write a CREATE TABLE statement for the cars.csv file. You may shorten the column names for convenience. For example, instead of "Engine Information.Engine Type" you may write EngineType. Use appropriate data types for each column. Declare columns as NOT NULL whenever possible.

  3. Add a "DROP TABLE IF EXISTS" statement above your CREATE TABLE statement. Make sure you can run the script again and again without error. The script should both drop and create the table, allowing you to start over.

  4. Write a "LOAD DATA LOCAL INFILE" statement to import the contents of cars.csv into the table you created.

    Note: In order for LOCAL INFILE to work, you must add a setting to your MySQL Workbench connection. Right-click and edit your connection, go to the Advanced tab, and add the following line under Others:


  5. Write a SELECT statement to show the contents of your table. The query should return the same number of rows as are in the original cars.csv file.

Part 2: Data Modifications

Complete the following steps using MySQL Workbench. Save your work in a file named hw4-modify.sql.

  1. Begin the file with the following comment. (Replace Ada's name with your own.)

    -- CS 374 HW 4 Part 2
    -- Name: Ada Lovelace
  2. Write an INSERT statement to add the following car to the database:

    Column Value
    Dimensions.Height 56
    Dimensions.Length 198
    Dimensions.Width 78
    Engine Information.Driveline All-wheel drive
    Engine Information.Engine Type 3-phase AC induction motor
    Engine Information.Hybrid False
    Engine Information.Number of Forward Gears 1
    Engine Information.Transmission Direct Drive
    Fuel Information.City mpg 124
    Fuel Information.Fuel Type Electric
    Fuel Information.Highway mpg 115
    Identification.Classification Electric Car
    Identification.ID 2023 Tesla Model S
    Identification.Make Tesla
    Identification.Model Year 2023 Model S
    Identification.Year 2023
    Engine Information.Engine Statistics.Horsepower 1020
    Engine Information.Engine Statistics.Torque 1050
  3. Write an UPDATE statement to change all Engine Information.Hybrid values to False, unless the engine type ends with the word Hybrid.

    • Hint: The following query shows how to search for Hybrid cars. When used with LIKE, the % character is a wildcard meaning "zero or more characters."

      SELECT * FROM car
      WHERE EngineType LIKE '%Hybrid';

    • Note: In order for this query to run, you need to disable a setting in MySQL Workbench. Go to Edit → Preferences, click SQL Editor (on the left), and uncheck Safe Updates (at the bottom).

  4. Write a DELETE statement that removes all cars that use E85 fuel but get less than 15 miles per gallon on the highway.

  5. Write a SELECT statement to show all the cars from 2010 or 2023. The query should return 1706 rows.


Test your scripts before submitting! You should be able to run all of hw4-create.sql, followed by all of hw4-modify.sql, without any errors. If you make a mistake during Part 2, be sure to run hw4-create.sql again to reset the database.


Submit both files to the HW4 assignment on Gradescope. We will grade this assignment manually by looking at your code.