Managing Tables   «Prev 

Deleting a table

Dropping a table 
DROP TABLE tablename
Dropping a table

  1. DROP TABLE: The required DROP TABLE keywords begin the DDL statement which deletes a table and all associated database objects.
  2. tablename: The tablename must identify an existing table.

To remove a table and all its data from the database.The corresponding SQL is 
DROP TABLE [schema.]table 
[CASCADE CONSTRAINTS]

  1. schema : is the schema containing the table. If you omit schema, Oracle assumes the table is in your own schema.
  2. table : is the name of the table to be dropped.
  3. CASCADE CONSTRAINTS : drops all referential integrity constraints that refer to primary and unique keys in the dropped table.
    If you omit this option and the referential integrity constraints exist, Oracle returns an error and does not drop the table.

Multitable Inserts

You can perform multiple inserts in a single command. You can perform all the inserts unconditionally or you can specify conditions using a "when clause" to tell Oracle how to manage the multiple inserts. If you specify all, then all the "when clauses" will be evaluated.
Specifying first tells Oracle to skip subsequent "when clauses" after it finds one that is true for the row being evaluated. You can also use an else clause to tell Oracle what to do if none of the when clauses evaluates to true. To illustrate this functionality, let us create a new table . 

drop table COMFORT_TEST;
create table COMFORT_TEST (
City VARCHAR2(13) NOT NULL,
SampleDate DATE NOT NULL,
Measure VARCHAR2(10),
Value NUMBER(3,1)
);

COMFORT_TEST will have multiple records for each record in COMFORT and its Measure column will have values such as 'Midnight', 'Noon', and 'Precip', allowing us to store a greater number of measures for each city on each sample date. Now populate COMFORT_TEST with data from COMFORT, unconditionally: 
insert ALL
into COMFORT_TEST (City, SampleDate, Measure, Value)
values (City, SampleDate, 'NOON', Noon)
into COMFORT_TEST (City, SampleDate, Measure, Value)
values (City, SampleDate, 'MIDNIGHT', Midnight)
into COMFORT_TEST (City, SampleDate, Measure, Value)
values (City, SampleDate, 'PRECIP', Precipitation)
select City, SampleDate, Noon, Midnight, Precipitation
from COMFORT
where City = 'KEENE';

12 rows created.