数据库的基础题和答案

1、 创建数据库和数据表，并完成以下相关题目。

几个基础实体表可以存储学生、班级、课程、教师、系别相关信息

写出相关数据表（包括未列出的关系表、其它表）创建语句（表详细属性可参照以下问题进行补全）

假定以下所有问题相关表数据都存在

2、当查找学生信息的同时，可以列出班主任信息

3、 查找学生姓名为xxx的人 

4、查找学生分数大于80的人

5、查找老师年龄小于30岁的人

6、查找老师的姓名，年龄，e-mail其中年龄不为xx岁的老师

7、查找学生分数80分或者90分

8、 查找学生分数为88分并且性别为男的学生 

9、 查找出学生分数在90—100之间的所有学生 

10、        查找出姓李的老师

11、        查找出老师使用的邮箱为lang的 

12、        查找出老师的号码为159开头的 

13、        查询老师年龄从高到低 

14、        查询出每个学生的班主任老师，只列出学生的姓名，年龄，老师的姓名，电话

15、        查询出学生姓名，性别，e_mail 、老师姓名，年龄，e_mail 

16、        将学生的年龄进行分组显示 

17、        查找出学生中年龄是19岁的人，并且19岁人中找出一个叫汪雪的人

18、        查询出学生年龄

19、        列出老师中年龄最大的3个人

20、        查询所有数学系学生的信息

21、        查询李老师所教的课程号、课程名

22、        查询年龄大于20岁的女同学的学号和姓名

23、             查询学号为‘H0301’学生所选修的全部课程成绩

24、             查询平均成绩都在80分以上的学生学号及平均成绩

25、             查询至少有6人选修的课程号

26、             查询C02号课程得最高分的学生的学号

27、             查询学号为’J0101’的学生选修的课程号和课程名

28、             ‘李小波’所选修的全部课程名称

29、             所有成绩都在70分以上的学生姓名及所在系

30、             英语成绩比数学成绩好的学生

31、             至少选修了两门课及以上的学生姓名和性别

32、             选修了李老师所讲课程的学生人数

33、             “数据结构”课程得最高分的学生的姓名、性别和所在系

34、             显示所有课程的选修情况

35、             取出没有选修“数据结构”课程的学生姓名和年龄

36、             没有选修李老师所讲课程的学生

37、             没有选修李老师和王老师所讲课程的学生

38、             检索至少选修课程“数据结构”和“C语言”的学生学号

39、             检索学习课程号为C02的学生学号、姓名和所在系

40、             检索选修课程号C01或C02的学生学号、姓名和所在系

 

 

2、
select student.*,teacher.*
from student,teacher,student_teacher
where
student.student_id = student_teacher.student_id
and
student_teacher.teacher_id = teacher.teacher_id
and
teacher.teacher_zw = '班主任';
3、
select * from student
where
student_name = 'xxx';
4、
select student.*
from student,
(
select student_id,avg(cj) as pjcj
from student_course
group by student_id
having avg(cj)>=80
) as test
where
student.student_id = test.student_id;
5、
select * from teacher
where
teacher_age <= 30;
6、
select teacher_name,teacher_age,teacher_email
from teacher
where
teacher_age <> xx;
7、
select student.*
from student,
(
select student_id,avg(cj) as pjcj
from student_course
group by student_id
) as test
where
student.student_id = test.student_id
and
(
test.pjcj = 80
or
test.pjcj = 90
);
8、
select student.*
from student,
(
select student_id,avg(cj) as pjcj
from student_course
group by student_id
) as test
where
student.student_id = test.student_id
and
test.pjcj = 80
and
student.student_sex = '男';
9、
select student.*
from student,
(
select student_id,avg(cj) as pjcj
from student_course
group by student_id
) as test
where
student.student_id = test.student_id
and
test.pjcj  between 90 and 100;
10、
select *
from teacher
where
teacher_name like '李%';
11、
select *
from teacher
where
teacher_email like '%lang%';
12、
select *
from teacher
where
teacher_mobile like '159%';
13、
select *
from teacher
order by teacher_age desc;
14、
详见第2题分析过程
15、
select student.student_name,
student.student_age,
student.student_sex,
teacher.teacher_name,
teacher.teacher_age,
teacher.teacher_email
from student,teacher,student_teacher
where
student.student_id = student_teacher.student_id
and
student_teacher.teacher_id = teacher.teacher_id;
16、
select student_age,student_name
from student
order by student_age;
17、
select *
from student
where
student_age = 19
and
student_name = '汪雪';
18、
select student_age
from student;
19、
select *
from teacher
order by teacher_age desc
limit 3;
20、
select student.*
from student,xibie
where
student.xibie_id = xibie.xibie_id
and
xibie.xibie_name = '数学系';
21、
select teacher.*,course.*
from teacher,course
where
teacher.course_id = course.course_id
and
teacher.teacher_name = '李老师';
22、
select student_code,student_name
from student
where
student_age > 20
and
student_sex = '女';
23、
select course.course_name,
student_course.cj
from student,student_course,course
where
student.student_id = student_course.student_id
and
student_course.course_id = course.course_id
and
student.student_code = 'H0301';
24、
select student.student_code,
test.pjcj
from student,
(
select student_id,avg(cj) as pjcj
from student_course
group by student_id
having avg(cj)>=80
) as test
where
student.student_id = test.student_id;
25、
select course.course_code,
test.num
from course,
(
select course_id,count(student_id) as num
from student_course
group by course_id
having count(student_id)>=6
) as test
where
course.course_id = test.course_id;
26、
select student.student_id,
student.student_code
from student,student_course,course
where
student.student_id = student_course.student_id
and
student_course.course_id = course.course_id
and
course.course_code = 'b02'
and
student_course.cj
in
(
select max(student_course.cj)
from student_course,course
where
student_course.course_id = course.course_id
and
course.course_code = 'b02'
);
27、
select course.course_code,
course.course_name
from student,student_course,course
where
student.student_id = student_course.student_id
and
student_course.course_id = course.course_id
and
student.student_code = '10001';
28、
select course.course_name
from student,student_course,course
where
student.student_id = student_course.student_id
and
student_course.course_id = course.course_id
and
student.student_name = 'aaa';
29、
select distinct
student.student_name,
xibie.xibie_name
from student,student_course,xibie
where
student.xibie_id = xibie.xibie_id
and
student.student_id = student_course.student_id
and
student.student_id  not in
(
select student_id
from  student_course
where
cj < 70
);
30、