Page 264 - Information_Practice_Fliipbook_Class11
P. 264

On executing the above statement, SQL will display the list of tables (see Table 9.3) as follows:
                                           Table 9.3: Result of show tables in company
                                                   +--------------------+
                                                   | Tables_in_Company  |
                                                   +--------------------+
                                                   | department         |
                                                   | employee           |
                                                   | project            |
                                                   | works_on           |
                                                   +--------------------+
        9.5.5 INSERT statement

        Once the database tables are created, we can store the data in the database. Storing the available data in the database
        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 Pradesh', 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, ..., AttributeK) VALUES (value1, value2, ..., valueK);
        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);

          250  Touchpad Informatics Practices-XI
   259   260   261   262   263   264   265   266   267   268   269