SQL Server2012——使用SQL语言进行简单查询

使用SQL语言进行简单查询

查询全体学生的学号和姓名

select sno,sname 
from student

查询全体学生的详细记录

select * 
from student

查询软件学院的学生姓名、年龄、系别

select sname,sage,sdept
from student where sdept='MA'

查询所有选修过课程的学生学号（不重复）

select distinct sno 
from sc

查询考试不及格的学生学号（不重复）

select distinct sno
from sc
where grade < 60

查询不是软件学院、计算机系的学生性别、年龄、系别

select ssex,sage,sdept
from student 
where sdept not in('CS','MA')

查询年龄18－20岁的学生学号、姓名、系别、年龄

select sno,sname,sdept,sage 
from student
where sage>=18 and sage<=20

查询姓刘的学生情况

select * 
from student
where sname like '刘%'

查询姓刘或姓李的学生情况

select *
from student 
where sname like '刘%'or sname like '李%'

查询姓刘且名字为两个字的学生情况

select * 
from student
where sname like '刘_'

查询1983年以后出生的学生姓名

select sname
from student
where sage<31

创建表 studentgrade(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)
insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95006',81,97,70)
insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95007',85,95,70)

select sum(mathgrade+englishgrade+chinesegrade) as sumgrades
from studentgrade group by sno

利用内部函数 year()查找软件学院学生的出生年份

select (year(getdate())-student.sage+1) 
from student 
where sdept='MA'

利用字符转换函数实现字符联接

select sname + '年龄为'+cast(sage as char(2))+'岁'
from student

查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列

select*
from student 
order by sdept,sage desc

查询总人数

select count(*) 
from student

查询选修了课程的学生人数

select count(distinct sno) 
from sc

查询选修了7号课程的学生总人数和平均成绩

select count(*),avg(grade)as avggrade 
from student ,sc 
where student.sno=sc.sno and sc.cno='7'

查询选修6号课程学生的最好成绩

select max(grade) as maxgrade 
from sc
where cno='6'

查询每个系的系名及学生人数

select sdept,count(*) 
from student group by sdept

查找每门课的选修人数及平均成绩

select cno,count(*),avg(grade) as avggrade 
from sc group by cno

查找没有先修课的课程情况

select * 
from course 
where cpno is null