8. 3F: List students Name and # of Courses in their major Write queries that return the…
- User
- 3F: List students Name and # of Courses in their major Write queries that return the following resultsets: Students are required to take 4 courses, and at least two of these courses must be from the department of their major. Write a query to list students' First Name, Last Name, and Number of Courses they are taking in their major department. The output should be sorted first by the Number of Courses in descending order, then by First Name in ascending order, then by the Last Name in ascending order. College Management System Schema department Colute Name faculty Colume lime firmane fetid student Colme lame $ id fernane street treet Detal dy state potal. a% Dra Type marchar001 sachasa Dea Spe cuch Alca Nabl Mica NA course Courn Neme studentcourse Column Mane 8 cavald page artille faculty Course Count Name Des Type Data Type dete Dea Type Now N.: Al se Nak: Her liab
- Assistant
Based on your data schema description, assuming the key relationships involved are standard (students linked to courses through a junction table, and courses linked to departments), and interpreting common database naming conventions, your schema details are somewhat unclear or incomplete. I'll provide an SQL query based on typical relational designs.
Assuming:
studenttable hasstudent_id,firstname, andlastname.departmenttable hasdepartment_id.coursetable hascourse_idanddepartment_id.studentcourseis a junction betweenstudentandcourse.
You'll also need a way to know a student's major. Typically, there'd be a
major_department_idin thestudenttable. Here's how you could write the query:SELECT s.firstname, s.lastname, COUNT(c.course_id) AS num_courses_in_major FROM student s JOIN studentcourse sc ON s.student_id = sc.student_id JOIN course c ON sc.course_id = c.course_id WHERE c.department_id = s.major_department_id GROUP BY s.firstname, s.lastname ORDER BY num_courses_in_major DESC, s.firstname ASC, s.lastname ASC;Explanation:
-
SELECT Clause: This part selects columns we are interested in: first name, last name of students, and counts the number of courses matching the students' major department.
-
FROM and JOIN Clauses: These specify the tables and their relationships.
- From
studenttostudentcourse, ensuring we're considering each registration. - From
studentcoursetocourse, so we can check if the course relates to the student's major.
- From
-
WHERE Clause: Filters courses to include only those that belong to the department of the student's major.
-
GROUP BY Clause: Necessary for the aggregation (
COUNT) to work correctly, grouping results by student names. -
ORDER BY Clause: As stipulated, the results are sorted by the count of courses in descending order, and then names in ascending order.
Assumptions:
- Each student’s major department is stored in
s.major_department_id. studentcourselinks students to the courses they are enrolled in.- Only enrolled courses within the major department are counted, per your scenario's rules.
Feel free to adjust the specific column names and tables based on actual schema details which weren't fully clear from the brief. The query assumes that all necessary fields like
major_department_idexist and are correctly populated in your database.