INSERT
|
To insert new rows into a table we need to specify the table that we are inserting into, as well as the column values that we are inserting, followed by the values that we want added to the table.
Note: It is important to leave out the primary key (the ID) as Access will create the next available number for this on its own. Task: On the right we are adding a new product to the Products table, copy this and run it to see what happens. |
|
INSERT Challenges
1) Add a new customer named John Doe, with a preferred name "Johnny," born on April 15, 1995
2) Add yourself as a new customer
3) Add a new Product "Fresh spam sandwich" costing $7
4) Record a new order where CustomerID is 3, ProductID is 2, the order was placed today, and it has not been sent or delivered
5) Add a new customer with the first name "Lim," last name "Taing," no preferred name, and a date of birth of May 20, 1981.
2) Add yourself as a new customer
3) Add a new Product "Fresh spam sandwich" costing $7
4) Record a new order where CustomerID is 3, ProductID is 2, the order was placed today, and it has not been sent or delivered
5) Add a new customer with the first name "Lim," last name "Taing," no preferred name, and a date of birth of May 20, 1981.
UPDATE
|
To update the information of a row we need to specify the columns that we want to update, as well as the new value that we want to enter. Importantly, we need to be very specific about where we want to update the information.
In the example on the right we are very specifically only updating the row that has CustomerID 16. Important: if you aren't specific with your WHERE then you risk updating more rows than you want (or even all rows) with the new information. Task: Copy the query on the right, run it and see what it does to the Customers table. |
|
UPDATE Challenges
1) Update the price of the product "A really good joke" to $30
2) Change the product name of the item with ProductID 3 to "Vintage artisan socks"
3) Increase the price of all products where the price is less than $20 by $1
4) Mark the order with OrderID 14 as both sent and delivered
5) Change the last name of the customer with CustomerID 3 to "Anderson"
2) Change the product name of the item with ProductID 3 to "Vintage artisan socks"
3) Increase the price of all products where the price is less than $20 by $1
4) Mark the order with OrderID 14 as both sent and delivered
5) Change the last name of the customer with CustomerID 3 to "Anderson"
DELETE
|
Similar to when we use UPDATE, when we delete something from a table using DELETE we need to state the table that we are deleting rows from, but we also need to be very specific on what rows we want to remove.
SUPER Important: Be very specific on your WHERE so that you only remove what you want to, once something is deleted there's no bringing it back. Task: Copy the query on the right, run it, then see what it does to the Orders table. |
|
DELETE Challenges
1) Delete all orders that were placed on November 23, 2024
2) Remove the customer with the last name "Doe" from the Customers table
3) Delete the Customer that has your name
2) Remove the customer with the last name "Doe" from the Customers table
3) Delete the Customer that has your name
