1. (10 pts each) Formulate a SQL query for each of the following questions. The

Question

1. (10 pts each) Formulate a SQL query for each of the following questions. The prefix […], if any, in front of a question specifies limitations which the SQL query must be constrained by. For each of the questions, your answer must include the SQL query (input, text) and the corresponding screenshot of the result set (query output).

a) For each department in department table, find out the department’s total number of students in ascending order, including department without any student.

The enclosed.sql file defines the following university database where primary key for each table is underlined department.dept-name is primary key (i.., unique) course.title is not unique instructor.name is not globally unique but is unique within the department student.name is not globally unique but is unique within the department student takes ID ID name dept name tot cred section course department advisor title dept name credits s id i id semester building budget time slot ilding room 10 time slot id end time instructor classroom ID ame dept name teaches ID sec id ear capacity salary

Explanation / Answer

1.a)

For this LEFT JOIN is used between the table department and student. This will list all the departments from the department table. Aggregate function COUNT() is used with group by to count the number of students in each department. Order by clause will list the result in ascending order.

Query-

SELECT d.dept_name, COUNT(s.ID) AS "Total Student"
FROM department AS d
LEFT JOIN student a
ON d.dept_name = s.dept_name
GROUP BY d.dept_name
ORDER BY COUNT(s.ID) ASC;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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