Q1. Show the number of instructors who have a zip of 10025 and a the street numb

Question

Q1. Show the number of instructors who have a zip of 10025 and a the street number of 518.

Q2. Display all students who live in the state of MA. List first name, last name, zip, and city. Sort by city (descending), zip (ascending), and last name (ascending).

Q3. List all zipcodes where at least three students AND at least four instructors reside. Show zip, state and city.

Q4. List all cities that have 10 or more students and instructors combined. Show city, state, number of student residents, number of instructor residents, and total student/instructor residents in that city. Sort by total in descending order.

Q5. List the instructor id and name of the instructors that teach fewer than 10 sections.

Q6. List all students (display student_id, first name, last name, and registration date) who registered on or after 2/23/2007 but have not enrolled in any course.

Q7. Find how many students are enrolled in sections taught by Todd Smythe.

Q8. List the students who have received any numeric grade score of at least 95 in an Advanced Java Programming course. Show student name, the grade type code, and the numeric grade.

Q9. List the instructors who teach Advanced Java Programming, without duplication. Show instructor name and course name.

Q10. Find the minimum, maximum, and average grade of final exams in sections taught by Todd Smythe.

Explanation / Answer

Solution:

The first four subparts have been answered as per Chegg guidelines, please repost others.

1)

SELECT COUNT(instructorID) FROM Instructor WHERE zip= '10025' AND streetNumber= '518'

2)

SELECT fName, lName, zip, city FROM Student WHERE state= 'MA' ORDER BY city DESC, zip ASC, lName ASC

3)

SELECT zipcode FROM Student INNER JOIN Instructor ON Student.key= Instructor.key WHERE COUNT(studentID)> = 3 AND COUNT(instructorID) >= 4

4)

SELECT city, state, COUNT(studentID), COUNT(instructorID) FROM Student INNER JOIN Instructor ON Student.key= Instructor.key WHERE SUM(COUNT(studentID), COUNT(instructorID)) AS total>= 10 ORDER BY total DESC

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.