数据库实验——复杂查询

--2.查询选修3号课程且成绩在70分以上的学生
select Student.*
from Student,SC
where  student.sno=sc.Sno and sc.Grade>70 and cno=3


--3.查询所有不比电子工程系的所有学生年龄大的其他系的学生信息
select * from student
where Sbirth > all(
	select Sbirth from Student
	where Sdept='电子工程'
) and Sdept!='电子工程'






--4.查询选修了3号课程的学生姓名、成绩及任课教师姓名
select Student.sname 姓名,grade 成绩,tname 教师姓名
from Student,sc,teacher
where Student.sno=sc.Sno 
and sc.tno=Teacher.tno
and sc.cno=3

--5.查询开设的课程种，选课人数不足15人的课程号及人数信息
select cno  ,count(*) as 选课人数
from sc  
group by cno
having count(*)<15

--6.查询选修课程数低于3门的学生信息
select Student.*
from Student 
where exists(
   select sno from sc
   where sno=Student.sno
   group by sno
   having count(*)<3
) 

--7.查询没有选修课程的同学的基本信息
select  * from Student
where not exists(
	select 1 from sc
	where sc.sno= Student.sno
)


select * from Student
where sno not in (select sno from sc)


--8.查询选修了3又选修了4的课程的学生姓名、课程名和成绩
SELECT 
    s.Sname AS 学生姓名,
    (SELECT Cname FROM Course WHERE Cno = '3') AS 课程3名称,  -- 固定3号课程名
    (SELECT Grade FROM SC WHERE Sno = s.Sno AND Cno = '3') AS 课程3成绩,
    (SELECT Cname FROM Course WHERE Cno = '4') AS 课程4名称,  -- 固定4号课程名
    (SELECT Grade FROM SC WHERE Sno = s.Sno AND Cno = '4') AS 课程4成绩
FROM Student s
WHERE 
	
    s.Sno IN (SELECT Sno FROM SC WHERE Cno = '3')
    AND 
    s.Sno IN (SELECT Sno FROM SC WHERE Cno = '4');

--9.查询选修了操作系统和数据库原理的课程的学生名单

select sname from student 
where sno in(
	select sno from sc
	where cno=(select cno from course where cname ='操作系统')
	intersect 
	select sno from sc 
	where cno=(select cno from course where cname ='数据库')
)


select sname from Student s
inner join sc sc1 on s.sno=sc1.Sno and sc1.cno='1'
inner join sc sc2 on s.sno =sc2.Sno and sc2.cno='4'


--10.查询所有同学的基本信息及选课情况（包含未选课的同学）

SELECT 
    s.Sno AS 学号,
    s.Sname AS 姓名,
    c.Cname AS 课程名,
    sc.Grade AS 成绩
FROM Student s
LEFT JOIN SC sc ON s.Sno = sc.Sno
LEFT JOIN Course c ON sc.Cno = c.Cno;  -- 二次左连接获取课程名

--11.列出所有参加了数据库考试的学生姓名和成绩

SELECT 
    s.Sname AS 学生姓名,
    sc.Grade AS 成绩
FROM Student s
LEFT JOIN SC sc ON s.Sno = sc.Sno 
    AND sc.Cno = (SELECT Cno FROM Course WHERE Cname = '数据库');


--12.查询所有学生和教师的姓名（允许重名）并标识类型（学生/老师）

--13.查询每一课程的间接选修课，以课程名、间接选修课名做列名
-- 查询每门课程的间接选修课（课程名、间接选修课名）
SELECT 
    c1.Cname AS 课程名,
    c3.Cname AS 间接选修课名
FROM Course c1
INNER JOIN Course c2 ON c1.Cpno = c2.Cno  -- 获取直接选修课（c2）
INNER JOIN Course c3 ON c2.Cpno = c3.Cno; -- 获取间接选修课（c3）

--14.列出所有没有选修数据结构课程的学生的学号、姓名、所在院系
SELECT 
    Sno, 
    Sname, 
    Sdept
FROM Student
WHERE Sno NOT IN (
    SELECT SC.Sno
    FROM SC
    INNER JOIN Course c ON SC.Cno = c.Cno
    WHERE c.Cname = '数据结构'
);