Tech Area

SQL Data Definition Language

In this section, the basic SQL Data Definition Language statements are introduced and their syntax is given with examples.

An Oracle database can contain one or more schemas. A schema is a collection of database objects that can include: tables, views, indexes and sequences. By default, each user has their own the schema which has the same name as the Oracle username. For example, a single Oracle database can have separate schemas for HOLOWCZAK, JONES, SMITH and GREEN.

Any object in the database must be created in only one schema. The object name is prefixed by the schema name as in: schema.object_name
By default, all objects are created in the user's own schema. For example, when JONES creates a database object such as a table, it is created in her own schema. If JONES creates an EMPLOYEE table, the full name of the table becomes: JONES.EMPLOYEE. Thus database objects with the same name can be created in more than one schema. This feature allows each user to have their own EMPLOYEE table, for example.

Database objects can be shared among several users by specifying the schema name. In order to work with a database object from another schema, a user must be granted authorization. See the section below on GRANT and REVOKE for more details.

Please note that many of these database objects and options are not available under Personal Oracle Lite. For example, foreign key constraints are not supported. Please see the on-line documentation for Personal Oracle Lite for more details.

Create, Modify and Drop Tables, Views and Sequences

SQL*Plus accepts SQL statements that allow a user to create, alter and drop table, view and sequence definitions. These statements are all standard ANSI SQL statements with the exception of CREATE SEQUENCE.

ALTER TABLE - Change an existing table definition. The table indicated in the ALTER statement must already exist. This statement can be used to add a new column or remove an existing column in a table, modify the data type for an existing column, or add or remove a constraint. ALTER TABLE has the following syntax for adding a new column to an existing table:
Another ALTER TABLE option can change a data type of column. The syntax is:

Finally, ALTER TABLE can also be used to add a constraint to a table such as for a PRIMARY KEY, FOREIGN KEY or CHECK CONSTRAINT. The syntax to add a PRIMARY KEY is: The syntax to add a FOREIGN KEY constraint is: In Oracle, you must use an ALTER TABLE statement to define a composite PRIMARY KEY (a key made up of two or more columns).
NOTE: In Oracle, there is no single command to drop a column of a table. In order to drop a column from a table, you must create a temporary table containing all of the columns and records that will be retained. Then drop the original table and rename the temporary table to the original name. This is demonstrated below in the section on Creating, Altering and Dropping Tables.
CREATE TABLE - Create a new table in the database. The table name must not already exist. CREATE TABLE has the following syntax: An alternate syntax can be used to create a table with a subset of rows or columns from an existing table.
DROP TABLE - Drop a table from the database. The table name must already exist in the database. The syntax for the DROP TABLE statement is:
CREATE INDEX - Create a new Index that facilitates rapid lookup of data. An index is typically created on the primary and/or secondary keys of the table. The basic syntax for the CREATE INDEX statement is:
DROP INDEX - Drop an index from the database. The syntax for the DROP INDEX statement is:
CREATE SEQUENCE - Create a new Oracle Sequence of values. The new sequence name must not exist. CREATE SEQUENCE has the following syntax:
DROP SEQUENCE - Drop an Oracle Sequence. The sequence name must exist. DROP SEQUENCE has the following syntax:
CREATE VIEW - Create a new view based on existing tables in the database. The table names must already exist. The new view name must not exist. CREATE VIEW has the following syntax:
where sql select statement is in the form:

Additional information on the SELECT statement and SQL queries can be found in the next section.
Note that an ORDER BY clause may not be added to the sql select statement when defining a view.
In general, views are read-only. That is, one may query a view but it is normally the case that views can not be operated on with INSERT, UPDATE or DELETE. This is especially true in cases where views joing two or more tables together or when a view contains an aggregate function.
DROP VIEW - Drop a view from the database. The view name must already exist in the database. The syntax for the DROP VIEW command is:

In the following section, each of the SQL DDL commands will be discussed in more detail.

Creating, Altering and Dropping Tables

A table is made up of one or more columns (also called attributes in relational theory). Each column is given a name and a data type that reflects the kind of data it will store. Oracle supports four basic data types called CHAR, NUMBER, DATE and RAW. There are also a few additional variations on the RAW and CHAR data types. The basic datatypes, uses and syntax, are as follows:

VARCHAR2 - Character data type. Can contain letters, numbers and punctuation. The syntax for this data type is: VARCHAR2(size) where size is the maximum number of alphanumeric characters the column can hold. For example VARCHAR2(25) can hold up to 25 alphanumeric characters. In Oracle8, the maximum size of a VARCHAR2 column is 4,000 bytes. The VARCHAR data type is a synonym for VARCHAR2. It is recommended to use VARCHAR2 instead of VARCHAR.
NUMBER - Numeric data type. Can contain integer or floating point numbers only. The syntax for this data type is: NUMBER(precision, scale) where precision is the total size of the number including decimal point and scale is the number of places to the right of the decimal. For example, NUMBER(6,2) can hold a number between -999.99 and 999.99.
DATE - Date and Time data type. Can contain a date and time portion in the format: DD-MON-YY HH:MI:SS. No additional information is needed when specifying the DATE data type. If no time component is supplied when the date is inserted, the time of 00:00:00 is used as a default. The output format of the date and time can be modified to conform to local standards.
RAW - Free form binary data. Can contain binary data up to 255 characters. Data type LONG RAW can contain up to 2 gigabytes of binary data. RAW and LONG RAW data cannot be indexed and can not be displayed or queried in SQL*Plus. Only one RAW column is allowed per table.
LOB - Large Object data types. These include BLOB (Binary Large OBject) and CLOB (Character Large OBject). More than one LOB column can appear in a table. These data types are the prefferred method for storing large objects such as text documents (CLOB), images, or video (BLOB).

A column may be specified as NULL or NOT NULL meaning the column may or may not be left blank, respectively. This check is made just before a new row is inserted into the table. By default, a column is created as NULL if no option is given.

In addition to specifying NOT NULL constraints, tables can also be created with constraints that enforce referential integrity (relationships among data between tables). Constraints can be added to one or more columns, or to the entire table.

Each table may have one PRIMARY KEY that consists of a single column containing no NULL values and no repeated values. A PRIMARY KEY with multiple columns can be identified using the ALTER TABLE command.

Up to 255 columns may be specified per table. Column names and table names must start with a letter and may not contain spaces or other punctuation except for the underscore character. Column names and table names are case insensitive. This means that you can specify the names of columns and tables in any way you like. For example, the following three SELECT statements are all identical:

SELECT lname, fname, address FROM employee; SELECT LNAME, FNAME, ADDRESS FROM EMPLOYEE; SELECT Lname, Fname, Address FROM Employee;

In the following example, a new table called ``employee'' is created with ten columns of a variety of types. The columns indicated by NOT NULL will be mandatory while the other columns, by default, will be optional.

SQL&amp;amp;gt; CREATE TABLE employee 2 (fname VARCHAR2(8), 3 minit VARCHAR2(2), 4 lname VARCHAR2(8), 5 ssn VARCHAR2(9) NOT NULL, 6 bdate DATE, 7 address VARCHAR2(27), 8 sex VARCHAR2(1), 9 salary NUMBER(7) NOT NULL, 10 superssn VARCHAR2(9), 11 dno NUMBER(1) NOT NULL) ; Table created. SQL&amp;amp;gt;

The numbers 2 through 11 before each line indicate the line number supplied by the SQL*Plus program as this statement was typed in. We will omit these numbers in the rest of the examples to facilitate copying and pasting this material directly into a live SQL*Plus session.

A new table can also be created with a subset of the columns in an existing table. In the following example, a new table called emp_department_1 is created with only the fname, minit, lname and bdate columns from the employee table. This new table is also populated with data from the employee table where the employees are from department number 1.

