MYSQL经典题型和一部分mongodb题

student学生表数据：

teacher老师表数据：

course课程表数据：

score成绩表数据：

1.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
select st.StuId,st.StuName as st_name,avg(sc.StuScore) from score as sc,student as st where st.StuId = sc.StuId group by st.StuId having avg(sc.StuScore) > 60;
2.查询所有学生的学号、姓名、选课数、总成绩
select st.StuId,st.StuName,count(sc.StuId),sum(sc.Stuscore) from student as st,score as sc where st.StuId = sc.StuId group by sc.StuId;
3.查询姓“张”的老师的个数
select count(TeaId) from Teacher where TeaName like ‘张%’;
4.查询学过“张三”老师所教的所有课的同学的学号、姓名
select Student.StuId,Student.StuName from student where StuId in (select score.StuId from score,course,teacher where score.CourseId = course.CourseId and course.TeaId = teacher.TeaId and Teacher.TeaName = ‘张三’);
5.查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名
select StuId,StuName from Student where StuId in (select StuId from score where CourseId=’0001’) and StuId in (select StuId from score where CourseId=’0002’)
6.查询所有课程成绩小于等于60分的学生的学号、姓名
select Student.StuId,Student.StuName from student where Student.StuId not in (select Student.StuId from student,score where Student.StuId = Score.StuId and StuScore > 60);
7.查询没有学全所有课的学生的学号、姓名
select Student.StuId,Student.StuName from student,score where Student.StuId = Score.StuId group by Student.StuId,Student.StuName having count(courseId) < (select count(courseId) from course);
8.查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名
select Student.StuId,Student.StuName from student,score where Student.StuId = Score.StuId and CourseId in (select courseId from score where StuId = ‘0001’);
9.查询每门课程被选修的学生数
select Course.courseName,count(Score.courseId) from Score,Course where Score.courseId = Course.courseId group by Score.courseId;
10.查询出只选修了一门课程的全部学生的学号和姓名
select Student.stuId,Student.stuName from Student,Score where Score.stuId = Student.stuId group by Score.stuId having count(Score.courseId) = 1;
11.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select Student.stuId,Student.stuName,avg(Score.stuScore) from Student,Score where Student.stuId = Score.stuId group by Student.stuId having avg(Score.stuScore) > 85;
12.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select courseId,avg(stuScore) from score group by courseId order by avg(stuScore),courseId desc;
13.查询课程名称为“语文”且分数低于60的学生姓名和分数
select Student.stuName,Score.stuScore from Student,Course,Score where Course.courseName = ‘语文’ and Course.courseId = Score.courseId and Score.stuId = Student.stuId and Score.stuScore < 60;
14.查询任何一门课程成绩在70分以上的学生学号、姓名、课程号和分数
select distinct Student.stuId,Student.stuName,Score.courseId,Score.stuScore from Student,Score where Score.stuScore>=70 and Score.stuId = Student.stuId;
15.统计每门课程的学生选修人数，要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号降序排序
select courseId,count() from score group by courseId order by count() desc,courseId desc;
16.查询没学过“张三”老师讲授的任一门课程的学生姓名
select stuName from Student where stuId not in(select stuId from Course,Teacher,Score where Teacher.teaName = ‘张三’ and Teacher.teaId = Course.teaId and Course.courseId = Score.courseId);
MongoDB应用
1 创建一年级的第三个班grade_1_3，随机添加 3 名学生，字段有，name,age，sex,hobby
db.grade_1_3.insert(
[{name:“张三”,age:18,sex:“男”,hobby:[“喝酒”,“音乐”,“电影”]},
{name:“李四”,age:12,sex:“男”,hobby:[“喝酒”,“音乐”,“电影”]},
{name:“王兰”,age:13,sex:“女”,hobby:[“化妆”,“画画”,“观察”]},
{name:“王五”,age:14,sex:“男”,hobby:[“烫头”,“打架”,“好人”]},
{name:“赵花”,age:15,sex:“女”,hobby:[“绣花”,“学习”,“钢琴”]},
{name:“赵六”,age:16,sex:“男”,hobby:[“抽烟”,“拉架”,“劝架”]},
{name:“钱八”,age:14,sex:“男”,hobby:[“电脑”,“游戏”,“吃鸡”]},
{name:“钱多”,age:15,sex:“男”,hobby:[“挣钱”,“研究”,“象棋”]},
{name:“周静”,age:17,sex:“女”,hobby:[“学习”,“跳舞”,“唱歌”]},
{name:“吴小”,age:15,sex:“男”,hobby:[“旅游”,“追星”,“娱乐”]}])
2查看一年级二班grade_1_3中的所有学生
db.grade_1_3.find().pretty()
3查看一年级二班grade_1_3中所有年龄14 岁的学生
db.grade_1_3.find({age:14})
4.查看一年级二班grade_1_3中所有年龄大于7岁并且小于 14 岁的学生
db.grade_1_3.find({age:{$gt:7,$lt:14}})
5.查看一年级二班grade_1_3所有年龄是 14 岁或 6 岁的学生
db.grade_1_3.find({KaTeX parse error: Expected 'EOF', got '}' at position 23: …e:14},{age:16}]}̲) 6.查看一年级二班grad…set:{age:8,hobby:[“跳舞”,“画画”]}})