Write in SQL Question1: List CourseNo , and the number of times that the course

Question

Write in SQL

Question1: List CourseNo, and the number of times that the course has been offered [Hint: Select CourseNo, followed by an aggregate function COUNT(*) from the table Offering. The query should involve a GROUP BY phrase].

Question 2: Select Firstname, Lastname, Department, Rank, and Salary for all faculty whose last name begins with ‘JO’ [Hint: use LIKE in your where clause with a syntax LIKE ‘JO*’].   

Question 3. Select CourseNo, Title, and the number of times that a course has been offered

[Hint: In this query, you need to retrieve data from two tables – Course and Offering. Since it involves a COUNT(*) function in the SELECT phrase, the SQL should also have a GROUP BY phrase after the WHERE phrase. Remember that CourseNo appears in both Course and Offering tables, you need to have join condition and an alias may be needed to make your statement easy to write].

Explanation / Answer

Question1: List CourseNo, and the number of times that the course has been offered [Hint: Select CourseNo, followed by an aggregate function COUNT(*) from the table Offering. The query should involve a GROUP BY phrase].

SELECT CourseNo "Couse Number",

COUNT(*) "No of Times offered"

FROM students

GROUP BY phrase;

Question 2: Select Firstname, Lastname, Department, Rank, and Salary for all faculty whose last name begins with ‘JO’ [Hint: use LIKE in your where clause with a syntax LIKE ‘JO*’].

SELECT FirstName, LastName,Department,Rank,Salary
FROM FacultyTable
WHERE LastName LIKE 'JO%'

Question 3. Select CourseNo, Title, and the number of times that a course has been offered

[Hint: In this query, you need to retrieve data from two tables – Course and Offering. Since it involves a COUNT(*) function in the SELECT phrase, the SQL should also have a GROUP BY phrase after the WHERE phrase. Remember that CourseNo appears in both Course and Offering tables, you need to have join condition and an alias may be needed to make your statement easy to write].

SELECT CourseNo AS CourseNumber, Title AS TitleOfTheCourse, COUNT(*) AS NoOfTimesOffered

FROM Course

     LEFT JOIN Offering

     ON Course.CourseNo = Offering.CourseNo

WHERE Orders.CustomerID is null;

GROUP BY phrase;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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