餐饮网站建设,在ppt里面做网站链接,免费的视频网站如何赚钱,免费网站生成软件实验三:select sno,snamefrom student;//(1)查询全体学生的学号和姓名select *from student;//(2)查询全体学生的详细记录select sname,sage,sdeptfrom student where sdeptMA;//(3)查询软件学院的学生姓名、年龄、系别select distinct snofrom sc;//(4)查询所有选修过课程的学…实验三:select sno,snamefrom student;//(1)查询全体学生的学号和姓名select *from student;//(2)查询全体学生的详细记录select sname,sage,sdeptfrom student where sdeptMA;//(3)查询软件学院的学生姓名、年龄、系别select distinct snofrom sc;//(4)查询所有选修过课程的学生学号(不重复)select distinct snofrom scwhere grade60;//(5)查询考试不及格的学生学号(不重复)select ssex,sage,sdeptfrom studentwhere sdept not in(CS,MA);//(6)查询不是软件学院、计算机系的学生性别、年龄、系别select sno,sname,sdept,sagefrom studentwhere sage18 and sage20;//(7)查询年龄1820岁的学生学号、姓名、系别、年龄select *from studentwhere sname like 刘%;//(8)查询姓刘的学生情况select *from studentwhere sname like 刘%or sname like 李%;//(9)查询姓刘或姓李的学生情况select *from studentwhere sname like 刘_;//(10)查询姓刘且名字为两个字的学生情况select snamefrom studentwhere sage31;//(11)查询1983年以后出生的学生姓名。create table studentgrade(sno char(8) PRimary key,mathgrade tinyint,englishgrade tinyint,chinesegrade tinyint)goinsert 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(mathgradeenglishgradechinesegrade) as sumgradesfrom studentgrade group by sno;(12)创建表 studentgrade(sno,mathgrade,englishigrade,chinesegrade)计算学生各科总成绩并赋予别名select (year(getdate())-student.sage1)from studentwhere sdeptMA;//(13)利用内部函数 year()查找软件学院学生的出生年份select sname 年龄为cast(sage as char(2))岁from student;Select sname ‘年龄为’cast(sage as char(2))’岁’From student//(14)利用字符转换函数实现字符联接。select*from student order by sdept,sage desc;//(15)查询全体学生情况查询结果按所在系升序排列对同一系中的学生按年龄降序排列。select count(*)from student;//查询总人数select count(distinct sno)from sc;//查询选修了课程的学生人数select count(*),avg(grade)as avggradefrom student ,scwhere student.snosc.sno and sc.cno1;//(18)查询选修了7号课程的学生总人数和平均成绩select max(grade) as maxgradefrom scwhere cno2;//(19)查询选修6号课程学生的最好成绩select sdept,count(*)from student group by sdept;//(20)查询每个系的系名及学生人数。select cno,count(*),avg(grade) as avggradefrom sc group by cno;//(21)查找每门课的选修人数及平均成绩select *from coursewhere cpno is null;//(22)查找没有先修课的课程情况
