DML (for MyShop)

1. Insert a Product:

   INSERT INTO myshop.products (name, quantity) VALUES ('Laptop', 15);
   • INSERT INTO: Adds new rows to a table.
   • Inserts a product named 'Laptop' with a quantity of 15 into the products table.

2. Insert Another Product:

   INSERT INTO myshop.products (name, quantity) VALUES ('Smartphone', 30);
   • Inserts a product named 'Smartphone' with a quantity of 30 into the products table.

3. Insert a Product with Default Quantity:

   INSERT INTO myshop.products (name) VALUES ('Headphones');
   • Inserts a product named 'Headphones' with the default quantity of 0 into the products table.

4. Insert a Category:

   INSERT INTO myshop.categories (name) VALUES ('Electronics');
   • Inserts a category named 'Electronics' into the categories table.

5. Insert Another Category:

   INSERT INTO myshop.categories (name) VALUES ('Accessories');
   • Inserts a category named 'Accessories' into the categories table.

6. Insert a Client:

   INSERT INTO myshop.clients (name) VALUES ('Mykola');
   • Inserts a client named 'Mykola' into the clients table.

7. Insert Another Client:

   INSERT INTO myshop.clients (name) VALUES ('Patron');
   • Inserts a client named 'Patron' into the clients table.

8. Insert an Order Using a SELECT to Get Client ID:

   INSERT INTO myshop.orders (order_id, client_id)
   SELECT 1, client_id FROM myshop.clients WHERE name = 'Patron';
   • SELECT: Retrieves data from a table.
   • Inserts an order with order_id 1 for the client named 'Patron' into the orders table.

9. Insert an Order Using a Known Client ID:

   INSERT INTO myshop.orders (order_id, client_id)
   VALUES (2, 1);
   • Inserts an order with order_id 2 for the client with client_id 1 into the orders table.

10. Insert Product-Category Association Using a SELECT:

    INSERT INTO myshop.product_category (product_id, category_id)
    SELECT p.product_id, c.category_id
    FROM myshop.products AS p, myshop.categories AS c
    WHERE p.name = 'Laptop' AND c.name = 'Electronics';
    • Inserts an association between the product 'Laptop' and the category 'Electronics' into the product_category table.

11. Insert Product-Category Association Using Known IDs:

    INSERT INTO myshop.product_category (product_id, category_id) VALUES (2, 2);
    • Inserts an association between the product with product_id 2 and the category with category_id 2 into the product_category table.

12. Insert Ordered Product Using a SELECT:

    INSERT INTO myshop.ordered_products (product_id, order_id, quantity)
    SELECT product_id, 1, 10 FROM myshop.products WHERE name = 'Smartphone';
    • Inserts an ordered product with product_id for 'Smartphone', order_id 1, and quantity 10 into the ordered_products table.

13. Insert Ordered Product Using Known IDs:

    INSERT INTO myshop.ordered_products (product_id, order_id, quantity)
    VALUES (1, 1, 5);
    • Inserts an ordered product with product_id 1, order_id 1, and quantity 5 into the ordered_products table.

14. Update Product Quantity:

    UPDATE myshop.products SET quantity = quantity + 10 WHERE name = 'Laptop';
    • UPDATE: Modifies existing rows in a table.
    • Increases the quantity of the product 'Laptop' by 10 in the products table.

15. Change Product Name:

    UPDATE myshop.products SET name = 'Portable Speaker' WHERE name = 'Headphones';
    • Changes the name of the product 'Headphones' to 'Portable Speaker' in the products table.

16. Change Category Name:

    UPDATE myshop.categories SET name = 'Electronic Devices' WHERE name = 'Electronics';
    • Changes the name of the category 'Electronics' to 'Electronic Devices' in the categories table.

17. Update Client Name:

    UPDATE myshop.clients SET name = 'Dog Patron' WHERE name = 'Patron';
    • Changes the name of the client 'Patron' to 'Dog Patron' in the clients table.

18. Change Client ID for an Order:

    UPDATE myshop.orders SET client_id = (SELECT client_id FROM myshop.clients WHERE name = 'Mykola') WHERE order_id = 1;
    • Changes the client_id of the order with order_id 1 to the client_id of 'Mykola' in the orders table.

19. Update Order Date:

    UPDATE myshop.orders SET order_date = '2024-01-01' WHERE order_id = 2;
    • Changes the order_date of the order with order_id 2 to '2024-01-01' in the orders table.

20. Change Category Associated with a Product:

    UPDATE myshop.product_category
    SET category_id = (SELECT category_id FROM myshop.categories WHERE name = 'Accessories')
    WHERE product_id = (SELECT product_id FROM myshop.products WHERE name = 'Laptop');
    • Changes the category_id associated with the product 'Laptop' to the category_id of 'Accessories' in the product_category table.

21. Update Quantity of a Product in an Order:

    UPDATE myshop.ordered_products SET quantity = 15 WHERE order_id = 1 AND product_id = (SELECT product_id FROM myshop.products WHERE name = 'Smartphone');
    • Updates the quantity to 15 for the product 'Smartphone' in the order with order_id 1 in the ordered_products table.

22. Link a Different Product to an Order:

    UPDATE myshop.ordered_products
    SET product_id = (SELECT product_id FROM myshop.products WHERE name = 'Portable Speaker')
    WHERE order_id = 1 AND product_id = (SELECT product_id FROM myshop.products WHERE name = 'Laptop');
    • Changes the product_id from 'Laptop' to 'Portable Speaker' in the order with order_id 1 in the ordered_products table.