1. Categories >> Software >> Databases >> SQL Server
  2. Suggest New Category

can any one answer this query,thank you in advance

Table 1 has 2 columns: EmployeeId, T shirtsize(values can
be 1,2,3)
Table 2 has 2 columns: EmployeeId, Region
Write SQL to Find the region which has the largest number
of people with Tshirt size=3

Question Posted / padmavasireddy

Answers were Sorted based on User's Feedback



can any one answer this query,thank you in advance Table 1 has 2 columns: EmployeeId, T shirtsize..

Answer / karthick veerappan

select top 1 region,count(*) from table2 where empid in
(select empid from table1 where tsize=3) group by region
order by count(*) desc

Is This Answer Correct ?    8 Yes 5 No

can any one answer this query,thank you in advance Table 1 has 2 columns: EmployeeId, T shirtsize..

Answer / sitaram karancheti

In Oracle:

SELECT COUNT(table1.emp_id), region
FROM table1, table2
WHERE tshirt_size = 3 AND table1.emp_id = Table2.emp_id
AND rownum < 2
GROUP BY region
ORDER BY COUNT(table1.emp_id) DESC

In MySql:

SELECT COUNT(table1.emp_id), region
FROM table1, table2
WHERE tshirt_size = 3 AND table1.emp_id = Table2.emp_id
GROUP BY region
ORDER BY COUNT(table1.emp_id) DESC LIMIT 1

Is This Answer Correct ?    4 Yes 2 No

can any one answer this query,thank you in advance Table 1 has 2 columns: EmployeeId, T shirtsize..

Answer / pradip jain

select top 1 region,count(t2.eid) a
from t2
join t1 on t1.eid=t2.eid
group by region,[T-Shirt_Size]
having [T-Shirt_Size]=3
order by a desc

Is This Answer Correct ?    1 Yes 0 No

can any one answer this query,thank you in advance Table 1 has 2 columns: EmployeeId, T shirtsize..

Answer / pradip jain

Himesh is also correct with little change..

select top 1 region,count(*) e
from t2 JOIN t1 ON (t1.eid = t2.eid and
t1.[T-Shirt_Size] = 3)
group by region
order by e desc

Is This Answer Correct ?    1 Yes 0 No

can any one answer this query,thank you in advance Table 1 has 2 columns: EmployeeId, T shirtsize..

Answer / susanna

select top 1 Count(r.Empid) as Ct,r.regionId from Emp_Region
r inner join Emp_Tshirt t on r.Empid= t.EmpId
where t.T_size=3 group by r.regionId order by Ct desc

Is This Answer Correct ?    1 Yes 0 No

can any one answer this query,thank you in advance Table 1 has 2 columns: EmployeeId, T shirtsize..

Answer / avaneesh bajoria

select top 1 t2.region , count(t1.tsize) as co from tsize
t1,region t2
where t1.empid = t2.empid
group by t2.region,t1.tsize
having t1.tsize = 3 order by co desc

Is This Answer Correct ?    4 Yes 4 No

can any one answer this query,thank you in advance Table 1 has 2 columns: EmployeeId, T shirtsize..

Answer / ashok

select b.Region, a.TSize, Count(a.TSize)
as TSizecount from Table1 a,Table2 b where a.Empid =
b.Empid and a.TSize=3 group by region, TSize order by
Region, count(*), TSize desc

Is This Answer Correct ?    0 Yes 0 No

can any one answer this query,thank you in advance Table 1 has 2 columns: EmployeeId, T shirtsize..

Answer / lakram5455

SELECT a.Region FROM
(SELECT TOP(1) a.TSize, COUNT(a.EId) AS TotalCount, b.Region
FROM emp a INNER JOIN Region b ON b.EId = a.EId WHERE TSize
= 3 GROUP BY a.TSize, b.Region ORDER BY TotalCount DESC) a

Is This Answer Correct ?    0 Yes 0 No

can any one answer this query,thank you in advance Table 1 has 2 columns: EmployeeId, T shirtsize..

Answer / anil gupta

select region, count(t1.size) from tab1 t1 inner join tab2
t2 on t1.eid = t2.eid where t1.size = 3 group by t2.region
order by count(t1.size) DESC and rownum = 1;

Is This Answer Correct ?    0 Yes 0 No

can any one answer this query,thank you in advance Table 1 has 2 columns: EmployeeId, T shirtsize..

Answer / ramesh babu

Create table #temptshirt(empid int,tshirt int)
Create table #tempregion(empid int,region varchar(200))

Insert into #temptshirt(empid,tshirt)values(100,2),(102,4),
(103,5),(104,3),(105,3),(106,3),(107,5),(108,6),(109,3),
(110,4),(111,3)
Insert into #tempregion(empid,region)values(100,'SA'),
(102,'SA'),(103,'UK'),(104,'UK'),(105,'PHL'),(106,'US'),
(107,'US'),(108,'US'),(109,'RSA'),(110,'RSA'),(111,'UK')

Select Top 1 T.region,T.Total
from
(
Select b.region,COUNT(*)[Total] from #temptshirt a inner
join #tempregion b on a.empid=b.empid
Where a.tshirt=3
group by b.region
)T
order by 2 desc

Is This Answer Correct ?    0 Yes 0 No

Post New Answer

More SQL Server Interview Questions

What are types of subqueries?

0 Answers  

What is deadlock and how to avoid the deadlocks.

3 Answers   TCS, Wipro,

what is a deadlock? : Sql server database administration

0 Answers  

How to loop through the result set with @@fetch_status?

0 Answers  

Your company has 50 branches all over the country all the branches, including the head office have sql server as the database every night all 50 branches upload certain information to the head office which replication topology is best suited for the above scenario?

0 Answers  




What is analysis service repository?

0 Answers  

what is the system function to get the current user's user id? : Sql server database administration

0 Answers  

What is the most common type of join?

0 Answers  

What are the new security features added in sql server 2012? : sql server security

0 Answers  

What are the restrictions applicable while creating views? : SQL Server Architecture

0 Answers  

What is database normalization?

7 Answers   Deloitte, Digicel, JPMorgan Chase, Verifone,

How do you size a resultset?

0 Answers  

For more SQL Server Interview Questions Click Here
Categories