/*(1)查询全部学生的学号、姓名、性别和年龄*/
select student_id, student_name, student_sex,
2016 - year(student_birthday) as student_age
from v_student;
/*(2)查询计算机学院的全部学生的学号,姓名,性别和出生年月日*/
/*sql server代码*/
select student_id, student_name, student_sex,
convert(varchar(10), student_birthday, 111) as student_birth
from v_student;
/*mysql代码*/
select student_id, student_name, student_sex, student_birthday
from v_student;
/*(3)查询教材中含有“教材”的教材信息*/
select *
from v_textbook
where textbook_name like '%教材%';
/*(4)查询在1997年以后出生的计算机学院的学生的学号,姓名,性别和出生年份*/
/*sql server代码*/
select student_id, student_name, student_sex,
datepart(yyyy, student_birthday) as s_birth_year
from v_student
where datepart(yyyy, student_birthday) >= 1997;
/*mysql代码*/
select student_id, student_name, student_sex,
extract(year from student_birthday) as s_birth_year
from v_student
where extract(year from student_birthday) >= 1997;
/*(5)查询年龄不在19到20之间的学生的学号,院系编号和姓名*/
/*sql server代码*/
select student_id, department_id, student_name
from v_student
where 2016 - datepart(yyyy, student_birthday)
not between 19 and 20;
/*mysql代码*/
select student_id, department_id, student_name
from v_student
where 2016 - extract(year from student_birthday)
not between 19 and 20;
/*(6)查询全部学生的学号,姓名,性别,所在院系名称和选课信息*/
select v_student.student_id, student_name, student_sex, department_name, course_name,
course_credit, course_hour
from v_student, v_student_score, v_department, v_course
where v_student.student_id = v_student_score.student_id
and v_student_score.course_id = v_course.course_id
and v_student.department_id = v_department.department_id;
/*(7)查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称*/
select v_student.student_id, student_name, course_name
from v_student, v_course, v_student_score
where grade < 60
and v_student_score.student_id = v_student.student_id
and v_student_score.course_id = v_course.course_id;
/*(8)通过查询为教师生成课程信息,包括:教师院系、教师姓名,任课名称,上课地点以及使用的教材名称*/
select distinct teac.teacher_name, depa.department_name,
cour.course_name, clas.classroom_location, text.textbook_name
from v_teacher
as teac
left join(
select department_name, department_id
from v_department
)
as depa
on(teac.department_id = depa.department_id)
left join(
select course_name, teacher_name
from v_course, v_teacher_course_task, v_teacher, v_department
where v_teacher_course_task.teacher_id = v_teacher.teacher_id
and v_teacher_course_task.course_id = v_course.course_id
)
as cour
on(teac.teacher_name = cour.teacher_name)
left join(
select classroom_location, department_name, teacher_name, course_name
from v_teacher, v_course_develop, v_classroom, v_department, v_course
where v_classroom.classroom_id = v_course_develop.classroom_id
and v_course_develop.teacher_id = v_teacher.teacher_id
and v_teacher.department_id = v_department.department_id
and v_course_develop.course_id = v_course.course_id
)
as clas
on(clas.department_name = depa.department_name
and clas.teacher_name = cour.teacher_name
and clas.course_name = cour.course_name)
left join(
select textbook_name, course_name, teacher_name
from v_textbook, v_textbook_selection, v_teacher_course_task, v_teacher, v_course
where v_textbook.textbook_id = v_textbook_selection.textbook_id
and v_textbook_selection.course_id = v_teacher_course_task.course_id
and v_teacher_course_task.teacher_id = v_teacher.teacher_id
and v_course.course_id = v_teacher_course_task.course_id
)
as text
on(text.course_name = cour.course_name
and text.teacher_name = clas.teacher_name)
;
/*(9)查询没有选修课程名中包含“数据”课程的学生,列出学生的学号和姓名*/
select distinct v_student.student_id, student_name
from v_student
where student_name not in(
select student_name
from v_student, v_course, v_student_score
where v_student.student_id = v_student_score.student_id
and v_course.course_id = v_student_score.course_id
and course_name not in(
select course_name
from v_course
where course_name not like '%数据%'
)
);
/*(10)检索年龄为20岁,同时至少选修了一门学分3分以上的课程的学生的姓名和院系名称*/
select student_name, department_name
from v_student, v_department, v_course, v_student_score
where course_credit >= 3
and (2016 - extract(year from student_birthday)) = 20
and v_student_score.student_id = v_student.student_id
and v_course.course_id = v_student_score.course_id
and v_student.department_id = v_department.department_id;
/*(11)通过信息数据库得到元数据,key_column_usage表描述了具有约束的键列*/
select *
from information_schema.key_column_usage
where referenced_table_name='v_student';
/*(12)将张三的院系编号改为122*/
update v_student
set department_id = '122'
where student_id = '3114005847';
3、运行结果: