mysql 练习题加进阶练习

DROP DATABASE IF EXISTS exps;
CREATE DATABASE exps;
USE exps;
CREATE TABLE s1_student(
    sno VARCHAR(10) PRIMARY KEY,
    sname VARCHAR(30),
    sbirthday VARCHAR(30),
    ssex VARCHAR(10),
    sclass VARCHAR(10)
);
CREATE TABLE s1_teacher(
    tno VARCHAR(10) PRIMARY KEY,
    tname VARCHAR(30),
    tsex VARCHAR(10),
    tbirthday VARCHAR(30),
    prof VARCHAR(30),
    depart VARCHAR(30)
);
CREATE TABLE s1_course(
    cno VARCHAR(10),
    cname VARCHAR(30),
    tno VARCHAR(10)
);
CREATE TABLE s1_score(
    sno VARCHAR(10),
    cno VARCHAR(10),
    degree INT
);
CREATE TABLE s1_grade(
    low INT,
    upp INT,
    rank VARCHAR(2)
);
 
INSERT INTO s1_student VALUES('108','曾华','1997/9/1','男','95033');
INSERT INTO s1_student VALUES('105','匡明','1995/10/2','男','95031');
INSERT INTO s1_student VALUES('107','王丽','1996/1/23','女','95033');
INSERT INTO s1_student VALUES('101','李军','1996/2/20','男','95033');
INSERT INTO s1_student VALUES('109','王芳','1995/2/10','女','95031');
INSERT INTO s1_student VALUES('103','陆君','1994/6/3','男','95031');
 
INSERT INTO s1_teacher VALUES('804','李诚','男','1979-12-2','副教授','计算机系');
INSERT INTO s1_teacher VALUES('856','张旭','男','1985-3-12','讲师','电子工程系');
INSERT INTO s1_teacher VALUES('825','王萍','女','1989-5-2','助教','计算机系');
INSERT INTO s1_teacher VALUES('831','刘冰','女','1988-8-2','助教','电子工程系');
 
INSERT INTO s1_course VALUES('3-105','计算机导论','825');
INSERT INTO s1_course VALUES('3-245','操作系统','804');
INSERT INTO s1_course VALUES('6-166','数据电路','856');
INSERT INTO s1_course VALUES('9-888','高等数学','800');
 
INSERT INTO s1_score VALUES('103','3-245',86);
INSERT INTO s1_score VALUES('105','3-245',75);
INSERT INTO s1_score VALUES('109','3-245',68);
INSERT INTO s1_score VALUES('103','3-105',92);
INSERT INTO s1_score VALUES('105','3-105',88);
INSERT INTO s1_score VALUES('109','3-105',76);
INSERT INTO s1_score VALUES('101','3-105',64);
INSERT INTO s1_score VALUES('107','3-105',91);
INSERT INTO s1_score VALUES('108','3-105',78);
INSERT INTO s1_score VALUES('101','6-166',85);
INSERT INTO s1_score VALUES('107','6-166',79);
INSERT INTO s1_score VALUES('108','6-166',81);
 
INSERT INTO s1_grade VALUES('90','100','A');
INSERT INTO s1_grade VALUES('80','89','B');
INSERT INTO s1_grade VALUES('70','79','C');
INSERT INTO s1_grade VALUES('60','69','D');
INSERT INTO s1_grade VALUES('0','59','E');
 
SELECT * FROM s1_student;
SELECT * FROM s1_teacher;
SELECT * FROM s1_course;
SELECT * FROM s1_score;
SELECT * FROM s1_grade;
 
 
SELECT sname,ssex,sclass FROM s1_student;
-- 2,查询教师表中所有不重复的depart列
SELECT DISTINCT depart FROM s1_teacher;
SELECT depart FROM s1_teacher GROUP BY depart;
 
-- 3.查询学生表中所有信息
SELECT * FROM s1_student;
 
-- 4.查询成绩表中成绩在60-80 之间的信息
SELECT * FROM s1_score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM s1_score WHERE degree>=60 AND degree<=80;
 
-- 5.查询成绩表中成绩为85/86/88的信息
SELECT * FROM s1_score WHERE degree IN (85,86,88); 
SELECT * FROM s1_score WHERE degree =85 OR degree =86 OR degree=88; 
 
