1. Write a SELECT statement that returns the same result set as this SELECT statement, but don't use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword. SELECT DISTINCT LastName, FirstName FROM Instructors i JOIN Courses c ON i. InstructorID = c. InstructorID ORDER BY LastName, FirstName 2. Write a SELECT statement that answers this question: Which instructors have an annual salary that's greater than the average annual salary for all instructors? Return the LastName, FirstName, and AnnualSalary columns for each Instructor. Sort the result set by the AnnualSalary column in descending sequence. 3. Write a SELECT statement that returns the LastName and FirstName columns from the Instructors table. Return one row for each instructor that doesn't have any courses in the Courses table. To do that, use a subquery introduced with the NOT EXISTS operator. Sort the result set by LastName and then by FirstName. 4. Write a SELECT statement that returns the LastName and FirstName columns from the Students table, along with a count of the number of courses each student is taking from the StudentCourses table. Return one row for each student who is taking more than one class. To do that, use a subquery with the IN class that groups the student course by StudentID. Group and sort the result set by the LastName and then by the FirstName. 5. Write a SELECT statement that returns the LastName, FirstName, and AnnualSalary columns of each instructor that has a unique annual salary. In other words, don't include instructors that have the same annual salary as another instructor. Sort the results by LastName and then by FirstName.
1. Write a SELECT statement that returns the same result set as this SELECT statement, but don't use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword. SELECT DISTINCT LastName, FirstName FROM Instructors i JOIN Courses c ON i. InstructorID = c. InstructorID ORDER BY LastName, FirstName 2. Write a SELECT statement that answers this question: Which instructors have an annual salary that's greater than the average annual salary for all instructors? Return the LastName, FirstName, and AnnualSalary columns for each Instructor. Sort the result set by the AnnualSalary column in descending sequence. 3. Write a SELECT statement that returns the LastName and FirstName columns from the Instructors table. Return one row for each instructor that doesn't have any courses in the Courses table. To do that, use a subquery introduced with the NOT EXISTS operator. Sort the result set by LastName and then by FirstName. 4. Write a SELECT statement that returns the LastName and FirstName columns from the Students table, along with a count of the number of courses each student is taking from the StudentCourses table. Return one row for each student who is taking more than one class. To do that, use a subquery with the IN class that groups the student course by StudentID. Group and sort the result set by the LastName and then by the FirstName. 5. Write a SELECT statement that returns the LastName, FirstName, and AnnualSalary columns of each instructor that has a unique annual salary. In other words, don't include instructors that have the same annual salary as another instructor. Sort the results by LastName and then by FirstName.
Chapter11: Group Functions
Section: Chapter Questions
Problem 18MC
Related questions
Question
100%
I need help on writing statements for these, I'm having trouble with the subqueries.
Transcribed Image Text:1. Write a SELECT statement that returns the same result set as this SELECT statement, but don't use a
join. Instead, use a subquery in a WHERE clause that uses the IN keyword.
SELECT DISTINCT LastName, FirstName
FROM Instructors i JOIN Courses c
ON i. InstructorID = c. InstructorID
ORDER BY LastName, FirstName
2. Write a SELECT statement that answers this question: Which instructors have an annual salary that's
greater than the average annual salary for all instructors?
Return the LastName, FirstName, and AnnualSalary columns for each Instructor.
Sort the result set by the AnnualSalary column in descending sequence.
3. Write a SELECT statement that returns the LastName and FirstName columns from the Instructors
table.
Return one row for each instructor that doesn't have any courses in the Courses table. To do that, use
a subquery introduced with the NOT EXISTS operator.
Sort the result set by LastName and then by FirstName.
4. Write a SELECT statement that returns the LastName and FirstName columns from the Students
table, along with a count of the number of courses each student is taking from the StudentCourses
table.
Return one row for each student who is taking more than one class. To do that, use a subquery with
the IN class that groups the student course by StudentID.
Group and sort the result set by the LastName and then by the FirstName.
5. Write a SELECT statement that returns the LastName, FirstName, and AnnualSalary columns of each
instructor that has a unique annual salary. In other words, don't include instructors that have the same
annual salary as another instructor.
Sort the results by LastName and then by FirstName.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 2 steps
Recommended textbooks for you