Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 61 - Modular_V2.0_SQL_Flipbook
P. 61

    WHERE PRODUCTS.Product_id = SALES.Product_id;

                         SELECT * FROM PRODUCT_SALES;
                     Output:
                                          +--------------+-------+----------+----------+
                                          | Product_name | Price | Quantity | Discount |
                                          +--------------+-------+----------+----------+
                                          | Laptop       | 50000 |  5       |  500     |
                                          +--------------+-------+----------+----------+
                                          | Smartphone   | 15000 | 10       | 1000     |
                                          +--------------+-------+----------+----------+
                                          | Headphones   |  2500 | 15       |  200     |
                                          +--------------+-------+----------+----------+
                                          | Keyboard     |  1500 |  8       |  100     |
                                          +--------------+-------+----------+----------+
                     Updating Views

                     Rules to be kept in mind before Updating Views are as follows:

                        A view must be based on only one table to be updated.
                        You cannot update a view that uses JOIN, GROUP BY, DISTINCT, or UNION.

                        Only columns shown in the view can be updated.
                        You must include all NOT NULL columns from the base table.
                        You need the correct permissions to update a view.

                        Views containing calculations or aggregate functions cannot be updated.

                        Updates must follow all rules of the original table structure.
                     Views can be updated using the following ways:

                     1.  When data needs to be updated in a view, the following syntax is used:

                         UPDATE  view_name
                         SET column_name = value
                         WHERE condition;

                          Example using tables: PRODUCTS and SALES

                         UPDATE PRODUCT_SALES
                         SET Discount = Discount + 100
                         WHERE Discount = 200;

                         SELECT * FROM PRODUCT_SALES;
                         Output:
                                        +--------------+----------+----------+----------+
                                        | Product_name | Price    | Quantity | Discount |
                                        +--------------+----------+----------+----------+
                                        | Laptop       | 50000    |  5       |  500     |
                                        +--------------+----------+----------+----------+
                                        | Smartphone   | 15000    | 10       | 1000     |
                                        +--------------+----------+----------+----------+
                                        | Headphones   |  2500    | 15       |  300     |
                                        +--------------+----------+----------+----------+
                                        | Keyboard     |  1500    |  8       |  100     |
                                        +--------------+----------+----------+----------+

                                                                                                                         59
                                                                                            Advanced Features of MySQL

| 56 57 58 59 60 61 62 63 64 65 66 |