-- 6.查询s1_student表中“95031”班或性别为“女”的同学记录
SELECT * FROM s1_student WHERE sclass='95031'OR ssex='女';
 
-- 7.以Class降序查询s1_student表的所有记录
SELECT sno,sname,sbirthday,ssex,sclass
FROM s1_student 
ORDER BY sclass 
DESC;
 
-- 8.以Cno升序、Degree降序查询s1_score表的所有记录
SELECT sno,cno,degree FROM s1_score ORDER BY cno ASC,degree DESC;
 
-- 9.查询“95031”班的学生人数
SELECT COUNT(sno) FROM s1_student s WHERE s.sclass='95031';
 
-- 10.查询s1_score表中的最高分的学生学号和课程号
SELECT sno,cno 
FROM s1_score s 
WHERE s.degree>=(SELECT MAX(degree) FROM s1_score s1);
 
-- 11.查询‘3-105’号课程的平均分
SELECT AVG(degree) FROM s1_score WHERE cno='3-105';
 
-- 12.查询s1_score表中至少有5名学生选修的并以3开头的课程的平均分数
SELECT cno,AVG(degree)
FROM s1_score
WHERE cno LIKE '3%'
GROUP BY cno
HAVING COUNT(sno)>=5;
 
SELECT cno,AVG(degree)
FROM s1_score
WHERE SUBSTR(cno,1,1)='3'
GROUP BY cno
HAVING COUNT(sno)>=5;
 
 
-- 13.查询最低分大于70，最高分小于90的Sno列
SELECT sno
FROM s1_score
GROUP BY sno
HAVING MIN(degree)>70 AND MAX(degree)<90;
 
-- 14.查询所有学生的Sname、Cno和Degree列
SELECT s.`sname`,ss.`cno`,ss.`degree`
FROM s1_student s
INNER JOIN s1_score ss
ON s.`sno`=ss.`sno`;
 
-- 15.查询所有学生的Sno、Cname和Degree列
SELECT s.`sno`,c.`cname`,s.`degree`
FROM s1_course c
INNER JOIN s1_score s
ON c.`cno`=s.`cno`;
 
-- 16.查询所有学生的Sname、Cname和Degree列
SELECT stu.`sname`,c.`cname`,s.`degree`
FROM s1_student stu`s1_student`
LEFT JOIN s1_score s
ON s.`sno`=stu.`sno`
LEFT JOIN s1_course c
ON c.`cno`=s.`cno`
 
-- 17.查询“95033”班所选课程的平均分
SELECT s.`cno`,AVG(degree)
FROM s1_score s
INNER JOIN s1_student stu
ON s.`sno`=stu.`sno`
WHERE stu.`sclass`='95033'
GROUP BY s.`cno`
 
-- 18.现查询所有同学的Sno、Cno和rank列
SELECT s.sno,s.cno,g.rank
FROM s1_score s
LEFT JOIN s1_grade g
ON s.`degree`>=g.low AND s.`degree`<=g.upp;
 
SELECT sno,cno,(SELECT rank FROM s1_grade WHERE low<=degree AND upp>=degree)AS rank 
FROM s1_score;
 
-- 19.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
SELECT *
FROM s1_score s
WHERE s.cno='3-105'
AND s.`degree`>(SELECT degree 
        FROM s1_score s 
        WHERE s.`cno`='3-105'
        AND s.`sno`='109');
 
SELECT s.`cno`,s.`degree`,s.`sno`
FROM s1_score s,s1_score ss
WHERE s.`cno`='3-105'
AND s.`degree`>ss.`degree`
AND ss.`cno`='3-105'
AND ss.`sno`='109';
 
-- 20.查询s1_score中选学一门以上课程的同学中分数为非最高分成绩的记录
SELECT *
FROM s1_score s
WHERE s.sno IN(SELECT ss.sno
        FROM s1_score ss
        GROUP BY ss.sno
        HAVING COUNT(ss.cno)>1)
AND s.`degree`<(SELECT MAX(degree)
        FROM s1_score sc
        WHERE sc.`cno`=s.`cno`
);
 
