- 练习题目：

-- 1.查询"数学 "课程比" 语文 "课程成绩高的学生的信息及课程分数

SELECT STUDENT.ID,STUDENT_NAME , A.SCORE MATH , B.SCORE CHINESE FROM 
(SELECT * FROM SCORE WHERE COURSE_ID =1 ) A
INNER JOIN 
(SELECT * FROM SCORE WHERE COURSE_ID=2)B
ON A.STUDENT_ID=B.STUDENT_ID 
INNER JOIN STUDENT 
ON  STUDENT.ID=B.STUDENT_ID 
WHERE A.SCORE>B.SCORE 

-- 1.1 查询同时存在" 数学 "课程和" 语文 "课程的情况

SELECT  STUDENT.*, A.STUDENT_ID AS MATH , B.STUDENT_ID AS CHINESE  FROM 
(SELECT * FROM SCORE WHERE COURSE_ID =1)A
INNER JOIN 
(SELECT * FROM SCORE WHERE COURSE_ID=2)B
ON A.STUDENT_ID=B.STUDENT_ID 
INNER JOIN STUDENT 
ON B.STUDENT_ID = STUDENT.ID


-- 1.2 查询存在" 数学 "课程但可能不存在" 语文 "课程的情况(不存在时显示为 null )

SELECT  A.SCORE AS MATH , B.SCORE CHINESE FROM 
 (SELECT * FROM SCORE WHERE COURSE_ID =1 ) A 
 LEFT JOIN  
 (SELECT * FROM SCORE WHERE COURSE_ID=2)B 
 ON A.STUDENT_ID=B.STUDENT_ID  


-- 1.3 查询不存在" 数学 "课程但存在" 语文 "课程的情况


SELECT   A.SCORE  MATH , B.SCORE  CHINESE  FROM 
 (SELECT * FROM SCORE WHERE COURSE_ID =1 ) A 
 RIGHT  JOIN 
 (SELECT  * FROM  SCORE WHERE COURSE_ID =2 )B 
 ON A.STUDENT_ID = B.STUDENT_ID 
 
 select * from student inner join score on student.id=score.student_id
  

-- 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

 SELECT A.*, STUDENT_NAME FROM 
(SELECT  STUDENT_ID,AVG(SCORE)  FROM SCORE 
GROUP BY STUDENT_ID HAVING AVG( SCORE )>60) A 
INNER JOIN STUDENT 
ON STUDENT.ID=A.STUDENT_ID


-- 3.查询在 成绩 表存在成绩的学生信息

SELECT student.id,student.student_name ,student.birthday ,student.sex FROM student 
   JOIN
     (SELECT DISTINCT  STUDENT_ID  FROM score WHERE score<>0)b
ON student.id = b.student_id  



-- 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT S.ID,STUDENT_NAME,COUNT(SCORE.COURSE_ID),SUM(SCORE)
   FROM SCORE
  LEFT JOIN COURSE C ON SCORE.COURSE_ID=C.ID
  RIGHT JOIN STUDENT S ON SCORE.STUDENT_ID=S.ID
  GROUP BY S.ID,STUDENT_NAME
      

-- 4.1 查有成绩的学生信息
SELECT * FROM STUDENT 
INNER  JOIN 
(SELECT  STUDENT_ID , count(*) FROM SCORE  GROUP BY STUDENT_ID )A
 ON STUDENT.ID = A.STUDENT_ID


-- 5.查询「李」姓老师的数量

SELECT * FROM TEACHER WHERE TEACHER_NAME  like '李%'

-- 6.查询学过「张三」老师授课的同学的信息
SELECT  * FROM STUDENT,SCORE,COURSE,TEACHER 
WHERE  STUDENT.ID=SCORE.STUDENT_ID
AND SCORE.COURSE_ID=COURSE.ID
AND COURSE.TEACHER_ID=TEACHER.ID
AND  TEACHER_NAME = '李四'


-- 7.查询没有学全所有课程的同学的信息

