Learn SQL with Cricket Data

Here is the Sample Dataset of the Indian Batsman

Table Name: Batsman

Let’s start with the Basic Queries in SQL

1.Write a SQL query to retrieve all the records from a table

Select * from Batsman;

Output:






2. Write an SQL query to fetch “Player” from the Batsman table in the upper case

Select upper(player)
From Batsman;


output:









3. SQL query to find the maximum runs from table ‘Batsman’

Select max (Runs)
From Batsman;


Output:

4. SQL query to find the minimum runs from table ‘Batsman’


Select min (Runs)
From Batsman;


output:


5. SQL query to find the Average runs from table ‘Batsman’


select avg(Runs)
From Batsman;


Output:

6. Write an SQL query to print the first 5 characters of ‘Player’ from Batsman.



select substring(Player,1,5)
From batsman;


output:

7. Write an SQL query to find the position of the alphabet (‘a’) in the Player_name column ‘Yuvraj singh’ from the Batsman table


select INSTR(player,BINARY’a’)
from Batsman
where player = ‘yuvraj singh’


output:



Tip:

The INSTR method is case-sensitive by default.
Using the Binary operator will make INSTR work as the case-sensitive function.

8. Write an SQL query to print the Player from Batsman table after replacing ‘a’ with ‘A’


select replace(player,’ a’,’ A’)
From Batsman


Output:






9. Write an SQL query to print all Player details from the Batsman table order by Runs 

Descending

Select player,runs
From Batsman
Order by Runs desc;



output:

10. Write an SQL query to print all Player details from the Batsman table order by Runs Ascending

Select player,Runs
From Batsman
Order by Runs asc;

Output:

11. SQL query to find the number of 50s scored by each player


Output:

12. SQL query to find current date and time

Select now();

Output:

13. Write an SQL query to print details of the Players whose first name starts with ‘V’



select * from batsman
Where player like ‘v%’

Output:

14. Write an SQL query to print details of the Players whose name contains ‘v’


select * from batsman
Where player like ‘%v%’


output:

15. SQL query to find the player who has scored the maximum number of Runs



select player,max(Runs)
From batsman
Where runs = (select max(Runs) from batsman);


output:

16. SQL query to find the top 5 players who have the most average

Select player,Ave
From batsman
Order by Ave desc
Limit 5;

Output:

17. SQL query to find the players who have more than 10000 runs and has an average of more than 40

Select player,Runs,Ave
From Batsman
Where runs>10000 and ave>40

Output:

18. SQL query to count the number of players from the Table name ‘Batsman’

Select count(player)
From Batsman

Output:

19. Write a SQL query to find the players averaged between 40 and 50

Select player,Ave
From batsman
Where Ave between 40 and 50;

Output:

20. Write a SQL query to show only ODD rows from the table ‘Batsman’

Select * from Batsman
Where mod(batsman_id,2)<>0;


Output:

Source: ESPN stats info, Howstat, Tech Beamers