SQL语句练习

一、已知有如下表，请用sql语句在mysql里建立相应的表

表1 学生表（student）

列名	意义	类型	约束
sno	学号	varchar(3)	主键
sname	姓名	varchar(10)	非空
ssex	性别	varchar(3)
sbirthday	生日	datetime
class	班级编号	varchar(5)	非空

表2 课程表（course）

列名	意义	类型	约束
cno	课程号	varchar(10)	主键
cname	课程名	varchar(30)	非空
tno	教师编号	varchar(10)	非空

表3 成绩表(grade)

列名	意义	类型	约束
sno	学号	varchar(3)	主键
cno	课程号	varchar(5)	主键
degree	分数	tinyint	非空

表4 教师表(teacher)

列名	意义	类型	约束
tno	教师编号	varchar(5)	主键
tname	教师姓名	varchar(20)	非空
tsex	性别	varchar(2)
tbirthday	生日	datetime
prof	职称	varchar(20)	非空
depart	系	varchar(30)	非空

表5 等级表(rank)

列名	意义	类型	约束
down	分数下届	tinyint	非空
up	分数上界	tinyint	非空
rank	级别	varchar(2)	主键

SQL语句：

CREATE TABLE `student` (
  `sno` varchar(3) NOT NULL,
  `sname` varchar(10) NOT NULL,
  `ssex` varchar(3) NOT NULL,
  `sbirthday` datetime NOT NULL,
  `class` varchar(5) NOT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `course` (
  `cno` varchar(10) NOT NULL,
  `cname` varchar(30) NOT NULL,
  `tno` varchar(10) NOT NULL,
  PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE `grade` (
  `sno` varchar(3) NOT NULL,
  `cno` varchar(5) NOT NULL,
  `degree` tinyint(4) NOT NULL,
  PRIMARY KEY (`sno`,`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `teacher` (
  `tno` varchar(5) NOT NULL,
  `tname` varchar(20) NOT NULL,
  `tsex` varchar(2) NOT NULL,
  `tbirthday` datetime NOT NULL,
  `prof` varchar(20) NOT NULL,
  `depart` varchar(30) NOT NULL,
  PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `rank` (
  `down` tinyint(4) NOT NULL,
  `up` tinyint(4) NOT NULL,
  `rank` varchar(2) NOT NULL,
  PRIMARY KEY (`rank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

二、将如下数据插入到表里

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' , '1977-09-01',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,’1975-10-02’,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,’1976-01-23’, 95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,’1976-02-20’,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,’1975-02-10’,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,’1974-06-03’,95031);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论', '825');
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' , '804');
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' , '856');
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' , '100');

INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES ('103','3-245',86);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');


insert into rank(down,up,rank) values(90,100,'A');
insert into rank(down,up,rank) values(80,89,'B');
insert into rank(down,up,rank) values(70,79,'C');
insert into rank(down,up,rank) values(60,69,'D');
insert into rank(down,up,rank) values(0,59,'E');

三、sql语句练习
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from student
2、查询“张旭“教师任课的学生成绩。
select g.degree from grade as g join course as c on g.cno=c.cno join teacher as t on c.tno=t.tno where t.tname='张旭';
3、查询选修某课程的同学人数多于5人的教师姓名。
select tname from teacher where tno in (select tno from course where cno in (select cno from grade group by cno having count(cno)>5));
4、查询95033班和95031班全体学生的记录。
Select * from student where class in (‘95033’, ‘95031’)
5、查询存在有85分以上成绩的课程Cno.
Select distinct cno from grade where degree>85;
6、查询出“计算机系“教师所教课程的成绩表。
select degree from grade where cno in (select cno from course where tno in (select tno from teacher where depart='计算机系'));
7、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学最低分的Cno、Sno和Degree,并按Degree从高到低次序排序。
select sno,cno,degree from grade where cno='3-105' and degree>(select min(degree) from grade where cno='3-245') order by degree desc;
8、查询同时选修编号为“3-105”和“3-245”并且“3-105”成绩高于“3-245”成绩的同学的Cno、Sno和Degree.
select g1.sno from grade as g1 join grade as g2 on g1.sno=g2.sno where g1.cno='3-105' and g2.cno='3-245' and g1.degree > g2.degree;
9、查询所有教师和同学的name、sex和birthday.
select sname,ssex,sbirthday from student union select tname,tsex,tbirthday from teacher;
10、查询所有“女”教师和“女”同学的name、sex和birthday.
select sname,ssex,sbirthday from student where ssex=’女’ union select tname,tsex,tbirthday from teacher where tsex=’女’;