Joins (inner join, outer join, cross join) in SQL – SQL Server Tutorial

JOINS  :–

  • The join is a data retrieval operation performed to get data multiple tables.

              DB                                                                      DWH

tables are normalized                                      tables are de-normalized.

more joins are required                                   less joins are required.

TYPES OF JOINS :-

(i) inner join

  •                  -equi join
  •                  -non – equi join
  •                  -self join

(II) outer join

  •                   -left
  •                   -right
  •                   -full

(III) cross join

(I) inner join in sql :-

            (a) Equi join  :-

  • Joining two tables  based on common column.
  • To perform equi there should be a common column,but the name of the common column need not be same.
  • SQL server performs join operators,when more than one lable is listed in from class.

Example  :- Syntax  : from emp, dno. 

Syntax  :select<collist>from<tablist>         Where<join cond>

            [and <join cond>and<cond>—–] 

Join condition :- The two tables are joined based on this condition.

Example  :- child.fk=parent.pk(if relationship exists)

                                                          (or)

            Table1.commncol=table2.commcol(if there is no relationship)

  • Join condition is based on equal to operation
  • To join ‘n’ tables (n-1) join condition are required. 

                    Emp                                                                                dept          .

Empno        ename        sal           d.no                           d.no             dname              loc.

1                       A              5             10                                 10                A/c                –

2                       B              6              20                               20                sales              –

3                       C              4               30                              30               purchase       –

4                       D             3                10                             40               market          –

5                        E             7                  –

Display enames and dept names  :-

Select  ename,dname,     From emp, dept, Where emp.dno=dept.no 

ename            dname

A                   alcs

B                 sales

C                 purchase

D                 A/C

Note: “In case of join queries better to use table Alias”

 Select e.ename, d.dname  From emp e,dept d where e.deptno=d.dno.

Display name of the employees working for sales department.

   Select e.ename

    From emp e,dept d

    Where e.deptno=d.dno

    And  d.dname =’sales’

Display names of the employees working for sales dept& more than earn 3000.

Select e.ename

From emp e,dept d

Where e.deptno=d.dno

And d.dname=’sales’

And sal>3000

Display ename , dname, experience, netsal

Emp                 Dept             emp-expr                 emp-netsal

Empno             dno         D-no         empno          expr       empno       netsal

1                         10               10                 1                    10              1             10,000    

2                         20              20                2                    12              2             20,000 

3                         30              30                3                    15              3             30,000     

4                        10                40               4                     8                4            40,000 

Select E.ename,d.dname,x.expr

From emp e,dept d,emp_expr x

Where e.deptno=d.deptno

And x.empno=e.empno

Disable and Enabling the Constraints in SQL – SQL Server Tutorial

DISABLE AND ENABLING THE CONSTRAINTS  :-

  • If the constraint is disabled, the constraint is exists in the table, but it will not work.

Syntax  :-        no check

Example  :-   alter table emp22

                              No check

                             Constraint ck_sal_emp22

Example :-    alter emp22

                             Check

                             Constraint ck_sal_emp22 ( Enable: Syntax:Check)

Update Rules in SQL – SQL Server Tutorial

UPDATE RULES  :-

The different update rules are

  • On  update no action
  • On update cascade
  • On update set null
  • On update set default

The update rules specifies how child record is effected if primary key of the parent record is updated.

(i) on update no action :-

  • Parent can’t be updated it matching child record exists.

(ii) on update cascade :-

  • If the foreign key is declared with or update cascade then if primary key is updated matching foreign key also is updated automatically.

(iii) on update set null :-

  • The foreign key declared with on update set null then if primary key is updated the matching foreign key value set to null.

(iv) on update set default :-

  • If primery key is updated do matching foreign key value set to default value

Dropping Constraints in SQL – SQL Server Tutorial

DROPPING CONSTRAINTS    :–

Syntax :–  

Alter table<tabname>

Drop constraint<name> 

  • Alter table emp88 drop constraint ck_sal_emp88 -> Check constraint will be dropped.
  • The primary key cannot be dropped,if it is referenced by any foreign key.

