50条SQL查询语句--Oracle环境

来源：互联网发布：jQuery数组方法大全编辑：程序博客网时间：2024/06/12 21:44
--有以下表的表结构：--学生表 (学号,姓名,出生日期,性别) 学生表 (注：学生表中出生日期类型是date)-- Student  Sno   Sname Sbirthday Ssex--课程表 (课程号,课程名,教师编号) -- Course   Cno  Cname   Tno--成绩表 (学号, 课程号, 分数) -- Score  Sno   Cno   Degree--教师表 (教师编号, 教师名称) -- Teacher  Tno      Tname--问题： --1、查询“001”课程比“002”课程成绩高的所有学生的学号； 　　SELECT T.SNO FROM STUDENT T WHERE          ( SELECT S.DEGREE FROM SCORE S WHERE S.CNO = '001' AND S.SNO = T.SNO )                 > (SELECT S.DEGREE FROM SCORE S WHERE S.CNO = '002' AND S.SNO = T.SNO );         --2、查询平均成绩大于60分的同学的学号和平均成绩；      SELECT T.SNO, AVG(T.DEGREE) FROM SCORE T GROUP BY T.SNO HAVING AVG(T.DEGREE) > 60;--3、查询所有同学的学号、姓名、选课数、总成绩；      SELECT T.SNO, T.SNAME,           (SELECT COUNT(S.CNO) FROM SCORE S WHERE S.SNO = T.SNO) AS 选课数,          (SELECT SUM(S.DEGREE) FROM SCORE S WHERE S.SNO = T.SNO) AS 总成绩 FROM STUDENT T;     --4、查询姓“李”的老师的个数；      SELECT COUNT(1) FROM TEACHER T WHERE T.TNAME LIKE '李%';     --5、查询没学过“叶平”老师课的同学的学号、姓名；      SELECT S.SNO, S.SNAME FROM STUDENT S WHERE S.SNO IN (               SELECT DISTINCT SC.SNO  FROM SCORE SC WHERE SC.CNO NOT IN (                      SELECT C.CNO FROM COURSE C WHERE C.TNO = ( SELECT T.TNO FROM TEACHER T WHERE T.TNAME = '叶平')));        --6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；      SELECT T.SNO,T.SNAME FROM STUDENT T WHERE             (SELECT COUNT(1) FROM SCORE S WHERE S.CNO = '001' AND S.SNO = T.SNO) > 0                     AND (SELECT COUNT(1) FROM SCORE S WHERE S.CNO = '002' AND S.SNO = T.SNO) > 0;--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；          SELECT S.SNO, S.SNAME FROM STUDENT S WHERE                 NOT EXISTS (SELECT C.CNO FROM COURSE C JOIN TEACHER T ON T.TNO = C.TNO WHERE                     T.TNAME = '叶平' AND C.CNO NOT IN (SELECT SC.CNO FROM SCORE SC WHERE SC.SNO = S.SNO));                  --8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；          SELECT T.SNO,T.SNAME FROM STUDENT T WHERE                (SELECT S.DEGREE FROM SCORE S WHERE S.SNO = T.SNO AND S.CNO = '002')                         > (SELECT S.DEGREE FROM SCORE S WHERE S.SNO = T.SNO AND S.CNO = '001');--9、查询所有课程成绩小于60分的同学的学号、姓名；          SELECT T.SNO,T.SNAME FROM STUDENT T WHERE T.SNO NOT IN (SELECT S.SNO FROM SCORE S WHERE S.DEGREE > 60 AND S.SNO = T.SNO);--10、查询没有学全所有课的同学的学号、姓名；          SELECT T.SNO,T.SNAME FROM STUDENT T WHERE                (SELECT COUNT(S.DEGREE) FROM SCORE S WHERE S.SNO = T.SNO)                 < (SELECT COUNT(C.CNO) FROM COURSE C);--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；          SELECT T.SNO,T.SNAME FROM STUDENT T WHERE T.SNO IN                (SELECT DISTINCT S.SNO FROM SCORE S WHERE S.CNO IN (SELECT S.CNO FROM SCORE S WHERE S.SNO = '1001'));                --12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名； --此题‘所有’和‘一门’有矛盾--若为‘所有课’         SELECT T.SNO,T.SNAME  FROM STUDENT T WHERE T.SNO IN                (SELECT DISTINCT S.SNO FROM SCORE S WHERE S.CNO NOT IN (SELECT S.CNO FROM SCORE S SHWRE S.SNO = '001'));--若为‘一门课’则与11题相同--13、把成绩表表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；           UPDATE SCORE S SET S.DEGREE = (SELECT AVG(SC.DEGREE) FROM SCORE SC WHERE SC.SNO = S.CNO)  WHERE                  S.CNO IN (SELECT C.CNO FROM COURSE C JOIN TEACHER T ON T.TNO = C.TNO WHERE T.TNAME = '叶平' );          COMMIT;--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；         SELECT T.SNO,T.SNAME FROM STUDENT T WHERE T.SNO != '1002'                AND ;--15、删除学习“叶平”老师课的SC表记录；          DELETE FROM SCORE S WHERE S.CNO IN (SELECT C.CNO FROM COURSE C WHERE C.TNO = (SELECT T.TNO FROM TEACHER T WHERE T.TNAME = '叶平'));         COMMIT;--16、向成绩表中插入一些记录，这些记录要求符合以下条件：--没有上过编号“003”课程的同学学号、课程编号“002”、 课程编号“002”课的平均成绩；           INSERT INTO SCORE  (SNO, CNO, DEGREE)                  SELECT SC.SNO, '002', (SELECT AVG(DEGREE) FROM SCORE  WHERE CNO = '002') FROM  SCORE SC WHERE                         SC.SNO NOT IN (SELECT S.SNO FROM SCORE S WHERE S.CNO = '003');          COMMIT;--17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，--按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分          SELECT T.SNO,         (SELECT S.DEGREE FROM SCORE S JOIN COURSE C ON C.CNO = S.CNO WHERE C.CNAME = '数据库' AND S.SNO = T.SNO) AS 数据库,         (SELECT S.DEGREE FROM SCORE S JOIN COURSE C ON C.CNO = S.CNO WHERE C.CNAME = '企业管理' AND S.SNO = T.SNO) AS 企业管理,         (SELECT S.DEGREE FROM SCORE S JOIN COURSE C ON C.CNO = S.CNO WHERE C.CNAME = '英语' AND S.SNO = T.SNO) AS 英语,         (SELECT COUNT(S.DEGREE) FROM SCORE S JOIN COURSE C ON C.CNO = S.CNO WHERE (C.CNAME =  '数据库' OR C.CNAME = '企业管理' OR C.CNAME = '英语') AND S.SNO = T.SNO) AS 有效课程数,         (SELECT AVG(S.DEGREE) FROM SCORE S JOIN COURSE C ON C.CNO = S.CNO WHERE (C.CNAME =  '数据库' OR C.CNAME = '企业管理' OR C.CNAME = '英语') AND S.SNO = T.SNO) AS 有效平均分           FROM STUDENT T ORDER BY 有效平均分 DESC;--18、查询各科成绩最高和最低的分：以如下形式显示：课程编号，最高分，最低分            SELECT S.CNO AS 课程编号, MAX(S.DEGREE) AS 最高分,MIN(S.DEGREE) AS 最低分 FROM SCORE S GROUP BY S.CNO;--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序           SELECT C.CNO,C.CNAME,          (SELECT AVG(S.DEGREE) FROM SCORE S  WHERE S.CNO = C.CNO) AS 平均成绩,          (CONVERT(VARCHAR2(10), ((SELECT COUNT(*) FROM SCORE S  WHERE S.CNO = C.CNO AND S.DEGREE >= 60) * 10000 / (SELECT COUNT(*) FROM SCORE S WHERE S.CNO = C.CNO)) / 100 ) + '%') AS 及格率          FROM COURSE C ORDER BY 平均成绩 ASC,及格率 DESC;--20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）       SELECT S.CNO,AVG(S.DEGREE) AS 平均分,      (SELECT COUNT(*) FROM SCORE SC WHERE SC.DEGREE >= 60 AND SC.CNO = S.CNO)/COUNT(S.DEGREE)*100 AS 及格率       FROM SCORE S GROUP BY S.CNO ORDER BY 平均分,及格率 DESC;--21、查询不同老师所教不同课程平均分从高到低显示            SELECT C.CNO,C.CNAME,T.TNAME,           (SELECT AVG(S.DEGREE) FROM SCORE S WHERE S.CNO = C.CNO) AS 平均分            FROM COURSE C JOIN TEACHER T ON T.TNO = C.TNO ORDER BY 平均分 DESC;--22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）        SELECT * FROM (SELECT S.SNO,S.DEGREE,S.CNO,ROW_NUMBER()OVER(ORDER BY S.DEGREE DESC) NUM FROM SCORE S WHERE S.CNO = '001') WHERE NUM BETWEEN 3 AND 6       UNION        SELECT * FROM (SELECT S.SNO,S.DEGREE,S.CNO,ROW_NUMBER()OVER(ORDER BY S.DEGREE DESC) NUM FROM SCORE S WHERE S.CNO = '002') WHERE NUM BETWEEN 3 AND 6       UNION       SELECT * FROM (SELECT S.SNO,S.DEGREE,S.CNO,ROW_NUMBER()OVER(ORDER BY S.DEGREE DESC) NUM FROM SCORE S WHERE S.CNO = '003') WHERE NUM BETWEEN 3 AND 6       UNION       SELECT * FROM (SELECT S.SNO,S.DEGREE,S.CNO,ROW_NUMBER()OVER(ORDER BY S.DEGREE DESC) NUM FROM SCORE S WHERE S.CNO = '004') WHERE NUM BETWEEN 3 AND 6;    --23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]             SELECT C.CNO,C.CNAME,            (SELECT COUNT(S.SNO) FROM SCORE S WHERE S.CNO = C.CNO AND S.DEGREE BETWEEN 85 AND 100) AS "[100-85]",            (SELECT COUNT(S.SNO) FROM SCORE S WHERE S.CNO = C.CNO AND S.DEGREE BETWEEN 70 AND 84) AS "[85-70]",            (SELECT COUNT(S.SNO) FROM SCORE S WHERE S.CNO = C.CNO AND S.DEGREE BETWEEN 60 AND 69) AS "[70-60]",            (SELECT COUNT(S.SNO) FROM SCORE S WHERE S.CNO = C.CNO AND S.DEGREE BETWEEN 0 AND 59) AS "[ <60]"             FROM COURSE C ;--24、查询学生平均成绩及其名次；            SELECT T.SNO,T.SNAME,            (SELECT AVG(S.DEGREE) FROM SCORE S WHERE S.SNO = T.SNO) AS 平均成绩,            (SELECT ROWNUM FROM SC) AS 名次             FROM STUDENT T JOIN (SELECT S.SNO,AVG(S.DEGREE) FROM SCORE S GROUP BY S.SNO ORDER BY AVG(S.DEGREE) DESC) AS SC ON T.SNO = SC.SNO;--25、查询各科成绩前三名的记录:(不考虑成绩并列情况)        SELECT * FROM (SELECT S.SNO,S.CNO,S.DEGREE,ROW_NUMBER()OVER(PARTITION BY S.CNO ORDER BY S.DEGREE) NUM FROM SCORE S) WHERE NUM <= 3;--26、查询每门课程被选修的学生数；         SELECT COUNT(S.CNO) FROM SCORE S GROUP BY S.CNO; --27、查询出只选修了一门课程的全部学生的学号和姓名 ；         SELECT T.SNO,T.SNAME FROM STUDENT T WHERE T.SNO IN (SELECT S.SNO FROM SCORE S GROUP BY S.SNO  HAVING COUNT(S.CNO) = 1);--28、查询男生、女生人数          SELECT DISTINCT (SELECT COUNT(1) FROM STUDENT T WHERE T.SSEX = '男') AS 男生人数,                  (SELECT COUNT(1) FROM STUDENT T WHERE T.SSEX = '女') AS 女生人数  FROM STUDENT T;--29、查询姓“张”的学生名单          SELECT * FROM STUDENT T WHERE T.SNAME LIKE '张%';--30、查询同名同性学生名单，并统计同名人数           SELECT DISTINCT T.SNAME, (SELECT COUNT(T.SNAME) FROM STUDENT S WHERE S.SNAME = T.SNAME) AS 同名人数  FROM STUDENT T WHERE                 (SELECT COUNT(T.SNAME) FROM STUDENT S WHERE S.SNAME = T.SNAME) >= 2;--31、1981年出生的学生名单          SELECT * FROM STUDENT T WHERE  TO_CHAR(T.SBIRTHDAY, 'YYYY') = '1981';--32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列           SELECT C.CNO,C.CNAME,(SELECT AVG(S.DEGREE) FROM SCORE S WHERE S.CNO = C.CNO) AS 平均成绩 FROM COURSE C ORDER BY 平均成绩 ASC, C.CNO DESC;--33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩           SELECT T.SNO,T.SNAME,(SELECT AVG(S.DEGREE) FROM SCORE S WHERE S.SNO = T.SNO) AS 平均成绩 FROM STUDENT T                    WHERE (SELECT AVG(S.DEGREE) FROM SCORE S WHERE S.SNO = T.SNO) > 85;--34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数           SELECT T.SNAME, (SELECT S.DEGREE FROM SCORE S WHERE S.CNO = (SELECT C.CNO FROM COURSE C WHERE C.CNAME = '数据库')) AS 分数 FROM STUDENT T                  WHERE (SELECT S.DEGREE FROM SCORE S WHERE S.CNO = (SELECT C.CNO FROM COURSE C WHERE C.CNAME = '数据库')) < 60;--35、查询所有学生的选课情况；           SELECT T.SNAME,C.CNAME FROM STUDENT T, COURSE C, SCORE S WHERE T.SNO = S.SNO AND S.CNO = C.CNO;--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；            SELECT T.SNAME,C.CNAME,S.DEGREE  FROM STUDENT T JOIN SCORE S ON S.SNO = T.SNO JOIN COURSE C ON C.CNO = S.CNO WHERE S.DEGREE >= 70;--37、查询不及格的课程，并按课程号从大到小排列            SELECT C.CNO,C.CNAME FROM COURSE C WHERE C.CNO IN (SELECT S.CNO FROM SCORE S WHERE S.DEGREE < 60) ORDER BY C.CNO DESC;--38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；            SELECT T.SNO,T.SNAME FROM STUDENT T WHERE T.SNO IN (SELECT S.SNO FROM SCORE S WHERE S.CNO = '005' AND S.DEGREE >= 80);--39、求选了课程的学生人数            SELECT COUNT(T.SNO) FROM STUDENT T WHERE T.SNO IN (SELECT S.SNO FROM SCORE S);--40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩            SELECT T.SNAME,(SELECT MAX(S.DEGREE)  FROM SCORE S JOIN COURSE C ON C.CNO = S.CNO JOIN TEACHER TE ON TE.TNO = C.TNO WHERE TE.TNAME = '叶平') AS 叶平老师所授课程成绩 FROM STUDENT T WHERE                   T.SNO = (SELECT S.SNO  FROM SCORE S JOIN COURSE C ON C.CNO = S.CNO JOIN TEACHER TE ON TE.TNO = C.TNO WHERE TE.TNAME = '叶平');--41、查询各个课程及相应的选修人数            SELECT  C.CNO,C.CNAME,(SELECT COUNT(*) FROM SCORE S WHERE S.CNO = C.CNO) AS 选修人数 FROM COURSE C;             --42、查询不同课程成绩相同的学生的学号、课程号、学生成绩            SELECT S.* FROM SCORE S WHERE EXISTS (SELECT * FROM SCORE SC WHERE SC.CNO != S.CNO AND SC.DEGREE = S.DEGREE);--43、查询每门功成绩最好的前两名          SELECT * FROM (SELECT S.SNO,S.CNO,S.DEGREE,ROW_NUMBER()OVER(PARTITION BY S.CNO ORDER BY S.DEGREE) NUM FROM SCORE S) WHERE NUM <= 2;      --44、统计每门课程的学生选修人数（超过10人的课程才统计）。--要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列             SELECT C.CNO AS 课程号,(SELECT COUNT(1) FROM SCORE S WHERE S.CNO = C.CNO) AS 选修人数 FROM COURSE C WHERE                   (SELECT COUNT(1) FROM SCORE S WHERE S.CNO = C.CNO) > 10 ORDER BY 选修人数 DESC, C.CNO ASC;--45、检索至少选修两门课程的学生学号            SELECT S.SNO  FROM SCORE S HAVING COUNT(S.CNO) >=2 GROUP BY S.SNO ;--46、查询全部学生都选修的课程的课程号和课程名           SELECT C.CNO,C.CNAME FROM COURSE C WHERE NOT EXISTS (SELECT * FROM STUDENT T WHERE T.SNO NOT IN (SELECT S.SNO FROM SCORE S WHERE S.CNO = C.CNO));         --47、查询没学过“叶平”老师讲授的任一门课程的学生姓名           SELECT T.SNO,T.SNAME FROM STUDENT T                   WHERE T.SNO NOT IN (SELECT S.SNO FROM SCORE S JOIN COURSE C ON C.CNO = S.CNO                         JOIN TEACHER TC ON TC.TNO = C.TNO WHERE TC.TNAME = '叶平');    --48、查询两门以上不及格课程的同学的学号及其平均成绩            SELECT S.SNO AS 两门以上不及格学号,AVG(S.DEGREE) AS 平均成绩 FROM SCORE S WHERE (SELECT COUNT(S.CNO) FROM SCORE S2 WHERE S2.DEGREE < 60 AND S2.SNO = S.SNO) >=2 GROUP BY S.SNO;           --49、检索“004”课程分数小于60，按分数降序排列的同学学号            SELECT S.SNO  FROM SCORE S WHERE S.CNO = '004' AND S.DEGREE < 60 ORDER BY S.DEGREE DESC;--50、删除“1002”同学的“001”课程的成绩            DELETE FROM SCORE S WHERE S.SNO = '1002' AND S.CNO = '001';           COMMIT;
不排除有错误，欢迎指正，Oracle 11g 环境测试过。
0 0