Categories of SQL Commands,

<< Rules of SQL Format, Data Types in SQL Server

Alter Table Statement >>

Database Management System (CS403)

Lecture No. 26

Reading Material

"Database Management Systems", 2nd edition, Raghu Ramakrishnan, Johannes Gehrke,

McGraw-Hill

Overview of Lecture

o Different Commands of SQL

In the previous lecture we have seen the database of an examination system. We had

drawn the ER model and then the relational model, which was normalized. In this

lecture we will now start with different commands of SQL.

Categories of SQL Commands

We have already read in our previous lecture that there are three different types

of commands of SQL, which are DDL, DML and DCL. We will now study DDL.

DDL

It deals with the structure of database.The DDL (Data Definition Language) allows

specification of not only a set of relations, but also the following information for each

relation:

The schema for each relation.

The domain of values associated with each attribute.

Integrity constraints.

The set of indices for each relation.

Security and authorization information.

Physical storage structure on disk.

Following are the three different commands of DDL:-

Create

The first data management step in any database project is to create the database. This

task can range from the elementary to the complicated, depending on your needs and

the database management system you have chosen. Many modern systems (including

Personal Oracle7) include graphical tools that enable you to completely build the

database with the click of a mouse button. This timesaving feature is certainly helpful,

but you should understand the SQL statements that execute in response to the mouse

clicks. This command is used to create a new database table. The table is created in

201

Database Management System (CS403)

the current default database. The name of the table must be unique to the database.

The name must begin with a letter and can be followed by any combination of

alphanumeric characters. The name is allowed to contain the underscore character

( _ ). This command can be used to create permanent disk-based or temporary in-

memory database tables. Data stored in a temporary table is lost when the server is

shutdown. To create a temporary table the "AS TEMP" attribute must be specified.

Note that querying against a temporary in-memory table is generally faster than

querying against a disk-based table. This command is non-transactional. If no file size

is given for a disk-based table, the table will be pre-allocated to 1MB. If no filegrowth

is given, the default is 50%. It is used to create new tables, fields, views and indexes.

It is used to create database. The format of statement is as under:

CREATE DATABASE db_name

For Example CREATE DATABASE EXAM. So now in this example database

of exam has been created. Next step is to create tables. There are two

approaches for creating the tables, which are:

Through SQL Create command

Through Enterprise Manager

Create table command is used to:

Create a table

Define attributes of the table with data types

Define different constraints on attributes, like primary and foreign keys,

check constraint, not null, default value etc.

The format of create table command is as under:

CREATE

TABLE

[

database_name.[

owner

]

owner.

]

table_name

{

column_definition

(

column_name

omputed_column_expression

| < table_constraint >

}

| [ { PRIMARY KEY | UNIQUE } [ ,...n ] ]

Let us now consider the CREATE statement used to create the Airport table definition

for the Airline Database.

CREATE TABLE Airport

(airport char(4) not null,

name varchar(20),

202

Database Management System (CS403)

checkin varchar(50),

resvtns varchar(12),

flightinfo varchar(12) );

Table Name.(Airport)

The name chosen for a table must be a valid name for the DBMS.

Column Names. (Airport, Name, ..., FlightInfo)

The names chosen for the columns of a table must also be a valid name for the

DBMS.

Data Types

Each column must be allocated an appropriate data type. In addition, key columns, i.e.

columns used to uniquely identify individual rows of a given table, may be specified

to be NOT NULL. The DBMS will then ensure that columns specified as NOT NULL

always contain a value.

The column definition is explained as under:

< column_definition > ::= { column_name data_type }

[ DEFAULT constant_expression ]

[ < column_constraint > ] [ ...n ]

The column constraint is explained as under:

< column_constraint > ::= [ CONSTRAINT constraint_name ]

{ [ NULL | NOT NULL ]

| [ { PRIMARY KEY | UNIQUE }

]

| [ [ FOREIGN KEY ]

REFERENCES ref_table [ ( ref_column ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

]

203

Database Management System (CS403)

| CHECK( logical_expression )

}

)

We will now see some examples of CREATE command. This is a very simple

command for creating a table.

CREATE TABLE Program (

prName char(4),

totSem tinyint,

prCredits smallint)

If this command is to written in SQL Server, it will be written in Query Analyzer.

We will now see an example in which has more attributes comparatively along

with different data types:

CREATE TABLE Student

(stId char(5),

stName char(25),

stFName char(25),

stAdres text,

stPhone char(10),

prName char(4)

curSem smallint,

cgpa real)

In this example there are more attributes and different data types are also there. We

will now see an example of creating a table with few constraints:

CREATE TABLE Student (

stId char(5) constraint ST_PK primary key

constraint ST_CK check (stId

like`S[0-

9][0-9][0-9][0-9]'),

stName char(25) not null,

stFName char(25),

stAdres text,

stPhone char(10),

prName char(4),

curSem smallint default 1,

cgpa real)

204

Database Management System (CS403)

Every constraint should be given a meaningful name as it can be referred later by its

name. The check constraint checks the values for any particular attribute. In this way

different types of constraints can be enforced in any table by CREATE command.

Summary

Designing a database properly is extremely important for the success of any

application. In today's lecture we have seen the CREATE command of SQL. How

different constraints are applied on this command with the help of different examples.

This is an important command and must be practiced as it is used to create database

and different tables. So create command is part of DDL.

Exercise:

Create a database of Exam System and create table of student with different

constraints in SQL Server.

205

Table of Contents: