Creating a Copy of a Table

Sheila wants to copy the EMPLOYEES table so that she can practice without affecting the real data. Watch Sheila create a copy of the table by using the Object Browser.


Sheila wants to have a copy the LOCATIONS table. Watch Sheila create a copy of the table by using the appropriate SQL statement.

Creating Tables Using SQL

Sheila needs to create the AUDIT_RECORD_TB1 table. This table will contain two columns. The user_value column is of the data type varchar2, and the date_recorded column is of the data type timestamp. Later, Sheila will use this table to record audit information when the salary column in the EMPLOYEES table changes.

Watch Sheila create the table AUDIT_RECORD_TB1 by using the SQL CREATE statement.

Creating Tables by Using the Object Browser

Sheila needs to create the DEPENDENTS table, which will contain the following columns: Id, FirstName, LastName, BirthDate, Relation, Gender, Benefits, and RelativeId.

In the DEPENDENTS table, no two rows have the same ID. The Gender column holds only one value of M or F. Also, the Benefits column stores large blocks of character data.

Watch Sheila create the DEPENDENTS table using the Object Browser.

Creating Tables

You create tables with the SQL CREATE TABLE statement. With Oracle Database XE, you have two options for creating tables.

* Use the graphical interface that generates the SQL statement
* Enter the CREATE TABLE statement in the SQL Workshop tool

When creating tables, you must provide:

* Table name
* Column name(s)
* Data types for each column

Guidelines for creating tables:

* Table and column naming rules

Must start with a letter, which is followed by a sequence of letters, numbers, _, #, or $

Must be 1 to 30 characters long

Must not be an Oracle server reserved password


* Most common data types

VARCHAR2
NUMBER
DATE
TIMESTAMP
CHAR



You can also set up constraints on your columns to control the data in them.

Working with Database Objects

Oracle Database XE provides an organized mechanism for storing, managing, and retrieving information.Tables are the basic storage structure for holding business data. In this module, you learn how to create tables and work with them.

You may want to modify data entered in tables. You may also want to maintain integrity with the data. Sometimes, you may want to remove tables that are no longer useful.

Now that Sheila has the Oracle Database XE software installed and working, and has familiarized herself with the tables in the HR schema, her next task is to build some tables and database objects. In the demonstrations, you watch Sheila create and modify tables, manage constraints, and remove tables.

Relational data base

What Is a Relational Database?

The concept of a relational database was originally developed back in 1970 by Dr. E.F. Codd. He
laid down the theory of relational databases in his seminal paper entitled “A Relational Model of
Data for Large Shared Data Banks,” published in Communications of the ACM (Association for
Computing Machinery), Vol. 13, No. 6, June 1970.
The basic concepts of a relational database are fairly easy to understand. A relational database
is a collection of related information that has been organized into tables. Each table stores data in
rows; the data is arranged into columns. The tables are stored in database schemas, which are
areas where users may store their own tables. A user may grant permissions to other users so they can access their tables. Most of us are familiar with data being stored in tables

Introducing the Structured Query Language (SQL)

Structured Query Language (SQL) is the standard language designed to access relational
databases. SQL should be pronounced as the letters “S-Q-L.”

NOTE
“S-Q-L” is the correct way to pronounce SQL according to the American National Standards Institute. However, the single word “sequel” is frequently used instead.

SQL is based on the groundbreaking work of Dr. E.F. Codd, with the first implementation of
SQL being developed by IBM in the mid-1970s. IBM was conducting a research project known as System R, and SQL was born from that project. Later, in 1979, a company then known as Relational Software Inc. (known today as Oracle Corporation) released the first commercial version of SQL. SQL is now fully standardized and recognized by the American National Standards Institute. SQL uses a simple syntax that is easy to learn and use. You’ll see some simple examples of its use in this chapter. There are five types of SQL statements, outlined in the following list:

Query statements retrieve rows stored in database tables. You write a query using the

SQL SELECT statement.
Data Manipulation Language (DML) statements modify the contents of tables. There are
three DML statements:
INSERT adds rows to a table.
UPDATE changes rows.
DELETE removes rows.

Data Definition Language (DDL) statements define the data structures, such as tables,
that make up a database. There are five basic types of DDL statements:

CREATE creates a database structure. For example, CREATE TABLE is used to create
a table; another example is CREATE USER, which is used to create a database user.

ALTER modifies a database structure. For example, ALTER TABLE is used to modify
a table.

TRUNCATE deletes all the rows from a table.

What Is a Relational Database

What Is a Relational Database?

The concept of a relational database was originally developed back in 1970 by Dr. E.F. Codd. He
laid down the theory of relational databases in his seminal paper entitled “A Relational Model of
Data for Large Shared Data Banks,” published in Communications of the ACM (Association for
Computing Machinery), Vol. 13, No. 6, June 1970.
The basic concepts of a relational database are fairly easy to understand. A relational database
is a collection of related information that has been organized into tables. Each table stores data in
rows; the data is arranged into columns. The tables are stored in database schemas, which are
areas where users may store their own tables. A user may grant permissions to other users so they can access their tables. Most of us are familiar with data being stored in tables

Introducing the Structured Query Language (SQL)

Structured Query Language (SQL) is the standard language designed to access relational
databases. SQL should be pronounced as the letters “S-Q-L.”

NOTE
“S-Q-L” is the correct way to pronounce SQL according to the American National Standards Institute. However, the single word “sequel” is frequently used instead.

SQL is based on the groundbreaking work of Dr. E.F. Codd, with the first implementation of
SQL being developed by IBM in the mid-1970s. IBM was conducting a research project known as System R, and SQL was born from that project. Later, in 1979, a company then known as Relational Software Inc. (known today as Oracle Corporation) released the first commercial version of SQL. SQL is now fully standardized and recognized by the American National Standards Institute. SQL uses a simple syntax that is easy to learn and use. You’ll see some simple examples of its use in this chapter. There are five types of SQL statements, outlined in the following list:

Query statements retrieve rows stored in database tables. You write a query using the

SQL SELECT statement.
Data Manipulation Language (DML) statements modify the contents of tables. There are
three DML statements:
INSERT adds rows to a table.
UPDATE changes rows.
DELETE removes rows.

Data Definition Language (DDL) statements define the data structures, such as tables,
that make up a database. There are five basic types of DDL statements:

CREATE creates a database structure. For example, CREATE TABLE is used to create
a table; another example is CREATE USER, which is used to create a database user.

ALTER modifies a database structure. For example, ALTER TABLE is used to modify
a table.

DROP removes a database structure. For example, DROP TABLE is used to remove a
table.

RENAME changes the name of a table.

TRUNCATE deletes all the rows from a table.