Oracle Learning - 8

SQL: CREATE Table

The basic syntax for a CREATE TABLE is:
CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
);
Each column must have a datatype. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.

For example:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)

SQL: CREATE Table from another table

You can also create a table from an existing table by copying the existing table's columns.
It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

Syntax #1 - Copying all columns from another table
The basic syntax is:
CREATE TABLE new_table
AS (SELECT * FROM old_table);

For example:
CREATE TABLE suppliers
AS (SELECT *
FROM companies
WHERE id > 1000);
This would create a new table called suppliers that included all columns from the companies table.
If there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #2 - Copying selected columns from another table
The basic syntax is:
CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n FROM old_table);

For example:
CREATE TABLE suppliers
AS (SELECT id, address, city, state, zip
FROM companies
WHERE id > 1000);
This would create a new table called suppliers, but the new table would only include the specified columns from the companies table.
Again, if there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #3 - Copying selected columns from multiple tables
The basic syntax is:
CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n
FROM old_table_1, old_table_2, ... old_table_n);


For example:
CREATE TABLE suppliers
AS (SELECT companies.id, companies.address, categories.cat_type
FROM companies, categories
WHERE companies.id = categories.id
AND companies.id > 1000);
This would create a new table called suppliers based on columns from both the companies and categories tables.

SQL: ALTER Table

The ALTER TABLE command allows you to add, modify, or drop a column from an existing table.

Adding column(s) to a table
Syntax #1
To add a column to an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
ADD column_name column-definition;
For example:
ALTER TABLE supplier
ADD supplier_name varchar2(50);
This will add a column called supplier_name to the supplier table.

Syntax #2
To add multiple columns to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD ( column_1 column-definition,
column_2 column-definition,
...
column_n column_definition );
For example:

ALTER TABLE supplier
ADD ( supplier_name varchar2(50),
city varchar2(45) );
This will add two columns (supplier_name and city) to the supplier table.

Modifying column(s) in a table
Syntax #1
To modify a column in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
MODIFY column_name column_type;
For example:
ALTER TABLE supplier
MODIFY supplier_name varchar2(100) not null;
This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

Syntax #2
To modify multiple columns in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY ( column_1 column_type,
column_2 column_type,
...
column_n column_type );
For example:

ALTER TABLE supplier
MODIFY ( supplier_name varchar2(100) not null,
city varchar2(75) );
This will modify both the supplier_name and city columns.

Drop column(s) in a table
Syntax #1
To drop a column in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
DROP COLUMN column_name;
For example:
ALTER TABLE supplier
DROP COLUMN supplier_name;
This will drop the column called supplier_name from the table called supplier.

Rename column(s) in a table
(NEW in Oracle 9i Release 2)
Syntax #1
Starting in Oracle 9i Release 2, you can now rename a column.
To rename a column in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
For example:
ALTER TABLE supplier
RENAME COLUMN supplier_name to sname;
This will rename the column called supplier_name to sname.

SQL: DROP Table

The basic syntax for a DROP TABLE is:
DROP TABLE table_name;

For example:
DROP TABLE supplier;
This would drop table called supplier.


SQL: Global Temporary tables

Global temporary tables are distinct within SQL sessions.
The basic syntax is:
CREATE GLOBAL TEMPORARY TABLE table_name ( ...);

For example:

CREATE GLOBAL TEMPORARY TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)
This would create a global temporary table called supplier .

SQL: Local Temporary tables

Local temporary tables are distinct within modules and embedded SQL programs within SQL sessions.
The basic syntax is:
DECLARE LOCAL TEMPORARY TABLE table_name ( ...);

SQL: VIEWS

A view is, in essence, a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables.
The syntax for a VIEW is:
CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;


For example:
CREATE VIEW sup_orders AS
SELECT supplier.supplier_id, orders.quantity, orders.price
FROM supplier, orders
WHERE supplier.supplier_id = orders.supplier_id
and supplier.supplier_name = 'IBM';
This would create a virtual table based on the result set of the select statement. You can now query the view as follows:
SELECT *
FROM sup_orders;

Frequently Asked Questions

Question: Can you update the data in a view?
Answer: A view is created by joining one or more tables. When you update record(s) in a view, it updates the records in the underlying tables that make up the view.
So, yes, you can update the data in a view providing you have the proper privileges to the underlying tables.