SELECT * FROM STUDENT,
 (SELECT STUDENT_ID,COUNT(*) FROM SCORE 
  GROUP BY STUDENT_ID HAVING COUNT(*) < (SELECT COUNT(*) FROM COURSE))  A
  
  WHERE STUDENT.ID= A.STUDENT_ID

-- 8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT * FROM STUDENT,
 (SELECT   DISTINCT STUDENT_ID FROM SCORE WHERE COURSE_ID IN (SELECT COURSE_ID  FROM SCORE 
WHERE COURSE_ID = 1 ) )A 
 WHERE STUDENT.ID = A.STUDENT_ID
 


-- 9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息

 SELECT  DISTINCT COURSE_ID FROM SCORE WHERE STUDENT_ID = 1 --学生01 的课程信息 
 
 
 SELECT COUNT(1) FROM SCORE WHERE STUDENT_ID=1 -- 学生01的总科目 
 
 SELECT DISTINCT STUDENT_ID FROM SCORE WHERE STUDENT_ID <>01 -- 不是01 学生的其它学生 
 
 SELECT * FROM SCORE WHERE STUDENT_ID <>01 
 AND COURSE_ID IN (SELECT DISTINCT COURSE_ID FROM SCORE WHERE STUDENT_ID=1)
 -- 不是01 学生的其它学生 ，学生01 的课程信息 
 
 -- 和01一样的科目 的同学们  
 SELECT DISTINCT STUDENT_ID  FROM SCORE WHERE STUDENT_ID <>1 
 AND COURSE_ID IN (SELECT DISTINCT COURSE_ID FROM SCORE WHERE STUDENT_ID=1)
 GROUP BY STUDENT_ID 
 HAVING   COUNT(1) = (SELECT COUNT(1) FROM SCORE WHERE STUDENT_ID =1)
 
 

-- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名

 SELECT student.* FROM student WHERE student.id NOT IN
      (SELECT STUDENT_ID FROM score
       WHERE COURSE_ID  IN
         (SELECT COURSE_ID  FROM course,teacher
        WHERE TEACHER.ID = COURSE.TEACHER_ID 
        AND teacher_name = '张三'));
        

-- 11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT STUDENT.ID,STUDENT_NAME,AVG(SCORE) FROM SCORE ,STUDENT
   WHERE STUDENT.ID=SCORE.STUDENT_ID AND SCORE<60  --不及格
 GROUP BY STUDENT.ID ,STUDENT_NAME 
 HAVING COUNT(*)>1  --查询 两门课程
 
 

-- 12.检索" 数学 "课程分数小于 60，按分数降序排列的学生信息


SELECT student.*,score FROM SCORE  inner join student on student.id= score.student_id
   WHERE   score <60  and course_id =1
   order by score asc 
-- 13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT STUDENT_ID , AVG(SCORE ) FROM SCORE  GROUP BY STUDENT_ID  ORDER BY AVG(SCORE) DESC
   

-- 14.查询各科成绩最高分、最低分和平均分：
 SELECT COURSE_ID , COURSE_NAME  ,MAX(SCORE),MIN(SCORE),AVG(SCORE) FROM SCORE 
  LEFT  JOIN  COURSE ON COURSE.ID = SCORE.COURSE_ID 
  GROUP BY COURSE_ID , COURSE_NAME 
  


-- 15.以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
/*
	及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

	要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

	按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
*/	
SELECT COURSE_ID, COURSE_NAME , MIN(SCORE),MAX(SCORE),AVG(SCORE),COUNT(STUDENT_ID),
 SUM (CASE WHEN SCORE>=60 THEN 1 ELSE 0 END)/count(STUDENT_ID )"及格",
 SUM (CASE WHEN SCORE>=70 AND SCORE <80 THEN 1 ELSE 0 END)/COUNT(STUDENT_ID) "中等",
 SUM (CASE WHEN SCORE>=80 AND SCORE<90  THEN 1 ELSE 0 END)/COUNT(STUDENT_ID) "优良",
 SUM (CASE WHEN SCORE>= 90 THEN 1 ELSE 0 END ) /COUNT(STUDENT_ID)"优秀"
 FROM SCORE 
