relational Algebra_ Solutions New Jersey Institute Of Technology IS 683

1. Select yearid, lgid, teamid, playerid and the number of home runs (HR) from the
BATTING table.
Π yearid, lgid, teamid, playerid, HR (Batting)

2. Modify the query in #1 so it also shows the number of hits that were for “extra bases” and
the batting average (H/AB). The Extra Bases column must be calculated by adding the
following columns together (2B, 3B and HR). Rename the derived column Extra Base
Hits.
Π yearid, lgid, teamid, playerid, HR (ρBatting_Avg/(H/AB) (ρExtra_Bases/(2B+3B+HR) (σ AB>0
(Batting))))


3. Select the yearid, lgid, Teamid, PlayerID and HR from the Batting table for all players who
hit 20 or more home runs (HR) in 2015 or 2016 and played on the New York Yankees.
Hint: Use teamid NYA in your where statement
Π yearid, lgid, teamid, playerid, HR
(σHR>=20 ^ (yearid=2015 v yearid = 2016) ^ teamid=’NYA’ (Batting))


4. Write a query that lists the playerid, birthcity, birthstate, salary and batting average for
all players born in New Jersey sorted by last name and year in ascending order.



P ⃪ Π playerid, nameLast, birthcity, birthstate (σ birthState=’NJ’ (People))
S ⃪ Π playerid, Salary, yearid (σ P.playerid = Salaries.playerid (Salaries))
B ⃪ Π playerid, yearid(ρBatting_Avg/(H/AB) (σ S.yearid = Batting.yearid ^ P.playerid =
Batting.playerid ^ AB>0 (Batting))


Result ⃪ P ∪ S ∪ B

5. Write the same query as #4 but use a LEFT JOIN.
PS ⃪ Π playerid, nameLast, birthcity, birthstate, salary, yearID (σ birthState=’NJ’
(People⟕Salaries))

Π playerid, nameLast, birthcity, birthstate, salary, yearID (ρBatting_Avg/(H/AB) (σAB>0
(τnameLast, PS.yearID(PS⟕ Batting))))




This study source was downloaded by 100000860007226 from CourseHero.com on 02-08-2023 14:30:42 GMT -06:00


https://www.coursehero.com/file/47474429/relationalAlgebra-Solutionpdf/