ITM500 - LABS 9 & 10 SOLUTION.doc

4 Pages
Unlock Document

Information Technology Management
ITM 500
Raymond Moss

--Using the Golf 2 Database -- Lab 9 --1 List the details for each tournament that has had at least 3 entries --in 2006 select * from Tournament where TourId in (select TourId from Entry where YEAR = 2006 group by Tourid having count(*) >= 3) --2 List the details for senior members whose barbill is the above average for their membership type select * from member where Membertype = 'senior' and barbill > (select avg(Barbill) from Member where MemberType = 'senior') --3 List the id and names of all members who played any tournament after the year 2005 --(Use a nested approach) select MemberID, Membername from member where memberId in (select memberid from entry where YEAR > 2004) --4 Repeat query 3 above but use a join. Only show each member's details once select distinct m.MemberID ,membername from member m inner join Entry e on e.memberid = m.memberid where year > 2004 --5. List the details of members who are coached by a member of TeamB --(Use a Nested Approach) select * from member where coachmemberid in (select memberid from member where TeamName = 'TeamB' ) --6 Repeat query 5 above but use a join. select mem.* from member mem inner join member coach on coach.memberid = mem.coachmemberid where coach.TeamName = 'TeamB' --7. List the member id and name of the coaches who coach Junior members -- who have a handicap over 20. Only show the coach details once select distinct coach.Memberid, coach.membername from member mem inner join member coach on coach.memberid = mem.coachmemberid where mem.MemberType = 'junior' and mem.Handicap > 20 --or select memberid, memberName from member where MemberID in (select coachmemberid from member where MemberType = 'junior' and handicap > 20) --8 List the details for all members that are managers of a team TeamA select member.* from member inner join team on memberid = mgrmemberid where team.TeamName = 'teama' -- or select * from member where MemberID in (select mgrmemberid from team where TeamName = 'teama') --9 List the member details for coaches that have --played in the 'Canterbury' tournament select * from Member where MemberID in (select CoachMemberID from Member inner join Entry e on e.MemberID = CoachMemberID inner join Tournament t on t.TourID = e.TourID and TourName = 'canterbury') --or select distinct c.* from Member m inner join Member c on m.CoachMemberID = c.memberid inner join Entry e o
More Less

Related notes for ITM 500

Log In


Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.