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

数据库题:
#创建数据库
create database db_test CHARACTER set utf8
#选择数据库
use db_test
#创建表1
create table province(pid int PRIMARY KEY auto_increment,
pname VARCHAR(255))
#创建表2
create table student(sid int PRIMARY key,
sname VARCHAR(255),
score VARCHAR(255),
joindate VARCHAR(255),
spid int)
#表1添加数据
insert into province VALUES(10,“河北”),(20,“黑龙江”),(30,“吉林”),(40,“辽宁”),(50,“内蒙古”)
#表2添加数据
insert into student VALUES(1001,“张三”,“95.5”,“2018-03-12”,30),(1002,“李四”,“93”,“2017-09-18”,10),(1003,“赵六”,“89”,“2018-09-12”,50),(1004,“孙七”,“75”,“2017-06-30”,10),(1005,“熊大”,“99.9”,“2018-03-22”,20),(1006,“熊二”,“65”,“2018-09-12”,30),(1007,“光头强”,“59”,“2015-12-09”,50)
#1.查询学生表中所有学生记录
select * from student
#2.查询姓名为李四的学生信息
select * from student where sname=“李四”
#3.查询2017年入学的学生学号，姓名，分数
select sid,sname,score from student where joindate like ‘2017%’
#4.查询省份编号为30并且分数高于80分的学生信息
select * from province inner join student on province.pid=student.spid where pid=30 and score>80
#5.查询名字是3个字的所有学生信息
select * from student where sname like ‘___’
select * from student where LENGTH(sname)=9
#6.查询名字是从1004至1007学生的姓名和入学日期
select sname,joindate from student where sid BETWEEN 1004 and 1007
#7.查询各省份的最高成绩，显示省份编号和最高成绩
select pid,max(score) from province inner join student on province.pid=student.spid GROUP BY pname
#8.查询所有学生姓名以及对应的专省份(去除笛卡尔积)
select sname,pname from province inner join student on province.pid=student.spid