实验四 四、1、列出所有不姓王的学生信息; select * from student where sname not like '王%’ 2、列出姓“王”且全名为2个汉字的学生; select * from student where sname like '王_’ 3、查询出课程名含有“系统”字串的所有课程基本信息; select * from course where cname like '%系统%’ 4、列出选修了'1’号课程的学生,按成绩的降序排列; select * from sc where cno='1’ order by grade desc 5、列出同时选修“1”号课程和“2”号课程的所有学生的学号; select sno from sc where cno='1' intersect select sno from sc where cno='2' 6、列出年龄超过年龄平均值的所有学生名单,按年龄的降序显示; select sname from student where sage>(select avg(sage) from student) order by sage desc 7、按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系,在结果集中列标题分别指定为“学号,姓名,性别,出生年份,院系”; select sno as 学号,sname as 姓名,ssex as 性别, year(getdate())-sage as 出生年份, sdept as 院系 from student order by 出生年份 8、按照课程号、成绩降序显示课程成绩在80分以上的学生的学号、课程号及成绩; Select sno as 学号,cno as 课程号,grade as 成绩 From sc Where grade >80 Order by cno desc,grade desc |
|