The CREATE TABLE statement allows you to create and define a table.
The basic syntax for a CREATE TABLE statement is:
CREATE TABLE table_name
( column1 datatype null/not null,
column2 datatype PRIMARY KEY, column3 datatype PRIMARY KEY,
…
);
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.
Example 1:
CREATE TABLE XX_PO_HEADERS_ALL
(
PO_ID NUMBER(12) PRIMARY KEY,
PO_NUMBER NUMBER(12),
SUPPLIER_NAME VARCHAR2(12) NOT NULL,
SUPPLIER_SITE VARCHAR2(12),
SHIP_TO VARCHAR2(12),
BILL_TO VARCHAR2(12)
)
Example 2:
CREATE TABLE XX_PO_LINES_ALL
(
PO_ID NUMBER(12),
LINE_NUMBER NUMBER(12),
ITEM_NAME VARCHAR2(12),
QUANTITY NUMBER(12),
ITEM_PRICE NUMBER(12),
ITEM_TAX NUMBER(12),
LINE_PRICE NUMBER(12),
ORDER_DATE DATE,
NEED_DATE VARCHAR2(12),
BILL_TO VARCHAR2(12)
)
Column Default Values
You can define tables to populate columns with default values as well using the default clause in a create table command. This clause is included as part of the column definition to tell Oracle what the default value for that column should be. When a row is added to the table and no value is defined for the column in the row being added, Oracle populates the column value for that row using the default value for the column. The following code block illustrates this point:
SQL> create table display
2 (col1 varchar2(10),
3 col2 number default 0);
Table created.
CREATE a table from another table
You can also create a table from an existing table by copying the existing table’s columns.
The basic syntax is:
CREATE TABLE table_name
AS (SELECT * FROM old_table);
Example1:
CREATE TABLE XX_PO_HEADERS_ALL_COPY
AS (Select * From XX_PO_HEADERS_ALL)
The above statement ‘ll create a new table that is just an exact copy of XX_PO_HEADERS_ALL.
Example 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);
Example 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);
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). If you want to create a blank table then use a condition which is always false in the where clause of the select statement.
Creating Temporary Tables
Most of the time, when you create a table in Oracle, the records that eventually populate that table will live inside your database forever (or at least until someone removes them). However, there might be situations where you want records in a table to live inside the database only for a short while. In this case, you can create temporary tables in Oracle, where the data placed into the tables persists for only the duration of the user session, or for the length of your current transaction.
A temporary table is created using the create global temporary table command. Why does a temporary table have to be global? So that the temporary table’s definition can be made available to every user on the system. However, the contents of a temporary table are visible only to the user session that added information to the temporary table, even though everyone can see the definition. Temporary tables are a relatively new feature in Oracle, and Oracle hasn’t had enough time yet to implement “local” temporary tables (that is, temporary tables that are only available to the user who owns them). Look for this functionality in later database releases. The appropriate create global temporary table command is shown in the following code block:
Create global temporary table XXX_PO_HEADERS_ALL as
Select *
From PO_HEADERS_ALL
Where 10=11
The purpose of writing the where clause is to make the temporary table blank. If we dont put the where clause the temporary table would contain all the rows of XXX_PO_HEADERS_ALL
Alter Table
Renaming a table
The basic syntax for renaming a table is:
ALTER TABLE table_name
RENAME TO new_table_name;
For example:
ALTER TABLE suppliers
RENAME TO vendors;
This will rename the suppliers table to vendors.
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 );
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;
Modifying column(s)(datatypes) 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 );
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;
Share this:
Create TABLE
The basic syntax for a CREATE TABLE statement is:
CREATE TABLE table_name
( column1 datatype null/not null,
column2 datatype PRIMARY KEY, column3 datatype PRIMARY KEY,
…
);
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.
Example 1:
CREATE TABLE XX_PO_HEADERS_ALL
(
PO_ID NUMBER(12) PRIMARY KEY,
PO_NUMBER NUMBER(12),
SUPPLIER_NAME VARCHAR2(12) NOT NULL,
SUPPLIER_SITE VARCHAR2(12),
SHIP_TO VARCHAR2(12),
BILL_TO VARCHAR2(12)
)
Example 2:
CREATE TABLE XX_PO_LINES_ALL
(
PO_ID NUMBER(12),
LINE_NUMBER NUMBER(12),
ITEM_NAME VARCHAR2(12),
QUANTITY NUMBER(12),
ITEM_PRICE NUMBER(12),
ITEM_TAX NUMBER(12),
LINE_PRICE NUMBER(12),
ORDER_DATE DATE,
NEED_DATE VARCHAR2(12),
BILL_TO VARCHAR2(12)
)
Column Default Values
You can define tables to populate columns with default values as well using the default clause in a create table command. This clause is included as part of the column definition to tell Oracle what the default value for that column should be. When a row is added to the table and no value is defined for the column in the row being added, Oracle populates the column value for that row using the default value for the column. The following code block illustrates this point:
SQL> create table display
2 (col1 varchar2(10),
3 col2 number default 0);
Table created.
CREATE a table from another table
You can also create a table from an existing table by copying the existing table’s columns.
The basic syntax is:
CREATE TABLE table_name
AS (SELECT * FROM old_table);
Example1:
CREATE TABLE XX_PO_HEADERS_ALL_COPY
AS (Select * From XX_PO_HEADERS_ALL)
The above statement ‘ll create a new table that is just an exact copy of XX_PO_HEADERS_ALL.
Example 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);
Example 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);
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). If you want to create a blank table then use a condition which is always false in the where clause of the select statement.
Creating Temporary Tables
Most of the time, when you create a table in Oracle, the records that eventually populate that table will live inside your database forever (or at least until someone removes them). However, there might be situations where you want records in a table to live inside the database only for a short while. In this case, you can create temporary tables in Oracle, where the data placed into the tables persists for only the duration of the user session, or for the length of your current transaction.
A temporary table is created using the create global temporary table command. Why does a temporary table have to be global? So that the temporary table’s definition can be made available to every user on the system. However, the contents of a temporary table are visible only to the user session that added information to the temporary table, even though everyone can see the definition. Temporary tables are a relatively new feature in Oracle, and Oracle hasn’t had enough time yet to implement “local” temporary tables (that is, temporary tables that are only available to the user who owns them). Look for this functionality in later database releases. The appropriate create global temporary table command is shown in the following code block:
Create global temporary table XXX_PO_HEADERS_ALL as
Select *
From PO_HEADERS_ALL
Where 10=11
The purpose of writing the where clause is to make the temporary table blank. If we dont put the where clause the temporary table would contain all the rows of XXX_PO_HEADERS_ALL
Share this:
DDL statements
This sub-category of SQL statements is of particular interest to database architects or database administrators who must define an original database design and who must respond to requirements and extend a database design. It is also of interest to database application developers, because there are times when the easiest way to meet an application requirement is to extend an existing database object definition.
In general DDL statements begin with one of the following keywords: CREATE, ALTER, or DROP. Examples of DDL statements for creating database objects include: CREATE TABLE, CREATE TRIGGER, CREATE PROCEDURE, and CREATE SEQUENCE. These statements generally contain multiple clauses used to define the characteristics and behavior of the database object. Examples of DDL statements for altering database objects include: ALTER TABLE, and ALTER PROCEDURE. These statements generally are used to alter a characteristic of a database object.
DDL statements can be executed from a variety of interactive and application interfaces although they are most commonly executed in scripts or from integrated development environments that support database and database object design.
Describing Tables
The best way to think of a table for most Oracle beginners is to envision a spreadsheet containing several records of data. Across the top, try to see a horizontal list of column names that label the values in these columns. Each record listed across the table is called a row. In SQL*Plus, the command describe enables you to obtain a basic listing of characteristics about the table.
SQL> DESCRIBE po_headers_all
Name Type Nullable Default Comments
————————– ————– ——– —————————
PO_HEADER_ID NUMBER
AGENT_ID NUMBER(9)
TYPE_LOOKUP_CODE VARCHAR2(25)
LAST_UPDATE_DATE DATE
LAST_UPDATED_BY NUMBER
SEGMENT1 VARCHAR2(20) R2(1)
Commenting Objects
You can also add comments to a table or column using the comment command. This is useful especially for large databases where you want others to understand some specific bits of information about a table, such as the type of information stored in the table. An example of using this command to add comments to a table appears in the following block:
SQL> comment on table employee is
2 ‘This is a table containing employees’;
Comment created.
You can see how to use the comment command for adding comments on table columns in the following code block:
SQL> comment on column employee.empid is
2 ‘unique text identifier for employees’;
Comment created.
Tip
Comment information on tables is stored in an object called USER_TAB_COMMENTS, whereas comment information for columns is stored in a different database object, called USER_COL_COMMENTS. These objects are part of the Oracle data dictionary. You’ll find out more about the Oracle data dictionary later in the book.
Share this:
Inline view : Subqueries in a from Clause
Select a.PO_HEADER_ID, a.Segment1, b.unit_price, b.Quantity
From PO_HEADERS_ALL a,
(
Select unit_price, Quantity, po_header_id
From PO_LINES_ALL
) b
Where a.PO_HEADER_ID=b.PO_HEADER_ID
Inline Views and Top-N QueriesTop-N queries use inline views and are handy for displaying a short list of table data, based on “greatest” or “least” criteria. For example, let’s say that profits for our company were exceptionally strong this year, and we want a list of the three lowest-paid employees in our company so that we could give them a raise. A top-N query would be useful for this purpose. Take a look at a top-N query that satisfies this business scenario:
SQL> select ename, job, sal, rownum
2 from (select ename, job, sal from emp
3 order by sal)
4 where rownum <=3;
ENAME JOB SAL ROWNUM
———- ——— ——— ———
SMITH CLERK 800 1
JAMES CLERK 950 2
ADAMS CLERK 1100 3
You need to know two important things about top-N queries for OCP. The first is their use of the inline view to list all data in the table in sorted order. The second is their use of ROWNUM—a virtual column identifying the row number in the table—to determine the top number of rows to return as output. Conversely, if we have to cut salaries based on poor company performance and want to obtain a listing of the highest-paid employees, whose salaries will be cut, we would reverse the sort order inside the inline view, as shown here:
SQL> select ename, job, sal, rownum
2 from (select ename, job, sal from emp
3 order by sal desc)
4 where rownum <=3;
ENAME JOB SAL ROWNUM
———- ——— ——— ———
KING PRESIDENT 5000 1
SCOTT ANALYST 3000 2
FORD ANALYST 3000 3
Share this:
Multiple-Column Subqueries
Select *
From PO_LINES_ALL
Where (PO_HEADER_ID, PO_LINE_ID) IN
(
Select PO_HEADER_ID, PO_LINE_ID
From PO_LINE_LOCATIONS_ALL
WHERE QUANTITY_RECEIVED < QUANTITY/2
AND CLOSED_CODE <> ‘CLOSED FOR RECEIVING’
)
The benefit of writing query in above format is that separating the requirements in tables. From PO_LINE_LOCATIONS_ALL we are only taking those data which are relevant for our purpose and our end aim is to view the PO_LINEA_ALL entries corresponding to some required conditions satisfied by entries in PO_LINE_LOCATIONS_ALL.
Share this: