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.
Question
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.
Solution
Here is the SQL statement that fulfills your request:
SELECT LastName, FirstName
FROM Instructors
WHERE NOT EXISTS (
SELECT 1
FROM Courses
WHERE Instructors.InstructorID = Courses.InstructorID
)
ORDER BY LastName, FirstName;
Let's break it down:
-
SELECT LastName, FirstName FROM Instructors: This part of the query selects the LastName and FirstName columns from the Instructors table. -
WHERE NOT EXISTS: This part of the query filters out instructors that do not have any courses in the Courses table. -
(SELECT 1 FROM Courses WHERE Instructors.InstructorID = Courses.InstructorID): This is the subquery that checks if an instructor has any courses. If the InstructorID of an instructor is found in the Courses table, then this instructor has at least one course. -
ORDER BY LastName, FirstName: This part of the query sorts the result set first by LastName and then by FirstName.
Similar Questions
SELECT DISTINCT LastName, FirstNameFROM Instructors i JOIN Courses c ON i.InstructorID = c.InstructorIDORDER BY LastName, FirstName ; Write a SELECT statement (Commented as 5A-2 ) 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
Select all data from the Courses Table and do a Full outer join in the StudentCourses by courseID. Comment which course has no students. This is indicated by a NULL studentID and a NULL CourseID.
Generate a list of the Instructor names by combining "Professor " and the first name, 1 space, and last name together. This column should be labeled as "Title". Do this only for rows that the first name is not a null value
Write a query to display the coursename of the courses having the coursefees above average fees of all the courses. Display the records sorted in ascending order based on coursename.Note:1. Display only the coursename of the courses having the coursefees above average fees of all the courses.2. Order by is mandatory.Sample Output:
Consider these following tables and only solve the query. Tables: 1) student: s_no (primary key), student_name, course_no (foreign key), age 2) courser-course-no (primary key), course-name.
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.