Department of Computer Science and Engineering (CSE)
Creation of Table
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
What is Table?
A table can represent a single entity (entity is an
object whose information is stored in database)
that you want to track within your system. Each
entity or table has number of characteristics. The
table must have a unique name through which it
can be referred to after its creation .The
characteristics of the table are called its
attributes. These attributes can hold data.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
What is Table?
This type of a table could represent a list of the
employees within your organization, or the orders placed
for your company's products. A table has one attribute,
which identifies each record uniquely; this attribute is
called as Primary Key. Each value in the Primary Key
attribute is unique and it cannot be NULL. Each record of
the table is called as tuple.
A table can also represent a relationship between two
entities. This type of a table could portray the association
between employees and their job skills, or the
relationship of products to orders. Within the tables,
foreign keys are used to represent relationships.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
• Table name and Column name can be 1 to 30 characters long. First character
must be alphabetic, but name may include letters, numbers and underscores.
• Names must contain only the characters A-Z, a-z, 0-9,
_(underscore),$ and # .
Table creation rules
• Names must not be an Oracle Server reserved word.
• Names must not duplicate the names of other objects owned by the same
Oracle server user.
• Table name is not case sensitive.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
SYNTAX
• Create Table [schema]
tablename (
{Column datatype [DEFAULT expr] [column constraint],
column datatype[DEFAULT expr] [column constraint],
…
[table_constraint]}
);
Create Table
table_name( column_name
datatype(width), column_name
datatype(width),…….);
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
Schema
It is the schema to contain the table. If it is
omitted, Oracle creates the table in creator’s own
schema.
Table name
It is the name of the table to be created. Table name
and Column name can be 1 to 30 characters long.
First character must be alphabetic, but name may
include letters, numbers and underscores. The table
name cannot be the name of another object owned
by same user and cannot be a reserved word.
Column
Specifies the name of a column of the table. The
number of columns in a table can range from 1 to 1000.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
Datatype
It is the datatype of a column. Specifies the
type and width of data to be stored in the
column.
Default
It specifies a value to be assigned to the column
if a subsequent INSERT statement omits a value
for the column. The datatype of the expression
must match the datatype of the column. A
DEFAULT expression cannot contain references
to other columns, the pseudo columns CURRVAL,
NEXTVAL, LEVEL and ROWNUM or data
constants that are not fully specified.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
SQL> Create table student
(Name varchar2 (20),
Class varchar2 (15),
Roll_no number (4),
Address varchar2 (30));
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
Example
∙Create a table student with the fields name, roll_no,
address and phone number.
SQl> Create table student
(Name varchar2 (20) CONSTRAINT NN_NAME NOT
NULL,
Roll_no number (5) NOT NULL,
Address varchar2 (40),
phone_no varchar2 (10));
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
Example
SQL>CREATE TABLE sale(
code char(4) NOT NULL,
description VARCHAR2(15) CONSTRAINT desc_item NOT
NULL,
quantity NUMBER(5), sale_date DATE, notes CHAR(15) NULL);
Above statement creates a table with fields code and
description which is mandatory i.e. cannot be left empty and
notes which is optional i.e. might not contain data.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
Creating a Table with rows from another table
A table is created using CREATE TABLE statement with
rows in place, derived from another table.
Syntax
CREATE TABLE table_name
[(Column
name……………)] AS
SELECT statement;
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
The table will be created with the specified columns and
the rows retrieved by the SELECT statement inserted into
it. If all the columns in the SELECT statement have well
defined names, (that is, no expressions, and so on), the
column specifications may be omitted. If column
specifications are given, the number of columns must
equal the number of items in the SELECT list.
Constraints information is inherited from the selected
table. Data type for the column cannot be specified.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
Example
∙Create a table emp2 from emp table having employee number,
name, mgr, and sal of employees in department 10 and 20.
SQL>CREATE TABLE emp2
AS
SELECT empno, ename, mgr, sal FROM emp
WHERE deptno in (10,20);
Table created
The above table contains all the records present in emp table
having deptno 10 or deptno 20. Also the constraints attached to
columns in emp table are attached to the columns of emp2 table.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
∙Create a table Salary from table emp having empno,
ename and salary details.
SQL> CREATE TABLE salary(employee_number,Name,
Salary)
AS
SELECT empno, ename, sal FROM emp;
Table created
The above table contains records of table emp
having empno, ename, salary details.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
Creating table from an existing Table
In addition to creating table as above, we can
also create a new table from the existing table
also .We apply the AS sub-query clause to
both create the table and insert rows
returned from the sub query. For example;
SQL> Create table emp1
AS
Select empno, ename, hiredate, sal from
EMP where comm IS NULL;
University Institute of Engineering (UIE)
