Scribd
Upload
4 views5 pages

Queries Example

The document outlines SQL queries related to a sports management database, focusing on college teams and their performance in various sports. It includes queries to find college details of winning teams, colleges with multiple participants, players with maximum runs and baskets, top chess players, and winners from the initial days of matches. Each query is accompanied by its corresponding SQL code and expected output.

Uploaded by

syedrohansajid
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views5 pages

Queries Example

The document outlines SQL queries related to a sports management database, focusing on college teams and their performance in various sports. It includes queries to find college details of winning teams, colleges with multiple participants, players with maximum runs and baskets, top chess players, and winners from the initial days of matches. Each query is accompanied by its corresponding SQL code and expected output.

Uploaded by

syedrohansajid
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

DATABASE MANAGEMENT SYSTEM

SPORTS MANAGEMENT

SQL QUERIES:

[Link] the college details of the team who has won the matches
on date “2016/04/13”.
Ans.
select college_id from ((select winner_id from (select match_no
from match_schedular where match_date='2016/04/13')
as r1 natural join cricket) as r2 join teams on
r2.winner_id=teams.team_id) union
select college_id from ( (select winner_id from (select match_no
from match_schedular where match_date='2016/04/13')
as r3 natural join basketball) as r4 join teams on
r4.winner_id=teams.team_id) union
select college_id from ((select winner_id from (select match_no
from match_schedular where match_date='2016/04/13')
as r5 natural join chess) as r6 join teams on
r6.winner_id=teams.team_id);

Output:
[Link] ID and Name of college which have atleast two
participants in the event?
Ans.
select college_id ,college_name from (select
college_id,college_name,count(team_id) as no_of_teams from
teams natural join college group by college_id,college_name) as r1
where no_of_teams>=2;

Output:
[Link] college_id’s of players who had made maximum run
and maximum basket?

Ans.
select college_id,college_name from (select college_id from (select
team_id from (select pid from (select max(runs) as max_runs from
cric_players_points) as r1 join cric_players_points on
r1.max_runs=cric_players_points.runs)
as r2 natural join players) as r3 natural join teams) as r4 natural
join college
union
select college_id,college_name from (select college_id from (select
team_id from (select pid from (select max(basket) as max_baskets
from basketball_players_points) as r5 join
basketball_players_points on
r5.max_baskets=basketball_players_points.basket)
as r6 natural join players) as r7 natural join teams) as r8 natural
join college;

Output:
[Link] mentor_id,match_date and top 3 player details in chess
game according to time(i.e. he has won in minimum time in all
matches)?
Ans.

select mentor_id,r2.* from (select * from players as p join (select


winner_id,match_date from chess_players_match order by time
ASC limit 3) as r1 on [Link]=r1.winner_id) as r2 JOIN teams on
r2.team_id=teams.team_id;

Output:
[Link] winner_id of chess players who have won the matches of
the initial 3 days of the event ?
Ans.

select distinct(winner_id) from (select distinct(match_date) from


match_schedular order by match_date ASC limit 3)
as r1 natural join chess_players_match;

Output:

You might also like