database sql training day 2

8/12/2019 Database SQL Training Day 2

1/11

Training Agenda :

Day 2

Constraints

DDL/ DML/ DCL statements Select Statement Clauses


2/11

Constraints:

A constraint is a property that is assigned to a column or a group of columns to

prevent incorrect or corrupt data from entering into the tables. These constraints

ensure the accuracy and reliability of the data into the tables.

There are five type of constraints

1 Primary Key

2 Foreign Key

3 Check

4 Unique

5 Not null


3/11

Primary Key:

Primary key is used to uniquely identify a row in a

table. A table can have only one primary key. Primary

keys dont allow null values. The data help across theprimary key column must be unique. It can be definedat the column level or at the table level.Primary Key can be defined while creating a table withCreate Table command or it can be added with the

Alter table command.Table Level:->

CREATE TABLE #Employee(

[Empid] [int] NOT NULL,

CONSTRAINT pk_empID PRIMARY KEY (Empid)

)Row Level:->

CREATE TABLE #Employee(

[Empid] [int] NOT NULL CONSTRAINT pk_empID PRIMARY KEY

Alter Table #Employee Add constraint Pk_rase Primary Key (Empid)

Alter table #Employee drop constraint Pk_rase


4/11

Foreign Key:

This is use to refer the primary key constraintof master table.

Foreign key allows duplicate and null values.

We can use on delete cascade during thecreation of foreign key so that on the deletion of

primary key records the corresponding foreignkey records get deleted automatically from allthe foreign key tables wherever the primarykey is being followed.


5/11

Unique key:

This constraint does not allowduplicate value but it allows one null value

unlike primary Key which does not allow anynulls.

Check:This constraint allows to write any

check condition on the column.

Not null:

This constraint does not allow null

values but allow duplicate values.


6/11

T-SQL Languages

DDL (Data Definition Language) DML (Data Manipulation Language)

DCL (Data Control Language)

DDL:Using this we can create,alterand dropthe

database object

Eg. Create table emp_new(emp_code smallint, ename nvarchar(20)

, job nvarchar(20), mgr smallint, hiredate datetime, sal bigint

, comm smallint, deptno int)


7/11

Applying Constraints SQL Syntax

Eg.

create table account_master

(acc_no int constraint cons_accno_pr_key primary key,

name varchar(30) not null,

acc_type char(12) check (acc_type in (saving, current)),

open_bal numeric check (open_bal >=1000),

email_id varchar (30) constraint email_uniqu unique)

Eg.

create table account_trans

(trans_date datetime,

acc_no int references account_master (acc_no) ondelete cascade,

trans_type char(10) check (trans_type in (Dr, Cr)),

trans_amt int check (trans_amt >0),

curr_bal int check (curr_bal>=1000))


8/11


9/11

DML :

Using this we can do any kind ofmanipulation of record.

InsertUpdate

Delete

Select

Insert:

insert into emp_new

(emp_code,ename,job, mgr

, hiredate ,sal,deptno)

Values(7369, SMITH,CLERK

,7902, 12/17/1980,800,20)


10/11

Update:

Update emp_new

[email protected]

Delete:

delete from emp_new

where emp_code=7369

Select:select emp_code, ename, job, sal

from emp


11/11

Select Statement

The SELECT statement is used to select data from aDatabase

Eg.Select Column1, Column2, Column3

From Table_Name

database sql training day 2

Documents