/*取第几高的成绩*/
SELECT *
FROM s1_score s
WHERE s.sno IN(SELECT ss.sno
        FROM s1_score ss
        GROUP BY ss.sno
        HAVING COUNT(ss.cno)>1)
AND s.`degree`IN(SELECT a.degree
        FROM s1_score a
        INNER JOIN s1_score b
        ON a.`degree`<=b.`degree`
        GROUP BY a.`degree`
        HAVING COUNT(b.`degree`)=2 -- 取第几高
);
 
/*取各科目前二高的成绩*/
SELECT * 
FROM s1_score s1
WHERE (
    SELECT COUNT(*)
    FROM s1_score s2
    WHERE s1.`cno`=s2.`cno`
    AND s1.`degree`<s2.`degree`)<2;
 
 
SELECT * FROM s1_score s1 
WHERE
    EXISTS( SELECT COUNT(*)
        FROM s1_score s2
        WHERE s1.`cno`=s2.`cno`
        AND s1.`degree`<s2.`degree`
        HAVING COUNT(*)<2
        );
 
 
 
 
-- 21.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录
SELECT *
FROM s1_score
WHERE degree>(SELECT degree
        FROM s1_score
        WHERE sno='109'
        AND cno='3-105'    
)
AND cno='3-105';
 
-- 22.查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列
SELECT *
FROM s1_student stu
WHERE SUBSTR(stu.`sbirthday`,1,4) =(
            SELECT SUBSTR(stu1.sbirthday,1,4)
            FROM s1_student stu1
            WHERE stu1.sno='108');
 
SELECT *
FROM s1_student stu
WHERE YEAR(stu.`sbirthday`)=(SELECT YEAR(sbirthday)
                FROM s1_student
                WHERE sno='108');
 
 
-- 23.查询“张旭“教师任课的学生成绩
SELECT *
FROM s1_score s
WHERE s.`cno`=(SELECT c.`cno`
        FROM s1_course c
        WHERE c.`tno`=(SELECT t.`tno`
        FROM s1_teacher t
        WHERE t.`tname`='张旭')
);
 
SELECT s.*
FROM s1_score s
INNER JOIN s1_course c
ON c.`cno`=s.`cno`
INNER JOIN s1_teacher t
ON t.`tno`=c.`tno`
WHERE t.`tname`='张旭';
 
-- 24.查询选修某课程的同学人数多于5人的教师姓名。
SELECT t.`tname`
FROM s1_teacher t
WHERE t.`tno`=(SELECT tno
           FROM s1_course
           WHERE cno=( SELECT s.`cno`
               FROM s1_score s
               GROUP BY s.`cno`
               HAVING COUNT(s.`sno`)>5
            )  
);
 
-- 25.查询95033班和95031班全体学生的记录
SELECT *
FROM s1_student s
WHERE s.`sclass`='95031' OR '95033';
 
-- 26.查询存在有85分以上成绩的课程Cno
SELECT cno
FROM s1_score s
WHERE s.`degree`>85
GROUP BY s.`cno`;
 
-- 27.查询出“计算机系“教师所教课程的成绩表
SELECT s.*
FROM s1_score s
WHERE s.`cno` IN( SELECT cno
        FROM s1_course
        WHERE tno IN(SELECT tno
                 FROM s1_teacher t
                 WHERE t.`depart`='计算机系'
                 )
        );
 
-- 28.查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof
SELECT t.`tname`,t.`prof`
FROM s1_teacher t
WHERE t.`depart`='计算机系' OR '电子工程系';
 
-- 29.查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的
-- Cno、Sno和Degree,并按Degree从高到低次序排序
SELECT s.*
FROM s1_score s
WHERE s.`cno`='3-105'
AND s.`degree`>(SELECT MIN(degree)
        FROM s1_score
        WHERE cno='3-245'
        )
ORDER BY s.`degree`DESC;
 
-- 30. 查询选修编号为“ 3-105 ”且成绩高于选修编号为“ 3-245 ”课程的同学的 Cno、Sno 和 Degree.
SELECT s.*
FROM s1_score s
WHERE s.`cno`='3-105'
AND s.`degree`>(SELECT MAX(degree)
        FROM s1_score
        WHERE cno='3-245'
        )
