Page 154 - Informatics_Practices_Fliipbook_Class12
P. 154

4.4.7 INSERT statement

        Once the database tables are created, we can store the data in the tables. Storing the available data in the database
        table is also called loading the database or populating the database. The SQL statement INSERT is used for this
        purpose. The syntax for a simple INSERT statement is as follows:
               INSERT INTO table_name

               VALUES (value1, value2, …, valueN) ;
        INSERT statement: To insert a row in a table
        INSERT statement allows us to insert a tuple by specifying table name and comma-separated attribute values
        within parenthesis. The values of type  CHAR, VARCHAR,  and DATE are enclosed within single-quotes or
        double-quotes. The order of values in the INSERT statement should correspond to the order of attributes. If the
        value of an attribute is not known, we use the NULL value. However, values of the primary key attributes cannot

        be NULL.
        Now let us insert into the EMPLOYEE table, a tuple for Raj  Reddy whose ID is 10001  and lives in Andhra
        Pradesh  as follows:

        INSERT INTO EMPLOYEE
        VALUES ('10001',  'Raj',  'Reddy',  'M', 'West Godavari',  'Andhra  Prade
        sh',534197,'1980-06-13',100000,2);



               Note that in the above example, the date has been provided in YYYY-MM-DD format. If you prefer, you can enter
               date in MM-DD-YYYY format as follows:
               INSERT INTO EMPLOYEE
               VALUES    ('10001','Raj','Reddy','M','West        Godavari','Andhra      Pradesh',534197,
               STR_TO_DATE("JUNE 13 1980", "%M %d %Y"), 100000, 2);



        Alternatively,  SQL  allows  us  to  specify  the  attributes  whose  values  are  known  by  enumerating  them  in  a

        comma-separated  list.  In  the  SQL  statement  below,  we  have  only  listed  five  attributes  (Salary, LName,
        FName,  Gender, ID) of the EMPLOYEE  table while inserting a tuple. Moreover, as the attributes are
        named, we can list them in any order. Note that there should be one to one correspondence between the attribute
        names and their values.
               INSERT INTO EMPLOYEE(Salary, LName, FName, Gender, ID)

               VALUES (100000, 'Reddy', 'Raj', 'M',  '10001');
        General syntax for the above statement is given below:

               INSERT INTO table_name (Attribute1, Attribute2, ..., AttributeN)
               VALUES (value1, value2, ..., valueN);
        Next,  suppose  the  values  of  attributes  Address,  City,  Pin_Code,  DOB and  Dept_No  are  not  known
        for Raj Reddy  whose ID  is  10001. The following INSERT  statement inserts such a tuple into the table
        EMPLOYEE:
               INSERT INTO EMPLOYEE

               VALUES ('10001', 'Raj', 'Reddy', 'M', NULL, NULL, NULL, NULL, 100000, NULL);
        IN TEXT EXERCISE:

        Write SQL statements to insert more rows into EMPLOYEE table so that it appears as shown in Table 4.3)



          140  Touchpad Informatics Practices-XII
   149   150   151   152   153   154   155   156   157   158   159