数据库原理实验指导（三）使用SQL语言进行简单查询【转载csdn】

--1.查询全体学生的学号和姓名select sno,sname from student

--2.查询全体学生的详细记录select * from student

--3.查询软件学院的学生姓名，年龄，系别select sname,sage,sdept from studentwhere sdept='CS'

--4.查询所有选修过课程的学生学号（不重复）select distinct sno from sc

--5.查询考试不及格的学生学号（不重复）select distinct sno from scwhere grade<60

--6.查询不是软件学院、计算机系的学生性别、年龄、系别        select ssex,sage,sdeptfrom student where sdept not in('CS','IS')                             

--7.查询年龄18－20岁的学生学号、姓名、系别、年龄；select sno,sname,sdept,sage from studentwhere sage between 18 and 20/*select sno,sname,sdept,sage from studentwhere sage>=18 and sage<=20;*/                          

--8.查询姓刘的学生情况select * from studentwhere sname like '刘%'

--9.查询姓刘或姓李的学生情况select * from studentwhere sname like '刘%'or sname like '李%'                   --多字符，单字符通配

--10.查询姓刘且名字为两个字的学生情况select * from studentwhere sname like '刘_'

--11.查询1983年以后出生的学生姓名select sname,sage from studentwhere sage<getdate()-1983                                   

--getdate()获取系统当前时间

--12.创建表 studentgrad(sno,mathgrade,englishigrade,chinesegrade)

计算学生各科总成绩并赋予别名create table studentgrade(sno char(8) primary key,mathgrade tinyint,englishgrade tinyint,chinesegrade tinyint)insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95001',85,95,74)insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95002',86,91,70)insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95003',80,92,71)insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95004',81,91,75)insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95005',87,97,78)select sno,sum(mathgrade+englishgrade+chinesegrade) as sumgradesfrom studentgrade group by sno

--13.利用内部函数 year()查找软件学院学生的出生年份select sname,(year(getdate())-student.sage )from student where sdept='CS'

--14.利用字符转换函数实现字符联接select sname + '年龄为'+cast(sage as char(2))+'岁'   

--字符转换函数cast(),sage后必须要加上as 字符型from student

--15.学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列select * from student order by sdept ,sage DESC                           --order by   asc升序  desc降序

--16.查询学生总人数select count(*) from student

--17.查询选修了课程的学生人数select  count(distinct sno) from sc

--18.查询选修了1号课程的学生总人数和平均成绩select count(sno),avg(grade)as avggrade from scwhere cno=1/*select count(*),avg(grade)as avggrade from student ,sc where student.sno=sc.sno and sc.cno='1'*/            --two

--19.查询选修2号课程学生的最好成绩select max(grade)as maxgrade from scwhere cno=2

--20.查询每个系的系名及学生人数select sdept,count(*) from student group by sdept/*select sdept,count(sno) from student group by sdept*/                                   --two--

21.查找每门课的选修人数及平均成绩select cno,count(*)as '选修人数',avg(grade)as avggrade from scgroup by cno

--22.查找没有先修课的课程情况select * from course where cpno is null
————————————————
版权声明：本文为CSDN博主「Black博士」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_42294230/articlehttps://img.qb5200.com/download-x/details/80426142