Mysql 练习题 加Mysql的表

CREATE TABLE J_TEACHER (
tno int NOT NULL PRIMARY KEY,
tname varchar(20) NOT NULL
);

INSERT INTO J_TEACHER(tno,tname)VALUES(1,’张老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(2,’王老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(3,’李老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(4,’赵老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(5,’刘老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(6,’向老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(7,’李文静’);
INSERT INTO J_TEACHER(tno,tname)VALUES(8,’叶平’);

CREATE TABLE J_STUDENT(
sno int NOT NULL PRIMARY KEY,
sname varchar(20) NOT NULL,
sage datetime NOT NULL,
ssex char(2) NOT NULL
);

INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(1,’张三’,’1980-1-23’,’男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(2,’李四’,’1982-12-12’,’男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(3,’张飒’,’1981-9-9’,’男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(4,’莉莉’,’1983-3-23’,’女’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(5,’王弼’,’1982-6-21’,’男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(6,’王丽’,’1984-10-10’,’女’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(7,’刘香’,’1980-12-22’,’女’);

CREATE TABLE J_COURSE(
cno int NOT NULL PRIMARY KEY,
cname varchar(20) NOT NULL,
tno int NOT NULL
);

insert into J_COURSE(cno,cname,tno) values(1,’企业管理’,3);
insert into J_COURSE(cno,cname,tno) values(2,’马克思’,1);
insert into J_COURSE(cno,cname,tno) values(3,’UML’,2);
insert into J_COURSE(cno,cname,tno) values(4,’数据库’,5);
insert into J_COURSE(cno,cname,tno) values(5,’物理’,8);

CREATE TABLE J_SCORE(
sno int NOT NULL,
cno int NOT NULL,
score int NOT NULL
);

ALTER TABLE J_SCORE ADD CONSTRAINT FK_SCORE_course FOREIGN KEY(cno)
REFERENCES J_COURSE (cno);
ALTER TABLE J_SCORE ADD CONSTRAINT FK_score_student FOREIGN KEY(sno)
REFERENCES J_STUDENT (sno);

INSERT INTO J_SCORE(sno,cno,score)VALUES(1,1,80);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,2,86);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,3,83);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,4,89);

INSERT INTO J_SCORE(sno,cno,score)VALUES(2,1,50);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,2,36);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,3,43);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,4,59);

INSERT INTO J_SCORE(sno,cno,score)VALUES(3,1,50);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,2,96);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,3,73);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,4,69);

INSERT INTO J_SCORE(sno,cno,score)VALUES(4,1,90);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,2,36);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,3,88);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,4,99);

INSERT INTO J_SCORE(sno,cno,score)VALUES(5,1,90);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,2,96);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,3,98);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,4,99);

INSERT INTO J_SCORE(sno,cno,score)VALUES(6,1,70);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,2,66);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,3,58);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,4,79);

INSERT INTO J_SCORE(sno,cno,score)VALUES(7,1,80);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,2,76);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,3,68);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,4,59);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,5,89);

*1、查询课程1的成绩 比 课程2的成绩 高 的所有学生的学号.
SELECT a.sno ,a.score,b.score from (SELECT sno ,score from j_score where cno = ‘1’) a ,
(SELECT sno ,score from j_score where cno = ‘2’) b
WHERE a.score > b.score and a.sno = b.sno

2、查询平均成绩大于60分的同学的学号和平均成绩；

3、查询所有同学的学号、姓名、选课数、总成绩

4、查询姓“李”的学生的个数；

*5、查询没学过“叶平”老师课的同学的学号、姓名；
select s.sno,s.sname from j_student s
where s.sno not in (select distinct(sc.sno) from
j_score sc,j_course c, j_teacher t
where sc.cno=c.cno and t.tno=c.tno and
t.tname=’叶平’)

*6、查询同时学过课程1和课程2的同学的学号、姓名
select s.sno, s.sname from j_student s
where s.sno in (select sc.sno from j_score sc
where sc.cno = 1)
and s.sno in (select sno from j_score where cno = 2)
7、查询学过“叶凡”老师所教所有课程的所有同学的学号、姓名
select s.sno,s.sname from j_student s
where s.sno in (select distinct(sc.sno) from
j_score sc,j_course c, j_teacher t
where sc.cno=c.cno and t.tno=c.tno
and t.tname=’叶平’)
8、查询 课程编号1的成绩 比 课程编号2的成绩 高的所有同学的学号、姓名
SELECT a.sno, STU.sname,a.score,b.score from j_student stu, (SELECT sno ,score from j_score where cno = ‘1’) a ,
(SELECT sno ,score from j_score where cno = ‘2’) b
WHERE a.score > b.score and a.sno = b.sno and stu.sno = b.sno
9、查询所有课程成绩小于60分的同学的学号、姓名

