数据库DQL---练习题01

--学生表
CREATE TABLE STUD(
    SID VARCHAR2(10),
    SNAME VARCHAR2(30),
    SAGE DATE,
    SGENDER VARCHAR2(3)
);

--课程表
CREATE TABLE COU(
	CID VARCHAR2(10),
	CNAME VARCHAR2(30),
	TID VARCHAR2(10)
);

--教师表
CREATE TABLE TEAC(
	TID VARCHAR2(10),
	TNAME VARCHAR2(30)
);

--成绩表（选课表）
CREATE TABLE SCO(
	SID VARCHAR2(10),
	CID VARCHAR2(10),
	SCORE NUMBER(3)
);


--数据准备
insert into STUD values('01' , '赵雷' , TO_DATE('1990-01-01','YYYY-MM-DD') , '男');
insert into STUD values('02' , '钱电' , TO_DATE('1990-12-21','YYYY-MM-DD') , '男');
insert into STUD values('03' , '孙风' , TO_DATE('1990-12-20','YYYY-MM-DD') , '男');
insert into STUD values('04' , '李云' , TO_DATE('1990-12-06','YYYY-MM-DD') , '男');
insert into STUD values('05' , '周梅' , TO_DATE('1991-12-01','YYYY-MM-DD') , '女');
insert into STUD values('06' , '吴兰' , TO_DATE('1992-01-01','YYYY-MM-DD') , '女');
insert into STUD values('07' , '郑竹' , TO_DATE('1989-01-01','YYYY-MM-DD') , '女');
insert into STUD values('09' , '张三' , TO_DATE('2017-12-20','YYYY-MM-DD') , '女');
insert into STUD values('10' , '李四' , TO_DATE('2017-12-25','YYYY-MM-DD') , '女');
insert into STUD values('11' , '李四' , TO_DATE('2012-06-06','YYYY-MM-DD') , '女');
insert into STUD values('12' , '赵六' , TO_DATE('2013-06-13','YYYY-MM-DD') , '女');
insert into STUD values('13' , '孙七' , TO_DATE('2014-06-01','YYYY-MM-DD') , '女');
COMMIT;

insert into COU values('01' , '语文' , '02');
insert into COU values('02' , '数学' , '01');
insert into COU values('03' , '英语' , '03');
insert into COU values('04' , '物理' , '04');
insert into COU values('05' , '化学' , '05');
COMMIT;

insert into TEAC values('01' , '张三');
insert into TEAC values('02' , '李四');
insert into TEAC values('03' , '王五');
insert into TEAC values('04' , '赵六');
insert into TEAC values('05' , '林瀚');
COMMIT;

insert into SCO values('01' , '01' , 80);
insert into SCO values('01' , '02' , 90);
insert into SCO values('01' , '03' , 99);
insert into SCO values('02' , '01' , 70);
insert into SCO values('02' , '02' , 60);
insert into SCO values('02' , '03' , 80);
insert into SCO values('03' , '01' , 80);
insert into SCO values('03' , '02' , 80);
insert into SCO values('03' , '03' , 80);
insert into SCO values('04' , '01' , 50);
insert into SCO values('04' , '02' , 30);
insert into SCO values('04' , '03' , 20);
insert into SCO values('05' , '01' , 76);
insert into SCO values('05' , '02' , 87);
insert into SCO values('06' , '01' , 31);
insert into SCO values('06' , '03' , 34);
insert into SCO values('07' , '01' , 99);
insert into SCO values('07' , '02' , 89);
insert into SCO values('07' , '03' , 98);
insert into SCO values('07' , '04' , 100);
COMMIT;


--查询同时存在" 01 "课程和" 02 "课程的情况
SELECT S1.*,S2.* FROM SCO S1 JOIN SCO S2 ON S1.SID = S2.SID WHERE S1.CID='01' AND S2.CID='02';

SELECT * FROM 
    (SELECT * FROM SCO WHERE SCO.CID = '01') T1, 
    (SELECT * FROM SCO WHERE SCO.CID = '02') T2
