SQL Query

Nilesh Sanyal

13y1.7kSQL

Consider following tables:

PLAYER(NAME,COUNTRY,AGE)

P_DATA(NAME,M_DATE,RUNS,WICKETS,CATCHES,AGAINST)

SQL Queries

1)List players who have played against all countries

2)For each country, name the player(s) who have scored maximum runs,taken maximum wickets in a match

Please provide SQL codes to solve above mentioned queries.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Accepted answer
Here it is

for 1:

select Name from
(
select NAME,COUNT(AGAINST) as CountryCount from P_DATA group by NAME
) as A
INNER JOIN
(
select count(distinct AGAINST) as TotalCountry from P_DATA
) AS B
ON
A.CountryCount=B.TotalCountry-1

for 2:

select A.COUNTRY,A.NAME,SUM(RUNS) as Run,SUM(WICKETS) as Wickets
from PLAYER A INNER JOIN P_DATA B ON B.NAME=A.NAME
group by A.COUNTRY,A.NAME

Regards,
Iftikar
Rasadul Alam Rashed13y ago
SQL Queries Optimization Tips
All time we use lot of SQL queries. But we don't consider about its performance. If we follow some tips then our query will be more efficient. Some of them are.

Use views and stored procedures instead of heavy-duty queries
Try to use constraints instead of triggers, whenever possible.
Use table variables instead of temporary tables.
Try to use UNION ALL statement instead of UNION
Try to avoid using the DISTINCT clause
Try to avoid using SQL Server cursors, whenever possible.
Try to avoid the HAVING clause

for more you can visit
http://cybarlab.com/sql-queries-optimization-tips
Nilesh Sanyal13y ago
Thank you,sir.
Iftikar Hussain13y ago
Hi,
For the first query I think to have one Country table, otherwise how you will find that player has been played against all country.

Regards,
Iftikar