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

