Assignment No. 2 (a)
Title: Design and Develop SQL DDL statements which demonstrate the use of SQL objects
such as Table, View , Index, Sequence, Synonym
Objectives: To study SQL DDL statements
Theory: SQL~ Structured Query Language
Data
Definition in
SQLCreating
Tables
Syntax:-
Create table
(colume_name 1
datatype size),
colume_name 2
datatype size(),
colume_name n datatype size());
e.g. Create table student with the following fields(name,roll,class,branch)
Create table student
(name
char(20),
Roll
number(
5), Class
char(10),
Branch
char(15));
Atable from a table
+ Synta
CREATE TABLE(,
)ASSELECT ,
FROM ;
- If the source table contains the records, then new table is also created with the same
records present in the source table.
Department of Computer Engineering, SCOE, Pune Page 19 If you want only structure without records then select statement must havecondition.
Syntax:
CREATE TABLE (, ) ASSELECT
, FROM WHERE 1=2; (Or)
CREATE TABLE (, ) ASSELECT
, FROM WHERE ColumnName
JULL;
Constraints
The definition of a table may include the specification of integrity constraints. Basicallytwo
types of constraints are provided: column constraints are associated with a single column
whereas table constraints are typically associated with more than one column. Aconstraint
can be named. It is advisable to name a constraint in order to get more meaningful
information when this constraint is violated due to, e.g,, an insertion of a tuple that violates
the constraint. If no name is specified for the constraint, Oracle automatically generates a
name of the pattern SYS Ccnumber>.Rules are enforced on data being stored in a table, are
called Constraints.
Both the Create table & Alter Table SQL can be used to write SQL
sentences thatattach constraints.
Basically constraints are of three types :
1) Domain
- Not Null
~ Check
2) Entity
- Primary Key
~ Unique
3) Referential
- Foreign key
4) Not Null:-Not null constraint can be applied at column level only.
‘We can define these constraints
1) at the time of table creation Syntax :
CREATE TABLE ( datatype(size) NOT NULL,
datatype(size),..._);
Department of Computer Engineering, SCOE, Pune Page 20 2) After the table creation
ALTER TABLE
Modify( datatype(size)
NOT NULL);
Check constraints
Can be bound to column or a table using CREATE TABLE
or ALTER TABLEcommand.
Checks are performed when write operation is performed .
- Insert or update statement causes the relevant check constraint.
- Ensures the integrity of the data in tables.
‘Syntax :
+ Check constraints at column levelSyntax :
CREATE TABLE (data
type(size)CHECK(column Namecondition),
( data type(size) CONSTRAINT
CHECK (column Name condition)...
%
* Check constraints at table level Syntax :
(CREATE TABLE
( data type(size),
data type(size),
CONSTRAINT CHECK (column Name condition),
Check constraints at table level
Syntax:
CREATE TABLE
( data type(size),
data type(size).....,
CHECK (column Name
condition));After table
creation
Alter table tablename
Add constraints constraintname
Department of Computer Engineering, SCOE, Pune Page 21 check(condition)The PRIMARY KEY Constraint
Aprimary key is one or more column(s) in a table used to uniquely identity
each row inthe table.
* Atable can have only one primary key. Can not be left blank Data must be
UNIQUE.
* Not allows null values
* Not allows duplicate values.
+ Unique index is created automatically if there is a
primary key. Primary key constraint defined at column level
Syntax:
(CREATE TABLE
( ()PRIMARY
KEY,).....);
* Primary key constraint defined at
Table levelSyntax:
(CREATE TABLE
( () ssPRIMARY
KEY( ));
* key constraint defined at Table level
Syntax:
CREATE TABLE
( ()constraint
constraint namePRIMARY KEY());
After table creation
Alter table table name
Add(constraint constraint name primary key(column name));
The Unique Key Constraint
~The unique column constraint permits multiple entries of
NULL into thecolumn.
Unique key not allowed duplicate values
Unique index is automatically created.
Table can have more than one unique key.
UNIQUE constraint defined at
Department of Computer Engineering, SCOE, Pune Page 22 column levelSyntax :
Create table tablename( (
UNIQUE), datatype()..
UNIQUE constraint defined at
table levelSyntax:
CREATE TABLE tablename ( (),
—_(), UNIQUE(,
));
After table creation
Alter table tablename
‘Add constraint constraintname unique(columnname);
The Foreign Key (Self Reference) Constraint Foreign key represents
relationshipsbetween tables.
A foreign key is @ column( or group of columns) whose values are derived
from primarykey or unique key of some other table.
Foreign key constraint defined at
column levelSyntax:
() REFERENCES
[()][ON DELETE CASCADE]
* Ifthe ON DELETE CASCADE option is set, a DELETE operation in the
+ master table will trigger a DELETE operation for corresponding records in all detailtables.
* If the ON DELETE SET NULL option is set, a DELETE operation in the
master table will set the value held by the foreign key of the detail tablesto
null.
Foreign key:
ALTER TABLE ADD CONSTRAINT
FOREIGNKEY () REFERENCES ;
FOREIGN KEY constraint at table level
1) FOREIGN KEY constraint defined with ON DELETE
CASCADE FOREIGN
KEY([,]) REFERENCES
[(, ) ON DELETE
CASCADE
Department of Computer Engineering, SCOE, Pune Page 23 * FOREIGN KEY constraint defined with ON DELETE SET NULL
* FOREIGN KEY([.]) REFERENCES
[(, ) ON DELETE SETNULL
To view theconstraint
‘Syntax:
Select constraint_name, constraint type,search_condition from
user_constraints where table name=;
Select constraint_name, column_name from
user_cons_columns wheretable_name=;
To drop the
constraintsSyntax:-
Drop constraint constraint name;
Describe commands
To view the structure of the table created use the DESCRIBE command.Thecommand
displays the column names and datatypes
Syntax-
Desc{ribe]
e.g desc student
Restrictions for creating a table:
1. Table names and column names must begin with letter.
2. Table names and column names can be 1 to 30characters long.
3. Table names must contain only the characters A-Z,a-z,0-9,underscore_$ and #
4. Table name should not be same as the name of another database object.
5. Table name must not be an ORACLE reserved word.
6. Column names should not be duplicate within a table definition.
Alteration of TABLE:-Alter table command
Syntax:
Case1:-
Alter table
‘Add{( colume_name 1
datatype size(),
colume_name 2
datatype size0,
colume_name n datatype size);
Department of Computer Engineering, SCOE, Pune Page 24 Case2:-
Alter table
Modify(colume_name 1
datatype size),
colume_name 2 datatype sizeQ,
colume_name n datatype size());
After you create a table, you may need to change the table structures because you need to
havea column definition needs to be changed. Alter table statement can be used for this
purpose.
You can add columns to a table using the ater table statement with the ADD clause.
Eg. Suppose you want to add enroll_no in the student table then we write
Alter table student Add(enroll_no number(10));
You can modify existing column in a table by using the alter table statement with modify
clause.
Eg, Suppose you want to modify or chang the size of previously defined field name in the
student table then we write
Alter table student modify (name char(25));Dropping a column from a table
Syntax:
ALTER TABLE DROP COLUMN ;
Drop table command Syntax:-
Drop table
Drop table command remnoves the definitions of an oracle table. When you drop a table
ithe database loses all the data in the table and all the indexes associated with it
e.g drop table student;
‘Truncate table commandSyntax:-
Trunc table
The truncate table statement is used to remove all rows from a table and to release the
storagespace used by the table.
e.g, Trunctable student;
Rename table commandSyntax:
Rename to
Rename statement is used to rename a table,view,sequence,or synonym.
Department of Computer Engineering, SCOE, Pune Page 25 e.g. Rename student to stud;
Database objects:-Index
‘An index is a schema object that can speed up retrieval of rows by using pointer.
‘An index provides direct & fast access to rows in a table, Index can be created
explicitly or automatically.
Automatically :- A unique index is created automatically when you define a primary key or
unique key constraint in a table definition.
Manually :- users can create non unique indexes or columns to speed up access time to the
rows.
Syntax:
Create index On table(columnf , column]...
Eg. Create index emp_ename_idx On emp(ename);
When to create an index
a) The column is used frequently in the WHERE clause or in a join condition.
b) The column contains a wide range of values.
©) The column contains a large number of values. To display created index of a table
user_indexes ix, user_ind_columns ic where
iciindex_nami
Removing an Index
‘Syntax:-
Drop index
; eg. Dropindex
emp_name_idx;
Note: 1) we cannot modify indexes.
2)To change an index, we must drop it and the re-create it.
Views
View is a logical representation of subsets of data from one or more tables. A view takes the
output of a query and treats it as a table therefore view can be called as stored query or a
virtual table.The tables upon which a view is based are called base tables, In Oracle the SQL
command to create a view (virtual table) has the form
Create [or replace] view [()] as
[with check option [constraint ];
‘The optional clause or replace re-creates the view ifit already exists. names the
columns of the view. If is not specified in the view definition, the columns of the
Department of Computer Engineering, SCOE, Pune Page 26 view get the same names as the attributes listed in the select statement (if possible).
Example: The following view contains the name, job title and the annual salary of employees
working in the department 20:
Create view DEPT20 as select ENAME, JOB, SAL 12 ANNUAL SALARY from EMP where
DEPTNO = 20;
In the select statement the column alias ANNUAL SALARY is specified for the
expressionSAL*12 and this alias is taken by the view. An alternative
formulation of the above view
definition is
Create view DEPT20 (ENAME, JOB, ANNUAL SALARY) as select ENAME, JOB, SAL
A view can be used in the same way as a table, that is, rows can be retrieved from a view(also
respective rows are not physically stored, but derived on basis of the select statement inthe
viewdefinition), or rows can even be modified. A view is evaluated again each time it is
accessed. In Oracle SQLno insert, update, or delete modifications on views are allowed
that use one of the following constructs in the view definition:
*Joins
* Aggregate function such as sum, min, max etc.
* set-valued sub queries (in, any, all) or test for existence (exists)
* group by clause or distinct clause
In combination with the clause with check option any update or insertion of a
row into the view is rejected if the new/modified row does not meet the view
definition, ie, these rows would notbe selected based on the select statement. A
with check option can be named using the constraintclause.
A view can be deleted using the command delete _. Todescribe the structure of a
view
e.g. Describe stud;
To display the contents of view e.g. Select * from stud
Removing a view:
Syntax:- Drop view
e.g. Drop view stud
Create [or replace] view [()] as
[with check option [constraint ];
Department of Computer Engineering, SCOE, Pune Page 27 The optional clause or replace re-creates the view if it already exists. names the
columns of the view. If is not specified in the view definition, the columns of the
view get the same names as the attributes listed in the select statement (if possible),
Example: The following view contains the name, job title and the annual salary of employees
working in the department 20:
Create view DEPT20 as select ENAME, JOB, SAL 12 ANNUAL SALARY from EMP where
DEPTNO = 20;
In the select statement the column alias ANNUAL SALARY is specified for the expression
‘SAL*12 and this alias is taken by the view. An alternative formulation of the above view
definition i
12 from EMP where DEPTNO = 20;
Create view DEPT20 (ENAME, JOB, ANNUAL SALARY) as select ENAME, JOB, SAL
A view can be used in the same way as a table, that is, rows can be retrieved from a view(also
respective rows are not physically stored, but derived on basis of the select statement in the
viewdefinition), or rows can even be modified.
Sequence:
‘A sequence is a database object, which can generate unique, sequential integer
values. It can be used to automatically generate primary key or unique key
values. A sequence can be either in anascending or descending order.
{start with n]
[imaxvalue n | nomaxvalue}] [{minvalue n|nominvalue}]
[cycle |nocycle}]
{cache n| nocache}:
Increment by n Specifies the interval between
Jsequence number where n isan
integer. If this clause is omitted, the
|sequence is increment by 1.
Start with n Specifies the first sequence
number tobe generated. If this
[clause is omitted ,
[the sequence is start with 1.
Manvalue n Specifies the maximum value, the
[sequence can generate
Department of Computer Engineering, SCOE, Pune Page 28 Nomax value n Specifies the maximum value of
10e27-1 for an ascending sequence
& -1 for descending sequence. This
is a default option.
Minvalue n Specifies the minimum sequence value.
Nominvalue n Specifies the minimum value of 1
for an ascending & 10e26-1 for
Jdescending sequence. This isa
default option.
Cycle Specifies that the sequence continues
ito generate values from the beginning
After creating a sequence we can access its values with the help of pseudo
columnslike curval & nextval.
Nextval :
nextval returns initial value of the sequence when reference to for the firsttime. Last references
to the nextval will increment the sequence using the increment by clause & returns the new
value.
Curval :
curval returns the current value of the sequence which is the value returned by the last
reference to last value
Modifyning a sequence:
The sequence can be modified when we want to perform the following :
Set or eliminate minvalue or maxvalue
+ Change the increment value.
Change the number of cache sequence number.
Syntax :
Alter sequence
[increment by n]
{start with n]
[{maxvalue n | nomaxvalue}]
[(minvalue n| nominvalue}]
{cycle | nocycle}]
[{cache n| nocache}];
Department of Computer Engineering, SCOE, Pune Page 29 A synonym isa database object which is used as an alias(alternative name)for a
table,view or sequence.
Tables
In relational database systems (DBS) data are represented using tables (relations).
‘Aquery issuedagainst the DBS also results in a table. A table has the following
structure:
Column 1
—Tuple
‘Acolumn is made up of a column name and a data type, and it describes an
attribute of the tuples. The structure of a table, also called relation schema, thus
is defined by its attributes. The type of information to be stored in a table is
defined by the data types of the attributes at table
creation time.SQL uses the terms table, row, and column for relation, tuple,
and attribute,respectively.
Atable can have up to 254 columns which may have different or same data types and
sets ovalues (domains), respectively. Possible domains are alphanumeric data (strings),
numbers and date formats.
Department of Computer Engineering, SCOE, Pune Page 30 Variable length
character
stringhaving
VARCHARQ(s | aximum » . » VARCHAR
ize) length size
bytes.
Variable length]
national
character STRING
NVARCHAR2 setstring VARCHAR,
(size) having
maximum
length size
bytes.
Now
deprecated
- VARCHAR is
VARCHAR | Synonym
for
VARCHAR2
but this usage
may change in
future versions,
Fixed length
character data
oflength size bytes
| bytes. This Default. | CHARAC
CHAR(size) minimum | and TER
minimu bizeis 1 byte) minimum
data. Such as Im size is] sizeis 1
codes A100, byte. byte.
B102..
Fixed length 2000 bytes
national Default and
character set minimum
size
Department of Computer Engineering, SCOE, Pune Page 31 data of length
size bytes.
Thisshould be
used for fixed
lengthdata.
Such as codes
‘A100, B10:
Number
NUMBER(ps| having
) precision p
andscale s.
PLSNTEG | pLs INTEGER
ER values require
less storage
and
The
precisionp
can range
from 1 to
28.
The scales
can range
from -84
to 127.
Department of Computer Engineering, SCOE, Pune
land minimufis 1 byte}
m size ist
byte.
The
precision p
can range
from 1
10 38.
The scale s
can range
from -84
to 127.
and
minimum
size is 1
10E125
maximum
38 decimal
digits
The scales
can range
from -84
Ito 127. For
floating
point don't
specifyps
REAL
has a
maximum
precision of
63 binary
digits, which
is roughly
magnitud
e range is
- 21474836
floating-
point:
DOUBLE
PRECISIO.
NFLOAT
binary_do
uble
binary_flo
at
integers:
INTEGER
SMALLIN
Page 32 provide better 47.
performance 21474836
than NUMBER 47
values.
So.use
PLS_INTEGER
where you can!
BINARY_INT signed integers magnitude | NATURAL
EGER (older slower rangeis | NATURA
version of ~ 21474836 LN
PLS_INTEGER) 47... | positive
21474836 | POSITIVEN
47 SIGNTYP
(Character data 2 32760
of variable 2 Icigabytes |Gigabytes | bytes Note
length (A |igabytes| this is
biggerversion |smalller than
the
maximum
width of a
LONG
to
December
31,4712
AD.
timestamp | the number off
(fractional se | _“gits in the
< fractional part
onds_precisio | fhe SECOND]
n) datetime field.|
Department of Computer Engineering, SCOE, Pune
Accepted
values of
ractional_s}
ec
londs_preci
sion are 0
woo
(default =
6) and months,
where
year precision is
the number of
digits in the
YEAR datetime
field.
Time in days,
hours, minutes,
and seconds.
day precision is
the maximum
number of digits
in DAY’
fractional_secon
lds_precision is the
max numberof
fractional digits i
the SECOND field]
LDAY
(day_precisi
on)TO
SECOND
(fractional_
sec
londs_precis
ion
)
Raw binary data
of length size
bytes.
You must
specify size fora
RAW value.
Raw binary data
of variable
length. (not
intrepreted by
PL/SQL)
LONG RAW
Hexadecimal
string
representing the
unique address
of arow inits
table.
Department of Computer Engineering, SCOE, Pune
Maximum:
size is2000
bytes Note this|
is smalller than
‘the maximum
width of a
LONG RAW
pseudocol
umn.)
Page 34 universal
rowid -
Hex string
representi
ng the
Hex string logical
representing address of } CHARTO
thelogical i ja row of an} ROWID
address of a is | index-
row of an organized | package:
index- table,
organizedtable either
physical,
logical, or
foreign
(non-
Oracle)
Binary format o'
an operating
system
MLSLABEL | — label.This
datatype is
used with
Trusted
Oracle7.
(Character Large[4Gigabyt
Object s
National
Character ASI9aby) Gigabytes | *193byt
tes es
LargeObject
The size
lof a BFILE
pointer to
binaryfile on
disk
Binary Large 4Gigaby| ‘4Gigabyt
Department of Computer Engineering, SCOE, Pune Page 35
