10/14/2015

T - SQL Real Time Scenario 3

T - SQL Real Time Scenario 3:

In this post, I am going to share some basic SQL Queries which are generally asked in the SQL interview:
So for example we have Employee data so basis on the employee data we will be playing with SQL queries:
Below are Create and Insert scripts for Employee temp table:
CREATE table #EmpData

(EMP_ID int

,first_name varchar(50)

,last_name varchar(50)

,Gender varchar(10)

,Salary int

,DeptID varchar(10)

,City varchar(50)

,county varchar(50)

,State varchar(50)

,zip  int

,phone1  varchar(50)

,email  varchar(100)

)
INSERT INTO #EmpData (EMP_ID,first_name   ,last_name  ,Gender     ,Salary     ,DeptID      ,city ,county     ,state      ,zip  ,phone1     ,email)

VALUES

(110,'James', 'Butt', 'M',54541,'HR','New Orleans', 'Orleans', 'LA', 70116,'504-621-8927', 'jbutt@gmail.com'),

(111,'Josephine', 'Darakjy', 'F',21542,'AC','Brighton', 'Livingston', 'MI', 48116,'810-292-9388', 'josephine_darakjy@darakjy.org'),

(112,'Art', 'Venere', 'F',246554,'IT','Bridgeport', 'Gloucester', 'NJ', 8014,'856-636-8749', 'art@venere.org'),

(113,'Lenna', 'Paprocki', 'M',54515,'HR','Anchorage', 'Anchorage', 'AK', 99501,'907-385-4412', 'lpaprocki@hotmail.com'),

(114,'Donette', 'Foller', 'F',11000,'IT','Hamilton', 'Butler', 'OH', 45011,'513-570-1893', 'donette.foller@cox.net'),

(115,'Simona', 'Morasca', 'M',8900,'HR','Ashland', 'Ashland', 'OH', 44805,'419-503-2484', 'simona@morasca.com'),

(116,'Mitsue', 'Tollner', 'F',9500,'FIN','Chicago', 'Cook', 'IL', 60632,'773-573-6914', 'mitsue_tollner@yahoo.com'),

(117,'Leota', 'Dilliard', 'F',15451,'FIN','San Jose', 'Santa Clara', 'CA', 95111,'408-752-3500', 'leota@hotmail.com'),

(118,'Sage', 'Wieser', 'F',45112,'HR','Sioux Falls', 'Minnehaha', 'SD', 57105,'605-414-2147', 'sage_wieser@cox.net'),

(119,'Kris', 'Marrier', 'M',74841,'IT','Baltimore', 'Baltimore City', 'MD', 21224,'410-655-8723', 'kris@gmail.com'),

(120,'Minna', 'Amigon', 'F',48414,'AC','Kulpsville', 'Montgomery', 'PA', 19443,'215-874-1229', 'minna_amigon@yahoo.com'),

(121,'Abel', 'Maclead', 'M',67415,'AC','Middle Island', 'Suffolk', 'NY', 11953,'631-335-3414', 'amaclead@gmail.com'),

(122,'Kiley', 'Caldarera', 'M',48451,'FIN','Los Angeles', 'Los Angeles', 'CA', 90034,'310-498-5651', 'kiley.caldarera@aol.com'),

(123,'Graciela', 'Ruta', 'M',87645,'FIN','Chagrin Falls', 'Geauga', 'OH', 44023,'440-780-8425', 'gruta@cox.net'),

(124,'Cammy', 'Albares', 'M',67132,'AC','Laredo', 'Webb', 'TX', 78045,'956-537-6195', 'calbares@gmail.com'),

(125,'Mattie', 'Poquette', 'M',41212,'IT','Phoenix', 'Maricopa', 'AZ', 85013,'602-277-4385', 'mattie@aol.com'),

(126,'Meaghan', 'Garufi', 'F',52641,'IT','Mc Minnville', 'Warren', 'TN', 37110,'931-313-9635', 'meaghan@hotmail.com'),

(127,'Gladys', 'Rim', 'F',65441,'SALE','Milwaukee', 'Milwaukee', 'WI', 53207,'414-661-9598', 'gladys.rim@rim.org'),

(128,'Yuki', 'Whobrey', 'F',115141,'IT','Taylor', 'Wayne', 'MI', 48180,'313-288-7937', 'yuki_whobrey@aol.com'),

(129,'Fletcher', 'Flosi', 'M',454311,'SALE','Rockford', 'Winnebago', 'IL', 61109,'815-828-2147', 'fletcher.flosi@yahoo.com'),

(130,'Bette', 'Nicka', 'M',484521,'FIN','Aston', 'Delaware', 'PA', 19014,'610-545-3615', 'bette_nicka@cox.net'),

(131,'Veronika', 'Inouye', 'F',46841,'SALE','San Jose', 'Santa Clara', 'CA', 95111,'408-540-1785', 'vinouye@aol.com'),

(132,'Willard', 'Kolmetz', 'M',5412,'SALE','Irving', 'Dallas', 'TX', 75062,'972-303-9197', 'willard@hotmail.com'),

(133,'Maryann', 'Royster', 'F',74812,'SALE','Albany', 'Albany', 'NY', 12204,'518-966-7987', 'mroyster@royster.com'),

(134,'Alisha', 'Slusarski', 'M',54851,'AC','Middlesex', 'Middlesex', 'NJ', 8846,'732-658-3154', 'alisha@slusarski.com'),

