Adding, Modifying, and Removing Rows

In this section, you ll learn how to add, modify, and remove rows in database tables. You do that using the SQL INSERT , UPDATE , and DELETE statements, respectively. This section doesn t exhaustively cover all the details of using these statements; you ll learn more about them in Chapter 8.

Adding a Row to a Table

You use the INSERT statement to add new rows to a table. You can specify the following information in an INSERT statement:

• The table into which the row is to be inserted

• A list of columns for which you want to specify column values

• A list of values to store in the specified columns

When inserting a row, you need to supply a value for the primary key and all other columns that are defined as NOT NULL . You don t have to specify values for the other columns if you don t want to ”and those columns will be automatically set to null.

You can tell which columns are defined as NOT NULL using the SQL*Plus DESCRIBE command. The following example describes the customers table:

 SQL>  DESCRIBE customers  Name Null? Type  ----------------------------------------- -------- ------------  CUSTOMER_ID NOT NULL NUMBER(38)  FIRST_NAME NOT NULL VARCHAR2(10)  LAST_NAME NOT NULL VARCHAR2(10)  DOB DATE  PHONE VARCHAR2(12) 

As you can see, the customer_id , first_name , and last_name columns are NOT NULL , meaning that you must supply a value for these columns. The dob and phone columns don t require a value ”you could omit the values if you wanted, and they would be automatically set to null.

The following INSERT statement adds a row to the customers table. Notice that the order of values in the VALUES list matches the order in which the columns are specified in the column list. Also notice that the statement has two parts : the column list and the values to be added.

 SQL>  INSERT INTO customers (    2  customer_id, first_name, last_name, dob, phone    3  ) VALUES (    4  6, 'Fred', 'Brown', '01-JAN-1970', '800-555-1215'    5  );  1 row created. 

Note  

SQL*Plus automatically numbers lines after you hit ENTER at the end of each line .

In the previous example, SQL*Plus responds that one row has been created after the INSERT statement is executed. You can verify this by issuing the following SELECT statement:

 SQL>  SELECT *  2  FROM customers;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  1 John Brown 01-JAN-65 800-555-1211  2 Cynthia Green 05-FEB-68 800-555-1212  3 Steve White 16-MAR-71 800-555-1213  4 Gail Black 800-555-1214  5 Doreen Blue 20-MAY-70  6 Fred Brown 01-JAN-70 800-555-1215 

Notice the new row that has been added to the table.

By default, the Oracle database displays dates in the format DD-MON-YY , where DD is the day number, MON are the first three characters of the month (in uppercase), and YY are the last two digits of the year. The database actually stores all four digits for the year, but by default it only displays the last two digits.