WHERE T1.SID = T2.SID;


--查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT S.*,T.* FROM STUD S JOIN (
SELECT S1.SID SID,S1.CID CID1,S1.SCORE SCORE1,S2.CID CID2,S2.SCORE SCORE2
FROM SCO S1 JOIN SCO S2 ON S1.SID = S2.SID WHERE S1.CID = '01' AND S2.CID = '02'
AND S1.SCORE > S2.SCORE) T ON S.SID = T.SID ;

--查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT * FROM  (SELECT * FROM SCO WHERE SCO.CID = '01')  T1
LEFT JOIN (SELECT * FROM SCO WHERE SCO.CID = '02')  T2 ON T1.SID = T2.SID;


--查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT * FROM SCO WHERE SCO.SID NOT IN (  SELECT SID FROM SCO WHERE SCO.CID = '01'
) AND SCO.CID= '02';


--查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT S.SID,S.SNAME,SCT.SC_AVG FROM STUD S JOIN (SELECT SID,AVG(SCORE) SC_AVG FROM SCO GROUP BY SID HAVING AVG(SCORE)>=60) SCT ON S.SID=SCT.SID;


--查询在 SC 表存在成绩的学生信息
SELECT * FROM STUD WHERE SID IN(SELECT DISTINCT SID FROM SCO);


--查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
SELECT S.SID,S.SNAME,T.CNT,T.SC_SUM FROM STUD S LEFT JOIN (SELECT SID,COUNT(*) CNT,SUM(SCORE) SC_SUM FROM SCO GROUP BY SID) T ON S.SID=T.SID;


--查有成绩的学生信息
SELECT * FROM STUD WHERE SID IN(SELECT SID FROM SCO WHERE SCORE IS NOT NULL);


--查询「李」姓老师的数量
SELECT COUNT(*) FROM TEAC T WHERE T.TNAME LIKE '李%';


--查询学过「张三」老师授课的同学的信息
SELECT * FROM STUD WHERE SID IN(SELECT SID FROM SCO WHERE CID IN(SELECT CID FROM COU WHERE TID=(SELECT T.TID FROM TEAC T WHERE T.TNAME = '张三')));

SELECT * FROM STUD S JOIN SCO SC ON S.SID = SC.SID JOIN COU C ON C.CID = SC.CID JOIN TEAC T ON T.TID = C.TID WHERE T.TNAME = '张三';


--查询没有学全所有课程的同学的信息
SELECT * FROM STUD WHERE SID IN(
SELECT T.SID FROM (SELECT S.SID,SC.CID CID FROM STUD S LEFT JOIN SCO SC ON S.SID = SC.SID) T
GROUP BY T.SID HAVING COUNT(T.CID)<(SELECT COUNT(*) FROM COU));

SELECT * FROM STUD WHERE STUD.SID NOT IN (SELECT SCO.SID FROM SCO
GROUP BY SCO.SID HAVING COUNT(SCO.CID)= (SELECT COUNT(CID) FROM COU) );


--查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT * FROM STUD WHERE SID IN(SELECT DISTINCT SID FROM SCO WHERE CID IN(SELECT CID FROM SCO C WHERE SID='01')  AND SID<>'01');


--查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
WITH TMP AS
(SELECT COUNT(*) CNT FROM SCO WHERE SID = '01')
SELECT * FROM STUD WHERE SID IN(
SELECT SID FROM
(SELECT S.SID,S.SNAME,SC.CID FROM STUD S LEFT JOIN SCO SC ON S.SID = SC.SID) T1
LEFT JOIN (SELECT CID FROM SCO WHERE SID = '01') T2 ON T1.CID = T2.CID
GROUP BY T1.SID HAVING COUNT(T2.CID)=(SELECT CNT FROM TMP) AND COUNT(T1.CID)=(SELECT CNT FROM TMP));


