Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 63 - Modular_V2.0_SQL_Flipbook
P. 63

    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      |
                                 +--------------+-------+----------+----------+-------------+
                                 | Monitor      | 12000 | 10       |  400     | 120000      |
                                 +--------------+-------+----------+----------+-------------+
                     Deleting a View

                     A view can be deleted using DELETE command. Syntax for deleting a view without or with a condition
                     is as follows:

                         DELETE FROM VIEW view_name;

                     OR
                         DELETE FROM VIEW view_name WHERE condition;

                     For example,

                         DELETE FROM VIEW PRODUCT_SALES;
                     This command deletes all the rows from the view and from the table consequently.

                         DELETE FROM PRODUCT_SALES WHERE Discount = 400;
                     This command deletes the rows where Product_name is 'Monitor'. The view after the execution of
                     above command is as follows:

                         SELECT * FROM PRODUCT_SALES;
                                 +--------------+-------+----------+----------+-------------+
                                 | 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      |
                                 +--------------+-------+----------+----------+-------------+
                     Precaution must be taken while deleting values from the view as it results in deletion from the main

                     table as well, from which the view was created.
                     Consider another example:

                         CREATE VIEW STUDENT_1 AS

                         SELECT Adm_no, Name
                         FROM Stu
                         WHERE Name IS NOT NULL

                         WITH CHECK OPTION;

                                                                                                                         61
                                                                                            Advanced Features of MySQL

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