SQL&amp;amp;gt; CREATE TABLE emp_department_1 AS SELECT fname, minit, lname, bdate FROM employee WHERE dno = 1 ; Table created. SQL&amp;amp;gt; DESCRIBE emp_department_1 Name Null? Type ------------------------------- -------- ---- FNAME VARCHAR2(8) MINIT VARCHAR2(2) LNAME VARCHAR2(8) BDATE DATE SQL&amp;amp;gt;

One can also create a new table with all of the columns from the original table, but with only a subset of the rows form the original table:

SQL> CREATE TABLE high_pay_emp
     AS SELECT *
     FROM employee
     WHERE salary > 50000 ;

 Table created.

DESCRIBE is an SQL*Plus command that displays the columns of a table and their data types. The syntax for the DESCRIBE command is:

DESCRIBE &amp;amp;lt;table name&amp;amp;gt; ;

The copying of data can be suppressed by giving a WHERE clause that always evaluates to FALSE for each record in the source table. The following example makes a duplicate of the employee table but does not copy any data into it.

SQL&amp;amp;gt; CREATE TABLE copy_of_employee AS SELECT * FROM employee WHERE 3=5 ; Table created. SQL&amp;amp;gt; DESCRIBE copy_of_employee Name Null? Type ------------------------------- -------- ---- FNAME VARCHAR2(8) MINIT VARCHAR2(2) LNAME VARCHAR2(8) SSN NOT NULL VARCHAR2(9) BDATE DATE ADDRESS VARCHAR2(27) SEX VARCHAR2(1) SALARY NOT NULL NUMBER(7) SUPERSSN VARCHAR2(9) DNO NOT NULL NUMBER(1)

Constraints can be added to the table at the time it is created, or at a later time using the ALTER TABLE statement. Constraints can include:

Primary key and Unique key constraints.
Foreign key constraints (for referential integrity).
Check constraints.

Here is an example of creating a primary key constraint on the empid column:

CREATE TABLE employee (fname VARCHAR2(8), minit VARCHAR2(2), lname VARCHAR2(8), ssn VARCHAR2(9) NOT NULL, bdate DATE, address VARCHAR2(27), sex VARCHAR2(1), salary NUMBER(7) NOT NULL, superssn VARCHAR2(9), dno NUMBER(1) NOT NULL, CONSTRAINT pk_emp PRIMARY KEY (ssn) );

Referential integrity constraints can also be added. In the following example, the dno column in the employee table references the dnumber column in the department table. If a department is deleted, all employees that reference the department are also deleted. This is given by the ON DELETE CASCADE option:

CREATE TABLE department (dnumber NUMBER(1), dname VARCHAR2(15), mgrssn VARCHAR2(9), mgrstartdate DATE CONSTRAINT pk_department PRIMARY KEY (dnumber) ); CREATE TABLE employee (fname VARCHAR2(8), minit VARCHAR2(2), lname VARCHAR2(8), ssn VARCHAR2(9) NOT NULL, bdate DATE, address VARCHAR2(27), sex VARCHAR2(1), salary NUMBER(7) NOT NULL, superssn VARCHAR2(9), dno NUMBER(1) NOT NULL, CONSTRAINT pk_emp PRIMARY KEY (ssn), CONSTRAINT fk_dno FOREIGN KEY (dno) REFERENCES department (dnumber) ON DELETE CASCADE);

In order to specify a foreign key constraint, the column in the child (or detail) table (e.g., the dnumber column in the department table in the above example) must be either the primary key or a unique key for the table. Thus, the child (or detail) table must be created first before the parent (or master) table is created using the above constraints.

Additional CREATE TABLE constraint statements allow the specification of what should happen when a row is deleted or updated in a parent table. In the above example, deleting a department causes all employees in that department to also be deleted. Other options include ON DELETE SET DEFAULT and ON DELETE SET NULL. In addition, the behavior of child tables when a parent table is updated can also be specified using an ON UPDATE clause.