--查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT * FROM STUD S WHERE S.SID NOT IN(
SELECT SC.SID FROM SCO SC WHERE SC.CID IN
(SELECT C.CID FROM COU C JOIN TEAC T ON C.TID = T.TID WHERE T.TNAME = '张三'));


--查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT S.SID,S.SNAME,T1.AVG_SC FROM STUD S JOIN 
(SELECT SC.SID,AVG(SC.SCORE) AVG_SC FROM SCO SC GROUP BY SC.SID) T1 ON S.SID=T1.SID JOIN 
(SELECT SC.SID FROM SCO SC WHERE SC.SCORE < 60 GROUP BY SC.SID HAVING COUNT(*)>=2) T2 ON T1.SID=T2.SID;


--检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT * FROM STUD S JOIN SCO SC ON S.SID=SC.SID WHERE SC.CID='01' AND SC.SCORE <60 ORDER BY SC.SCORE DESC;


--按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT SC.SID,SC.CID,SC.SCORE,T1.AVG_SC FROM SCO SC LEFT JOIN  
(SELECT SC.SID,AVG(SC.SCORE) AVG_SC FROM SCO SC GROUP BY SC.SID) T1 ON SC.SID=T1.SID
 ORDER BY T1.AVG_SC DESC;


--查询各科成绩最高分、最低分和平均分、及格率，中等率，优良率，优秀率,要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT SC.CID 课程号,COUNT(*) 选修人数,MAX(SC.SCORE) 最高分,MIN(SC.SCORE) 最低分,ROUND(AVG(SC.SCORE),2) 平均分, 
   ROUND(SUM(CASE WHEN SC.SCORE>=60 THEN 1 ELSE 0 END)/COUNT(*),4)*100||'%' 及格率,
   ROUND(SUM(CASE WHEN SC.SCORE>=70 AND SC.SCORE<80 THEN 1 ELSE 0 END)/COUNT(*),4)*100||'%' 中等率,
   ROUND(SUM(CASE WHEN SC.SCORE>=80 AND SC.SCORE<90 THEN 1 ELSE 0 END)/COUNT(*),4)*100||'%' 优良率,
   ROUND(SUM(CASE WHEN SC.SCORE>=90 THEN 1 ELSE 0 END)/COUNT(*),4)*100||'%' 优秀率
FROM SCO SC GROUP BY SC.CID ORDER BY 选修人数,课程号;


--按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
SELECT SC1.SID,SC1.CID,SC1.SCORE,COUNT(SC2.SCORE)+1 RK FROM SCO SC1 LEFT JOIN SCO SC2 
ON SC1.CID=SC2.CID AND SC1.SCORE < SC2.SCORE 
GROUP BY SC1.CID,SC1.SID,SC1.SCORE
ORDER BY SC1.CID,RK ASC;

SELECT SC.*,RANK()OVER(PARTITION BY SC.CID ORDER BY SC.SCORE DESC)FROM SCO SC;


--查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT T1.SID,T1.SUM_SC,COUNT(T2.SUM_SC)+1 RK FROM 
(SELECT SC.SID,SUM(SC.SCORE) SUM_SC FROM SCO SC GROUP BY SC.SID) T1 LEFT JOIN 
(SELECT SC.SID,SUM(SC.SCORE) SUM_SC FROM SCO SC GROUP BY SC.SID) T2
ON T1.SUM_SC<T2.SUM_SC GROUP BY T1.SID,T1.SUM_SC ORDER BY RK;