ORDER BY s.`degree`DESC;
 
-- 31. 查询所有教师和同学的 name、sex 和 birthday. 
SELECT sname asname ,ssex FROM s1_student 
UNION ALL
SELECT tname asname ,tsex FROM s1_teacher; 
 
-- 32. 查询所有“女”教师和“女”同学的 name、sex 和 birthday. 
SELECT sname AS NAME ,ssex AS sex,sbirthday AS birthday FROM s1_student WHERE ssex= '女'
UNION ALL
SELECT tname AS NAME ,tsex AS sex,tbirthday AS birthday FROM s1_teacher WHERE tsex= '女' ; 
 
-- 33. 查询成绩比该课程平均成绩低的同学的成绩表。
SELECT *
FROM s1_score s
WHERE s.`degree`<( SELECT AVG(degree)
           FROM s1_score
           WHERE cno=s.`cno`
           GROUP BY cno);
            
-- 34. 查询所有任课教师的 Tname和 Depart.
SELECT tname,depart 
FROM s1_teacher t
INNER JOIN s1_course c
ON t.`tno`=c.`tno`;
 
-- 35. 查询所有未讲课的教师的 Tname和 Depart. 
SELECT tname,depart 
FROM s1_teacher t 
WHERE t.tno NOT IN( SELECT tno 
            FROM s1_course); 
 
-- 36. 查询至少有 2 名男生的班号。
SELECT s.`sclass`
FROM s1_student s
WHERE s.`ssex`='男'
GROUP BY s.`sclass`
HAVING COUNT(s.`ssex`)>=2
 
 
-- 37. 查询 s1_student 表中不姓“王”的同学记录。
SELECT * 
FROM s1_student s
WHERE SUBSTR(s.`sname`,1,1) != '王'; 
 
 
-- 38. 查询 s1_student 表中每个学生的姓名和年龄。
SELECT s.`sname`,YEAR(NOW())-YEAR(s.`sbirthday`) AS age
FROM s1_student s
 
 
-- 39. 查询 s1_student 表中最大和最小的 Sbirthday 日期值。
SELECT MAX(sbirthday), MIN(sbirthday)
FROM s1_student; 
 
-- 40. 以班号和年龄从大到小的顺序查询 s1_student 表中的全部记录。
SELECT * 
FROM s1_student 
ORDER BY sclass DESC,sbirthday ASC;
    
-- 41. 查询“男”教师及其所上的课程。
SELECT th.tno,th.tname,th.tsex,co.cno 
FROM s1_teacher th
INNER JOIN s1_course co 
ON th.tno=co.tno 
WHERE th. tsex= '男' ; 
 
-- 42. 查询最高分同学的 Sno、 Cno 和 Degree 列。
SELECT * 
FROM s1_score 
WHERE degree =( SELECT MAX( degree ) 
        FROM s1_score);
         
         
-- 43. 查询和“李军”同性别的所有同学的 Sname. 
SELECT sname 
FROM s1_student 
WHERE ssex =( SELECT ssex FROM s1_student WHERE sname= '李军' ) 
AND sname!='李军' ;
 
 
-- 44. 查询和“李军”同性别并同班的同学 Sname. 
SELECT stu.sname
FROM s1_student stu
INNER JOIN ( SELECT stu1.`ssex` sex,stu1.`sclass` class
         FROM s1_student stu1
         WHERE stu1.`sname`='李军'
         ) s
ON stu.`ssex`=s.sex
AND stu.`sclass`=s.class;
 
  
-- 45. 查询所有选修“计算机导论”课程的“男”同学的成绩表
SELECT *
FROM s1_score sc
INNER JOIN s1_course c
ON sc.`cno`=c.`cno`
INNER JOIN s1_student stu
ON stu.`sno`=sc.`sno`
WHERE c.`cname`='计算机导论'
AND stu.`ssex`='男';

进阶练习

--查询“ c001 ”课程比“ c002”课程成绩高的所有学生的学号；
select t1.sno,t1.cno,t1.score,t2.cno,t2.score from
(select * from sc
where cno='c001') t1
inner join (select * from sc
where cno='c002')t2 on t1.sno = t2.sno
where t1.score>t2.score;
 
