Section 24 – Lessons 336 & 337 – Complete 2020 Web Development Bootcamp

Lesson 336 – SQL Commands: DELETE

In order to delete information from your table, you use DELETE FROM. So to delete the pencils item from the products table, you would use this SQL code –

DELETE FROM products
WHERE id = 2

Lesson 337 – Understanding SQL Relationships, Foreign Keys and Inner Joins

The first challenge of this lesson is to add the pencil item along with its id, price and stock amount back into the products table. This is the SQL code which does that –

INSERT INTO products (id, name, price, stock)
VALUES (2, "Pencil", 0.80, 12)

The next step is to create a new orders table using this code –

CREATE TABLE orders (
    id INT NOT NULL,
    order_number INT,
    customer_id INT,
    product_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
)

The orders table incorporates two foreign keys. The two foreign keys are “customer_id” and “product_id”. The foreign key “customer_id” in the orders table refers to the primary id in the customers table. Similarly, the foreign key “product_id” refers to the primary id in the products table.

The next step is to create an order in the orders table using this code –

INSERT INTO orders
VALUES (1, 4362, 2, 1)

Now that there is an order in the orders table, you can join your tables together using the keyword JOIN. The most common type of JOIN is the INNER JOIN. The INNER JOIN selects data that has matching values in both tables.

You can join information from the orders table with information from the products table using this code –

SELECT orders.order_number, products.name, products.price, products.stock
FROM orders
INNER JOIN products
ON orders.product_id = products.id

For more information on INNER JOIN have a look at this page.