I have learnt SQL almost exclusively for a task given to me by a potential employer as a final task. I wanted some feedback more on the syntax/structure of my queries. They all allow me to access the data I have been asked for in the questions so must do the job, but I have probably missed out on best-practices and taken the long way round most of the quetions.
Any feedback at all will be kindly received.
Thanks,
Sam
Find Agent-Client pairs that have previously worked with each other.
I have assumed that you have used client and entertainer interchangeably. There are 79 Agent-Entertainer pairs that have previously worked with each other
• SELECT DISTINCT Engagements.EntertainerID, entertainers.entstagename, agents.agentID, CONCAT(agents.agtfirstname,' ',agents.agtlastname) AS AgentName • FROM Entertainers • INNER JOIN Engagements • ON Entertainers.EntertainerID = Engagements.EntertainerID • INNER JOIN Agents • ON engagements.agentID = Agents.agentID
Find agents who have more than three clients.
All of the Agents have more than three clients.
• SELECT agents.agentID • FROM Entertainers • INNER JOIN Engagements • ON Entertainers.EntertainerID = Engagements.EntertainerID • inner join Agents • ON engagements.agentID = Agents.agentID • GROUP BY agents.agentID • HAVING COUNT(Agents.agentID)>3
Find all customers who are interested in rock music (any direction) and output their data for sending advertising.
• SELECT * FROM Customers • INNER JOIN Musical_preferences • ON customers.customerID = Musical_Preferences.customerID • INNER JOIN Musical_Styles • ON Musical_preferences.styleID = Musical_styles.styleID • WHERE Musical_styles.stylename • LIKE '%Rock%'
List all classical music events held in Seattle.
Engagement numbers 29, 30, 36, 55, 61, 72, 103, 112 & 128.
• SELECT EngagementNumber from Engagements • INNER JOIN Entertainers • ON engagements.entertainerID = entertainers.entertainerID • INNER JOIN Entertainer_Styles • ON entertainers.entertainerID = entertainer_styles.entertainerID • INNER JOIN Musical_styles • ON entertainer_styles.styleID = Musical_styles.styleID • WHERE entertainers.entcity = 'Seattle' AND Musical_styles.stylename = 'classical'
List all activities in which more women than men participated.
I can’t quite manage to get the correct query for this question. I’ve successfully joined the tables with the below query but I cannot work out the syntax to count the amount of each gender for each engagement and then compare them against each other to return the engagements in which more females than males attend.
• SELECT * FROM Engagements • INNER JOIN Entertainers • ON entertainers.entertainerID = engagements.entertainerID • INNER JOIN entertainer_members • ON entertainer_members.entertainerID = entertainers.EntertainerID • INNER JOIN Members • ON members.memberID = entertainer_members.memberID
Which client pays their agent the most? Agent salary and commissionRate are null so I have assumed that the Entertainer with the highest total sum of contractPrice’s will pay their agent the most
• Entertainer ID 1008 with entStageName Country Feeling • SELECT (Engagements.contractprice), Engagements.EntertainerID, entertainers.entstagename • FROM Entertainers • INNER JOIN Engagements • ON Entertainers.EntertainerID = Engagements.EntertainerID • inner join Agents • ON engagements.agentID = Agents.agentID
What contracts expire on July 15, 1999?
• EngagementNumber 3.
• SELECT * FROM Engagements WHERE EndDate='1999-07-15';
Find your customers who live in Bellevue and love classical music.
• 10011 Joyce Bonnicksen
• SELECT * FROM Customers • INNER JOIN Musical_preferences • ON customers.customerID = Musical_Preferences.customerID • INNER JOIN Musical_Styles • ON Musical_preferences.styleID = Musical_styles.styleID • WHERE Musical_styles.stylename = 'Classical'AND customers.custcity = 'Bellevue'
Which state had the most concerts in the third quarter of 1999?
• WA
Which of the agents received the largest profits in 1999?
• Agent 1 Will Thompson with 23530
• SELECT sum(Engagements.contractprice) as Will • FROM Entertainers • INNER JOIN Engagements • ON Entertainers.EntertainerID = Engagements.EntertainerID • INNER join agents • on engagements.agentID = agents.agentID • where agtfirstname = 'Will';
SchoolSchedulingData
Which faculty has the most teachers?
• I have assumed that faculty refers to the categories table because the departmentName field is Null.
• The math faculty has the most teachers with 12.
• SELECT count(categories.categoryid) AS StaffCount, categories.categorydescription FROM faculty • INNER JOIN faculty_categories • ON faculty_categories.staffId = faculty.staffID • INNER JOIN staff • ON staff.staffID = faculty_categories.staffID • inner join categories • ON categories.categoryid = faculty_categories.categoryid • GROUP BY categories.categorydescription
What are the 3 most popular subjects among students?
• Business Tax Account 11 students, Art History 10 students and Composition – Intermediate 7 students
• SELECT TOP 3 SubjectName, count(student_schedules.studentID) from Subjects • INNER JOIN classes • ON Classes.SubjectID = subjects.subjectID • INNER JOIN student_schedules • ON student_schedules.classID = classes.classID • GROUP BY subjectname • ORDER BY count(student_schedules.studentID) DESC
Which city has the most students?
• Seattle with 3
• SELECT COUNT(studcity), studcity from Students • Group by studcity
Students from which city study painting the most?
•
What class starts at 8 a.m. on Monday in room 1627?
• ClassID 1004 Drawing
• SELECT classID, subjects.subjectname from Classes • INNER JOIN subjects • ON subjects.subjectID = classes.subjectID • WHERE classes.starttime = '08:00:00' AND ClassroomID = '1627' AND Monday_schedule = '1'
Which classes are free on Wednesday at 11.00 and have a phone?
I have assumed that free means that Wednesday_schedule is 0.
• ClassID 1642
• SELECT ClassID FROM class_rooms • inner join classes • ON class_rooms.classroomID = classes.classroomID • WHERE phoneavailable = '1' AND Wednesday_schedule = '0' AND starttime = '11:00:00'
In which building are the most activities?
• Instructional Building with 31
• SELECT buildingname, COUNT(classID) AS 'Number of classes' from buildings • INNER JOIN class_rooms • ON class_rooms.buildingcode = buildings.buildingcode • INNER JOIN Classes • ON classes.classroomID = Class_rooms.classroomID • GROUP BY Buildingname • ORDER BY COUNT(classID) DESC
What is the average salary of the staff?
• 51066.00
• SELECT ROUND(AVG(salary), 0) AS AvgSalary FROM faculty • INNER JOIN faculty_categories • ON faculty_categories.staffId = faculty.staffID • INNER JOIN staff • ON staff.staffID = faculty_categories.staffID
Which of the teachers has been working at the school the longest?
• John Levering who has hired 1982-11-20
• select staffid, stffirstname, stflastname, datahired from staff • ORDER BY datahired
Display the name and surname of the student with the highest rating.
I have assumed that highest rating refers to the single highest grade, rather than the average for each student, however this can be easily changed by using AVG instead of MAX.
• SELECT TOP 1 studfirstname, studlastname, MAX(student_schedules.grade) AS Grade from students • INNER JOIN student_schedules • ON students.studentid = student_schedules.studentID • GROUP BY studfirstname, studlastname • ORDER BY Grade DESC