select s1.* from sc s1 where s1.cno='c00l' and
exists (select * from sc s2 where s2.sno=s1.sno
and s2.score<s1.score and s2.cno='c002');
--2查询平均成绩大于60 分的同学的学号和平均成绩；
select sno,round(avg(score),2) avg_score from sc
group by sno
having round(avg(score),2)>60;
--3查询所有同学的学号、姓名、选课数、总成绩;
select t.sno,t.sname,count(t.score) class_count,sum(nvl(t.score,0)) sum_score from(
select st.sno,st.sname,sc.score from student st
left join sc on sc.sno=st.sno)t
group by t.sno,t.sname
--4查询姓“刘”的老师的个数；
select count(tname) count from teacher
where substr(tname,1,1)= '刘'
--5查询没学过“谌燕”老师课的同学的学号、姓名；
select st.sno,st.sname from student st
left join(
select sno from sc
left join(
select cno from course
where tno=(select tno from teacher where tname='谌燕'))t on t.cno=sc.cno
group by sno)t on t.sno=st.sno
where t.sno is null
order by st.sno;
 
select st.sno,st.sname from student st
where  not exists
(select sc.sno from teacher te
inner join course co on te.tno=co.tno and te.tname='谌燕'
inner join sc on sc.cno= co.cno where sc.sno =st.sno group by sc.sno);
--6查询学过“ c001”并且也学过编号“ c002”课程的同学的学号、姓名；
select st.sno,st.sname from student st 
right join(
select sno from sc s1 where s1.cno='c001' and
exists(select * from sc s2 where s1.sno=s2.sno and s2.cno='c002'))t on t.sno=st.sno
 
--7.查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
 
-- 8查询课程编号“ c002”的成绩比课程编号“ c001”课程低的所有同学的学号、姓名；
select st.sno,st.sname from student st
right join(
select s1.sno,s1.cno,s1.score from sc s1
where s1.cno='c001' and exists (
select * from sc s2 where s1.sno=s2.sno and s1.score>s2.score and s2.cno='c002'
)) t on t.sno=st.sno;
 
-- 9查询所有课程成绩小于60 分的同学的学号、姓名；
select st.sno,st.sname from student st
right join(
select sno,max(score) min_score from sc
group by sno
having max(score)<60) t on st.sno=t.sno;
 
select st.sno,st.sname from student st
where exists(
select sno,max(score) max_score from sc
where sc.sno=st.sno
group by sno
having max(score)<60 );
-- 10查询没有学全所有课的同学的学号、姓名
select st.sno,st.sname from student st
left join sc s on s.sno=st.sno
group by st.sno
having count(s.score)<(select count(cno) from course);
-- 11查询至少有一门课与学号为“ s001”的同学所学相同的同学的学号和姓名；
select st.sno,st.sname from student st
right join(
select sc.sno ,count(sc.score) from sc
left join(
select cno c from sc where sno='s001') t on t.c=sc.cno
where sc.sno != 's001' group by sc.sno having count(sc.score)>=1)t2 on t2.sno= st.sno;
 
select distinct st.sno,st.sname from student st
inner join sc s1  on s1.sno= st.sno
where exists(select cno from sc s2 where s1.cno=s2.cno and s2.sno='s001')
and st.sno != 's001';
-- 12查询至少学过学号为 “s002” 同学所有门课的其他同学学号和姓名；
select st.sno,st.sname from student st
right join(
select sc.sno ,count(sc.score) from sc
left join(
select cno c from sc where sno='s002') t on t.c=sc.cno
where sc.sno != 's002' group by sc.sno having count(sc.score)>=(select count(score) from sc where sno='s002'))t2 on t2.sno= st.sno;
 
select st.sno,st.sname from student st
inner join sc s1 on s1.sno=st.sno
where exists(select s2.cno from sc s2 where s1.cno=s2.cno and s2.sno='s002') and st.sno != 's002'
group by st.sno, st.sname
having count(s1.score)>=(select count(score) from sc where sno='s002');
-- 13 把“ SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；
select * from sc s
where exists(select t.* from(
select co.cno from course co
where exists(select tno from teacher te
where te.tname='谌燕' and te.tno=co.tno))t where t.cno=s.cno);
 
