Category: Tutorials

SELF JOIN  :-

• A self join is a joining table to itself.
• To perform self join the same table must be listed twice in different Alias.
• Self join is performed in table having self referential integrity.

Display employee names and manager names:

Select x.ename, y.ename manager From  emp x,emp y Where x.mgr= y.empno

Display names of the employee working under manager  blake

Select x.ename employee From emp x, emp y  Where x.mgr=y.empno and y.ename=’BLAKE’

Display names of the employees whos earning morethan his manager.

Select x.ename employee  From  emp x ,emp y x.sal > y.sal

List of employee whose are joined before the manager.

Select x.ename    employee From   emp x,emp y Where   x.mgr=y.empno And x.hiredate<y.hiredate

Write a query to produce the following output

             Ename         Dname        Grade           Mgrname

                 Select  x.ename,d.dname,g.grade,y.ename  mname

                      From emp  x  ,emp y ,dept d,salgrade g

                         Where   x.deptno= d.deptno

                              And

                             x.sal between    g.lowsal and  g.highsal

                           and

                             x.mgr=y.empno

{Note : here,we are used to equi, non equi & self}

ANSI STYLE OF SELF JOIN   :-

Select x.ename,y.ename  mname From emp x inner join emp y Or(x.mgr=y.empno)

NON- EQUI  JOIN  :-

• Joining two tables not based on common column it’s called Non-equi join.

Syntax :-  Select <collist> from <tablelist> Where <join cond> [and<join cond> and<cond>]

Here join condition is based on other than equal operator,so it is Called non-equi join

•                 Emp                                                                           salary grade
• Empno          ename             sal                    Grade      lowsal       highsal
• 1                      A                       300                      1             100           200
• 2                       B                       200                     2               201          300
• 3                       C                        100                     3               301          400
• 4                        D                        400                    4               401           501

Display empnames and their grades:

Select e.ename,g.grade From    emp   e,  salgrade g Where e.sal between g.lowsal and g.highsal

Display the names of employee whos grade=3

Select e.ename From emp   e,salgrade  g Where    e.sal Between    g.lowsal    and   g.highsal And g.grade=3 .

Display ename,dname,grade

Select   e.ename,.dname,g.grade From  emp  e,dept  d,salgrade  g Where e.deptno=d.dno And   e.sal between   g.lowsal and g.highsal

Note: {here we are used both equi & non-equi joins}

Student 

Sno                  sname             s1        s2           s3

result

Result                   Lavg                   Havg

 Dist                        70                        100

 First                        60                        69.99

Second                     50                        59.99

Third                        35                        49.99

Display  sname  & result :

Select s.sname ,r.result From    student    s   ,result r Where   (s.s1+s.s2+s.s3)/3 between r.Lavg  and r.Havg.

ANSI  :-  

                    Select e.ename, g.grade From emp e  inner join g.lowsal and g.highsal.

ANSI JOINS OR SQL/92 JOINS   :-       

Example :- Select e.ename, d.dname from emp e inner join dept d on e.deptno=d.deptno

Joining 3 tables :-

Example  :-   Select e.ename , d.dname, x.expr from emp e  inner join dept d on e.deptno=d.deptno Inner join Emp_expr  x On x.empno=e.empno.

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  :-

• 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  :-

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    :–

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 :-

• 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 :–

• 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 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