Day4.docx

ASSIGNMENT - IV

1. Considering the following relations: Player(Jersey_no, Player_name, Against, Run) Team(Jersey_no, Team_name) Serve the SQL queries: a) Find out the names of the players whose 2nd letter of the name is ‘E’. b) Find out the total runs scored in the tournament by Tendulkar. c) Find out the Jersey number, Player name and Team name of every player in ascending order. d) Display Player name and run of the player who scored the highest run against India. e) Display Player name and run of the player who scored the highest run against India and Sri Lanka.

ANSWERs: a) MariaDB [play]> select Player_name from Player where Player_name like '_E%'; +--------------------+ | Player_name

|

+--------------------+ | Veerendra Shehbagh | | Kedar Jadhav

|

+--------------------+ 2 rows in set (0.00 sec) b) MariaDB [play]> select sum(Run)as Total from Player where Player_name='Sachin Tendulkar'; +-------+ | Total | +-------+ | 600 | +-------+ 1 row in set (0.00 sec)

c) MariaDB [play]> select Player.Jersey_no,Player.Player_name,Team.Team_name from Player,Team where Player.Jersey_no=Team.Jersey_no order by Player.Player_name asc; +-----------+--------------------+------------+ | Jersey_no | Player_name

| Team_name |

+-----------+--------------------+------------+ |

5 | Kedar Jadhav

| Australia |

|

6 | Sachin Tendulkar | Ind

|

|

7 | Sachin Tendulkar | India

|

|

4 | Veerendra Shehbagh | Bangladesh |

|

3 | Virat Kohli

| Sri Lanka |

+-----------+--------------------+------------+ 5 rows in set (0.00 sec) d) MariaDB [play]> select Player_name,max(Run)as Highest from Player where Against='India'; +---------------+---------+ | Player_name | Highest | +---------------+---------+ | Glenn Maxwell |

400 |

+---------------+---------+ 1 row in set (0.00 sec) e) MariaDB [play]> select Player_name,max(Run)as Highest from Player where Against='India'or Against='Sri Lanka'; +---------------+---------+ | Player_name | Highest | +---------------+---------+ | Glenn Maxwell |

400 |

+---------------+---------+ 1 row in set (0.00 sec)