Adding and Modifying Data - Assignments

1. Create Table

We are going to work 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.

SELECT
  *
FROM
  groceries;

grocery_name	quantity

Columns: 2
Rows: 0

Is it logical what you see?

Empty table, no rows added yet.

---

2. Add Rows

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

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

Use INSERT INTO and VALUES.

INSERT INTO
  groceries (grocery_name, quantity)
VALUES
  ('Tomatoes', 5),
  ('Bananas', 2),
  ('Apples', 6);

View the result with the query below:

SELECT
  *
FROM
  groceries;

grocery_name	quantity
Tomatoes	5
Bananas	2
Apples	6

Columns: 2
Rows: 3

---

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:

UPDATE
  groceries
SET
  quantity = 3
WHERE
  grocery_name = 'Bananas';

View the result with the query below:

SELECT
  *
FROM
  groceries;

grocery_name	quantity
Tomatoes	5
Bananas	3
Apples	6

Columns: 2
Rows: 3

---

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).

ALTER TABLE
  groceries
ADD
  default_shop VARCHAR (50);

View the result with the query below:

SELECT
  *
FROM
  groceries;

grocery_name	quantity	default_shop
Tomatoes	5
Bananas	3
Apples	6

Columns: 3
Rows: 3

---

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>;

UPDATE
  groceries
SET
  default_shop = 'Lidl'
WHERE
  grocery_name = 'Tomatoes';
UPDATE
  groceries
SET
  default_shop = 'Jumbo'
WHERE
  grocery_name = 'Bananas';
UPDATE
  groceries
SET
  default_shop = 'Albert Heijn'
WHERE
  grocery_name = 'Apples';

View the result with the query below:

SELECT
  *
FROM
  groceries;

grocery_name	quantity	default_shop
Tomatoes	5	Lidl
Bananas	3	Jumbo
Apples	6	Albert Heijn

Columns: 3
Rows: 3