**Write SQL SELECT commands to answer the following queries.** Consider the foll
ID: 3683561 • Letter: #
Question
**Write SQL SELECT commands to answer the following queries.**
Consider the following relational database for the Super Baseball League. It keeps track of teams in the league, coaches and players on the teams, work experience of the coaches, bats belonging to each team, and which players have played on which teams. Note the following facts about this environment:
? The database keeps track of the history of all of the teams that each player has played on and all of the players who have played on each team.
? The database only keeps track of the current team that a coach works for.
? Team number, team name, and player number are each unique attributes across the league.
? Coach name is only unique within a team (and we assume that a team cannot have two coaches of the same name.)
? Serial number (for bats) is only unique within a team.
? In the Affiliation table, the Years attribute indicates that number of years that a player played on a team; the batting average is for the years that a player played on a team.
INFORMATION:
1. Find the names and cities of all of the teams with team numbers greater than 3. List the results alphabetically by team name.
2. List all of the coaches whose last names begin with “R” and who have between 5 and 10 years of experience as college coaches (see YEARSEXPERIENCE and EXPERIENCETYPE).
3. Find the total number of years of experience of Coach Taylor on team number 3.
4. Find the number of different types of experience that Coach Taylor on team number 3 has.
5. Find the total number of years of experience of each coach on team number 3.
6. How many different manufacturers make bats for the league’s teams?
7. Assume that team names are unique. Find the names of the players who have played for the Yankees for at least five years (see YEARS in the AFFILIATION Table.)
8. Assume that team names are unique. Find the total number of years of work experience of each coach on the Royals, but only include in the result those coaches who have more than eight years of experience.
9. Find the names of the league’s youngest players.
10. Find the names of the league’s youngest players whose last names begin with the letter “M”.
Explanation / Answer
1)
select teamname , city from team where teamnum > 3 ;
2)
// for lastname assuming the
// data in column coachname is stored as <firstname>space<lastname>
// so let coachname for Taylor is 'SomeFirstName Taylor'
// column experiencetype contains values like 'college' or 'school' or 'university' , etc
select distinct coachname from workexperience
where lower(experiencetype)='college'
and yearsexperience is between 5 and 10
and coachname like '% R%' ;
3)
//assuming data in column coachname is stored as <firstname>space<lastname>
// so let coachname for Taylor is 'SomeFirstName Taylor'
select sum(yearsexperience) from workexperience
where teamnum = 3 and coachname like '% Taylor';
4)
select distinct experiencetype from workexperience
where teamnum = 3 and coachname like '% Taylor';
5)
select sum(yearsexperience),coachname from workexperience
where teamnum = 3
group by coachname;
6)
select distinct manufacturer from bats;
7)
select playername,playernum from player p
join affiliation a on ( p.playernum = a.playernum)
join team t on (a.teamnum = t.teamnum)
where t.teamname = 'YANKEES' and a.years >=5 ;
8)
select coachname , sum(yearsexperience from workexperience w
join team t on ( w.teamnum = t.teamnum)
where t.teamname='ROYALS' and w.yearsexperience >=8
group by w.coachname;
9)
select * from player where age = ( select min(age) from player);
10)
select * from player where age = ( select min(age) from player where playername like '% M%' );
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.