Class Notes (785,637)
Canada (481,693)
ITM 500 (22)
Lecture

compiled SQL labs.docx
Premium

10 Pages
259 Views
Unlock Document

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

Description
--Lab Week 2 Solution --Using the GolfClub Databasewrite theSQL queriesto satisfy thefollowing. Submit this file with your SQL anwsersvia Blackboard --Q1. List theid , name,join date, phoneand member typefor all members SELECT MemberId, MemberName, JoinDate, Phone, MemberType FROM Member --Q2. List theid , name,join date, phoneand member typefor all female members SELECT MemberId, MemberName, JoinDate, Phone, MemberType FROM Member WHERE Gender = 'f' --Q3. List theid, namegender andhandicap for all memberswhosehandicapleast 10 but is no morethan 20 SELECT MemberId, MemberName, Gender, Handicap FROM Member WHERE Handicap >= 10 AND Handicap <= 20 -- or SELECT MemberId, MemberName, Gender, Handicap FROM Member WHERE Handicap between 10 AND 20 --Q4 List thedetails for all memberswho arefemale andwho also haveabarbill under $25 SELECT * FROM Member WHERE Gender = 'f' AND BarBill < $25 --Q5. List thedetails for all memberswhosetypeis either senior or junior SELECT * FROM Member WHERE MemberType = 'Senior' OR MemberType= 'Junior' --or SELECT * FROM Member WHERE MemberType IN ('Senior', 'Junior' ) --Q6. List thedetails for all memberswhosetypeis either Junior or Senior andwho also haveahandicapover 20 SELECT * FROM Member WHERE (Membertype= 'Junior' or Membertype= 'Senior') AND Handicap > 20 --or SELECT * FROM Member WHERE MemberType IN ('Junior','Senior' ) AND Handicap > 20 --Q7. List details for all tour entriesexcept thosewhosetour year is either 2004 or 2006. SELECT * FROM TourEntry WHERE NOT (TourYear = 2004 OR TourYear = 2006) --or SELECT * FROM TourEntry WHERE TourYear NOT IN (2004,2006) --or SELECT * FROM TourEntry WHERE TourYear <> 2004 AND TourYear <> 2006 --Q8. List thedetails for all Senior typemembers. Sequencetheoutput by namewithin Gender i.e. gender is the major key SELECT * FROM Member WHERE MemberType = 'Senior' ORDER BY Gender, MemberName --Q9. List thedetails for all male memberswith abar bill over $35. Sequencetheoutput from thehighest to lowest bar bill amount SELECT * FROM Member WHERE Gender = 'm' AND BarBill > 35 ORDER BY BarBill DESC --Q10. List the details for all memberswhosetype is either Senior or Junior, andwho also haveahandicap over 10 and abar bill under $60. -- Sequencetheoutput by namewithin member type SELECT * FROM Member WHERE MemberType IN ('Junior','Senior') AND Handicap > 10 AND BarBill < 60 ORDER BY MemberType, MemberName --or SELECT * FROM Member WHERE (MemberType= 'Junior' OR MemberType= 'Senior') AND Handicap >10 AND BarBill < 60 ORDER BY MemberType, MemberName --Lab Week 3 Solution --Using the GolfClub Databasewrite theSQL queriesto satisfy thefollowing. Submit this file with your SQL anwsersvia Blackboard --1. List the details for all membersthat play on ateamwith -- the characters"red" anywherein teamname SELECT * FROM member WHERE TeamNameLIKE '%red%' --2 List thedetails for all memberswhosenameendswith --thecharacters"ton" SELECT * FROM Member WHERE MemberNameLIKE '%ton' --3. List the details for all memberswhosephonenumber's2nd digit is a"1" SELECT * FROM Member WHERE PhoneLIKE '_1%' --4. List the details for all membersthat joined after theendof February 2007 SELECT * FROM Member WHERE JoinDate> '2007/02/28' --5. List the details for all membersthat joined during July 2007 SELECT * FROM Member WHERE YEAR(JoinDate) = 2007 AND MONTH(JoinDate) = 7 --or SELECT * FROM Member WHERE JoinDate >= '2007/07/01' AND Joindate<= '2007/07/31' --6. Count thetotal number of senior female members SELECT COUNT(*) AS "FemaleSenior Members" FROM Member WHERE Gender = 'f' AND MemberType = 'Senior' --7. Count thenumber of different teamsthat Junior membersplay on SELECT COUNT(DISTINCT TeamName) "Total Different Teamswith Juniors" FROM Member WHERE MemberType = 'Junior' --8. Count thenumber of Junior membersthat play on ateam. SELECT COUNT(TeamName) "Total Junior TeamPlayers" FROM Member WHERE MemberType = 'Junior' --or SELECT COUNT(*) "Total Junior TeamPlayers" FROM Member WHERE MemberType = 'Junior' AND TeamNameIS NOT NULL --9.What is thehighest bar bill for afemale member SELECT MAX(BarBill)AS "Maximum Female BarBill" FROM Member WHERE Gender = 'F' --10. What is theaveragehandicap for aJunior member SELECT AVG(Handicap) AS "AverageJunior Handicap" FROM Member WHERE MemberType = 'Junior' --11. Count the number of membersthat haveateam SELECT COUNT(TeamName) AS "Memberson aTeam" FROM Member -- or SELECT COUNT(*) AS "Memberson aTeam" FROM Member WHERE TeamNameIS NOT NULL --12. Count thenumber of membersthat don't haveateam SELECT COUNT(*) AS "MembersNot On aTeam" FROM Member WHERE TeamNameIS NULL Lab 4 --Lab Week 4 --Using the GolfClub Databasewrite theSQL queriesto satisfy thefollowing. Submit this file with your SQL anwsersvia Blackboard --1 List thetotal barbill for each member type. Sequencetheoutput, highest to lowest average. Select SUM(Barbill) as"Total barbill for eachmember type", membertype from member Group by Membertype Order by SUM(Barbill) desc --2 List theaveragebarbill for eachteam but only show thesituationswheretheaverageis lessthan $50 Select AVG(Barbill) as"Averagebarbill for each teamthat is lessthan $50", teamNamefrom member group by teamname having AVG(barbill) < 50 --3 List thedetails of all tournamentsthat hadentriesin 2006. Show eachtournament only once Select distinct tournament.* from tournament inner join tourentry on tournament.tourid = tourentry.tourid wheretouryear = '2006' --4 List theteamnameand senior member count for eachteam. Only show for situationswheretherearelessthan 5 members select count(*) as"Senior member count for eachteam", teamnamefrom member wheremembertype = 'senior' group by teamname having count(*) < 5 --5 List thenameof any tournament that hadmorethan 2 entriesin 2006. select distinct tourNamefrom tournament inner join tourentry on tournament.tourid = tourentry.tourid wheretouryear = '2006' group by tourname having count (tourentry.tourid) > 2 --6 List thedetails for all membersthat haveever entered theWestcoast tournament. Show eachmember only once select distinct member.* from member inner join tourentry on member.memberid = tourentry.MemberID wheretourid = 36 --7. List the details for all membersand the teamsthat they play on. Sequencetheoutput by member namewithin team select * from member order by teamname, MemberName --8 List thedetails for theall tournamentsandall their entriesfor 2006. Sequenceby member id within tournament within Tour type select * from tournament inner join tourentry on tournament.tourid = tourentry.TourID wheretouryear = 2006 order by tourtype,tourname,memberid --9. List the details for all male membersaswell astheTournament namefor tournamentsthey haveentered. --Only show theinformation onceeven if they haveentered thesametournament many times. --Sequenceby tournament namewithin member id. select distinct member.*, tourNamefrom member inner join tourentry on member.memberid = t
More Less

Related notes for ITM 500

Log In


OR

Don't have an account?

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