db2 trianing class 02

8/12/2019 Db2 Trianing Class 02

1/18

Structured QueryLanguage

DB2 Training Class 02


2/18

Introduction to SQL

Structured Query Language (SQL) is a standard language usedto work with database objects and the data they contain.

Using SQL, we can define, alter, and delete database objects,

as well as insert, update, delete, and retrieve data values storedin database tables.

SQL is not case sensitive but re uire a se!icolon at the endof each SQL state!ent.

SQL state!ents can be e"ecuted interactively using tools andthey can also be e!bedded in high#level progra!!inglanguage source code.


3/18

Database Tables

$ database !ost often contains one or !ore tables. %ach table isidentified by a na!e (e.g. &'usto!ers& or & rders&). ables containrecords (rows) with data.

*elow is an e"a!ple of a table called &+ersons -

Person_Id Last_Name First_Name City

A0001 ANIL P HYD

A0002 SRINU D PUNE

A0003 SUNIL B BANG


4/18

Categories o SQL Statements!

L

L is abbreviation of Data Definition Language . /t isused to create and !odify the structure of databaseobjects in database.

%"a!ples- '0%$%, $L%0, 0 + state!ents

1L

1L is abbreviation of Data Manipulation Language ./t is used to retrieve store data, !odify, delete, insert andupdate data in database.

%"a!ples- S%L%' , U+ $ %, /2S%0 , %L% %state!ents


5/18

Categories o SQL Statements!

'L

'L is abbreviation of Transactional ControlLanguage . /t is used to !anage different transactions

occurring within a database.%"a!ples- ' 11/ , 0 LL*$'3 state!ents

'L

'L is abbreviation of Data Control Language . /t isused to create roles, per!issions, and referentialintegrity as well it is used to control access to database

by securing it.

%"a!ples- 40$2 , 0%5 3% state!ents


6/18

DDL Statements

he !ost i!portant L state!ents in SQL are-

CREATE DATABASE # creates a new database

ALTER DATABASE # !odifies a database

CREATE TABLE # creates a new table

ALTER TABLE # !odifies a table

DRO TABLE # deletes a table

CREATE !"DE# # creates an inde" (search key)

DRO !"DE# # deletes an inde"


7/18

C"#$T# Statement

'0%$ % $*L% +0 6%'(+0 62 '7$0(8) 2 2ULL,

+0 62$1% 5$0'7$0(9:) 2 2ULL, %+ 2 S1$LL/2 ,

*U 4% %'/1$L(8,;),S $0 $ % $ %,%2 $ % $%) /2 *. S?

'0%$ % $*L% %1+1$S(%1+/ S$1LL/2 2 2ULL,%


8/18

DB2 % C&B&L D$T$T'P#S

DB$ COBOL'7$0(n) 9:


9/18

(nderstanding Constraints

2 2ULL constraints

efault constraints

'7%'3 constraints

U2/QU% constraints

0eferential integrity constraints

/nfor!ational constraints


10/18

C&NST"$INTs

'onstraint is a !echanis! to control data.

"%LL& 2ull !eans unknown, /f the value is not suppliedduring an insertion of row then null will be inserted into this

colu!n (2ull is identified as ########)."OT "%LL& Fe need to !andatorily pass values for the

colu!ns defined with not null constraint. Fe specify all the pri!ary keys with 2ot 2ull.

"OT "%LL '!T( DE)A%LT& /f the value is not suppliedduring an insertion of row, then based on the colu!n defaultvalues will be !oved into the table.


11/18

$LT#" and D"&P

$L %0 is used to !odify the able.

ALTER S*"TA#&

$L %0 $*L% $*L%2$1% $ ' LU12 2$1%$$ A+% ' 2S 0$/2 ?

0 + is used to drop entire able.

DRO S*"TA#&

0 + $*L% $*L%2$1%?


12/18

D)L

/t is used to retrieve, store, !odify, delete, insert and updatedata in database.

!"SERT !"TO # /nserts new data into a able

SELECT # %"tracts data fro! a able

% DATE # Updates data in a able

DELETE # eletes data fro! a able


13/18

INS#"T

/t is used to insert rows in the table.

!"SERT SA2 $B-

/2S%0 /2 $*L% 2$1%

(' LU129, ' LU12;GGGGGGGGGGGG' LU12n)

5$LU%S

(' LU129 5$LU%, ' LU12; 5$LU%G.' LU12n 5$LU%)?


14/18

(PD$T# and D#L#T#

Update - /t is used to update all rows in the table or selective rows.

% DATE SA2 $B-

U+ $ % $*L% 2$1% S% ' LU12 2$1% H 2%F 5$LU%

IF7%0% ' 2 / / 2J?

elete - /t is used to delete selective rows fro! table.

DELETE SA2 $B-

%L% %


15/18

T*e S#L#CT Statement


16/18

C&))IT and "&LLB$C+

he ' 11/ state!ent co!!its the database changes that were!ade during the current transaction, !aking the changes

per!anent.

SA2 $B- ' 11/ ?

he 0 LL*$'3 state!ent backs out, or cancels, the database

changes that are !ade by the current transaction and restoreschanged data to the state before the transaction began.

SA2 $B - 0 LL*$'3?


17/18

,"$NT and "#-&+#

/t is used to give privileges on table.

+RA"T SA2 $B-

40$2 S%L%' , /2S%0 , %L% % 2 $*L%2$1% < 00$'


18/18

T*an. you

db2 trianing class 02

Documents