* 创建数据库表数据
* use test;
* CREATE TABLE test.Student(Sid VARCHAR(10),Sname VARCHAR(10),Sage DATETIME,Ssex NVARCHAR(10));
* INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
* INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
* INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');
* INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');
* INSERT INTO Student VALUES('05' , '孙风' , '1991-12-01' , '男');
* INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
* INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
* INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女');
* CREATE TABLE test.Course(Cid VARCHAR(10),Cname VARCHAR(10),Tid VARCHAR(10));
* INSERT INTO Course VALUES('01' , '语文' , '02');
* INSERT INTO Course VALUES('02' , '数学' , '01');
* INSERT INTO Course VALUES('03' , '英语' , '03');
* CREATE TABLE test.Teacher(Tid VARCHAR(10),Tname VARCHAR(10));
* INSERT INTO Teacher VALUES('01' , '张三');
* INSERT INTO Teacher VALUES('02' , '李四');
* INSERT INTO Teacher VALUES('03' , '王五');
* CREATE TABLE test.SC(Sid VARCHAR(10),Cid VARCHAR(10),score DECIMAL(18,1));
* INSERT INTO SC VALUES('01' , '01' , 80);
* INSERT INTO SC VALUES('01' , '02' , 90);
* INSERT INTO SC VALUES('01' , '03' , 99);
* INSERT INTO SC VALUES('02' , '01' , 70);
* INSERT INTO SC VALUES('02' , '02' , 60);
* INSERT INTO SC VALUES('02' , '03' , 80);
* INSERT INTO SC VALUES('03' , '01' , 80);
* INSERT INTO SC VALUES('03' , '02' , 80);
* INSERT INTO SC VALUES('03' , '03' , 80);
* INSERT INTO SC VALUES('04' , '01' , 50);
* INSERT INTO SC VALUES('04' , '02' , 30);
* INSERT INTO SC VALUES('04' , '03' , 20);
* INSERT INTO SC VALUES('05' , '01' , 76);
* INSERT INTO SC VALUES('05' , '02' , 87);
* INSERT INTO SC VALUES('06' , '01' , 31);
* INSERT INTO SC VALUES('06' , '03' , 34);
* INSERT INTO SC VALUES('07' , '02' , 89);
* INSERT INTO SC VALUES('07' , '03' , 98);
* Q&A
* Questions
* 0、查询sc表中，平均成绩，行号显示出来
* 1、查询“01”课程比“02”课程成绩高的所有学生的学号；
* 2、查询平均成绩大于60 分的同学的学号和平均成绩；
* 3、查询所有同学的学号、姓名、选课数、总成绩；
* 4、查询姓“张”的老师的个数；
* 5、查询没学过“张三”老师课的同学的8学号、姓名；
* 6、查询学过“01”并且也学过编号“02”课程的同学的学号、姓名；
* 7、查询学过“张三”老师所教的所有课的同学的学号、姓名；
* 8、查询课程编号“02”的成绩比课程编号“01”课程低的所有同学的学号、姓名；
* 9、查询所有课程成绩小于60 分的同学的学号、姓名；
* 10、查询没有学全所有课的同学的学号、姓名；
* 11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
* 12、查询至少学过学号为“01”同学所有课程中的一门课的其他同学学号和姓名；
* 13、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
* 14、查询和“01”号的同学学习的课程相同的其他同学学号和姓名；
* 15、删除学习“张三”老师课的SC 表记录；
* 16、向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“02”课程的同学学号、“02”课程的平均成绩；
* 17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
* 18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
* 19、查询不同老师所教不同课程平均分从高到低显示
* 20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
* 21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
* 22、查询每门课程被选修的学生数
* 23、查询出只选修了一门课程的全部学生的学号和姓名
* 24、查询男生、女生人数
* 25、查询姓“孙”的学生名单
* 26、查询同名同性学生名单，并统计其人数
* 27、1991 年出生的学生名单(注：Student 表中Sage 列的类型是number)
* 28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
* 29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
* 30、查询课程名称为“数学”，且分数低于60 的学生姓名和分数
* 31、查询所有学生的选课情况；
* 32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；
* 33、查询不及格的课程，并按课程号从大到小排列
* 34、查询课程编号为'01' 且课程成绩在75 分以上的学生的学号和姓名；
* 35、求选了课程的学生人数
* 36、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
* 37、查询各个课程及相应的选修人数
* 38、查询不同课程成绩相同的学生的姓名
* 39、查询每门功课成绩最好的前两名
* 40、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列41、检索至少选修两门课程的学生学号
* 42、查询全部学生都选修的课程的课程号和课程名
* 43、查询没学过“张三”老师讲授的任一门课程的学生姓名
* 44、查询两门以上不及格课程的同学的学号及其平均成绩
* 45、检索“03”课程分数小于60，按分数降序排列的同学学号
* 46、删除“02”同学的“01”课程的成绩
* Answsers
* 0\. 数据来源：SC表
* SELECT c.sid,c.aa,(@rowNum:=@rowNum+1) AS rowNo
* FROM
* (SELECT sid,AVG(score)aa FROM sc GROUP BY sid ORDER BY aa DESC)c,
* (SELECT (@rowNum:=0))b;
* 1. 数据来源：SC表
* SELECT a.sid FROM
* (SELECT * FROM sc WHERE cid='01') a,
* (SELECT * FROM sc WHERE cid='02') b
* WHERE a.sid=b.sid AND a.score > b.score;
* 2\. 数据来源：SC表
* SQL1: （按照：平均分 = 总分/已考试的科目数 计算）
* select sc.sid,avg(sc.score) ss
* from sc
* group by sc.sid
* having ss > 60 ;
* SQL2: （按照：平均分 = 总分/总的科目数 计算）
* SELECT c.sid,SUM(c.score)/(SELECT COUNT(*) FROM test.course) AS ss
* FROM test.SC c
* GROUP BY c.sid
* HAVING ss > 60;
* 3\. 数据来源：SC & Student
* SELECT s.sid,s.sname,[b.cc](http://b.cc),b.ss
* FROM student s LEFT JOIN (SELECTsid,COUNT(cid)cc,SUM(sc
* ore)ss FROM sc GROUP BY sid)b
* ON s.sid=b.sid;
* 4\. 数据来源：Teacher
* SELECT COUNT(*) FROM teacher WHERE Tname LIKE ‘张%';
* 5.数据来源：Teacher，Course,Sc, Student
* SELECT sid,sname FROM student
* WHERE sid NOT IN (SELECT sc.sid
* FROM sc,(SELECT c.cid
* FROM course c,(SELECT tid FROM teacher WHERE tname = '张三')a
* WHERE c.tid = a.tid)b
* WHERE sc.cid = b.cid);
* 6.
* SELECT s.sid,s.sname
* FROM student s,
* (SELECT a.sid FROM
* (SELECT sid,cid FROM sc WHERE cid='01')a,
* (SELECT sid,cid FROM sc WHERE cid='02')b WHERE a.sid=b.sid)c
* WHERE s.sid=c.sid;
* 7.
* SELECT st.sid,st.sname FROM student st JOIN sc ON st.sid=sc.sid
* JOIN course c ON sc.cid=c.cid
* JOIN teacher t ON c.tid=t.tid
* WHERE t.tname='张三' ;
* 8.
* SELECT st.sid,st.sname
* FROM student st JOIN
* (SELECT a.sid esid FROM
* (SELECT * FROM sc WHERE cid='01') a,
* (SELECT * FROM sc WHERE cid='02') b
* WHERE a.sid=b.sid AND a.score > b.score)e
* ON st.sid = e.esid ;
* 9.
* SELECT DISTINCT st.sid,st.sname
* FROM student st
* JOIN sc ON st.sid=sc.sid
* JOIN course c ON sc.cid=c.cid
* WHERE sc.score <60 ;
* 10.
* SELECT sid,sname FROM student WHERE sid NOT IN
* (SELECT a.sid FROM
* (SELECT sid,COUNT(cid)cc FROM sc GROUP BY sid)a,
* (SELECT COUNT(cid)bb FROM course)b
* WHERE [a.cc](http://a.cc)=b.bb)
* 11.
* selectst.sid,st.sname fromstudent st,
* (select distinct a.sid from
* (select * from sc) a,
* (select * from sc where sc.sid='01') b
* where a.cid=b.cid) h
* where st.sid=h.sid and st.sid<>'01'；
* 12.
* selectst.sid,st.sname
* from student st,
* (select distinct sc.sid psid from sc
* left join student st
* on st.sid=sc.sid
* where sc.sid<>'01'
* and sc.cid in
* (select cid from sc
* where sid='01'))p
* where st.sid=p.psid;
* 13.
* 1> MSSQL中可以用此方法,但是MYSQL中却不行
* update sc
* set sc.score = (select avg(sc.score) from sc where sc.cid=
* (select a.Cid from course a,teacher b where a.Tid=b.Tid and b.tname='张三'))
* where sc.cid = (select a.Cid from course a,teacher b where a.T=b.T and b.tname='张三');
* 2>MYSQL中Update中有select子查询,必须innerjoin才可以.
* update sc
* inner join (select avg(sc.score)av from sc where sc.cid=
* (select a.cid from course a,teacher b where a.Tid=b.Tid and b.tname='张三'))A set sc.score = A.av
* where sc.cid = (select a.cid from course a,teacher b where a.Tid=b.Tid and b.tname='张三');
* 14.
* select st.sid,st.sname from student st,sc
* where st.sid=sc.sid
* and sc.cid in (select sc.cid from sc where sc.sid='01') and sc.sid<>'01'
* group by st.sid,st.sname
* having count(*)=(select count(*) from sc where sid='01');
* 15.
* delete from sc where sc.cid in (select cid from course c join (select tid from teacher where tname = '张三')d on c.tid=d.tid);
* 16.
* INSERT INTO sc(sid,cid,score)
* SELECT DISTINCT st.sid,sc.cid,(SELECT AVG(score)FROM sc WHEREcid='02')
* FROM student st,sc
* WHERE NOT EXISTS
* (SELECT * FROM sc WHERE cid='02' AND sc.sid=st.sid)
* AND sc.cid='02' ;
* 17.
* SELECT cid ,MAX(score),MIN(score) FROM sc GROUP BY cid;
* 18.
* SELECT B.CID,B.A,Z.P
* FROM (SELECT cid,AVG(score)a FROM sc GROUP BY cid)B,
* (SELECT y.cid e,x.b/y.c p FROM
* (SELECT cid,COUNT(*)c FROM sc GROUP BY cid)Y,
* (SELECT cid,COUNT(*)b FROM sc WHERE score>=60 GROUP BYcid)X
* WHERE x.cid=y.cid)Z
* WHERE B.CID=z.e
* ORDER BY B.A ASC,Z.P DESC;
* 或者：
* SELECT cid,AVG(score) a,SUM(CASE WHEN score>=60 THEN 1ELSE 0 END)/COUNT(*) AS p FROM sc GROUP BY cid
* ORDER BY a ASC, p DESC ;
* 19.
* SELECT
* t.tid,
* t.tname,
* c.cid,
* c.cname,
* AVG(score)a
* FROM
* sc,
* course c,
* teacher t
* WHERE sc.cid = c.cid
* AND c.tid = t.tid
* GROUP BY c.cid
* ORDER BY a DESC ;
* 20.
* SELECT C.Cid,c.Cname,D.Y "[100-85]",D.U "[85-70]",D.I"[70-60]",D.O "[<60]"
* FROM COURSE C JOIN
* (SELECT sc.Cid E,
* SUM(CASE WHEN sc.score BETWEEN 85 AND 100 THEN 1 ELSE 0 END)AS Y,
* SUM(CASE WHEN sc.score BETWEEN 70 AND 85 THEN 1 ELSE 0 END)AS U,
* SUM(CASE WHEN sc.score BETWEEN 60 AND 70 THEN 1 ELSE 0 END)AS I,
* SUM(CASE WHEN sc.score <60 THEN 1 ELSE 0 END) AS O
* FROM sc
* GROUP BY sc.cid)D
* ON C.Cid=D.E ;
* 21.
* SELECT a.* FROM sc a
* WHERE 3 > (SELECT COUNT(*) FROM sc WHERE cid = a.cid ANDscore > a.score )
* ORDER BY a.cid,a.score DESC;
* Oracle：SELECT * FROM
* (SELECT sid,cid,score,row_number()over(PARTITION BY cid ORDERBY score DESC) rn FROM sc)
* WHERE rn<4 ;
* 22.
* SELECT cid,COUNT(sid) FROM sc GROUP BY cid;
* 23.
* SELECT s.sid,s.sname
* FROM student s,
* (SELECT sid,COUNT(cid)cc FROM sc GROUP BY sid)a
* WHERE s.sid=a.sid AND a.cc=1;
* 24.
* SELECT ssex,COUNT(*) FROM student GROUP BY ssex ;
* 25.
* SELECT * FROM student WHERE sname LIKE '孙%';
* 26.
* SELECT sname,ssex,COUNT(*)FROM student
* GROUP BY sname,ssex HAVING COUNT(*)>1;
* 27.
* MySQL日期格式：
* SELECT DATE_FORMAT('2008-08-08 22:23:01', '%Y%m%d%H%i%s');
* SELECT * FROM student
* WHERE DATE_FORMAT(sage,'%Y')=1991
* 28.
* SELECT cid,AVG(score)a FROM sc GROUP BY cid
* ORDER BY a ASC,cid DESC;
* 29.
* SELECT st.sid,st.sname,AVG(score) FROM student st
* LEFT JOIN sc
* ON sc.sid=st.sid
* GROUP BY st.sid,st.sname HAVING AVG(score)>85;
* 或：
* SELECT s.sid,s.sname,b.a
* FROM student s,
* (SELECT sid,AVG(score)a FROM sc GROUP BY sid)b
* WHERE s.sid = b.sid AND b.a>85;
* 30.
* SELECT x.xsname,sc.score
* FROM (SELECT st.sid xsid,st.sname xsname
* FROM student st WHERE st.sid NOT IN
* (SELECT sc.sid
* FROM sc,
* (SELECT cid FROM course WHERE cname='数学')a
* WHERE
* sc.cid=a.cid
* AND sc.score>=60))X
* LEFT JOIN sc
* ON sc.sid=x.xsid
* AND sc.cid IN
* (SELECT cid FROM course WHERE cname='数学') ；
* 31.
* SELECT st.sid,st.sname,c.cname FROM student st,sc,course c
* WHERE sc.sid=st.sid AND sc.cid=c.cid;
* 32.
* select st.sname,c.cname,sc.score from student st,sc,course cwhere sc.sid=st.sid and sc.cid=c.cid and sc.score>70
* 33.
* SELECT sc.sid,c.cname,sc.score FROM sc,course c
* WHERE sc.cid=c.cid AND sc.score<60 ORDER BY sc.cid DESC;
* 34.
* SELECT st.sid,st.sname,sc.score FROM sc,student st
* WHERE sc.sid=st.sid AND cid='01' AND score>75;
* 35.
* SELECT COUNT(DISTINCT sid) FROM sc;
* 36.
* SELECT st.sname,score FROM student st,sc ,course c,teacher t
* WHERE
* st.sid=sc.sid AND sc.cid=c.cid AND c.tid=t.tid
* AND t.tname='张三' AND sc.score=
* (SELECT MAX(score)FROM sc WHERE sc.cid=c.cid) ;
* 37.
* SELECT cid,COUNT(sid) FROM sc GROUP BY cid;
* 38.
* SELECT st.Sname,bb.cid,bb.score
* FROM student st,
* (SELECT aa.sid,aa.cid,aa.score
* FROM
* (SELECT DISTINCT a.* FROM sc a ,sc b
* WHERE a.score=b.score
* AND a.cid<>b.cid)aa)bb
* WHERE st.sid = bb.sid ;
* 39.
* SELECT a.* FROM sc a
* WHERE 2 > (SELECT COUNT(*) FROM sc WHERE cid = a.cid ANDscore > a.score )
* ORDER BY a.cid,a.score DESC;
* 40.
* SELECT cid,COUNT(sid) FROM sc GROUP BY cid
* HAVING COUNT(sid)>5
* ORDER BY COUNT(sid) DESC,cid ASC;
* 41.
* SELECT sid,COUNT(cid) FROM sc GROUP BY sid
* HAVING COUNT(cid)>=2;
* 42.
* select distinct(c.cid),c.cname from course c ,scwhere sc.cid=c.cid
* 43.
* SELECT st.sname FROM student st
* WHERE st.sid NOT IN
* (SELECT DISTINCT sc.sid FROM sc,course c,teacher t
* WHERE sc.cid=c.cid AND c.tid=t.tid AND t.tname='张三') ;
* 44.
* SELECT sid,AVG(score)FROM sc
* WHERE sid IN
* (SELECT sid FROM sc WHERE sc.score<60
* GROUP BY sid HAVING COUNT(cid)>1) GROUP BY sid ;
* 45.
* SELECT SID
* FROM SC
* WHERE CID='03' AND SCORE<60
* ORDER BY SCORE DESC;
* 46.
* DELETE FROM sc WHERE sid='02' AND cid='01' ;
* 查询每门课前三名
* 创建数据库表数据
* create table ttt (
* id int primary key,
* gid char,
* col1 int,
* score int
* );
* insert into ttt values
* (1,'A',31,6),
* (2,'B',25,83),
* (3,'C',76,21),
* (4,'D',63,56),
* (5,'E',3,17),
* (6,'A',29,97),
* (7,'B',88,63),
* (8,'C',16,22),
* (9,'D',25,43),
* (10,'E',45,28),
* (11,'A',2,78),
* (12,'B',30,79),
* (13,'C',96,73),
* (14,'D',37,40),
* (15,'E',14,86),
* (16,'A',32,67),
* (17,'B',84,38),
* (18,'C',27,9),
* (19,'D',31,21),
* (20,'E',80,63),
* (21,'A',89,9),
* (22,'B',15,22),
* (23,'C',46,84),
* (24,'D',54,79),
* (25,'E',85,64),
* (26,'A',87,13),
* (27,'B',40,45),
* (28,'C',34,90),
* (29,'D',63,8),
* (30,'E',66,40),
* (31,'A',83,49),
* (32,'B',4,90),
* (33,'C',81,7),
* (34,'D',11,12),
* (35,'E',85,10),
* (36,'A',39,75),
* (37,'B',22,39),
* (38,'C',76,67),
* (39,'D',20,11),
* (40,'E',81,36);
* Q&A
* 期望结果
* 1) N=1 取GID每组 score最大的记录
* +----+------+------+------+
* | id | gid | col1 | score |
* +----+------+------+------+
* | 6 | A | 29 | 97 |
* | 15 | E | 14 | 86 |
* | 24 | D | 54 | 79 |
* | 28 | C | 34 | 90 |
* | 32 | B | 4 | 90 |
* +----+------+------+------+
* 2) N=3 取GID每组 score最大的3条记录
* +----+------+------+------+
* | id | gid | col1 | score |
* +----+------+------+------+
* | 6 | A | 29 | 97 |
* | 11 | A | 2 | 78 |
* | 36 | A | 39 | 75 |
* | 32 | B | 4 | 90 |
* | 2 | B | 25 | 83 |
* | 12 | B | 30 | 79 |
* | 28 | C | 34 | 90 |
* | 23 | C | 46 | 84 |
* | 13 | C | 96 | 73 |
* | 24 | D | 54 | 79 |
* | 4 | D | 63 | 56 |
* | 9 | D | 25 | 43 |
* | 15 | E | 14 | 86 |
* | 25 | E | 85 | 64 |
* | 20 | E | 80 | 63 |
* +----+------+------+------+
* Answers:
* 1)
* select a.gid,a.score from ttt a
* where 1>=(
* select count(*) from ttt b
* where a.gid = b.gid and a.score<=b.score)
* order by gid,score desc;
* 2)
* select a.gid,a.score from ttt a
* where 3>=(
* select count(*) from ttt b
* where a.gid = b.gid and a.score<=b.score)
* order by gid,score desc;
网友评论