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

