Module 6 Lab: Data Manipulation (DML)

You now have the power to create, alter, and destroy data. These are the operations that happen behind the scenes every time you click “Add to Cart”, “Edit Profile”, or “Delete Post” on a website.

In this lab, you will act as the backend database for an E-Commerce shopping cart.

The Scenario

User 101 is currently browsing the website and making several rapid changes to their shopping_cart.

The shopping_cart table has the following columns:

• id (The primary key, which will auto-increment if you don’t provide it)
• user_id (The customer)
• product_id (The item they want to buy)
• quantity (How many they want)

You need to execute three distinct SQL commands sequentially. You can write them all in the terminal, separated by semicolons (;).

Task 1: Add an item The user clicked “Add to Cart” on a new item. Write an INSERT INTO statement to add a row where user_id is 101, product_id is 88, and quantity is 2. (You can omit the id column from your insert).

Task 2: Modify an item The user went to their cart and changed the quantity dropdown for a specific item. Write an UPDATE statement to change the quantity to 3, but only for the row where user_id is 101 and the product_id is 55.

Task 3: Remove an item The user clicked the “Remove” button next to an item. Write a DELETE FROM statement to remove the row where user_id is 101 and the product_id is 56.

[!WARNING] Be very careful with your WHERE clauses in steps 2 and 3! Make sure you use AND to verify both the user_id AND the product_id, otherwise you might accidentally alter another user’s cart!

Complete these three tasks to finish Module 6. Next, we will learn how to design tables from scratch in Module 7: DDL.