ITM500 - LABS 9 & 10 SOLUTION.doc

4 Pages
501 Views
Unlock Document

Department
Information Technology Management
Course
ITM 500
Professor
Raymond Moss
Semester
Winter

Description
--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


OR

Join OneClass

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

Sign up

Join to view


OR

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.


Submit