Trigger

CREATE TABLE StuDetails
(
    Rollno        int,
    StuName        varchar(100),
    Address        varchar(300),
    City        varchar(30),
    Country        varchar(30),
   
)
INSERT INTO StuDetails
VALUES (106,'deva','gingee','chennai','india')

CREATE TABLE Attendance
(
    Rollno        int,
    Attendance    int,
    Mon            int,
    Years        int
)

INSERT INTO Attendance
VALUES(106,02,09,2008)

CREATE TABLE Mark
(
    Rollno        int,
    TotalMark    int,
    Mon            int,
    Years        int
)

INSERT INTO Mark
VALUES(106,560,09,2008)

--------------------------------------------------------------------------------------------------------------------

Joins :-

1 .Inner Join
2. Outer join
    i.Left outer join or left join
    ii.Right outer join or right join
3.Cross join
4.self  join


Inner join:-
------------

SELECT    stu.Rollno,stu.StuName,stu.Address,stu.City,stu.Country,Att.Attendance,Att.Mon,Att.Years
FROM    StuDetails stu
INNER JOIN
        Attendance Att
        ON(stu.Rollno=Att.Rollno and Att.Attendance>10)
WHERE     stu.Country='India'


SELECT    stu.Rollno,stu.StuName,stu.Address,stu.City,stu.Country,Att.Attendance,Att.Mon,Att.Years,Mrk.TotalMark
FROM    StuDetails stu
INNER JOIN
        Attendance Att
        ON(stu.Rollno=Att.Rollno and Att.Attendance>10)
INNER JOIN
        Mark    Mrk
        ON(stu.Rollno=Mrk.Rollno and Mrk.Mon<9)
WHERE     stu.Country='India'

Outer join:-
------------
i.left outer join

SELECT  stu.Rollno,stu.StuName,stu.Address,stu.City,stu.Country,Att.Attendance,Att.Mon,Att.Years
FROM    StuDetails stu 
LEFT OUTER JOIN
        Attendance Att
        ON(stu.Rollno=Att.Rollno)
WHERE     stu.Country='India'


ii.Right outer join

SELECT  stu.Rollno,stu.StuName,stu.Address,stu.City,stu.Country,Att.Attendance,Att.Mon,Att.Years
FROM    StuDetails stu 
RIGHT OUTER JOIN 
        Attendance Att
        ON(stu.Rollno=Att.Rollno)
WHERE    stu.Country='India'

------------------------------------------------------------------------------------

select    *
from sysobjects
where xtype='U'

select    *
from    TblEmpService(nolock)

select    *
from    TblEmpMaster

select    *
from    TblEmpTax

INNER JOIN :-

CREATE View V_JOIN AS
SELECT    MAS.EmpId,MAS.EmpName,MAS.Address,MAS.DOB,
        TAX,TA,TAX.HRA,TAX.PF,TAX.ESI,
        SER.Technologies,SER.ExperInYears,SER.PreCoJD
FROM    TblEmpMaster MAS(nolock)
INNER JOIN
        TblEmpTax TAX (nolock)
        ON(MAS.EmpId=TAX.EmpId)
INNER JOIN
        TblEmpService SER (nolock)
        ON(MAS.EmpId=SER.EmpId)
WHERE    MAS.Address='Namakkal'

OUTER JOIN:-
i.LEFT OUTER JOIN

SELECT    MAS.EmpId,MAS.EmpName,MAS.Address,MAS.DOB,
        TAX,TA,TAX.HRA,TAX.PF,TAX.ESI,
        SER.Technologies,SER.ExperInYears,SER.PreCoJD
FROM    TblEmpMaster MAS(nolock)
LEFT OUTER JOIN
        TblEmpTax TAX (nolock)
        ON(MAS.EmpId=TAX.EmpId)
LEFT OUTER JOIN
        TblEmpService SER (nolock)
        ON(MAS.EmpId=SER.EmpId)
WHERE    MAS.Address='Namakkal'


ii.RIGHT OUTER JOIN :-

SELECT    MAS.EmpId,MAS.EmpName,MAS.Address,MAS.DOB,
        TAX,TA,TAX.HRA,TAX.PF,TAX.ESI,
        SER.Technologies,SER.ExperInYears,SER.PreCoJD
FROM    TblEmpMaster MAS(nolock)
RIGHT OUTER JOIN
        TblEmpTax TAX (nolock)
        ON(MAS.EmpId=TAX.EmpId)
RIGHT OUTER JOIN
        TblEmpService SER (nolock)
        ON(MAS.EmpId=SER.EmpId)
WHERE    MAS.Address='Namakkal'


SELF JOIN:-

SELECT    M1.EmpId,M1.EmpName,M1.Address,M1.DOB,
        M1.City,M2.City
FROM    TblEmpMaster M1(nolock)
LEFT OUTER JOIN
        TblEmpMaster M2(nolock)
        ON(M1.EmpId=M2.EmpId and M2.Address='Karur')
WHERE    M1.Address='Namakkal'


CROSS JOIN:-

SELECT    *
FROM    TblEmpMaster (nolock)
CROSS JOIN
        TblEmpTax (nolock)