Experiment No: 1

Title: Implementation of DDL commands of SQL with suitable examples

 Create table
 Alter table
 Drop Table

Objective:
To understand the different issues involved in the design and implementation of a
database system
To understand and use data definition language to write query for a database
Theory:
Oracle has many tools such as SQL * PLUS, Oracle Forms, Oracle Report Writer, Oracle
Graphics etc.

 SQL * PLUS: The SQL * PLUS tool is made up of two distinct parts. These are
 Interactive SQL: Interactive SQL is designed for create, access and
manipulate data structures like tables and indexes.
 PL/SQL: PL/SQL can be used to developed programs for different
applications.

 Oracle Forms: This tool allows you to create a data entry screen along with the
suitable menu objects. Thus it is the oracle forms tool that handles data gathering and
data validation in a commercial application.

 Report Writer: Report writer allows programmers to prepare innovative reports

using data from the oracle structures like tables, views etc. It is the report writer tool
that handles the reporting section of commercial application.

 Oracle Graphics: Some of the data can be better represented in the form of pictures.
The oracle graphics tool allows programmers to prepare graphs using data from oracle
structures like tables, views etc.

5
SQL (Structured Query Language):
Structured Query Language is a database computer language designed for
managing data in relational database management systems (RDBMS), and originally based
upon Relational Algebra. Its scope includes data query and update, schema creation and
modification, and data access control.
SQL was one of the first languages for Edgar F. Codd's relational model and became the
most widely used language for relational databases.
 IBM developed SQL in mid of 1970’s.
 Oracle incorporated in the year 1979.
 SQL used by IBM/DB2 and DS Database Systems.
 SQL adopted as standard language for RDBS by ASNI in 1989.

DATA TYPES:

1. CHAR (Size): This data type is used to store character strings values of fixed length.
The size in brackets determines the number of characters the cell can hold. The
maximum number of character is 255 characters.

2. VARCHAR (Size) / VARCHAR2 (Size): This data type is used to store variable length
alphanumeric data. The maximum character can hold is 2000 character.

3. NUMBER (P, S): The NUMBER data type is used to store number (fixed or floating
point). Number of virtually any magnitude may be stored up to 38 digits of precision.

Number as large as 9.99 * 10 124. The precision (p) determines the number of places to
the right of the decimal. If scale is omitted then the default is zero. If precision is
omitted, values are stored with their original precision up to the maximum of 38 digits.

4. DATE: This data type is used to represent date and time. The standard format is DD-
MM-YY as in 17-SEP-2009. To enter dates other than the standard format, use the
appropriate functions. Date time stores date in the 24-Hours format. By default the time

6
 in a date field is 12:00:00 am, if no time portion is specified. The default date for a date
field is the first day the current month.

5. LONG: This data type is used to store variable length character strings containing up to
2GB. Long data can be used to store arrays of binary data in ASCII format. LONG
values cannot be indexed, and the normal character functions such as SUBSTR cannot
be applied.

6. RAW: The RAW data type is used to store binary data, such as digitized picture or
image. Data loaded into columns of these data types are stored without any further
conversion. RAW data type can have a maximum length of 255 bytes. LONG RAW data
type can contain up to 2GB.

SQL language is sub-divided into several language elements, including:

 Clauses, which are in some cases optional, constituent components of statements and

queries.

 Expressions, which can produce either scalar values or tables consisting of columns and

rows of data.

 Predicates which specify conditions that can be evaluated to SQL three-valued logic

(3VL) Boolean truth values and which are used to limit the effects of statements and

queries, or to change program flow.

 Queries which retrieve data based on specific criteria.

 Statements which may have a persistent effect on schemas and data, or which may

control transactions, program flow, connections, sessions, or diagnostics.

 SQL statements also include the semicolon (";") statement terminator. Though not

required on every platform, it is defined as a standard part of the SQL grammar.

7
  Insignificant white space is generally ignored in SQL statements and queries, making it

easier to format SQL code for readability.

There are five types of SQL statements. They are:

1. DATA DEFINITION LANGUAGE (DDL)

2. DATA MANIPULATION LANGUAGE (DML)

3. DATA RETRIEVAL LANGUAGE (DRL)

4. TRANSATIONAL CONTROL LANGUAGE (TCL)

5. DATA CONTROL LANGUAGE (DCL)

1. DATA DEFINITION LANGUAGE (DDL): The Data Definition Language (DDL) is

used to create and destroy databases and database objects. These commands will primarily be
used by database administrators during the setup and removal phases of a database project.
Let's take a look at the structure and usage of four basic DDL commands:
1. CREATE 2. ALTER 3. DROP 4. RENAME

1. CREATE:
(a)CREATE TABLE: This is used to create a new relation (table)

Syntax: CREATE TABLE <relation_name/table_name >

(field_1 data_type(size),field_2 data_type(size), .. . );

Example:
SQL> CREATE TABLE Student (sno NUMBER (3), sname CHAR (10), class CHAR (5));

2. ALTER:
(a) ALTER TABLE ...ADD...: This is used to add some extra fields into existing
relation.

8
Syntax: ALTER TABLE relation_name ADD (new field_1 data_type(size), new field_2
data_type(size),..);
Example: SQL>ALTER TABLE std ADD (Address CHAR(10));

(b) ALTER TABLE...MODIFY...: This is used to change the width as well as data
type of fields of existing relations.

Syntax: ALTER TABLE relation_name MODIFY (field_1 newdata_type(Size), field_2

newdata_type(Size), ... field_newdata_type(Size));

Example:SQL>ALTER TABLE student MODIFY(sname VARCHAR(10),class

VARCHAR(5));

c) ALTER TABLE..DROP .... This is used to remove any field of existing relations.

Syntax: ALTER TABLE relation_name DROP COLUMN (field_name);

Example:SQL>ALTER TABLE student DROP column (sname);

d)ALTER TABLE..RENAME...: This is used to change the name of fields in

existing relations.

Syntax: ALTER TABLE relation_name RENAME COLUMN (OLD field_name) to

(NEW field_name);

Example: SQL>ALTER TABLE student RENAME COLUMN sname to stu_name;

3. DROP TABLE: This is used to delete the structure of a relation. It permanently deletes
the records in the table.
Syntax: DROP TABLE relation_name;
Example: SQL>DROP TABLE std;

4. RENAME: It is used to modify the name of the existing database object.

Syntax: RENAME TABLE old_relation_name TO new_relation_name;
Example: SQL>RENAME TABLE std TO std1;