The INSERT statement is used to put new rows into the database. This can be done one row at a time using the VALUES expression or a whole set of records at a time using a subquery. The following is the syntax for an INSERT statement:
INSERT INTO schema.table column(s) VALUES subquery
where schema is an optional parameter to identify which database schema to use for the insert. The default is your own.
table is mandatory and is the name of the table.
column is a list of columns that will receive the inserted values.
VALUES is used when one row of data will be inserted. Values are represented as constants.
subquery is used when the VALUES option is not used. The columns in the subquery must match the sequence and data types of the columns in the insert list.
INSERT INTO dept (dept_no,
name,
adrs_id)
VALUES (dept_seq.NEXTVAL,
'CUSTOMER SERVICE',
adrs_seq.NEXTVAL);
This example inserts one row into the table dept. Sequences dept_seq and adrs_seq are used to retrieve the next numeric values for dept_no and adrs_id.
If multiple rows need to be inserted, the INSERT EXAMPLE 1 statement would have to executed for each individual row. If a subquery can be used, multiple rows would be inserted for each row returned by the subquery.
INSERT INTO emp (emp_id,
first_name,
last_name,
dept_no,
hire_date,
job_title,
salary,
manager_id)
SELECT emp_seq.NEXTVAL,
new.first_name,
new.last_name,
30,
SYSDATE,
'CUSTOMER REPRESENTATIVE',
new.salary,
220
FROM candidates new
WHERE new.accept = 'YES'
AND new.dept_no = 30;
This example will insert all rows form the candidates table that have been assigned to department number 30 and have been accepted. Because the department number and manager ID are known, they are used as constants in the subquery. The UPDATE statement is used to change existing rows in the database. The syntax for the UPDATE statement is
UPDATE schema.table SET column(s) = expr sub query WHERE condition
where
schema is an optional parameter to identify which database schema to use for the update. The default is your own.
table is mandatory and is the name of the table.
SET is a mandatory reserved word.
column is a list of columns that will receive the updated values.
expr is the new value to be assigned.
sub query is a select statement that will retrieve the new data values.
WHERE is optional and is used to restrict which rows are to be updated.
UPDATE emp
SET dept_no = 30
WHERE last_name = 'DOE'
AND first_name = 'JOHN';
This example will transfer an employee named JOHN DOE to department 30. If there is more than one JOHN DOE, further restrictions will have to be made in the WHERE clause.
UPDATE emp
SET salary = salary + (salary * .05);
This update example will give everyone in table emp a 5 percent increase in salary.
UPDATE emp a
SET a.salary = (SELECT a.salary
+ (a.salary * DECODE(d.name, 'SALES', .1,
'ADMIN', .07,
.06))
FROM dept d
WHERE d.dept_no = a.dept_no)
WHERE a.dept_no = (SELECT dept_no
FROM dept y, addresses z
WHERE y.adrs_id = z.adrs_id
AND z.city = 'ROCHESTER');
This example will give raises to employees located in Rochester. The amount of the raise is handled by the DECODE statement evaluating the department name. Employees in the Sales department will receive a 10 percent raise, the Admin department receives a seven percent raise, and everyone else receives a 6 percent raise.
The DELETE statement is used to remove database rows. The syntax for DELETE is
DELETE FROM schema.table WHERE condition
where
SCHEMA is an optional parameter to identify which database schema to use for the delete. The default is your own.
TABLE is mandatory and is the name of the table.
WHERE restricts the delete operation.
DELETE FROM addresses
WHERE adrs_id = (SELECT e.adrs_id
FROM emp e
WHERE e.last_name = 'DOE'
AND e.first_name = 'JOHN');
DELETE FROM emp e
WHERE e.last_name = 'DOE'
AND e.first_name = 'JOHN';
If employee John Doe left the company, you probably would want to delete him from the database. One way to accomplish this is to delete the row containing his name from the addresses table and the emp table. In order to find John Doe in the addresses table, you must perform a subquery using the emp table. Therefore, the entry in the emp table has to be the last row to be deleted, or else there would be an orphan row in the addresses table.
DELETE FROM dept
WHERE adrs_id is null;
In this example, all rows in the dept table will be deleted if the corresponding adrs_id is null.
Deletes are permanent! Once the commit has taken place, it is impossible to get the row(s) back apart from issuing an INSERT statement. There is not an undo command available.
INSERT INTO schema.table column(s) VALUES subquery
where schema is an optional parameter to identify which database schema to use for the insert. The default is your own.
table is mandatory and is the name of the table.
column is a list of columns that will receive the inserted values.
VALUES is used when one row of data will be inserted. Values are represented as constants.
subquery is used when the VALUES option is not used. The columns in the subquery must match the sequence and data types of the columns in the insert list.
INSERT INTO dept (dept_no,
name,
adrs_id)
VALUES (dept_seq.NEXTVAL,
'CUSTOMER SERVICE',
adrs_seq.NEXTVAL);
This example inserts one row into the table dept. Sequences dept_seq and adrs_seq are used to retrieve the next numeric values for dept_no and adrs_id.
If multiple rows need to be inserted, the INSERT EXAMPLE 1 statement would have to executed for each individual row. If a subquery can be used, multiple rows would be inserted for each row returned by the subquery.
INSERT INTO emp (emp_id,
first_name,
last_name,
dept_no,
hire_date,
job_title,
salary,
manager_id)
SELECT emp_seq.NEXTVAL,
new.first_name,
new.last_name,
30,
SYSDATE,
'CUSTOMER REPRESENTATIVE',
new.salary,
220
FROM candidates new
WHERE new.accept = 'YES'
AND new.dept_no = 30;
This example will insert all rows form the candidates table that have been assigned to department number 30 and have been accepted. Because the department number and manager ID are known, they are used as constants in the subquery. The UPDATE statement is used to change existing rows in the database. The syntax for the UPDATE statement is
UPDATE schema.table SET column(s) = expr sub query WHERE condition
where
schema is an optional parameter to identify which database schema to use for the update. The default is your own.
table is mandatory and is the name of the table.
SET is a mandatory reserved word.
column is a list of columns that will receive the updated values.
expr is the new value to be assigned.
sub query is a select statement that will retrieve the new data values.
WHERE is optional and is used to restrict which rows are to be updated.
UPDATE emp
SET dept_no = 30
WHERE last_name = 'DOE'
AND first_name = 'JOHN';
This example will transfer an employee named JOHN DOE to department 30. If there is more than one JOHN DOE, further restrictions will have to be made in the WHERE clause.
UPDATE emp
SET salary = salary + (salary * .05);
This update example will give everyone in table emp a 5 percent increase in salary.
UPDATE emp a
SET a.salary = (SELECT a.salary
+ (a.salary * DECODE(d.name, 'SALES', .1,
'ADMIN', .07,
.06))
FROM dept d
WHERE d.dept_no = a.dept_no)
WHERE a.dept_no = (SELECT dept_no
FROM dept y, addresses z
WHERE y.adrs_id = z.adrs_id
AND z.city = 'ROCHESTER');
This example will give raises to employees located in Rochester. The amount of the raise is handled by the DECODE statement evaluating the department name. Employees in the Sales department will receive a 10 percent raise, the Admin department receives a seven percent raise, and everyone else receives a 6 percent raise.
The DELETE statement is used to remove database rows. The syntax for DELETE is
DELETE FROM schema.table WHERE condition
where
SCHEMA is an optional parameter to identify which database schema to use for the delete. The default is your own.
TABLE is mandatory and is the name of the table.
WHERE restricts the delete operation.
DELETE FROM addresses
WHERE adrs_id = (SELECT e.adrs_id
FROM emp e
WHERE e.last_name = 'DOE'
AND e.first_name = 'JOHN');
DELETE FROM emp e
WHERE e.last_name = 'DOE'
AND e.first_name = 'JOHN';
If employee John Doe left the company, you probably would want to delete him from the database. One way to accomplish this is to delete the row containing his name from the addresses table and the emp table. In order to find John Doe in the addresses table, you must perform a subquery using the emp table. Therefore, the entry in the emp table has to be the last row to be deleted, or else there would be an orphan row in the addresses table.
DELETE FROM dept
WHERE adrs_id is null;
In this example, all rows in the dept table will be deleted if the corresponding adrs_id is null.
Deletes are permanent! Once the commit has taken place, it is impossible to get the row(s) back apart from issuing an INSERT statement. There is not an undo command available.
