Adding and Modifying Data - Assignments

1. Create Table

We will start with a table for a shopping list.

Execute the query below.

CREATE TABLE groceries (
  grocery_name VARCHAR (50),
  quantity INT
);

This query creates:

• A table named groceries.
• The columns:
  • grocery_name: the name of an item.
  • quantity: the required amount.

Do you understand why the respective data types were chosen?

---

Complete the following query to retrieve all data from the new table:

• Use SELECT and FROM.

...
  *
...
  ...;

Is it logical what you see?

---

2. Add Rows

Complete the following query to add several rows to the groceries table.

• 'Tomatoes', 5
• 'Bananas', 2
• 'Apples', 6

Use INSERT INTO and VALUES.

...
  groceries (..., ...)
...
  (..., ...),
  (..., ...),
  (..., ...);

View the result with the query below:

SELECT
  *
FROM
  groceries;

---

3. Modify Values

A mistake was made. Therefore, an adjustment needs to be made.

Instead of 2, 3 bananas need to be bought.

With UPDATE, SET, and WHERE, we can make adjustments.

• UPDATE allows you to make a change.
• From SET, you specify the intended change.
• With WHERE, you filter a selection that needs to be adjusted.

Complete the query below and make the adjustment:

...
  groceries
...
  ... = ...
WHERE
  ... = ...;

View the result with the query below:

SELECT
  *
FROM
  groceries;

---

4. (Extra) Add Column

It is desired to keep more details of the groceries.

For this, a column needs to be added where the default store where you buy an item can be specified.

Complete the query below and make the adjustment.

• Add column default_shop.
  • Data type VARCHAR (50).

...
  ...
...
  ... ...;

View the result with the query below:

SELECT
  *
FROM
  groceries;

---

5. (Extra) Fill Column

The extra column is not yet filled with values.

Write a query to fill the new column default_shop with values:

• 'Tomatoes': 'Lidl'
• 'Bananas': 'Jumbo'
• 'Apples': 'Albert Heijn'

Note: you can write multiple queries in succession.

<query_1>;
<...>;
<query_n>;

...

View the result with the query below:

SELECT
  *
FROM
  groceries;