SQL SERVER / INTEGRATION SERVICES: 2013

Thursday, 5 September 2013

What are the different types of ISOLATION levels in SQL Server ?

Different types of ISOLATION levels in SQL Server?

Read Uncommitted
Read Committed
Repeatable Read
Serilalizable Read
Snapshot
Read Committed Snapshot

Monday, 2 September 2013

AdventureWorks Employee details using JOINS

/* Find the Employee details like Name,department,shift....... from AdventureWorks database */

SELECT
H.EmployeeID,
C.FirstName+' '+C.LastName AS FULL_NAME,
D.Name AS DEPARTMENT_NAME,
S.Name AS SHIFT_TYPE,
CONVERT(VARCHAR(15),S.StartTime,108)AS StartTime,
CONVERT(VARCHAR(10),S.EndTime,108) AS EndTime,
P.PayFrequency,
P.Rate
FROM HumanResources.EmployeeDepartmentHistory H
JOIN
HumanResources.Employee E
ON H.EmployeeID = E.EmployeeID
JOIN
Person.Contact C
ON E.ContactID = C.ContactID
JOIN
HumanResources.Department D
ON D.DepartmentID = H.DepartmentID
JOIN
HumanResources.Shift S
ON S.ShiftID = H.ShiftID
JOIN
HumanResources.EmployeePayHistory P
ON P.EmployeeID = E.EmployeeID

WHICH AGE GROUP PEOPLE IS GETTING HIGHEST SALARY

/* WHICH AGE GROUP PEOPLE IS GETTING HIGHEST SALARY */

-- MAXIMUM SALARY
-- MINIMUM SALARY

/* REPLACE MIN() FUNCTION WITH MAX() YOU WILL GET MAXIMUM SALARY OR ELSE YOU WILL GET MINIMUM SALARY */

CREATE DATABASE MY_PRACTICE

GO

USE MY_PRACTICE

GO

CREATE TABLE [dbo].[persons](
[id] [int] IDENTITY(1,1) PRIMARY KEY,
[Name] [char](20) NULL,
[age] [int] NULL,
[salary] [float] NULL)

GO

INSERT INTO persons VALUES ('sagar',28,5000),('shirish',25,2000),('raj',32,4000),('AMBI',27,8000)
,('SURESH',30,6000),('vidya',22,1500)

GO

SELECT * FROM persons

GO

SELECT ID,NAME,AGE,MIN(salary)as MAX_SALARY FROM persons
WHERE salary = (SELECT MIN(salary) FROM persons WHERE age BETWEEN 20 AND 25)
GROUP BY id,Name,age

UNION ALL

SELECT ID,NAME,AGE,MIN(salary)as MAX_SALARY FROM persons
WHERE salary = (SELECT MIN(salary) FROM persons WHERE age BETWEEN 26 AND 30)
GROUP BY id,Name,age

UNION ALL

SELECT ID,NAME,AGE,MIN(salary)as MAX_SALARY FROM persons
WHERE salary = (SELECT MIN(salary) FROM persons WHERE age BETWEEN 31 AND 35)
GROUP BY id,Name,age

Find the Highest,2nd Highest,Nth Highest Salary in SQL SERVER

Hello,

Below is the complete SQL QUERY to find the Highest Salary and 2nd Highest salary & Nth highest salary.

CREATE DATABASE MY_PRACTICE

GO

USE MY_PRACTICE

GO
CREATE TABLE [dbo].[persons](
[id] [int] IDENTITY(1,1) PRIMARY KEY,
[Name] [char](20) NULL,
[age] [int] NULL,
[salary] [float] NULL)

GO

INSERT INTO persons VALUES ('sagar',28,5000),('shirish',25,2000),('raj',32,4000),('AMBI',27,8000)
,('SURESH',30,6000)

go
--Employee details
select * from dbo.persons
go

-- Find the highest salary of the employee
select name,MAX(salary) from persons
group by Name

go
-- Find the 2nd highest salary of the employee
select id,name,max(salary) as second_high_salary from persons
where salary <> (select MAX(salary)from persons)
group by id,Name order by second_high_salary desc

go
-- Find the Nth highest salary for the Employee
select * from persons P1
WHERE (1) = (SELECT COUNT(DISTINCT(P2.salary)) FROM persons P2 WHERE P2.salary > P1.salary )

-- USING TOP KEYWORD FIND THE NTH HIGHEST SALARY

SELECT TOP 1 SALARY
FROM (SELECT DISTINCT TOP 1 SALARY FROM persons
ORDER BY salary DESC)
AS EMP
ORDER BY salary

Friday, 23 August 2013

Run SQL Syntax in EXECUTE msdb.dbo.sp_send_dbmail using dynamic variables.

Declare @Recipient_List nvarchar(max)
Declare @body_Content nvarchar(max)
Declare @Subject nvarchar(max)
Declare @xml nvarchar(max)

set @Recipient_List = 'Yourmail_id@000.com'

set @xml = cast((select count(*) as 'td'

fromdbo.Table_duplicates for XML path('tr'),elements) as nvarchar(max))

set @body_Content = '<html><body><h3> Duplicate Records Found. Verify the table Duplicates to check the duplicate records</h3>

<table border = 1>

<tr>

<th> Total Nunber of Duplicate records </th></tr>'
set

@body_Content = @body_Content + @xml + '</table></body></html>'

EXECUTE msdb.dbo.sp_send_dbmail

@recipients = @Recipient_List,

@subject = 'Count for Duplicate records',

@body = @body_Content,

@body_format = 'HTML'

Monday, 19 August 2013

Move corrupted Flat files into the Error Path using SSIS

One of my interview question in SSIS:

I have nightly SSIS packages are running every day. And this package is schduled to load the 10 flat files into OLEDB database in every 10 minutes. Now i got one wrong or corrupted Flat file.

The Question is:

Configure a DTSX package if there is any Corrupted file came with missing columns or Delimeters the it should move into the Error Path Location.

How to move Bad or Corrupted flat files or excel files in to the error path?

Answer:

Makesure Maximum Error count is more than zero.

Basically Maximum Error Count will avoid maximun number of errors.

Variable: Create a Variable inorder to MAP the For Each Loop Container and Excel Connection manager(Souce)

Let me share more thought on this senarios.

Thank you

Vidya Sagar Malla

Pages