--统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT C.CID,C.CNAME,T.SUMA,T.SUMAR,T.SUMB,T.SUMBR,T.SUMC,T.SUMCR,T.SUMD,T.SUMDR FROM COU C LEFT JOIN 
(SELECT SC.CID,
   SUM(CASE WHEN SC.SCORE BETWEEN 85 AND 100 THEN 1 ELSE 0 END) SUMA,
   ROUND(SUM(CASE WHEN SC.SCORE BETWEEN 85 AND 100 THEN 1 ELSE 0 END)/COUNT(*),4)*100||'%' SUMAR,
   SUM(CASE WHEN SC.SCORE BETWEEN 70 AND 85 THEN 1 ELSE 0 END) SUMB,
   ROUND(SUM(CASE WHEN SC.SCORE BETWEEN 70 AND 85 THEN 1 ELSE 0 END)/COUNT(*),4)*100||'%' SUMBR,
   SUM(CASE WHEN SC.SCORE BETWEEN 60 AND 70 THEN 1 ELSE 0 END) SUMC,
   ROUND(SUM(CASE WHEN SC.SCORE BETWEEN 60 AND 70 THEN 1 ELSE 0 END)/COUNT(*),4)*100||'%' SUMCR,
   SUM(CASE WHEN SC.SCORE BETWEEN 0 AND 60 THEN 1 ELSE 0 END) SUMD,
   ROUND(SUM(CASE WHEN SC.SCORE BETWEEN 0 AND 60 THEN 1 ELSE 0 END)/COUNT(*),4)*100||'%' SUMDR
FROM SCO SC GROUP BY SC.CID) T ON C.CID = T.CID;


--查询各科成绩前三名的记录
SELECT * FROM
(SELECT SC.*,RANK()OVER(PARTITION BY SC.CID ORDER BY SCORE DESC) RK FROM SCO SC) T
WHERE T.RK<=3;


--查询每门课程被选修的学生数
SELECT C.CID,COUNT(SC.SID) FROM COU C LEFT JOIN SCO SC ON C.CID = SC.CID GROUP BY C.CID;


--查询出只选修两门课程的学生学号和姓名
SELECT S.SID,S.SNAME FROM STUD S WHERE S.SID IN(SELECT SC.SID FROM SCO SC GROUP BY SC.SID HAVING COUNT(SC.CID)=2);


--查询男生、女生人数
SELECT S.SGENDER,COUNT(*) RS FROM STUD S GROUP BY S.SGENDER;


--查询名字中含有「风」字的学生信息
SELECT * FROM STUD S WHERE S.SNAME LIKE '%风%';


--查询同名学生名单，并统计同名人数(找到同名的名字并统计个数)
SELECT S.SNAME,COUNT(*) FROM STUD S GROUP BY S.SNAME HAVING COUNT(*)>=2;


--查询 1990 年出生的学生名单
SELECT S.*,TO_CHAR(S.SAGE,'YYYY') FROM STUD S WHERE TO_CHAR(S.SAGE,'YYYY') = '1990';


--查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT SC.CID,AVG(SCORE) AVG_SC FROM SCO SC GROUP BY SC.CID ORDER BY AVG_SC DESC,SC.CID;


--查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT S.SID,S.SNAME,AVG(SC.SCORE) AVG_SC 
FROM STUD S LEFT JOIN SCO SC ON S.SID=SC.SID 
GROUP BY S.SID,S.SNAME HAVING AVG(SC.SCORE)>=85;


--查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT S.SID,S.SNAME,T.SCORE FROM STUD S JOIN 
(SELECT SID,SCORE FROM SCO WHERE CID=(SELECT CID FROM COU WHERE CNAME='数学') AND SCORE<60) T
ON S.SID=T.SID;


--查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT S.*,SC.CID,SC.SCORE FROM STUD S LEFT JOIN SCO SC ON S.SID=SC.SID;


--查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT S.SNAME,C.CNAME,T.SCORE FROM STUD S  
JOIN (SELECT * FROM SCO WHERE SCORE>70) T ON S.SID=T.SID 
JOIN COU C ON T.CID=C.CID;


--查询存在不及格的课程
SELECT CID FROM SCO WHERE SCORE<60 GROUP BY CID;

SELECT DISTINCT CID FROM SCO WHERE SCORE<60;


--查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
SELECT SID,SNAME FROM STUD WHERE SID IN(SELECT SID FROM SCO WHERE CID='01' AND SCORE>=80);


--求每门课程的学生人数
SELECT C.CID,COUNT(SID) RS FROM COU C LEFT JOIN SCO SC ON C.CID=SC.CID GROUP BY C.CID;


