数据查询及更新

一、目的

1. 掌握SELECT语句的基本语法和查询条件表示方法；
2. 掌握数据表的连接查询、嵌套查询、集合查询的使用方法。
3. 掌握创建及管理视图的方法；

二、要求

1. 了解SELECT语句的基本语法格式和执行方法；
2. 掌握连接查询、嵌套查询和集合查询的语法规则；
3. 掌握使用界面方式和命令方式创建及管理视图；

三、内容

1.以实验3数据库为基础，请使用T-SQL 语句实现进行以下操作： 

1）查询选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；

use sql1
select student表.sno,sname,sdept,sc表.cno,grade
from student表,sc表,course表
where student表.sno=sc表.sno and sc表.cno = course表.cno and cname in ('数学','大学英语')

 2) 查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；

use sql1
select *
from student表
where sage<>(
    select sage
    from student表
    where sname = '张力'
)

3) 按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；

use sql1
select student表.sno,sname,sdept,sum(ccredit) 已修学分
from student表,course表,sc表
where student表.sno = sc表.sno and sc表.cno = course表.cno
group by student表.sno,sname,sdept

 4) 查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；

use sql1
select student表.sno,sname,cno
from student表,sc表
where student表.sno = sc表.sno and cno in(
    select cno
    from sc表,student表
    where student.sno = sc表.sno and student表.sname = '张力')

 

5） 查询只被一名学生选修的程号课程的课、课程名；

use sql1
select cno,cname
from course
where cno in(
    select cno
    from sc表
    group by cno
    having count(*) = 1 
)

 6）使用嵌套查询出选修了“数据结构”课程的学生学号和姓名；

use sql1
select sno,sname
from student表
where student表.sno in(
    select sno
    from sc表
    where sc表.sno in(
        select sno
        from course表
        where course表.cname = '数据结构'
        )
)

 7）使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；

use sql1
select sname,sage,sdept
from student表
where sdept<>'CS' and sage<ANY (
    select sage
    from student表
    where sdept = 'CS'
    )

 

8） 使用ANY、ALL 查询，列出其他院系中比WM系所有学生年龄小的学生的姓名；

use sql1
select sname
from student表
where sage < ALL
(
select sage
from student表
where sdept = 'WM') and sdept!='WM'

9）使用集合查询查询选修1号课程同时选修2号课程的同学的学号与姓名；

use sql1
select sno,sname
from student表
where sno in
        (select sno
         from sc表
         where cno = '1'
         intersect
         select sno
         from sc表
         where cno = '2'
)

 

 10）显示选修02号课程的成绩前两名的学生学号及成绩；

use sql1
select top(2) sno,grade
from sc表
where cno = '2'
order by grade desc

 

11） 显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；

use sql1
select sdept 院系,count(case when ssex = '男' then 1 end )  男生人数,count(case when ssex = '女' then 1 end) 女生人数
from student表
where sdept is not null
group by sdept

12）列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

use sql1
select sno,avg(grade) 平均成绩
from sc表
where grade<60
group by sno
having count(*) >= 2;

13） 显示选修课程数最多的学号及选修课程数最少的学号；

use sql1
select sno 学号,count(cno) 选修课程数
from sc表
group by sno
having count(cno) >= all(select count(cno) from sc表 group by sno)
or count(cno) <= all(select count(cno) from sc表 group by sno)

2.以实验数据库为基础数据，进行如下数据更新。

1）修改student表，将cs系姓名为“李咏”的学生姓名改为“李勇”；

use sql1
update student表 set sname = '李勇'
where sdept = 'cs' and sname = '李咏'
select * 
from student表
where sname = '李勇'

2）将学号为“200515010”的学生信息重新设置为“王丹丹、女、20、MA”；

use sql1
update student表
set sname = '王丹丹',ssex = '女',sage = '20',sdept = 'MA'
where sno = '200515010'
select *
from student表
where sno = '200515010'

 3）修改course表,将“数据处理”的学分改为3学分；

use sql1
update course表 set ccredit = '3'
where cname = '数据处理'
select *
from course表
where cname = '数据处理'

4） 修改SC表，将选修课程“1”的同学成绩加5分；

use sql1
update sc表
set grade = grade+5 where cno = '1'
select *
from sc表 
where cno = '1'

5）删除数据表student中无专业的学生记录；

use sql1
delete from student表
where sdept is null
select *
from student表

6）删除数据表course中学分低于1学分的课程信息；

use sql1
delete from course表
where ccredit<1
select *
from course表

 

如有错误望批评指正，谢谢！