update sc s1 set score =(select avg(score) from sc s2) where exists(select co.cno from course co
where exists(select tno from teacher te where te.tname='谌燕' and te.tno=co.tno)and c0.cno=s1.cno)
 
select co.cno from course co
where exists(select tno from teacher te
where te.tname='谌燕' and te.tno=co.tno)
-- 14查询和“ s002”号的同学学习的课程完全相同的其他同学学号和姓名；
select st.sno,st.sname from student st
inner join(
select sc.sno ,count(sc.score) from sc
left join(
select cno c from sc where sno='s002') t on t.c=sc.cno
where sc.sno != 's002' group by sc.sno having count(sc.score)=(select count(score) from sc where sno='s002'))t2 on t2.sno= st.sno;
 
select st.sno,st.sname from student st
inner join (select sno,count(cno) from sc group by sno having count(cno)=(select count(score) from sc where sno ='s002')) t on st.sno = t.sno left join sc s1 on t.sno=s1.sno 
where exists(select * from sc s2 where s1.cno=s2.cno and s2.sno='s002') and s1.sno != 's002'
group by  st.sno, st.sname
 
 
-- 15删除学习“谌燕”老师课的SC 表记录；
-- 16向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“ c002 ”课程的同学学号、“ c002”号课的平均成绩
insert into sc (sno,cno,score) select sno ,'c002' cno ,
(select round(avg(score),2) from sc where sc.cno='c002') avg_core
from student st
where not exists (select sno from sc where st.sno = sc.sno and sc.cno != 'c002');
commit;
select * from sc order by sno;
 
delete from sc where exists (select sno from student st
where not exists (select sno from sc where st.sno = sc.sno and sc.cno != 'c002')and sc.sno=st.sno)
commit;
 
-- 17查询各科成绩最高和最低的分：以如下形式显示：课程ID ，最高分，最低分
-- 18按各科平均成绩从低到高和及格率的百分数从高到低顺序；
 
-- 19查询不同老师所教不同课程平均分从高到低显示；
-- 20统计列印各科成绩, 各分数段人数: 课程ID, 课程名称,[100-85],[85-70],[70-60],[ <60]
select sc.cno,c.cname,
sum(case when sc.score between 85 and 100 then 1 else 0 end)"[100-85]",
sum(case when sc.score between 70 and 85 then 1 else 0 end) "[85-70]",
sum(case when sc.score between 60 and 70 then 1 else 0 end) "[70-60]",
sum(case when sc.score <60 then 1 else 0 end) "[<60]"
from  sc 
inner join course c on sc.cno = c.cno
group by sc.cno, c.cname;
-- 21查询各科成绩前三名的记录:( 不考虑成绩并列情况) ；
select * from (select sc.* ,row_number() over(partition by cno order by score desc) rank  from sc)
where rank <= 3
 
select cno,score from sc s1 where (
select count(score) from sc s2 where s1.score<s2.score and s1.cno=s2.cno)<3
order by cno ,score desc;
-- 22查询每门课程被选修的学生数
select co.cno,co.cname ,count(sno)
from sc 
right join course co on sc.cno=co.cno
group by co.cno,co.cname
-- 23查询出只选修了一门课程的全部学生的学号和姓名；
select sc.sno,st.sname ,count(score) from sc 
inner join student st on st.sno=sc.sno
group by sc.sno,st.sname
having count(score)=1
-- 24查询男生、女生人数；
select sum(case when ssex='男' then 1 else 0 end) "男生人数",
       sum(case when ssex='女' then 1 else 0 end) "女生人数"  
from student;
 
select ssex ,count(sno) from student 
group by ssex;
 
-- 25查询姓“张”的学生名单
select st.sno,st.sname from student st 
where substr(st.sname,1,1)='张';
-- 26查询同名同性学生名单，并统计同名人数；
select s1.sname,count(sno) from student s1 
where exists(select * from student s2 where s1.sname=s2.sname and s1.ssex =s2. ssex)
group by s1.sname;
  