(135,'Allene', 'Iturbide', 'F',751654,'HR','Stevens Point', 'Portage', 'WI', 54481,'715-662-6764', 'allene_iturbide@cox.net'),

(136,'Chanel', 'Caudy', 'F',48745,'HR','Shawnee', 'Johnson', 'KS', 66218,'913-388-2079', 'chanel.caudy@caudy.org'),

(137,'Ezekiel', 'Chui', 'M',8563,'SALE','Easton', 'Talbot', 'MD', 21601,'410-669-1642', 'ezekiel@chui.com'),

(138,'Willow', 'Kusko', 'M',66216,'HR','New York', 'New York', 'NY', 10011,'212-582-4976', 'wkusko@yahoo.com')

In the above insert script you can see I have inserted multiple records by a single insert statement so this is the example to insert multiple records by a single insert statement.

 
Below are some basic but tricky SQL questions with their solutions by using this employee data:

Ø  Update Male TO Female and Female To Male with single updtae query in Gender column

 
UPDATE #EmpData SET Gender =(CASE WHEN Gender = 'F' THEN 'M' ELSE 'F' END)
 

Ø  Swap FistName value into LastName and LastName into FirstName form a single update query
 

UPDATE #EmpData SET first_name=last_name ,last_name=first_name

 
Ø  Show highest salary DeptID wise

 
SELECT DeptID, MAX(Salary) as HighestSalary From #EmpData

group BY DeptID

 
Ø  Generate rank numbers based on the salry for all employee without using rank function:

 
SELECT EMP_ID,first_name,last_name  ,Salary,

(SELECT COUNT(*)+1  FROM #EmpData B WHERE A.Salary<B.Salary) AS Rank FROM #EmpData A

ORDER BY Salary DESC
 

If we use Rank Function

 
SELECT EMP_ID,first_name,last_name  ,Salary,

DENSE_RANK() OVER(PARTITION BY 1 ORDER by A.Salary DESC) as Rank

FROM #EmpData A

 
Ø  Generate rank numbers based on the salry for each department without using rank function:

 
SELECT EMP_ID,first_name,last_name  ,Salary,DeptID,

(SELECT COUNT(*)+1  FROM #EmpData B WHERE A.Salary<B.Salary and A.DeptID=B.DeptID) AS Ranks FROM #EmpData A

ORDER BY A.DeptID ,A.Salary DESC

 

If we use Rank Function

 
SELECT EMP_ID,first_name,last_name  ,Salary,DeptID,

DENSE_RANK() OVER(PARTITION BY DeptID ORDER by A.DeptID,A.Salary DESC) as Rank

FROM #EmpData A
        

Ø  Get 2nd highest salary for each department without using rank function:

 
SELECT EMP_ID,first_name,last_name  ,Salary,DeptID,

(SELECT COUNT(*)+1  FROM #EmpData B WHERE A.Salary<B.Salary and A.DeptID=B.DeptID) AS Ranks

FROM #EmpData A

Where (SELECT COUNT(*)+1  FROM #EmpData B WHERE A.Salary<B.Salary and A.DeptID=B.DeptID)=2

ORDER BY A.DeptID ,A.Salary DESC

If we use Rank Function

Select * from (

            SELECT EMP_ID,first_name,last_name  ,Salary,DeptID,

            DENSE_RANK() OVER(PARTITION BY DeptID ORDER by A.DeptID,A.Salary DESC) as Rank

            FROM #EmpData A

            ) as EmpData WHERE EmpData.Rank=2

Ø  Get top 5 highest salry from all employess data without using rank function:

 
SELECT EMP_ID,first_name,last_name  ,Salary,DeptID,

(SELECT COUNT(*)+1  FROM #EmpData B WHERE A.Salary<B.Salary ) AS Ranks

FROM #EmpData A

Where (SELECT COUNT(*)+1  FROM #EmpData B WHERE A.Salary<B.Salary )<=5

ORDER BY A.Salary DESC

If we use Rank Function

Select * from (

            SELECT EMP_ID,first_name,last_name  ,Salary,DeptID,

            DENSE_RANK() OVER(PARTITION BY 1 ORDER by A.Salary DESC) as Rank

            FROM #EmpData A

            ) as EmpData WHERE EmpData.Rank<=5

Ø  Count distinct email domain name

 
SELECT Substring(email,Charindex('@',email)+1,LEN(Email)) EmailDoaminName,COUNT(1) as TotalCount

from #EmpData

Group By Substring(email,Charindex('@',email)+1,LEN(Email))

Ø  Show Department's total Salary in each row without group by clause

 
SELECT EMP_ID,first_name,last_name,Gender,Salary,DeptID,

Sum(Salary) Over (Partition By DeptID) as DepartmentTotalSalary

From #EmpData

Order BY DeptID

 
Ø  Show Department's Highest Salary in each row without group by clause

 
SELECT EMP_ID,first_name,last_name,Gender,Salary,DeptID,

Max(Salary) Over (Partition By DeptID) as DepartmentHighestSalary

From #EmpData

Order BY DeptID, Salary

 
Ø  Show Department wise total count for Female and Male column, Pivot kind of thing but without using pivot function

 
SELECT DeptID,

Sum(CASE WHEN Gender='M' THEN 1 ELSE 0 END) as TotalMale,

Sum(CASE WHEN Gender='M' THEN 1 ELSE 0 END) as TotalFemale

FROM #EmpData

Group BY DeptID


If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!