数据查询（提高篇）-------实验五_数据库查询1999年出生的学生(1)_数据库查询出生在1999年的学生的学号、姓名、出生日期和系部编号。-CSDN博客

本文链接：https://blog.csdn.net/2401_84862306/article/details/138693519

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上鸿蒙开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

四、实验内容
1.查询全体学生的学号和姓名
select sname,sno from student
2.查询全体学生的详细记录
select * from student
3.查询CS学院的学生姓名、年龄、系别
select sname,sage,sdept from student where sdept=‘CS’

4.查询所有选修过课程的学生学号
select distinct sno from sc

5.查询考试不及格的学生学号
select sno from sc where grade<=60

6.查询不是CS学院的学生姓名、年龄、系别（三种方法实现）
select sname,sage,sdept from student where sdept not in(‘CS’)

select sname,sage,sdept from student where sdept!=‘CS’

select sname,sage,sdept from student where sdept not like ‘CS’

–
select sname,sage,sdept from student where sno not in(
select sno from student where sdept like ‘CS’ )

select sname,sage,sdept from student where sno in(select sno from student where sdept not like ‘CS’ )

select sname,sage,sdept from student s1 where not exists(select * from student s2 where sdept='cs’and s1.sno=sno)

7.查询年龄在19-20岁的学生学号、姓名、系别、年龄
select sno,sname,sage,sdept from student where sage between 19 and 20
select sno,sname,sage,sdept from student where sage>=19 and sage<=20

8.查询姓刘的学生情况
select * from student where sname like ‘刘%’

9.查询姓刘或姓李的学生情况（注意不同的写法）
select * from student where sname like ‘刘%’ or sname like ‘李%’
select * from student where sname like ‘[刘李]%’

10.查询姓刘且名字为两个字的学生情况
select * from student where sname like ‘刘_’

11.查询1999年以后出生的学生姓名
select sname,year(getdate())-sage birth from student where year(getdate())-sage>1999

12.利用内部函数year()查询CS学院学生的出生年份
select sname,year(getdate())-sage birth from student where sdept=‘cs’
13.查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。
select * from student order by sdept,sage desc
14.查询学生总人数
select count() renshu from student
15.查询选修了课程的学生人数（两种方法实现）
select count()renshu from student where exists (select * from sc where student.sno=sc.sno)
select count(distinct sno) xuanxiurenhsu from sc
16.查询选修了2号课程的学生总人数和平均成绩
select count(*) renshu,avg(grade) average from sc where cno=‘2’

17.查询选修2号课程学生的最好成绩
select max(grade) from sc where cno=‘2’
18.查询每个系的系名及学生人数
select sdept,count(*) renshu from student group by sdept

19.查询每门课的选修人数及平均成绩
select cno,count(*)renshu,avg(grade) average from sc group by cno

20.查询没有被选修的课程信息（两种方法实现）
select * from course where cno not in(select distinct cno from sc)
select * from course where not EXISTS (select * from sc where cno=course.cno)

create database EDUC
create table student(sno char(8) primary key,sname char(8),ssex char(2) constraint ck_student_ssex check(ssex=‘男’ or ssex=‘女’),sage int,sdept char(10))
create table course(cno char(2) primary key,cname char(30),criedit int,cpno char(3))
create table sc(sno char(8),cno char(2),grade int constraint ck_sc_grade check(grade<=100 and grade>=1),primary key(sno,cno),
foreign key(sno) references student(sno),foreign key(cno) references course(cno))
insert into student(sno,sname,ssex,sage,sdept)
values(‘95001’,‘李勇’,‘男’,‘20’,‘CS’)
insert into student(sno,sname,ssex,sage,sdept)
values(‘95002’,‘刘晨’,‘女’,‘19’,‘IS’)
insert into student(sno,sname,ssex,sage,sdept)
values(‘95003’,‘王敏’,‘女’,‘18’,‘MA’)
insert into student(sno,sname,ssex,sage,sdept)
values(‘95004’,‘张立’,‘男’,‘19’,‘IS’)
insert into student(sno,sname,ssex,sage,sdept)
values(‘95005’,‘刘云云’,‘女’,‘18’,‘CS’)
insert into course(cno,cname,criedit,cpno)
values(‘1’,‘数据库’,‘4’,‘5’)
insert into course(cno,cname,criedit,cpno)
values(‘2’,‘数学’,‘6’,‘’)
insert into course(cno,cname,criedit,cpno)
values(‘3’,‘信息系统’,‘3’,‘1’)
insert into course(cno,cname,criedit,cpno)
values(‘4’,‘操作系统’,‘4’,‘6’)
insert into course(cno,cname,criedit,cpno)
values(‘5’,‘数据结构’,‘4’,‘7’)
insert into course(cno,cname,criedit,cpno)
values(‘6’,‘数据处理’,‘3’,‘’)
insert into course(cno,cname,criedit,cpno)
values(‘7’,‘PASCAL语言’,‘4’,‘6’)