--1:查询全体学生的学号和姓名
select sno, sname from student;
--2:查询全体学生的姓名、学号和所在系
select sno, sname,sdept from student;
--3: 查询全体学生的详细记录
select * from student
--4: 查询全体学生的姓名及其出生年份
select sname, 2011-sage as 出生年份 from student;
--5:查询全体学生姓名、出生年份和所在系,要求用小写字母表示所有系名
select sname, 2011-sage as 出生年份,lower(sdept) from student;
--6:查询选修了课程的学生学号
select distinct sno from sc;
--7:查询计算机系(IS)所有学生的名单
select sname from student where sdept = "is";
--8:查询所有年龄在20以下学生的姓名和年龄
select sname ,sage from student where sage <20;
--9: 查询考试成绩有不及格的学生的学号
select distinct sno from sc where grade < 60;
--10: 查询年龄在20-23 (包括20和23)之间的学生的姓名、系别和年龄
select sname,sdept,sage from student where sage>=20 and sage<=23;
--11: 查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别
select sname ,ssex from student where sdept = 'is' or sdept='ma' or sdept = 'CS';或
select sname,ssex from student where sdept in("is","ma","cs");
--12: 查询学号为95001的学生的详细情况
select * from student where sno = 95001;
--13: 查询所有姓林的学生的姓名、学号和性别
select sname,sno,ssex from student where sname like "林%";
--14: 查询姓“欧阳”且全名为三个汉字的学生的姓名
select sname from student where sname like "欧阳_";
--15:查询名字中第二个字为“燕”字的学生姓名和学号
select sname,sno from student where sname like "_燕%";
--16:查询所有不姓“刘”的学生的姓名
select sname from student where sname not like "^刘%";
--17:查询课程名为“DB_DESIGN”的课程号的学分
select ccredit from course where cname = "db_design";
--18:查询缺少成绩的学生的学号和相应的课程号(成绩字段值为Null)
select sno,cno from sc where grade<=>null;
--19: 查询所有有成绩的学生的学号和课程号
select sno ,cno from sc ;
--20: 查询所有计算机系年龄在20以下的学生姓名
select sname from student where sage<20 and sdept="cs";
--21: 查询选修了3号课程的学生的学号和成绩,查询结果按分数降序排列
select sno,grade from sc where cno =3 order by grade desc;
--22: 查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列
select * from student order by sdept,sage desc;
--23: 查询学生总人数
select count(sno) from student;
--24: 查询选修了课程的学生人数
select count(distinct sno) from sc;
--25: 计算1号课程的学生的平均成绩
select avg(grade) from sc where cno = 1;
--26: 计算1号课程的学生的最高成绩分数
select max(grade) from sc where cno =1;
--27:求各个课程号及相应的选课人数
select distinct cno,count(sno) from sc group by cno;
-- 查询每个学生选修的的课程数
select sno,count(sno) from sc group by sno;
--28: 查询选修了三门以上课程的学生学号
select sno from sc group by sno having count(sno)>3;
select sno,count(cno) as kcnum from sc group by sno having kcnum>3;
--29:查询每个学生及其选修课情况
select sno,sname,sage ,sdept ,grade from student left join sc on student.sno=sc.sno;
--30:查询每一门课的间接先行课
select cno, (select c2.cpno from course as c2 where c2.cno=c1.cpno ) as 间接先修课程 from course as c1;
--31:选修2号课程且成绩在90以上的学生的学号和姓名
select student.sno,sname from student,sc where student.sno = sc.sno and cno=2 and grade>=90;
//join连接 select s.sno,sname from student as s join sc on s.sno =sc.sno where cno=2 and grade>=90;
--32:查询每个学生的学号、姓名、选修的课程名及成绩
select sno,sc.cno,cname from sc join course using(cno);
select s.sno,sname,sage,cname,grade from student as s left join(select sno,sc.cno,cname from sc join course using(cno))as t on s.sno=t.sno;
--33:查询与’林燕芳’在同一个系学习的学生姓名
select sname from student where sdept=(select sdept from student where sname="林燕芳") and sname !="林燕芳";
--34: 查询其他系中比信息系某一学生小的学生姓名和年龄
select sname,sage from student where sage
--35:查询所有选修了1号课程的学生的学生姓名
select student.sname from student,sc where student.sno=sc.sno and sc.cno=1;
//select sname from student where sno in (select sno from sc where cno=1);
--36:查询选修了全部课程的学生姓名
select sname from student where sno in(select sno from sc group by sno having count(cno)=(select count(*) from course));