use wangchang;
CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5))charset = gbk;
CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL)charset = gbk;
CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL)charset = gbk;
CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME not null, PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL)charset = gbk;
/*--日期一定要打引号, 插入dateTime时间必须是引号中的'1976-02-20' 否则incorrect datetime value */
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 SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(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','助教','电子工程系');
select *from STUDENT;
+-----+-------+------+---------------------+-------+
| SNO | SNAME | SSEX | SBIRTHDAY | CLASS |
+-----+-------+------+---------------------+-------+
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
select *from COURSE ;
+-------+------------+-----+
| CNO | CNAME | TNO |
+-------+------------+-----+
| 6-166 | 数据电路 | 856 |
| 9-888 | 高等数学 | 100 |
| 6-166 | 数据电路 | 856 |
| 9-888 | 高等数学 | 100 |
| 6-166 | 数据电路 | 856 |
| 9-888 | 高等数学 | 100 |
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数据电路 | 856 |
| 9-888 | 高等数学 | 100 |
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数据电路 | 856 |
| 9-888 | 高等数学 | 100 |
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数据电路 | 856 |
| 9-888 | 高等数学 | 100 |
+-------+------------+-----+
18 rows in set (0.00 sec)
select *from SCORE;
select *from teacher;
mysql> select *from teacher;
+-----+-------+------+---------------------+--------+------------+
| TNO | TNAME | TSEX | TBIRTHDAY | PROF | DEPART |
+-----+-------+------+---------------------+--------+------------+
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
+-----+-------+------+---------------------+--------+------------+
;
/*1、 查询Student表中的所有记录的Sname、Ssex和Class列。*/
select SNAME,SSEX,Class from student;
/* 2、 查询教师所有的单位即不重复的Depart列。*/
select distinct DEPART FROM teacher;
/*3、 查询Student表的所有记录。*/
select * from student;
/*4、 查询Score表中成绩在60到80之间的所有记录。*/
select *from score where DEGREE<80 and DEGREE>60;
SELECT * FROM SCORE WHERE DEGREE BETWEEN 60 AND 80;
/*5、 查询Score表中成绩为85,86或88的记录。*/
select *from score where DEGREE=85 or DEGREE=86 or DEGREE=88 ;
SELECT * FROM SCORE WHERE DEGREE IN (85,86,88);
/*6、 查询Student表中“95031”班或性别为“女”的同学记录。*/
select *from student where CLASS='95031' or SSEX ='女';
/* 7、 以Class降序查询Student表的所有记录。*/
select * from student order by CLASS desc;
/* 8、 以Cno升序、Degree降序查询Score表的所有记录。*/
select * from score order by cno , degree desc;/*不能连用order by */
/*9、 查询“95031”班的学生人数。 */
select count(*)from student where class = '95031';
/*10、查询Score表中的最高分的学生学号和课程号。*/
select sno,cno from score where degree=(select max(degree) from score) ;
select sno,cno from score where degree= max(degree) ;/*错误的代码,无效函数*/
/*11、查询‘3-105’号课程的平均分。*/
select avg(degree) from score where CNO = '3-105';
/*12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。*/
select avg (degree),cno from score where cno like '3%' group by cno having count(sno) >=5;
/*13、查询最低分大于70,最高分小于90的Sno列。*/
select sno from score group by sno having min(degree)>70 and max(degree)<90;/*用having and比较好,其他的易出错*/
/* 14、查询所有学生的Sname、Cno和Degree列。*/
select A.Sname,B.CNO,B.DEGREE FROM student as A join score as B on A.sno = B.sno;
/*15、查询所有学生的Sno、Cname和Degree列。*/
select A.sno,B.Cname,A.Degree from SCORE as A JOIN COURSE AS B on A.CNO = B.CNO;
/*16、查询所有学生的Sname、Cname和Degree列。 */
select A.Sname,B.Cname,C.Degree FROM STUDENT as A join(course AS B,SCORE AS C) ON A.SNO =C.SNO AND B.CNO =C.CNO;
/* 17、查询“95033”班所选课程的平均分。*/
select avg(A.DEGREE) FROM SCORE AS A JOIN STUDENT AS B on A.sno = B.sno where B.CLASS = '95033';
/*18、假设使用如下命令建立了一个grade表:*/
create table grades(low numeric(3,0),upp int(3), rank char(1));
insert into grades values(90,100,'A');
insert into grades values(80,89,'B');
insert into grades values(70,79,'C');
insert into grades values(60,69,'D');
insert into grades values(0,59,'E');
commit;
/*现查询所有同学的Sno、Cno和rank列。*/
select A.sno,B.cno,C.rank from student A join (score B,grades C) on A.SNO = B.SNO BETWEEN C.low and C.upp;/*不对*/
select A.sno,A.cno,B.rank from SCORE as A join grades as B where A.degree BETWEEN B.LOW AND B.UPP;
/* 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。 */
select A.* from score A join score B where A.cno ='3-105' and A.degree> B.degree and B.cno ='3-105' and B.sno ='109';
select A.* from score A where A.degree> all(select degree from score where score.cno = '3-105' and score.sno = '109');
/* 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。*/
select * from score where degree<(select max(degree) from score ) group by sno having count(sno)>1 order by degree;
select * from score where degree<(select max(degree) from score ) group by sno having count(sno)>1;
/*21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。*/
select * from score where sno = '109' and cno = '3-105';
/*22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。*/
select sno ,sname ,sbirthday from student where sno = '108';
/*23、查询“张旭“教师任课的学生成绩。*/
select A.SNO ,A.DEGREE FROM score A join (teacher B,COURSE C) where A.cno = C.cno and B.TNO = C.TNO and B.TNAME= '张旭' ;
/*24、查询选修某课程的同学人数多于5人的教师姓名。*/
select A.tname FROM teacher A JOIN (course B, SCORE C ) ON (B.TNO = A.TNO and B.CNO = C.CNO) group by c.cno HAVING count(C.cno)>5;
/*25、查询95033班和95031班全体学生的记录。*/
SELECT * FROM STUDENT WHERE CLASS = '95033' or class = '95031';
/*26、查询存在有85分以上成绩的课程Cno.*/
select distinct cno from score group by degree having max(degree)>85;
select distinct cno from score where degree in (select degree from score where degree >85);/*更优*/
/*27、查询出“计算机系“教师所教课程的成绩表。*/
select A.*FROM SCORE A JOIN (COURSE B,TEACHER C) on A.CNO = B.CNO AND B.TNO = C.TNO where c.depart = '计算机系';
select *FROM SCORE WHERE CNO IN (SELECT CNO FROM COURSE A JOIN TEACHER B ON A.TNO = B.TNO WHERE B.DEPART = '计算机系');
/*28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。*/
select Tname,Prof from teacher where DEPART ='计算机系' and Prof not in (select Prof from teacher where depart = '电子工程系');
/*29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。*/
select cno , sno , degree from score where cno ='3-105' and degree >= any
(select degree from score where cno ='3-245') order by degree desc;
/*30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.*/
select cno , sno , degree from score where cno ='3-105' and degree >= all
(select degree from score where cno ='3-245');
/*31、查询所有教师和同学的name、sex和birthday.*/
select sname as name ,ssex as sex ,sbirthday as birthday from student
union select tname as name ,tsex as sex ,tbirthday as birthday from teacher;
/*32、查询所有“女”教师和“女”同学的name、sex和birthday.*/
select sname as name ,ssex as sex ,sbirthday as birthday from student where ssex ='女'
union select tname as name ,tsex as sex ,tbirthday as birthday from teacher where tsex ='女';
/*33、查询成绩比该课程平均成绩低的同学的成绩表。*/
select A.*from score A where degree <=(select avg(degree) from score B where A.CNO = B.CNO);
/*34、查询所有任课教师的Tname和Depart.*/
select Tname,Depart from teacher where tno in (select tno from course);
select A.Tname,A.Depart from teacher A JOIN COURSE B ON A.TNO = B.TNO ;
/*35 查询所有未讲课的教师的Tname和Depart.*/
select Tname,Depart from teacher where tno not in (select tno from course);
select tname,depart from teacher a where not exists
(select * from course b where a.tno=b.tno);
/*36、查询至少有2名男生的班号。*/
SELECT CLASS FROM STUDENT WHERE SSex ='男' group by class having count(ssex)>=2;
/*37、查询Student表中不姓“王”的同学记录。*/
SELECT * FROM STUDENT WHERE sname not like '王%';
/*38、查询Student表中每个学生的姓名和年龄。*/
select sname ,(year(now())-year(sbirthday)) as age from student;
/*39、查询Student表中最大和最小的Sbirthday日期值。*/
select sname ,sbirthday from student where sbirthday = (select min(sbirthday) from student) union
select sname ,sbirthday from student where sbirthday = (select max(sbirthday) from student);
/*40、以班号和年龄从大到小的顺序查询Student表中的全部记录。*/
select class ,(year(now())-year(sbirthday)) as age from student order by class desc, age desc;
/*41、查询“男”教师及其所上的课程。*/
select A.tname , B.cname from teacher A join course B on A.TNO =B.TNO WHERE A.tsex = '男' ;
/*42、查询最高分同学的Sno、Cno和Degree列。*/
select A.sno ,B.Cno ,B.degree from student A JOIN SCORE B ON A.SNO = B.SNO WHERE B.DEGREE = (select max(DEGREE) FROM SCORE);
SELECT A.* FROM SCORE A WHERE DEGREE = (select max(DEGREE) FROM SCORE);
/*43、查询和“李军”同性别的所有同学的Sname.*/
select sname from student A where ssex = (select ssex from student B where B.sname = '李军' ) AND CLASS =(select CLASS from student C where C.sname = '李军');
SELECT SNAME FROM STUDENT A WHERE SSEX=(SELECT SSEX FROM STUDENT B WHERE B.SNAME='李军' )
AND CLASS=(SELECT CLASS FROM STUDENT C WHERE c.SNAME='李军');/*有误*/
/*45、查询所有选修“计算机导论”课程的“男”同学的成绩表*/
select *from score where sno = (select sno from student where ssex = '男')
and cno = (select cno from course where cname ='计算机导论');
SELECT A.* FROM SCORE A JOIN (STUDENT B,COURSE C) USING(sno,CNO) WHERE B.SSEX='男'
AND C.CNAME='计算机导论';/*有问题*/
利用别人的sql练习题进行修改
最新推荐文章于 2020-10-11 00:28:03 发布