Rules of SQL Format, Data Types in SQL Server

<< Vertical Partitioning, Replication, MS SQL Server

Categories of SQL Commands, >>

Database Management System (CS403)

Lecture No. 25

Reading Material

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

McGraw-Hill

Overview of Lecture

o Structured Query Language (SQL)

In the previous lecture we have studied the partitioning and replication of data. From

this lecture onwards we will study different rules of SQL for writing different

commands.

Rules of SQL Format

SQL, at its simplest, is a basic language that allows you to "talk" to a database and

extract useful information. With SQL, you may read, write, and remove information

from a database. SQL commands can be divided into two main sub languages. The

Data Definition Language (DDL) contains the commands used to create and destroy

databases and database objects. After the database structure is defined with DDL,

database administrators and users can utilize the Data Manipulation Language to

insert, retrieve and modify the data contained within it. Following are the rules for

writing the commands in SQL:-

· Reserved words are written in capital like SELECT or INSERT.

User-defined identifiers are written in lowercase

Identifiers should be valid, which means that they can start with @,_

alphabets ,or with numbers. The maximum length can be of 256. The reserved

words should not be used as identifiers.

Those things in the command which are optional are kept in [ ]

Curly braces means required items

| means choices

[,.....n] means n items separated by comma

Consider the following example:-

SELECT [ALL|DISTINCT]

{*|select_list}

FROM {table|view[,...n]}

196

Database Management System (CS403)

Select * from std

Data Types in SQL Server

In Microsoft SQL ServerTM, each column, local variable, expression, and parameter

has a related data type, which is an attribute that specifies the type of data (integer,

character, money, and so on) that the object can hold. SQL Server supplies a set of

system data types that define all of the types of data that can be used with SQL

Server. The set of system-supplied data types is shown below:-

Integers:

Biggint

Integer (whole number) data from 2

(-9,223,372,036,854,775,808)

through 263-1 (9,223,372,036,854,775,807).

Int

Integer (whole number) data from -231 (-2,147,483,648) through 231 - 1

(2,147,483,647).

Smallint

Integer data from -215 (-32,768) through 215 - 1 (32,767).

Tinyint

Integer data from 0 through 255.

bit

Integer data with either a 1 or 0 value.

Decimal and Numeric

Decimal

Fixed precision and scale numeric data from -1038 +1 through 1038 1.

Numeric

Functionally equivalent to decimal.

Text:

It handles the textual data. Following are the different data types.

Char:

By default 30 characters, max 8000

197

Database Management System (CS403)

Varchar:

Variable length text, max 8000

Text:

Variable length automatically

nchar,

nvarchar, ntext

Money:

It is used to handle the monetary data

Small money: 6 digits, 4 decimal

Money:

15 digits, 4 decimal

Floating point:

Float

Real

Date:

Smalldatetime

datetime

198

Database Management System (CS403)

Examination System Database

Examination

We will now transfer this conceptual database design into relational database design

as under:-

PROGRAM (prName, totSem, prCredits)

(crCode, crName, crCredits, prName)

COURSE

SEMESTER (semName, stDate, endDate)

(crCode, semName, facId)

CROFRD

(facId, fName, fQual, fSal, rank)

FACULTY

STUDENT (stId, stName, stFName, stAdres,stPhone, prName, curSem, cgpa)

(stId, crCode, semName, mTerm,sMrks, fMrks, totMrks, grade, gp)

ENROLL

SEM_RES (stId, semName, totCrs, totCrdts, totGP, gpa)

It is used to specify a database scheme as a set of definitions expressed in a DDL.

DDL statements are compiled, resulting in a set of tables stored in a special file called

199

Database Management System (CS403)

a data dictionary or data directory. The data directory contains metadata (data about

data) the storage structure and access methods used by the database system are

specified by a set of definitions in a special type of DDL called a data storage and

definition language

Data Manipulation is retrieval, insertion, deletion and modification of information

from the database. A DML is a language, which enables users to access and

manipulate data. The goal is to provide efficient human interaction with the system.

There are two types of DML.First is Procedural: in which the user specifies what data

is needed and how to get it Second is Nonprocedural: in which the user only specifies

what data is needed

The category of SQL statements that control access to the data and to the database.

Examples are the GRANT and REVOKE statements.

Summary:

In today's lecture we have read about the basics of SQL. It is used to communicate

with a database. According to ANSI (American National Standards Institute), it is the

standard language for relational database management systems. SQL statements are

used to perform tasks such as update data on a database, or retrieve data from a

database. Some common relational database management systems that use SQL are:

Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database

systems use SQL, most of them also have their own additional proprietary extensions

that are usually only used on their system. However, the standard SQL commands

such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to

accomplish almost everything that one needs to do with a database. In the end we

have also seen the different types of SQL commands and their functions.

Exercise:

Practice the basic commands of SQL like SELECT, INSERT and CREATE.

200

Table of Contents: