As above, but show the total number of credits for such courses (taught by that instructor). You should use SQL aggregation on courses taught by that instructor.
As above, but display the total credits for each of the instructors, along with the ID of the instructor; don’t bother about the name of the instructors. (Don’t bother about instructors who have not taught any course, they can be omitted)
mysql>select id,sum(credits)->from teaches, course
->where course.course_id=teaches.course_id
->groupby id;+-------+--------------+| id |sum(credits)|+-------+--------------+|10101|10||12121|3||15151|3||22222|4||32343|3||45565|7||76766|8||83821|11||98345|3|+-------+--------------+9rowsinset
Find average instructors’ salaries for each of courses, along with the course_id and title of the course, taught by instructors of 内功学院, the result should be sorted from the lowest to the highest according to the average salaries.
mysql>select course.course_id, title,avg(salary)->from course, instructor, teaches
->where instructor.id=teaches.id and teaches.course_id=course.course_id and instructor.dept_name='内功学院'->groupby course.course_id, title
->orderbyavg(salary);+-----------+----------+-------------+| course_id | title |avg(salary)|+-----------+----------+-------------+| cn5 | 太极 |65000|| cn3 | 九阳神功 |65000|| cn1 | 内功基础 |70000|| cn4 | 易筋经 |83500|| cn2 | 九阴真经 |92000|+-----------+----------+-------------+5rowsinset
Find the names of all courses which have been taught in 南疆雨林 ever. (there should be no duplicate names)
mysql>selectdistinct title
->from course, section
->where course.course_id=section.course_id and building='南疆雨林';+----------+| title |+----------+| 枪法 || 内功基础 || 坑蒙拐骗 |+----------+3rowsinset
Display the IDs and names of all students who have never registered for a course.
mysql>select id, name
->from student
->where tot_cred=0;+-------+------+| id | name |+-------+------+|70557| 杨康 |+-------+------+1rowinset
Find the id and names of the courses which have been registered by some students without evaluated grade.
Find the courses which are the Subsequence courses of other courses. The result should involve the ids and titles of the Subsequence courses and the ids and titles of its prerequisites. (note: the names of columns in result should show the roles of the courses clearly)