Sign Up Login


  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

If i am handling 150 servers then how to check the active jobs of all the servers?

0 Answers  

What is a trigger?

7 Answers  

Stored Procedure returns data from multiple tables. How to access it in your ASP.Net code?

0 Answers   HCL,

If you are working on a SQL database and if suddenly a developer changes the code and your queries results start giving errors,how will you check using a T-SQL query (on system tables) that what has changed in the database.

2 Answers   Microsoft,

write a query for list of owner who are having multiple bikes in below table 1 shanker pulsar 2 shanker Honda 3 shanker car 4 Balu pulsar 5 Balu Honda 6 Balu car 7 Shyam pulsar 8 Jaya Honda 9 Deepa car 10 vasu car

12 Answers   Cognizant, TCS,




What is xdr?

0 Answers  

Define inner join in sql server joins?

0 Answers  

How to delete the duplicate rows from a table in SQL Server ??

3 Answers  

Difference Between ORDER BY Clause and GROUP BY Clause in SQL?

0 Answers   MCN Solutions,

Repair mdf file

2 Answers  

I hav 10 records only one column 1,2,3,4,5......10 now am writing query select max(column name) Then What is The Output

9 Answers   Brigade, Wipro,

What is lock escalation? : sql server database administration

0 Answers  

For more SQL Server Interview Questions Click Here
Categories