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 the different ways of moving data/databases between servers and databases in SQL Server?

3 Answers   HCL,

why would you call update statistics? : Sql server database administration

0 Answers  

What are pessimistic lock and optimistic lock?

0 Answers  

How to generate create procedure script on an existing stored procedure?

0 Answers  

Write a query to find 5th highest amount paid from the customer table.

0 Answers  




What are the character string functions supported by sql server 2005?

0 Answers  

Explain candidate key, alternate key, and composite key?

0 Answers  

How can we delete Duplicate row in table?

0 Answers   Wipro,

Which autogrowth database setting is good?

0 Answers  

How to create a testing table with test data in ms sql server?

0 Answers  

Can we passed multiple recordset(set of records) using a Stored Procedure

2 Answers  

What are the advantages of user-defined functions over stored procedures in sql server?

0 Answers  

For more SQL Server Interview Questions Click Here
Categories