Golgappa.net | Golgappa.org | BagIndia.net | BodyIndia.Com | CabIndia.net | CarsBikes.net | CarsBikes.org | CashIndia.net | ConsumerIndia.net | CookingIndia.net | DataIndia.net | DealIndia.net | EmailIndia.net | FirstTablet.com | FirstTourist.com | ForsaleIndia.net | IndiaBody.Com | IndiaCab.net | IndiaCash.net | IndiaModel.net | KidForum.net | OfficeIndia.net | PaysIndia.com | RestaurantIndia.net | RestaurantsIndia.net | SaleForum.net | SellForum.net | SoldIndia.com | StarIndia.net | TomatoCab.com | TomatoCabs.com | TownIndia.com
Interested to Buy Any Domain ? << Click Here >> for more details...

  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

Why functions are used in sql server?

0 Answers  

Suppose you want to implement the one-to-many relationships while designing tables. How would you do it?

0 Answers  

difference between Clustered index and non clustered index ?

0 Answers   Infosys,

What are sp_configure commands and set commands?

0 Answers  

what is mean by constraints in sql?

5 Answers   Granada, IBM,

What are Row versions of DataRow?

0 Answers  

Retrieve the unique rows from table without using UNIQUE and DISTINCT keywords.

0 Answers   Global Logic,

what is a deadlock? : Sql server database administration

0 Answers  

what is memory-optimized nonclustered indexes

0 Answers  

Which system table contains information on constraints on all the tables created?

2 Answers  

What are the types of resultset?

0 Answers  

What will happen if a column containing char type data is changed to the nchar data type?

0 Answers  

For more SQL Server Interview Questions Click Here
Categories