JOIN

JOIN : Sql Join are used to combine rows from two or more tables.

Different SQL JOINs

1. Inner Join : Return rows when there is at least one match in both tables.
2. Left Outer Join : Return all rows from the left table, even if there are no matches in the right table.
3. Right Outer Join : Return all rows from the right table, even if there are on matches in the left table.
4. Full Outer Join : Return rows when there is a match in the one of the table.

1^st Table –
SELECT * FROM Student

2^nd Table –
SELECT * FROM Course

 
1) Inner Join : Return rows when there is at least one match in both tables.
INPUT—

SELECT  Student.Id, Student.Name, Student.City, Student.Mobile,
                Student.Email, Course.Code, Course.Name
FROM Student
INNER JOIN Course ON Student.CourseId=Course.Id;

OUTPUT –


2) Left Outer Join : Return all rows from the left table, even if there are no matches in the right table.
INPUT—

SELECT Student.Id, Student.Name, Student.City, Student.Mobile,
               Student.Email, Course.Code, Course.Name
FROM Student
LEFT OUTER JOIN Course ON Student.CourseId=Course.Id;

OUTPUT—


3) Right Outer Join : Return all rows from the right table, even if there are on matches in the left table.

INPUT –
SELECT Student.Id, Student.Name, Student.City, Student.Mobile,
               Student.Email, Course.Code, Course.Name
FROM Student
RIGHT OUTER JOIN Course ON Student.CourseId=Course.Id;

OUTPUT—


4) Full Outer Join : Return rows when there is a match in the one of the table.
INPUT—

SELECT Student.Id, Student.Name, Student.City, Student.Mobile,
               Student.Email, Course.Code, Course.Name
FROM Student
FULL OUTER JOIN Course ON Student.CourseId=Course.Id;

OUTPUT—

How to create a SSIS Project

 

1. Open Bids

Go to Start // Programs // Microsoft SQL Server 2008 R2 and open SQL Server Business Intelligence Development Studio

Below is example using Windows 7 and SQL Server 2008 R2.

1. Create new project
In BIDS select File // New // Project…

You will get new project dialog box where you should:

• Select Business Intelligence Projects in Project Types
• Select Integration Services Project in Templates:
• Give it a name (Try to avoid spaces for compatibility reasons)
• Remember or change location
• Click ok to create SSIS Project

AGGREGATE FUNCTION

The aggregate functions are :-

1) COUNT

2) SUM

3) MAX

4) MIN

5) AVG

INPUT –  SELECT * FROM Salary;

OUTPUT --
 

 
1) COUNT Functions used :--
 
SYNTAX – COUNT [ (*) \ (DISTINCT \ ALL) ] (COLUMN NAME)

INPUT – SELECT COUNT(*) FROM Salary;

OUTPUT—



INPUT – SELECT Department, COUNT(FirstName) AS UserName
                FROM Salary
                GROUP BY Department;

OUTPUT—



INPUT – SELECT Position,COUNT(FirstName) AS UserName
                FROM Salary
                GROUP BY Position;

OUTPUT –

 

2) SUM Functions Used :-

SYNTAX – SUM( [DISTINCT] COLUMN NAME)

INPUT – SELECT SUM(Salary),SUM(Bonus)
                FROM Salary;

OUTPUT –



INPUT – SELECT Department,SUM(Salary)+SUM(Bonus)
                FROM Salary
                GROUP BY Department;

OUTPUT –

 

3) MAX Functions Used :-


SYNTAX – MAX( [DISTINCT] COLUMN NAME)



INPUT – SELECT MAX(Salary)
                FROM Salary;

OUTPUT –

 

4) MIN Functions Used :-


SYNTAX – MIN( [DISTINCT] COLUMN NAME)


INPUT – SELECT MIN(Salary)
                FROM Salary;

OUTPUT –

 

5) AVG Functions Used :-


SYNTAX – AVG( [DISTINCT] COLUMN NAME)


INPUT – SELECT Department,AVG(Salary)
                FROM Salary
                GROUP BY Department;

OUTPUT –