Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 60 - Modular_V2.0_SQL_Flipbook
P. 60

    Now let's consider the following Table-CUSTOMERS:

                                                            Table-CUSTOMERS

                                    Customer_id           Name               City           Transport

                                           1         Raj               Mumbai            Bus
                                           2         Priya             Delhi             Bus

                                           3         Amit              Bangalore         Train
                                           4         Neha              Chennai           Flight

                                           5         Sanjay            Hyderabad         Train

                       CREATE VIEW BUS_ROUTERS AS SELECT Name, City
                       FROM CUSTOMERS
                       WHERE Transport = 'Bus';

                  Here, a view named as BUS_ROUTERS will be created and the result set having records who use
                  Bus will be displayed.

                       Select * from BUS_ROUTERS;
                  It will show the names and city of people who avail Bus service.
                                                          +-------+--------+
                                                          | Name  | City   |
                                                          +-------+--------+
                                                          | Raj   | Mumbai |
                                                          +-------+--------+
                                                          | Priya | Delhi  |
                                                          +-------+--------+

                  Let's create two new tables, PRODUCTS and SALES, and then create a view called PRODUCT_SALES
                  using an equi join based on the common column Product_id.

                                                             Table-PRODUCTS
                                             Product_id       Product_name             Price
                                                 101               Laptop             50000
                                                 102            Smartphone            15000
                                                 103            Headphones             2500

                                                 104             Keyboard              1500

                                                               Table-SALES

                                     Sale_id          Product_id           Quantity            Discount
                                       201                 101                  5                 500
                                       202                 102                 10                 1000
                                       203                 103                 15                 200
                                       204                 104                  8                 100

                       CREATE VIEW PRODUCT_SALES AS

                       SELECT PRODUCTS.Product_name, PRODUCTS.Price, SALES.Quantity, SALES.Discount
                       FROM PRODUCTS, SALES

                    58
                          Touchpad MODULAR (Ver. 2.0)

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