数据库例题三

Select * From Z_course;
Select * From Z_student;
Select * From Z_stu_cour;

--查询表中前五个同学的姓名，专业
Select t.name,t.major,t.id,Rownum From z_student t Where Rownum<6 Order By t.id

--查询各学生的学号和姓名以及截止到现在各学生的年龄
Select t.name,t.code,Trunc((Sysdate-t.birthday)/365)||'岁'age From z_student t

--查询1991年出生的学生姓名和专业
Select t.Name, t.Major, To_Date(1991, 'yyyy') From z_Student t

Select * From z_student t Where to_char(t.birthday,'yyyy')=1991

--查询历史, 广告, 国际新闻专业的所有学生信息
Select * From z_student t Join z_course e On e.cour_code=t.major
And e.cour_name Like '国际新闻%' Or e.cour_name Like '历史%' Or e.cour_name Like '广告%'

--查询没有成绩的学生信息
Select * From z_student s Where s.code Not In (Select Distinct sc.stu_code From z_stu_cour sc)
--Exists
Select * From z_student s Where Not Exists(Select * From z_stu_cour r Where s.code=r.stu_code)

--查询计算机专业的没有记录生日的学生信息
Select * From z_student s Where s.birthday Is Not Null

--查询按照专业降序，学号升序排列所有学生信息
Select Distinct * From z_student t,z_course e,z_stu_cour r Where t.code=r.stu_code And r.cour_code=e.cour_code
Order By t.major Asc,t.code Desc

--查询所有的课程代码和每个课程的平均成绩并按照课程号排序，再剔除课程代码不是C-ADV-240的课程
Select sc.cour_code,Avg(sc.degree)From z_stu_cour sc
Where sc.cour_code <>'C-ADV-240' Group By sc.cou_code order By sc.cour_code

--查询出每个课程代码的最高分和最低分
Select s.cour_code,Max(s.degree),Min(s.degree) From z_stu_cour s
Group By sc.cour_code Order By s.cour_code

--查询出历史学专业有多少人
Select s.major, Count(*) From z_student s Where
s.major=(Select c.cour_code From z_course c Where c.cour_name Like '%历史%') Group By s.major

--查询出各专业里男女生各有多少人
Select s.major, Decode(s.sex, 1, '男', '女'), Count(*)  From z_student s Group By s.major, s.sex

--查询出学生所有课程的平均分在50分以上的学生学号
Select s.stu_code, avg(s.degree) From z_stu_cour s Group By s.stu_code Having Avg(s.degree) > 50

--查询每个学生有几门课成绩高于80分
Select s.stu_code, Count(*) From z_stu_cour s Where sc.degree > 80 Group By s.stu_code

--查询选修广告学专业的基础美术这门课程的学生学号，姓名，成绩
Select s.name, sc.degree From z_student s Left Join z_stu_cour sc On s.code=sc.stu_code Where 
sc.cour_code=(Select c.cour_code From z_course c Where c.cour_name Like '%基础美术%'

--查询生日是同一天的学生信息
Select Distinct s1.* From z_student s1, z_student s2 Where s1.id!=s2.id And s1.birthday=s2.birthday

Select * From z_student s2 Where s2.birthday In (Select s.birthday From z_student s Group By s.birthday Having Count(1) > 1)

--查询平均分大于等于课程号为C-ADV-239的课程号和平均分的记录
Select s.cour_code Avg(s.degree)
from z_stu_cour s
Group By s.cour_code
Having avg(s.degree) >= (select avg(s2.degree)
From z_stu_cour s2
Where s2.cour_code = 'c-adv-239'
Group By s2.cour_code)

--查询历史学专业下的课程的及格率(课程得分在50以上的人数除以总人数)
Select s.cour_code,
sum(Case
 Where s.degree > 50 Then
1
Else
0
End) / Count(*) --及格率
From z_stu_ cour s
Where s.cour_code In (Select c.cour_code
From z_course c
Connect By c.p_cour_code = prior c.cour_code
Start With c.cour_name = '历史学专业')
Group By s.cour_code 

--查询没有选修C-NEWS-101这门课程的学生信息和课程信息
Select (Select s.name From z_student s Where s.code = s.stu_code),
(Select c.cour_name From z_course c Where c.cour_code = s.cour_code),
s.degree
From z_stu_cour s
Where s.cour_code != 'c-news-101'