Question

d. Assume the following sample of data :

Team

TeamID	TeamName	City	State	Win	Loss
1001	Wildcats	Los Angeles	CA	10	3
1002	Hawks	San Francisco	CA	8	5
1003	Renegades	Santa Barbara	CA	4	9
1004	Buffalos	San Diego	CA	3	10

Player

PlayerID	TeamID	CollegeID	PlayerName	Age	Position
101	1001	1080	Julio Peters	23	WR
102	1003	1080	Lance Randolph	29	QB
103	1003	2001	Timothy Haas	27	TE
115	1002	1478	Joey Daniel	32	LB
106	1004	1479	Hobert Golden	25	FB
110	1001	1478	Hunter Padilla	21	QB

College

CollegeID	CollegeName	City	State
1478	Buffalo University	San Diego	CA
1479	Apple College	Mountain View	CA
1080	Boolean College	San Francisco	CA
2001	Data University	Portland	OR

Using the previous tables, write the SQL queries that perform the following actions.

-List the name of all the teams with more wins than losses

-List the player ID and player name of all the players between 25 and 30 years old

-List the player name, age, position and college name for all the Quarterbacks(QB) coming from Boolean College and all the linebackers(LB) coming from Buffalo University.

-Using join queries, list the player name, team name , college name and city for all players playing for team located in the same city and state as their college.

Answer 1

Question are answered in top to down order:

Select teamname from team where win>loss;

Select playerid,playername from player where age between 25 and 30;

Select p.playername,p.age,p.position,c.collegename from player p inner join college c on (p.collegeid=c.collegeid) where (p.position='QB' and c.collegename='Boolean College') or (p.position='LB' and c.collegename='Buffalo University');

Select p.playername, t.teamname, c.collegename, c.city from player p inner join team t on (p.teamid=t.teamid) inner join college c on (p.collegeid=c.collegeid) where t.city=c.city and t.state=c.state;