--成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT T1.*,ROWNUM FROM 
(SELECT S.*,SC.CID,SC.SCORE FROM STUD S JOIN SCO SC ON S.SID=SC.SID 
JOIN COU C ON SC.CID=C.CID 
JOIN TEAC T ON C.TID=T.TID WHERE T.TNAME ='张三' ORDER BY SCORE DESC) T1
WHERE ROWNUM=1;


--成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT S.*,T3.SCORE FROM STUD S JOIN 
(SELECT T1.SID,T1.SCORE,COUNT(T2.SCORE)+1 RK FROM
(SELECT S.SID,SC.SCORE,SC.CID FROM STUD S JOIN SCO SC ON S.SID=SC.SID JOIN COU C ON C.CID=SC.CID JOIN TEAC T ON C.TID=T.TID 
WHERE T.TNAME='张三') T1 LEFT JOIN 
(SELECT S.SID,SC.SCORE,SC.CID FROM STUD S JOIN SCO SC ON S.SID=SC.SID JOIN COU C ON C.CID=SC.CID JOIN TEAC T ON C.TID=T.TID 
WHERE T.TNAME='张三') T2 ON T1.SCORE<T2.SCORE
GROUP BY T1.SID,T1.CID,T1.SCORE) T3 ON S.SID=T3.SID WHERE RK=1;


--查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT SC1.SID,SC1.CID,SC1.SCORE FROM SCO SC1 JOIN SCO SC2 ON SC1.SID=SC2.SID AND SC1.CID<>SC2.CID 
WHERE SC1.SCORE=SC2.SCORE 
GROUP BY SC1.SID,SC1.CID,SC1.SCORE;


--查询每门功成绩最好的前两名
SELECT * FROM 
(SELECT SCO.*,DENSE_RANK()OVER(PARTITION BY SCO.CID ORDER BY SCO.SCORE DESC) RK FROM SCO) T 
WHERE RK<=2;


--统计每门课程的学生选修人数（超过 5 人的课程才统计）
SELECT C.CID,COUNT(S.SID) CNT FROM COU C LEFT JOIN SCO S ON C.CID = S.CID GROUP BY C.CID
HAVING COUNT(S.SID)>5;


--检索至少选修两门课程的学生学号
SELECT S.SID,COUNT(SC.CID) FROM STUD S LEFT JOIN SCO SC ON S.SID=SC.SID GROUP BY S.SID
HAVING COUNT(SC.CID)>=2;


--查询选修了全部课程的学生信息
SELECT * FROM STUD WHERE SID IN(
SELECT SID FROM SCO GROUP BY SID HAVING COUNT(CID)=(SELECT COUNT(CID) FROM COU));


--查询各学生的年龄，只按年份来算
SELECT S.SNAME,MONTHS_BETWEEN(SYSDATE,S.SAGE)/12 FROM STUD S;


--按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
SELECT T.DT1,T.DT2,SAGE,CASE WHEN T.DT1<T.DT2 THEN ADD_MONTHS(T.SAGE,12) ELSE T.SAGE END NEW_SAGE FROM
(SELECT TO_CHAR(SYSDATE,'MM/DD') DT1,TO_CHAR(S.SAGE,'MM/DD') DT2,S.SAGE FROM STUD S) T;


--查询本周过生日的学生
SELECT * FROM STUD S WHERE TO_CHAR(S.SAGE,'WW')=TO_CHAR(SYSDATE,'WW');


--查询下周过生日的学生
SELECT * FROM STUD S WHERE TO_CHAR(S.SAGE,'WW')=TO_CHAR(SYSDATE,'WW')+1;


--查询本月过生日的学生
SELECT * FROM STUD S WHERE TO_CHAR(S.SAGE,'MM')=TO_CHAR(SYSDATE,'MM');


--查询下月过生日的学生
SELECT * FROM STUD S WHERE TO_CHAR(S.SAGE,'MM')=TO_CHAR(SYSDATE,'MM')+1;