Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 62 - Modular_V2.0_SQL_Flipbook
P. 62

    2.   When you are working with aggregate functions or need to add new columns in the view, the

                      following syntax is used:

                       CREATE OR REPLACE VIEW view_name AS
                       SELECT column1, column2, ...

                       FROM table_name

                       WHERE condition;
                      Example using tables: PRODUCTS and SALES

                       CREATE OR REPLACE VIEW PRODUCT_SALES AS

                       SELECT PRODUCTS.Product_name, PRODUCTS.Price, SALES.Quantity, SALES.Discount,
                       (SALES.Quantity * PRODUCTS.Price) AS Total_sales  FROM PRODUCTS, SALES

                       WHERE PRODUCTS.Product_id = SALES.Product_id;
                        You will replace the PRODUCT_SALES view and add an additional column, such as Total_sales,

                      which can be calculated by multiplying Quantity and Price.

                       SELECT * FROM PRODUCT_SALES;
                      Output:
                               +--------------+-------+----------+----------+-------------+
                               | Product_name | Price | Quantity | Discount | Total_sales |
                               +--------------+-------+----------+----------+-------------+
                               | Laptop       | 50000 |  5       |  500     | 250000      |
                               +--------------+-------+----------+----------+-------------+
                               | Smartphone   | 15000 | 10       | 1000     | 150000      |
                               +--------------+-------+----------+----------+-------------+
                               | Headphones   |  2500 | 15       |  300     |  37500      |
                               +--------------+-------+----------+----------+-------------+
                               | Keyboard     |  1500 |  8       |  100     |  12000      |
                               +--------------+-------+----------+----------+-------------+
                  INSERT INTO View

                  You can insert values into a virtual table (view) created using views. Syntax for inserting values in a
                  view is as follows:

                       INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....);
                  Where value1, value2, etc., correspond to the values inserted into column1, column2, and so on.

                  Example using tables: PRODUCTS and SALES

                       INSERT INTO PRODUCT_SALES (Product_name, Price, Quantity,  Discount)  VALUES
                       ('Monitor', 12000.00, 10, 400.00);

                       SELECT * FROM PRODUCT_SALES;

                  This command will fetch all the new values along with the previous ones from the view
                  PRODUCT_SALES;

                    60
                          Touchpad MODULAR (Ver. 2.0)

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