Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 64 - Modular_V2.0_SQL_Flipbook
P. 64

    In this a view named "STUDENT_1" is created with a condition for "Name" column as NOT NULL, if
                  we now try to insert a new row with null value in the ‘Name’ column then it will give an error because

                  "Name" does not allow NULL. The clause WITH CHECK OPTION prevents entering any value that do
                  not match with the view conditions, mentioned at the time of its declaration.
                  A view can be deleted entirely(including its structure) using DROP VIEW command. Syntax is as

                  follows:
                                                                Hands On
                       DROP VIEW view_name;

                  For example:                                   Create Authors (AuthorID, Name, Country)
                       DROP VIEW PRODUCT_SALES;                  and  Books (BookID,  AuthorID,  Title,
                                                                 Year) tables. Then, create a view AuthorBooks
                  The above command deletes
                                                                 that  shows author  names and  the  titles of  the
                  the entire structure of the view
                                                                 books they’ve written.  Finally, write  a query  to
                  Student_1.
                                                                 retrieve data from the AuthorBooks view.
                  Views can  be used for  security
                  purposes.  View doesn’t contain
                  data of its own it only holds the data temporarily. Commands like DROP VIEW and ALTER VIEW do
                  not affect underlying data, commands like INSERT/UPDATE/DELETE in a view do affect base tables.
                  So effective use of views is in the hands of the programmer.

                       INDEX

                  An index is a structure that is used to improve the performance of database activity. Index helps in

                  quick data retrieval and it acts like an index page of a book that helps in quick access to the required
                  page of the book. Similarly index in MySQL saves time for scanning of database entirely and reaches
                  to the required piece of data quickly. It makes the process of search faster and efficient. It helps the
                  pointer to reach the desired record rather than sequentially going through each record one by one.
                  It is a mechanism which help to quickly retrieve a record from a table or a view. A database table can
                  have one or more indexes associated with it.

                                      CONTENTS

                                                                                      This page numbering
                                      01   Introduction to DBMS            7             will help ease in
                                          Understanding Database and DBMS     What is a Relational Database?
                                          Keys               Understanding SQL        moving quickly to the
                                          Different Types of Commands in SQL      Modern DBMS
                                      02   Introduction to MySQL          14          desired chapter/topic
                                          Installing MySQL     Data Types in MySQL
                                          Creating a Database     Creating a Table
                                          Modifying a Table     Deleting a Table
                                      03   Handling Records in MySQL      24
                                          Inserting Records in a Table     Retrieving Records from a Table
                                          Updating Records in a Table     Deleting Records from a Table
                                          Using DISTINCT Clause     Using Operators
                                          MySQL Comments
                                         Test Sheet-1                     34
                                      04   More on MySQL                  36
                                          Aliasing           Performing Calculations in Queries
                                          Using Group By     Using Order By
                                          Function
                                      05   Advanced Features of MySQL     46
                                          Subqueries         Joins
                                          Views              Index
                                         Test Sheet-2                     64
                                         Project Work                     66
                                         Explore More (Latest Trends)     68
                                         Uncover Artificial Intelligence & Robotics   73
                                         OGO Cyber Sample Questions       77
                                         Glossary                         80
                    62
                          Touchpad MODULAR (Ver. 2.0)
                                                        (vi)

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