DELETE RULES  :– “if specifies how child record is effected if the parent record is deleted”.

  • on delete no action
  • on delete cascade
  • on delete set null
  • on delete set default.

(i) on delete no action  :-

  • If foreign key is declared with out any delete rule then parent record cannot be deleted,If  matching child records exist.

(ii) on delete cascade   :-

  • If foreign key is declared with on delete cascade, if we delete the parent record, The parent is deleted along with child records. 

Create table dept33 (dno into primery key, Dnam varchar(20)).

Create table emp33 (eno int primery key, Ename varchar(20), Dno int references  dept33(dno) On delete cascade)

(iii) on delete set null  :-

  • If foreign key is declared with  or delete set null then if you delete the parent record, parent record is deleted with out deleting the child record but the child record foreign key values is set to null.

Example  :-

create table dept33 (dno into primery key, Dname varchar (20)) (Parent)

Create table emp33 (eno int primery key, Ename varchar (20), Dno int references dept33 (dno) On delete set null) (Child)

(iv) on delete set default  :-

  • If foreign key is declared with on delete set default, if parent record is deleted then child record foreign key value set to default value.

Example :-

Create table dept33 (dno int primery key, Dname varchar(20))

Create table emp33 (eno int primery key, Ename varchar (20), Dno int references dept33(dno)

Adding Constraint in a Existing Table – SQL Server Tutorial

Adding constraint in a existing table :-

  • Alter is used to add constraint in an existing table.

Example :- Create table emp88 (eno int, Ename varchar(20), Sal smallmoney, Dno int)

Adding primary key   :-

Syntax  : {for any constraint}

Alter table <tabname>

Add{constraint<name>}type(collist)

Note:- Before adding primary key constraint make

The column is not null column then add primary key.

                 Sp_help emp88

Example : alter table emp88, Alter column eno int not null -> make the column is not null

Example  :

Alter table emp88

Add primary key (eno)

Adding check constraint :-

Alter table emp88

Add constraint ck_sal_emp88

                      optional

Check(sal>3000) 

Adding foreign key   :-

Alter table emp88

         Add constraint fk_dno_emp88

Foreign key(dno)references dept22(dno) 

Default Constraint in SQL – SQL Server Tutorial

Default Constraint :–

  • This is to provide default value for a column.
  • When user doesn’t provide any value then sql server inserts default value.

Declaration :

  • Create table emp88 (eno int, Ename varchar(20), Doj  date time default getdate())
  • Insert into emp88 values (1,’a’,16-oct-09)
  • Insert into emp88 values (2,’B’,17-oct-09)

Default <– Insert into emp88 (eno,ename) values (3,’B’)

Self Referential Integrity Constraint in SQL – SQL Server Tutorial

SELF REFERNTIAL INTEGRITY  :-

A foreign key in one table refers primery key of same table then it is called “self referential integrity” and also called “Unary relationship”

  • Empno                     ename                    manager
  • 1                                 a                           —
  • 2                                 b                           1
  • 3                                 c                            1                          
  • 4                                 d                            2                                    
  • 5                                 e                             10

The above table consists primary key -> Empno and Foreign Key -> Manager

 {here,Foreign Key refers to primary key in the same table}

Example   :-  Create table emp33

(eno int primery key, Ename varchar(20), Manager int referencies emp33(eno))

(ii) TABLE LEVEL   :

  • The table level constraint are declared after declaring all the columns.
  • Use table level when multiple column participates in constraints declaration.

Syntax    :-     Create table <tabname>

 (colname data type (size), Colname data  type (size), [constraint<name>] type (collist),–).

  • Not-null can not be declared in table level.

Example   :–

Create table stududent   (sid int primery key, sname varchar (20) Not null)

Create table course (cid int primery key, Cname varchar(20) not null).

Create table student_course

(sid int references stud(sid), cid int references course(cid), doc date time, primery key (sid,cid))

create table certificates

(cent no int primery key, Doi date time, Sid int, cid int, constraint fk_sid_cid foreign key (sid,cid) references stud_course(sid,cid))

Cust                                                                Items
cid      cname                                                icode           dept       rate

Order                                                            Order-details
Ordno  ord date   custid                        ordno     icode        qty

Different Integrity Constraints in SQL Server

Different Integrity Constraints in SQL Server

  1. UNIQUE
  2. NOT NULL
  3. PRIMERY KEY
  4. CHECK
  5. FOREIGN KEY

Integrity Constraints:

  1. Entity Integrity (Unique, Primary Key)
  2. Domain Integrity (Not Null, Check)
  3. Referential Integrity (Foreign Key)

(i) unique key integrity constraints:-

  • Unique does not allow duplicate values.

Example  :    empno                    int                   unique

                           100

                           101

                           100

Note: it allows null values

(ii) not null key integrity constraints     :-

  • A column declared with not null into that column null values are not allowed.

Example  : Ename varchar (20) not null.

Note : it allows duplicate values

(iii) primary key integrity constraints   :

  • A column declared with primery key doesn’t allow duplicate and null values.
  • A column declared with primery key,uniquely identifies each record in a table.
  • A table allows only one primery key.
  • Numeric columns are preferred as primery keys then character columns.

(iv) check integrity constraints sql:-

  • Check constraints validate data based on a condition.

Example  :- sal amallmoney check (sal>3000)

Sal smallmoney check(sal between 1000 and 5000)

(v) Forign Key integrity constraints sql  :

  • A foreign key refers primery key(or)unique columns of another/same table.
  • It is used to establish relationship between two tables,.

Rule :– The values of foreign key should be match with primery key value. (or) foreign can be null.

How to declare the foreign key :-

Dro int references dept(dno)
  • After declaring foreign key a relationship is established between two tables and that relationship is called parent/child relationship(or)master/detail relationship.
  • Table holding primary key is “parent” and table holding foreign key is “child” -> (referring).

Constraints can be declared in two ways-

  1. Column level
  2. Table level

(i) COLUMN LEVEL  :-

  • Column level constraints are declared immediately declaring column.
  • Use column level if only column participating constraints declaration.

Syntax   :-

Create table <tabname>

(colname datatype(size)

[constraint<name>]type,

Colname data type(size)

[constraint <name>]type,

——————————-

)

Example :- dept22 table

  • Create table dept22 (dno int primery key, dname varchar(20) unique not null,loc varchar (15))
  • Insert into dept22 values (10,’sales’,’hys’)

Example  :- emp22 table:-> parent

  • Create table emp22 (eno int constraint pk_emp22 primery key, Ename varchar(20) not null, Sal smallmoney constraintck_sal_emp22 check(sal>3000), Dno int references dept22(dno))
  • Insert into emp22 values (1,’a’,5000,10)

1) By default sql server creates one to many relationship.
2) To create one to one relationship declare forign key with unique constraint.

Integrity Constraints in SQL Server – SQL Server Tutorial

INTEGRITY CONSTRAINTS    :-     

  • Business rules can be implemented in two ways  :
  1. Declarative Approach
  2. Procedural Approach
  • Declarative using integrity constraints
  • Procedural using database triggers.
  • If business rules are simple,then they com be implemented by using Integrity constraints.
  • If business rules are complex, then the database-triggers.
  • Integrity constraints used to : implement the business rule and Used to validate data.
  • An integrity constraint ensures data integrity.

Integrity constraints are 3 types  :-

  1. Entity integrity
  2. Domain integrity
  3. Referential integrity

Identity in SQL Server – SQL Server Tutorial

IDENTITY:–    

  • It is used to generate serial numbers for a column in a table mostly for a primary keys.

Syntax :- identity (seed,incr)

                       Seed ->Starting value.

                        Incr -> increment by value

  • Both seed,incr are optional.
  • If you don’t mention the seed ,incr then the default value is (1,1).

Example :- Create table cust

(cid int identity(100,1),

Cname varchar (20))

Insert into cust (cname)

Values (‘a’)

Select * from cust

     cid              cname

    100                  a

    101                  b

    102                  c