LEFT JOIN COURSE ON SCORE.COURSE_ID= COURSE.ID 
GROUP BY COURSE_ID , COURSE_NAME  


	
-- 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

SELECT  STUDENT_ID, COURSE_ID, SCORE , RANK () 
OVER (PARTITION BY COURSE_ID ORDER BY SCORE  desc )名次   FROM SCORE


-- 16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
SELECT STUDENT_ID , RANK () OVER ( ORDER BY SUM(SCORE) DESC ) 名次 , 
SUM(SCORE)  FROM SCORE  GROUP BY STUDENT_ID ;


-- 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

SELECT STUDENT_ID ,S , DENSE_RANK () OVER (ORDER BY S DESC ) 名次 
 FROM (SELECT * FROM (SELECT STUDENT_ID , SUM(SCORE) S FROM SCORE  GROUP BY STUDENT_ID )) T 
 

-- 17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

SELECT COURSE.ID,COURSE_NAME,
CONCAT (ROUND (SUM(CASE WHEN SCORE>=85 THEN 1 ELSE 0  END )/COUNT(*)*100,2),'%')"[100-85]百分比",
CONCAT (ROUND (SUM (CASE WHEN SCORE<85 AND SCORE<=70 THEN 1 ELSE 0 END )/COUNT(*)*100,2),'%')"[85-70] 百分比",
CONCAT (ROUND (SUM (CASE WHEN SCORE <70 AND SCORE>=60 THEN 1 ELSE 0 END )/COUNT(*)*100,2),'%')"[70-60]百分比",
CONCAT (ROUND (SUM (CASE WHEN SCORE <60 THEN 1 ELSE 0 END )/COUNT(*)*100,2),'%')"[60-0]百分比"
FROM SCORE LEFT JOIN COURSE ON SCORE.COURSE_ID=COURSE.ID 
GROUP BY COURSE.ID ,COURSE_NAME


-- 18.查询各科成绩前三名的记录

SELECT * FROM 
(SELECT STUDENT_ID ,COURSE_ID , SCORE , RANK () OVER 
(PARTITION BY COURSE_ID ORDER BY SCORE DESC )A FROM SCORE)B
WHERE B.A <=3

-- 19.查询每门课程被选修的学生数

SELECT SCORE.COURSE_ID , COUNT(* ) FROM SCORE GROUP  BY SCORE.COURSE_ID


-- 20.查询出只选修两门课程的学生学号和姓名


SELECT STUDENT.*,B.A FROM STUDENT,
(SELECT STUDENT_ID , COUNT (COURSE_ID) A FROM SCORE GROUP BY STUDENT_ID )B
WHERE STUDENT.ID=B.STUDENT_ID
AND B.A =2

-- 21.查询男生、女生人数

 select sex,count(*) from student group by sex
 
-- 22.查询名字中含有「风」字的学生信息

SELECT * FROM STUDENT WHERE STUDENT_NAME LIKE '%风%'


-- 23.查询同名同性学生名单，并统计同名人数

SELECT STUDENT_NAME, sex ,COUNT(*)  FROM STUDENT 
GROUP BY STUDENT_NAME,sex  HAVING COUNT(*)>=2 



-- 24.查询 1990 年出生的学生名单
--方法1
SELECT   STUDENT_NAME, TO_NUMBER (TO_CHAR(BIRTHDAY,'YYYY')) 年份  FROM STUDENT
 where  TO_NUMBER (TO_CHAR(BIRTHDAY,'YYYY')) like '1990' 


 --方法2
select * from student where to_char(birthday,'yyyy')='1990'
 


-- 25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT COURSE_ID,COURSE_NAME,AVG(SCORE)  FROM 
 SCORE INNER JOIN COURSE  ON SCORE.COURSE_ID=COURSE.ID
