SQL Query

Question

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.

Iftikar Hussain · 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 Rashed · Answer

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 Sanyal · Answer

Thank you,sir.

Iftikar Hussain · Answer

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