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

--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