Here is the table format WinRDBI Sun Devil Health Club Relational Database Schem
ID: 3632715 • Letter: H
Question
Here is the table format
WinRDBI Sun Devil Health Club Relational Database Schema
clubAccount ( acctNum, dateJoined, address, phone )
member ( mID, fName, lName, age, primary, monthlyFee, acctNum )
trainer ( mID, insID, startDate, duration, fee )
instructor ( insID, ifName, ilName )
class ( cID, cName, cDescription )
classSchedule ( cID, day, time, insID )
attendance ( mID, cID, day, time, classDate )
runningLog( mId, runNum, runDate, runDistance, runTime )
If needed link to actual .rdb file : http://www.mediafire.com/?rmjg8kzfeo1q77z
Write SQL language to do the following: I use Win-RDBI to be specific but anything helps
Which instructors have worked as a trainer for more than 2 (>2) different club accounts? Order the results in ascending order by instructor last name and first name.
output table format: (ilName, ifName)
For each scheduled class (i.e., a class offered at a give day and time), calculate the average class attendance. Print the class name, day, time, and average attendance. Order in descending order by average class attendance.
output table format: (cName, day, time, avgClassAttendance)
Which club account has family members that have logged the most number of running miles? Print the club account number, number of members on the account, and the total running miles. Order in descending order by total running miles.
output table format: (acctNum, numOfAcctMembers, totalAcctRunningMiles)
Which instructor has taught the class with the largest attendance? Print the instructor’s first name, last name, the class name, the largest attendance value, and the class date and time of the class with the largest attendance.
output table format: (ifName, ilName, cName, largestAttendanceValue, classDate, time)
Explanation / Answer
Dear, Here the question is multiple post, please post remaining questions in another post. 1) Which instructors have worked as a trainer for more than 2 (>2) different club accounts? Order the results in ascending order by instructor last name and first name. output table format: (ilName, ifName) Select ilName, ifName From instructor, trainer, clubAccount, member Where instructor.insID = trainer.insID And trainer.mid = member.mid And clubAccount.acctNum = member.acctNum Having count(clubAccount.acctNum)>2 Order by ASC ilName, ifName 2) For each scheduled class (i.e., a class offered at a give day and time), calculate the average class attendance. Print the class name, day, time, and average attendance. Order in descending order by average class attendance. output table format: (cName, day, time, avgClassAttendance) Select cName, day, time, avg(count(mID)) as avgClassAttendance From class, classSchedule, attendance, member Where class.cID = classSchedule.cID And classSchedule.cID = attendance.cID And attendance.mID = member.mID Having avg(count(mID)) Order by DESC count(mID)Hope this could helps you!!!