缺陷报告与数据库题(3)

数据库的题:
#创建数据库
create database jiaqiwork CHARACTER set utf8
#选择数据库
use jiaqiwork
#创建表1
create table major(mid int PRIMARY key auto_increment,mname VARCHAR(255))
#创建表2
create table student(sid int PRIMARY key auto_increment,sname VARCHAR(255),
score int,
joindate VARCHAR(255),
smid int)
#表1插入数据
insert into major VALUES(10,“影视传媒”),(20,“物联网”),(30,“大数据”),(40,“国际贸易”),(50,“PHP”)
#表2插入数据
insert into student VALUES(1001,“张大民”,95.5,“2018-03-12”,30),
(1002,“刘德华”,93,“2017-09-18”,10),(1003,“刘能”,87,“2018-09-12”,50),
(1004,“范冰冰”,89,“2016-11-20”,10),(1005,“周润发”,75,“2017-06-30”,10),
(1006,“郭靖”,99.9,“2018-03-22”,20),(1007,“周立波”,65,“2018-09-12”,30),
(1008,“詹姆斯”,59,“2015-12-09”,50)
#1.查询表中所有学生的姓名和分数
select sname,score from student
#2.查询分数小于80分的学生编号,姓名，和入学日期
select sid,sname,joindate from student where score<80
#3.查询2018年入学的学生姓名，分数和入学时间
select sname,score,joindate from student where joindate like ‘2018%’
#4.查询专业编号为10并且分数低于80分的学生信息
select * from student where smid=10 and score<80
#5.查询姓刘的学生的个人信息
select * from student where sname like ‘刘%’
#6.查询所有学生的平均成绩
select avg(score) from student
#7.查询各专业的最高分数，查询内容包括:专业编号，最高分数
select smid,max(score) from student inner join major on student.smid=major.mid GROUP BY mname
#8.查询所有学生姓名及对应的专业名称(去除笛卡尔积)
select sname,mname from major inner join student on major.mid=student.smid
#9.查询各专业最高分的学生记录(子查询)
select smid,max(score) from student
select * from major mname where mid=(select smid from student(select max(score) from student))
#10.用左连接(专业表为左表),查询各专业的专业编号，专业名称，人数
select mid,mname,count(sname) from major left join student on major.mid=student.smid