T - SQL Real Time
Scenario 3:
UPDATE #EmpData SET Gender =(CASE WHEN Gender = 'F' THEN 'M' ELSE 'F' END)
SELECT DeptID, MAX(Salary) as HighestSalary From
#EmpData
SELECT EMP_ID,first_name,last_name ,Salary,
SELECT EMP_ID,first_name,last_name ,Salary,
SELECT EMP_ID,first_name,last_name ,Salary,DeptID,
SELECT EMP_ID,first_name,last_name ,Salary,DeptID,
SELECT EMP_ID,first_name,last_name ,Salary,DeptID,
SELECT Substring(email,Charindex('@',email)+1,LEN(Email))
EmailDoaminName,COUNT(1) as TotalCount
SELECT EMP_ID,first_name,last_name,Gender,Salary,DeptID,
SELECT EMP_ID,first_name,last_name,Gender,Salary,DeptID,
SELECT DeptID,
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
Ø
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
group BY DeptID
Ø
Generate rank numbers based on the salry for all employee without using
rank function:
(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
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 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
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 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
from #EmpData
Group By Substring(email,Charindex('@',email)+1,LEN(Email))
Ø
Show Department's total Salary in each row without group by clause
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
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
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!
No comments:
Post a Comment