CHECK constraints can be added to check the values for a given column. This can be used to allow only a specific set of valid values for a column. In the following example, CHECK constraints are added to limit the valid values for the sex column and to check if the salary is greater than 10,000 (be sure to DROP TABLE employee before you try the next one). CREATE TABLE employee (fname VARCHAR2(8), minit VARCHAR2(2), lname VARCHAR2(8), ssn VARCHAR2(9) NOT NULL, bdate DATE, address VARCHAR2(27), sex VARCHAR2(1) CONSTRAINT ck_sex CHECK (sex IN ('M', 'F')), salary NUMBER(7) NOT NULL CONSTRAINT ck_salary CHECK (salary &amp;amp;gt; 10000), superssn VARCHAR2(9), dno NUMBER(1) NOT NULL, CONSTRAINT pk_emp PRIMARY KEY (ssn), CONSTRAINT fk_dno FOREIGN KEY (dno) REFERENCES department (dnumber) ON DELETE CASCADE);

The CHECK constraints are activated when inserting a new row or when updating existing data. In the following example, the value given for sex is 'm': SQL&amp;amp;gt; insert into employee values 2 ('Joe', 'M', 'Smith', '123456789', '01-JUN-45', 3 '123 Smith St.', 'm', 45000, '123456789', 1) ; insert into employee values * ERROR at line 1: ORA-02290: check constraint (HOLOWCZAK.CK_SEX) violated

In the previous examples, constraints were given names with the following prefixes:

Primary key constraints: pk_
Foreign key constraints: fk_
Check constraints: ck_

Naming constraints in this fashion is simply a convenience. Any name may be given to a constraint.

The ALTER TABLE command can be used to add a new column to an existing table or to change the data type of an existing column. The following examples add a new column manager to an existing table named emp_department_1 and then modify the data type of the fname column.

SQL&amp;amp;gt; DESCRIBE emp_department_1 Name Null? Type ------------------------------- -------- ---- FNAME VARCHAR2(8) MINIT VARCHAR2(2) LNAME VARCHAR2(8) BDATE DATE SQL&amp;amp;gt; ALTER TABLE emp_department_1 ADD (manager VARCHAR2(8)) ; Table altered. SQL&amp;amp;gt; ALTER TABLE emp_department_1 MODIFY (fname VARCHAR2(15)); Table altered. SQL&amp;amp;gt; DESCRIBE emp_department_1 Name Null? Type ------------------------------- -------- ---- FNAME VARCHAR2(15) MINIT VARCHAR2(2) LNAME VARCHAR2(8) BDATE DATE MANAGER VARCHAR2(8)

The ALTER TABLE command can also be used to change the datatype of column provided there is no data in the table. To get around this if there is data in the table, create a temporary table using all of the data from the existing table, delete the existing records from the original table, alter the datatype, and then insert the records from the temporary table back into the original table. For example, assume the emp_department_1 table has some records in it and we want to change the datatype for the MANAGER column: CREATE TABLE temp AS SELECT * FROM emp_department_1; DELETE FROM emp_department_1; ALTER TABLE emp_department_1 MODIFY (manager VARCHAR2(15)); INSERT INTO emp_department_1 SELECT * FROM temp; DROP TABLE temp; This trick can also be used to drop a column from a table. Assume the Employee table has the following columns: fname, minit, lname, ssn, bdate, address, sex, salary, superssn and dno, and we want to drop the salary column from the table: CREATE TABLE temp AS SELECT fname, minit, lname, ssn, bdate, address, sex, superssn, dno FROM employee; DROP TABLE employee; CREATE TABLE employee AS SELECT * FROM temp;

The DROP TABLE command can be used to drop a table definition and all of its data from the database. In the following example, the table emp_department_1 created previously, is dropped from the database.

SQL&amp;amp;gt; DROP TABLE emp_department_1 ; Table dropped.

Tech Area

Pages

Sunday, June 20, 2010

SQL Data Definition Language

Create, Modify and Drop Tables, Views and Sequences

Creating, Altering and Dropping Tables

No comments:

Post a Comment