select sname ,ssex,count(sno) from student 
group by sname,ssex
having count(sno)>1 
-- 27 1991 年出生的学生名单( 注： Student 表中Sage 列的类型是number)
select sno,sname, extract( year from sysdate)-sage birthaday from student
where extract( year from sysdate)-sage=1991
-- 28查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select cno,round(avg(score),2) from sc 
group by cno
order by avg(score),cno desc;
-- 29查询平均成绩大于85 的所有学生的学号、姓名和平均成绩；
select sc.sno,s.sname,avg(sc.score) from sc
inner join student s on sc.sno = s.sno
group by sc.sno, s.sname
having avg(sc.score)>85;
-- 30查询课程名称为“数据库”，且分数低于60 的学生姓名和分数
select s.sname ,sc.score from  sc
inner join student s on sc.sno = s.sno
where sc.score<60 and sc.cno=(select cno from course where cname='数据库');
-- 31查询所有学生的选课情况；
select st.sno,if(s.cno is not null,s.cno,'未选课') from student st
left join sc s on st.sno = s.sno;
-- 32查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；
select st.sno, st.sname,c.cname,s.score from student st
inner join sc s on st.sno = s.sno
inner join course c on s.cno = c.cno
where exists(select * from(select sno,min(score) from sc group by sno having min(score)>70) t where t.sno=st.sno);
-- 33查询不及格的课程，并按课程号从大到小排列；
select cno ,score  from sc
where score<60
order by cno desc;
-- 34查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名
select st.sno ,st.sname from student st
inner join sc s on st.sno = s.sno
where s.cno='c001' and s.score>80;
-- 35求选了课程的学生人数；
select count(*) from(select sno from sc
group by sno)t;
-- 36查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
select sc.sno,st.sname,sc.score from sc
inner join
student st on st.sno =sc.sno
where sc.score=(select max(t1.score) from (
select sno,score from sc
where exists(select * from
(select cno from  course co inner join teacher t on co.tno = t.tno and t.tname='谌燕')t
where t.cno=sc.cno)) t1);
-- 37查询各个课程及相应的选修人数；
select c.cno ,count(sno)  from sc
right join course c on sc.cno = c.cno
group by c.cno;
-- 38查询不同课程成绩相同的学生的学号、课程号、学生成绩；
select  s1.sno,s1.cno,s1.score,s2.cno,s2.score from sc s1
inner join sc s2 on s1.cno != s2.cno and s1.score =s2.score and s1.sno=s2.sno
-- 39查询每门功课成绩最好的前两名；
select * from(
select sc.*,row_number() over (partition by cno order by score desc ) rank  from sc)
where rank<=2;
-- 40统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列 若人数相同，按课程号升序排列；
select co.cno,(case when count(sno)>10 then to_char(count(sno)) when count(sno) between 1 and 10  then '选修人数小于10人' else '未有人选修该课程' end) count_st from sc 
right join course co on co.cno=sc.cno
group by co.cno
order by count_st desc,cno asc;
-- 41检索至少选修两门课程的学生学号；
select sno, count(cno) from sc
group by sno
having count(cno)>=2;
-- 42查询全部学生都选修的课程的课程号和课程名；
select sc.cno, c.cname,count(sno)  from sc
inner  join  course c on sc.cno = c.cno
group by sc.cno, c.cname
having count(sno)=(select count(sno) from student);
 
-- 43查询没学过“谌燕”老师讲授的任一门课程的学生姓名；
select st.sno,st.sname from student st
left join(
select sno from sc
where exists(select * from (select cno from course
inner join teacher t on course.tno = t.tno and t.tname='谌燕')t
where t.cno=sc.cno)
group by sno) t on t.sno=st.sno
where t.sno is null;
-- 44查询两门以上不及格课程的同学的学号及其平均成绩；
select sno ,count(score),avg(score) from sc
where score <60
group by sno
having count(score)>=1;
-- 45检索“ c004”课程分数小于60，按分数降序排列的同学学号；
select cno ,score from sc
where cno='c004' and score<60
group by score, cno
order by score;
-- 46删除“ s002”同学的“ c001”课程的成绩；
delete from sc where sno='s002' and cno='c001';
insert into sc  values('s002','c001',80.9