Structured Query Language (SQL).pptx

Introduction

• _{Also pronounced as “Sequel”}

• _{A de-facto standard for relational DBMS}

• _{Standard accepted by bodies like ANSI and}

Introduction

• _{First commercial DBMS that support SQL in}

1979 was Oracle

• _{Another form of query language is}

History

•

_{Relational data model introduced by}

Codd in 1970

•

_{A relational DBMS project started by}

History

•

_{First commercial DBMS launched was}

Oracle in 1979

•

_{Other early DBMSs DB2, INGRES}

•

_{ANSI and ISO defined first standard of}

History

• _{Later two more standards known as}

SQL-92 and SQL-99 were defined

• _{Almost all of the current DBMSs support}

SQL-92 and many support SQL-99 partially or completely

• _{SQL today is supported in all sort of}

Benefits of Standard SQL

•

_{Reduced training cost}

•

_{Application portability}

•

_{Application longevity}

•

_{Reduced dependence on a single}

vendor

SQL

• _{SQL is used for any type of interaction}

with the database through DBMS

• _{Right from creating database, tables,}

views, users

• _{Insertion, updation, deletion of data in the}

SQL and DBMSs

• _{Implementation of SQL always includes the}

flavor of the particular DBMS, like in names of data types

• _{Proprietary SQLs exist, like T-SQL and}

Rules of the Format

• _{Writing general format}

– _{Reserved words in capital}

– _{User-defined identifiers in lowercase} – _{Valid identifiers}

– _{Optionals in [ ]}

– _{Curly braces means required items} – _{| means chioces}

Example

• _{SELECT [ALL|DISTINCT] {*|select_list}}

FROM {table|view[,…n]}

Data Types in SS

• _Integer

– _{Bit ( 0 or 1)}

– _{Tinyint (0 through 255)} – _{Smallint -32768 to 32767} – _{Int -2}31 _{to 2}31_{-1 (10 digits)}

– _{Bigint -2}63 _{to 2}63_{-1 (19 digits)}

Data Types in SS



_{Text: handles textual data}

– _{char: by default 30 characters, max 8000} – _{varchar: variable length text, max 8000} – _{Text: variable length automatically} – _{nchar, nvarchar, ntext}



_{Money: handles monetary data}

Data Type in SS

• _{Floating point}

– _Float – _Real

• _Date

PROGRAM COURSES

CRS_OFFERED

TEACHER SEMESTER

PROGRAM (prName, totSem, prCredits)

COURSE (crCode, crName, crCredits, prName) SEMESTER (semName, stDate, endDate)

CROFRD (crCode, semName, facId)

SQL Commands Types

SQL commands are categorized in

three broad classes

•

_{Data definition language (DDL)}

•

_{Data manipulation language (DML)}

DDL

•

_Create

•

_Alter

Create Command

• _{Creating database}

CREATE DATABASE db_name Create database exam

• _{Next step is to create tables}

• _{Two approaches (In Microsoft SQL Server):}

Create Table Command

• _{Create table command is used to:}

– _{Create a table}

– _{Define attributes of the table with data types} – _{Define different constraints on attributes, like}

Format of Create Table

• _{CREATE TABLE}

[ database_name.[ owner ] . | owner. ] table_name

( { < column_definition >

| column_name AS computed_column_expression

| < table_constraint > }

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

Format of Create Table

< column_definition > ::= { column_name data_type } [ DEFAULT constant_expression ]

Format of Create Table

< 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 } ] ] |

CREATE TABLE Program ( prName char(4), totSem tinyint,

prCredits smallint)

CREATE TABLE Student (stId char(5),

stName varchar(25), stFName varchar(25), stAdres text,

stPhone char(10), prName char(4)

curSem smallint, cgpa real)

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)

stId char(5) constraint ST_PK primary key

CREATE TABLE Student

(stId char(5) primary key

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 tinyint default 1, cgpa real)

• _{CREATE TABLE semester (}

semName char(5) primary key, stDate smalldatetime,

endDate smalldatetime,

constraint ck_date_val check (datediff(week, stDate, endDate) between 14 and 19))

Alter Table Statement

•

_{Purpose is to make changes in the}

definition of a table already created

through Create statement

•

_{Can add, drop attributes or}

• _{ALTER TABLE table}

{ [ ALTER COLUMN column_name

{ new_data_type [ ( precision [ , scale ] ) ]

[ NULL | NOT NULL ] ]

| ADD

{ [ < column_definition > ]

| column_name AS computed_column_expression

} [ ,...n ] | DROP

{ [ CONSTRAINT ] constraint_name

| COLUMN column } [ ,...n ]

| { CHECK | NOCHECK } CONSTRAINT

{ ALL | constraint_name [ ,...n ] }

_{ALTER TABLE Student}

add constraint fk_st_pr

foreign key (prName) references Program (prName)

_{ALTER TABLE program}

add constraint ck_totSem check (totSem < 9)

Removing or Changing Attribute

• _{ALTER TABLE student}

ALTER COLUMN stFName char(20)

• _{Alter table student}

drop column curSem

• _{Alter table student}

Removing Rows and Tables

• _{TRUNCATE TABLE table_name} • _{Truncate table class}

Data Manipulation Language