GROUP BY COURSE_ID ,COURSE_NAME
ORDER BY AVG(SCORE) DESC , COURSE_ID

-- 26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

SELECT STUDENT.ID,STUDENT_NAME,AVG(SCORE)  FROM STUDENT
INNER JOIN SCORE ON STUDENT.ID= SCORE.STUDENT_ID

GROUP BY  STUDENT.ID,STUDENT_NAME
HAVING  AVG(SCORE) >=85 

ORDER BY AVG(SCORE) DESC 


-- 27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT A.STUDENT_NAME, STUDENT_ID  , SCORE FROM SCORE 
INNER JOIN COURSE ON SCORE.COURSE_ID = COURSE.ID
INNER JOIN STUDENT A  ON SCORE.STUDENT_ID=A.ID

WHERE COURSE_ID =2
HAVING SCORE <=60
GROUP BY STUDENT_ID ,SCORE ,A.STUDENT_NAME


-- 28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT STUDENT_NAME,SCORE,COURSE_NAME FROM STUDENT 
LEFT JOIN SCORE ON STUDENT.ID = SCORE.STUDENT_ID 
LEFT JOIN COURSE ON COURSE.ID=SCORE.COURSE_ID


-- 29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

SELECT STUDENT_NAME,COURSE_NAME,SCORE FROM STUDENT 
LEFT JOIN SCORE ON STUDENT.ID= SCORE.STUDENT_ID 
LEFT JOIN COURSE ON COURSE.ID=SCORE.COURSE_ID
WHERE SCORE >=70 


-- 30.查询不及格的课程

SELECT SCORE ,COURSE_NAME FROM  COURSE 
INNER JOIN SCORE ON SCORE.COURSE_ID=COURSE.ID
GROUP BY SCORE,COURSE_NAME
HAVING SCORE<60



-- 31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

SELECT STUDENT_ID,STUDENT_NAME  FROM SCORE INNER JOIN STUDENT 
ON SCORE.STUDENT_ID=STUDENT.ID
 WHERE  COURSE_ID =1 AND SCORE>=80


-- 32.求每门课程的学生人数

SELECT COURSE_ID ,COUNT(*)  FROM SCORE GROUP BY  COURSE_ID

-- 33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT STUDENT_NAME,SCORE FROM STUDENT 
INNER JOIN SCORE  ON STUDENT.ID=SCORE.STUDENT_ID
INNER JOIN COURSE  ON COURSE.ID=SCORE.COURSE_ID
INNER JOIN TEACHER  ON COURSE.TEACHER_ID=TEACHER.ID
WHERE TEACHER_NAME='张三'
AND  SCORE =(SELECT MAX(SCORE) FROM SCORE WHERE COURSE_ID = 
(SELECT COURSE.ID FROM COURSE WHERE TEACHER_ID = 
(SELECT TEACHER.ID FROM TEACHER WHERE TEACHER_NAME = '张三')))


--34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT *   FROM (SELECT DENSE_RANK () OVER (ORDER BY SCORE DESC)A

 FROM SCORE WHERE COURSE_ID=(SELECT COURSE.ID FROM 
COURSE WHERE TEACHER_ID = 
 (SELECT TEACHER.ID  FROM TEACHER WHERE TEACHER_NAME ='张三') ))B
 WHERE B.A=1

-- 35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select  a.COURSE_id, a.STUDENT_id,  a.score from SCORE  a
inner join 
SCORE   b
on a.STUDENT_ID = b.student_id
and a.COURSE_ID != b.course_id
and a.score = b.score
group by a.COURSE_ID, a.STUDENT_ID,a.score


-- 36.查询每门功成绩最好的前两名

SELECT SC.COURSE_ID,SC.SCORE FROM SCORE  SC WHERE (SELECT COUNT (*)
 FROM SCORE WHERE COURSE_ID= SC.COURSE_ID AND

SC.SCORE<SCORE )<2  ORDER BY SC.COURSE_ID,SC.SCORE DESC 


-- 37.统计每门课程的学生选修人数（超过 5 人的课程才统计）。

SELECT  COUNT(DISTINCT  STUDENT_ID),COURSE_ID  FROM SCORE  
 GROUP BY COURSE_ID  HAVING  COUNT(DISTINCT  STUDENT_ID)    >5 
 ORDER BY  COUNT(DISTINCT  STUDENT_ID)  DESC , COURSE_ID ASC 

-- 38.检索至少选修两门课程的学生学号

SELECT STUDENT_ID ,COUNT(COURSE_ID) FROM SCORE 
GROUP BY STUDENT_ID HAVING COUNT(COURSE_ID) >=2  


-- 39.查询选修了全部课程的学生信息

SELECT STUDENT_ID, STUDENT_NAME,SEX,BIRTHDAY ,COUNT(COURSE_ID) FROM SCORE

INNER JOIN STUDENT ON SCORE.STUDENT_ID=STUDENT.ID
 GROUP BY STUDENT_ID,STUDENT_NAME,SEX,BIRTHDAY   HAVING COUNT(COURSE_ID) >=3


-- 40.查询各学生的年龄，只按年份来算
SELECT STUDENT.ID,STUDENT_NAME,BIRTHDAY,(TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(BIRTHDAY,'YYYY')) 
FROM   STUDENT  



-- 41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select student_name,floor (to_char(sysdate-birthday)/365)年龄 from student

-- 42.查询本周过生日的学生
--方法1
SELECT STUDENT.ID,STUDENT_NAME,TO_CHAR(BIRTHDAY,'YYYYMMDD'),SEX FROM STUDENT 
WHERE TO_CHAR(BIRTHDAY,'MMDD')>=TO_CHAR(TRUNC(SYSDATE,'IW'),'MMDD') 
AND TO_CHAR (BIRTHDAY,'MMDD')<TO_CHAR(TRUNC (SYSDATE,'IW')+6,'MMDD')

--方法2
select *from student where to_char(sysdate,'ww')=to_char(birthday,'ww')

-- 43.查询下周过生日的学生
--方法1
Select STUDENT.ID,STUDENT_NAME,to_char(BIRTHDAY,'yyyymmdd'),
sex From student  Where to_char(BIRTHDAY,'mmdd')>=
to_char(trunc(sysdate,'iw')+7,'mmdd') and to_char(BIRTHDAY,' mmdd')
<to_char(trunc(sysdate,'iw')+13,' mmdd') 

--方法2


select *from student where to_char(sysdate,'ww')=to_char(birthday,'ww')-1

-- 44.查询本月过生日的学生
--方法1
SELECT STUDENT.ID,STUDENT_NAME,TO_CHAR(BIRTHDAY,'YYYYMMDD'),SEX FROM STUDENT 
WHERE TO_CHAR(BIRTHDAY,'MM')=TO_CHAR(SYSDATE,'MM')

--方法2

select *from student where to_char(sysdate,'mm')=to_char(birthday,'mm')


-- 45.查询下月过生日的学生
select *from student where to_char(sysdate,'mm')=to_char(birthday,'mm')+1



SELECT TO_CHAR (SYSDATE, 'YYYY') 年，
TO_CHAR (SYSDATE,'MM')月，
TO_CHAR(SYSDATE,'DD')日，
TO_CHAR(SYSDATE,'HH24')时，
TO_CHAR(SYSDATE,'MI')分，
TO_CHAR(SYSDATE,'SS')秒，
TO_CHAR(SYSDATE,'DAY')天，
TO_CHAR(SYSDATE,'Q') 第几季度，
TO_CHAR(SYSDATE,'W')当月第几周，
TO_CHAR(SYSDATE,'WW')当年第几周，
TO_CHAR(SYSDATE,'D')当周第几天，
TO_CHAR(SYSDATE,'DDD')当年第几天