#1、查詢計算機學院學生的最高年齡、最低年齡及之間相差的年齡數。select max(t.age) as max_age, min(t.age) as min_age, max(t.age) - min(t.age) as D_valuefrom (	select student_id, v_student.department_id,	(extract(year from current_date()) - extract(year from student_birthday)) as age	from v_student, v_department	where v_student.department_id = v_department.department_id	and department_name = '計算機學院')t;#2、查詢總成績在200分以上（含200分）的學生編號和總成績。select student_id, sum(grade) as sum_gradefrom v_student_scoregroup by student_idhaving sum(grade) >= 200;#3、查詢選修兩門以上的課程的學生學號和選課門數。select student_id, count(student_id) as numfrom v_student_scoregroup by student_idhaving count(*) > 2;#4、求選課在2門以上且各門課程均及格的學生的學號及其總成績，查詢結果按總成績降序列出。select student_id, sum(grade) as sum_grade from v_student_scorewhere grade >= 60group by student_idhaving count(*) >= 2order by sum(grade) desc;#5、查詢至少有一門課程在80分以上的學生。select v_student_score.student_id, student_namefrom v_student, v_student_scorewhere grade > 80and v_student.student_id = v_student_score.student_idgroup by v_student_score.student_idhaving count(*) >= 1;#6、查詢教材名稱中不包含“Database”、“SQL”、“數據庫”等字樣的教材信息。select * from v_textbookwhere textbook_name not like '%Database%'and textbook_name not like '%SQL%'and textbook_name not like '%數據庫%';#7、為院系表的院系負責人增加外鍵約束。#首先根據院系表創建一個新表v_admincreate table v_admin(	select department_administrator, department_phone	from v_department);alter table v_adminadd PRimary key(department_administrator);#增加外鍵約束alter table v_department	add constraint fk_admin	foreign key(department_administrator)	references v_admin(department_administrator);#查看外鍵select * from information_schema.TABLE_CONSTRAINTS as twhere t.table_name = 'v_department';#8、把講授數據庫的教師的工齡增加1年。update v_teacherleft join (	select v_teacher_course_task.teacher_id	from v_teacher_course_task, v_course	where v_course.course_id = v_teacher_course_task.course_id	and v_course.course_name = '數據庫')ton v_teacher.teacher_id = t.teacher_idset teacher_workyear = date_add(teacher_workyear, interval -1 year)where v_teacher.teacher_id = t.teacher_id;select * from v_teacher;#9、將課程名稱為“數據庫”的成績增加1分。update v_student_scoreleft join(	select v_student_score.course_id	from v_student_score, v_course	where v_course.course_name = '數據庫'	and v_course.course_id = v_student_score.course_id)ton v_student_score.course_id = t.course_idset grade = grade + 1where v_student_score.course_id = t.course_id;3、運行結果：（1）查詢計算機學院學生的最高年齡、最低年齡及之間相差的年齡數。圖3.1 第1題執行結果（2）查詢總成績在200分以上（含200分）的學生編號和總成績。圖3.2 第2題執行結果（3）查詢選修兩門以上的課程的學生學號和選課門數。圖3.3 第3題執行結果（4）求選課在2門以上且各門課程均及格的學生的學號及其總成績，查詢結果按總成績降序列出。圖3.4 第4題執行結果（5）查詢至少有一門課程在80分以上的學生。圖3.5  第5題執行結果（6）查詢教材名稱中不包含“Database”、“SQL”、“數據庫”等字樣的教材信息。圖3.6 第6題執行結果（7）為院系表的院系負責人增加外鍵約束。圖3.7 第7題執行結果（8）把講授數據庫的教師的工齡增加1年。圖3.8 第8題執行結果（9）將課程名稱為“數據庫”的成績增加1分。圖3.9 第9題執行結果