10、查询所有课程成绩大于60分的同学的学号、姓名

11、查询没有学全所有课的同学的学号、姓名
select t2.mysno,s.sname from (SELECT count(c.cno) as ‘num1’ from j_course c) t1,
(SELECT sc.sno as ‘mysno’ ,count(sc.sno) as ‘num2’ from j_score sc
GROUP BY sc.sno) t2 ,j_student s
WHERE t1.num1 > t2.num2 and t2.mysno = s.sno
12、查询至少有一门课程 与 学号为1的同学所学课程 相同的同学的学号和姓名
SELECT DISTINCT s.sno,s.sname from j_student s ,j_score sc
WHERE s.sno <> 1 and s.sno = sc.sno and sc.cno in (SELECT cno from j_score where sno = 1)
13、查询和2号同学学习的课程完全相同的其他同学学号和姓名
SELECT s.sno ,s.sname from j_score sc,j_student s
WHERE s.sno <> 2 and s.sno = sc.sno
GROUP BY s.sno ,s.sname
HAVING SUM(sc.cno) = (SELECT SUM(cno) from j_score where sno =2)

SELECT * FROM j_score A
WHERE (
SELECT COUNT(*) FROM j_score B
WHERE A.cno=B.cno
AND B.score>=A.score
)<=2
ORDER BY A.cno ASC,A.score DESC

15、查询各科成绩最高分和最低分：
以如下形式显示：课程号，最高分，最低分
select cno as 课程号, max(score) as 最高分, min(score) 最低分
from j_score group by cno

16、查询每门课程被选修的学生数
SELECT c.cname,COUNT(sc.sno) from j_course c LEFT JOIN j_score sc
on c.cno = sc.cno GROUP BY c.cname
17、查询出只选修了一门课程的全部学生的学号和姓名
SELECT s.sno ,s.sname,COUNT(sc.cno) from j_student s LEFT JOIN j_score sc
on s.sno = sc.sno GROUP BY s.sno,s.sname HAVING COUNT(sc.cno) = 1
18、查询同名同性学生名单，并统计同名人数
SELECT s.sname ,COUNT(s.sname) from j_student s ,
(SELECT sname,ssex from j_student )t
WHERE s.sname = t.sname and s.ssex = t.ssex
GROUP BY s.sname

SELECT t.sname ,t.ssex ,COUNT(t.sname) from  j_student t 

GROUP BY t.sname ,t.ssex HAVING COUNT(t.sname) > 1
19、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select s.sname,cname, score
from j_student s,j_score sc,j_course c,j_teacher t
where s.sno=sc.sno and sc.cno=c.cno and c.tno=t.tno
and t.tname =’叶平’
and sc.score=(select max(score)from j_score where cno = C.cno)

20、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select distinct A.sno, A.cno,B.score
from j_score A ,j_score B
where A.Score=B.Score and A.cno <>B.cno
order by B.score

21、查询每门课程成绩最好的前两名的学生ID
–以科目，按成绩给记录编号，然后取出编号<=2的，就是每科前两名
SELECT *
FROM j_score A
WHERE (SELECT COUNT(*)
FROM j_score B
WHERE B.cno = A.cno
AND B.score >= A.score) <= 2
ORDER BY A.cno, A.score DESC
22、检索至少选修了5门课程的学生学号
select sno from j_score group by sno having count(1) >= 5
23、查询没学过“叶平”老师讲授的任一门课程的学生姓名
select sno, sname from j_student
where sno not in(
select sno from j_score where cno in
(select a.cno from j_course a, j_teacher b where a.tno = b.tno and b.tname = ‘叶平’))

24、查询两门以上不及格课程的同学的学号及其平均成绩
select
sno,avg(score) 平均分,COUNT(sno) as 不及格课程
from j_score
where score<60
group by sno
having COUNT(sno)>2

25、查询最受欢迎的老师(选修学生最多的老师)
select cno 课程ID, count(cno) 选修人数
from j_score group by cno
having count(cno) in (
SELECT t.num from (select count(cno) num from j_score group by cno order by count(cno) desc LIMIT 0,1)t
)
order by 选修人数 desc