–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/article/details/80426142