SQL-Server2012综合练习题1 - 参

1．创建数据库：

操作1.1：创建一个test数据库，其主数据文件逻辑名test_data，物理文件名test_data.mdf，初始大小10MB，最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为test_log，物理文件名为test_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为10%。

2．创建表：

操作2.1：创建学生表：

USE test

GO

CREATE TABLE student

(

    st_id         nVarChar(9)     primary key NOT NULL ,

    st_nm         nVarChar(8)     NOT NULL ,

    st_sex     nVarChar(2)     NULL ,

    st_birth     datetime         NULL ,

    st_score     int             NULL ,

    st_date     datetime         NULL ,

    st_ from     nVarChar(20)    NULL ,

    st_dpid     nVarChar(2)     NULL ,

    st_ mnt     tinyint         NULL

)

GO

操作2.2：创建课程信息表：

USE test

GO

CREATE TABLE couse

(

    cs_id     nVarChar(4)     primary key NOT NULL ,

    cs_nm     nVarChar(20)     NOT NULL ,

    cs_tm     int             NULL ,

    cs_sc     int             NULL

)

GO

操作2.3：创建选课表：

USE test

GO

CREATE TABLE slt_couse

(

    cs_id     nVarChar(4)     NOT NULL ,

    st_id     nVarChar(9)     NOT NULL ,

    score     int             NULL ,

    sltdate     datetime         NULL

)

GO

操作2.4：创建院系信息表：

USE test

GO

CREATE TABLE dept

(

    dp_id     nVarChar(2)     NOT NULL ,

    dp_nm     nVarChar(20)    NOT NULL ,

    dp_drt    nVarChar(8)     NULL ,

    dp_tel    nVarChar(12)    NULL

)

GO 

3．表中插入数据

操作3.1：向dept表插入一条记录，系号11，系名自动控制系，系主任为李其余，电话81234567

INSERT INTO dept VALUES('11', '自动控制系', '李其余', '81234567')

操作3.2：向student表插入一条记录，学号070201001，姓名为王小五，性别为男，出生日期为1990年9月9日，系号为11，其余字段为NULL或默认值

INSERT INTO student(st_id, st_nm, st_sex, st_birth, st_dpid)

VALUES ('070201001', '王小五', '男', '1990.9.9', '11' )

操作3.3：向couse表插入一条记录，课程号1234，课程名为操作系统，其余字段为NULL或默认值

INSERT INTO couse(cs_id, cs_nm) VALUES ('1234', '操作系统')

操作3.4：向slt_couse表插入一条记录，课程号1234，学名070201001，其余字段为NULL或默认值

INSERT INTO slt_couse(cs_id, st_id) VALUES ('1234', '070201001')

4．修改表中数据

操作4.1：修改student表记录，将王小五的入学成绩改为88

UPDATE student SET st_score=88 WHERE st_nm='王小五'

操作4.2：修改couse表记录，将所有记录的学分改为4，学时改为

UPDATE couse SET cs_tm=, cs_sc=4

操作4.3：修改slt_couse表记录，将课程号为1234，学名为070201001的记录的成绩改为77

UPDATE slt_couse SET score=77 WHERE cs_id='1234' AND st_id='070201001'

5．删除表中数据

操作5.1：删除slt_couse表记录，将课程号为1234，学名为070201001的记录删除

DELETE FROM slt_couse WHERE cs_id='1234' AND st_id='070201001'

操作5.2：删除couse表记录，将课程号为1234的记录删除

DELETE FROM couse WHERE cs_id='1234'

6.简单查询

（1）查询表中所有的列

操作6.1：查询所有系的信息

SELECT * FROM dept

（2）查询表中指定列的信息

操作6.2：查询所有的课程号与课程名称

SELECT cs_id, cs_nm FROM couse

（3）在查询列表中使用列表达式

操作6.3：在查询student表时使用列表达式：入学成绩+400

SELECT st_id, st_nm, st_score, st_score+400 AS new_score

FROM student

（4）重新命名查询结果

操作6.4：使用AS关键字为dept表中属性指定列名：系号、系名、系主任、联系电话

SELECT dp_id AS 系号, dp_nm AS 系名, dp_drt AS 系主任, dp_tel AS联系电话

FROM dept

操作6.5：使用"="号为couse表中属性指定列名：课程号、课程名、学时(=cs_sc*16)、学分

SELECT 课程号=cs_id, 课程名=cs_nm, 学分=cs_sc, 学时=cs_sc*16

FROM couse

（5）增加说明列

操作6.6：查询dept表的系号、系名和系主任，向查询结果中插入说明列：系号、系名和系主任

SELECT '系号:', dp_id, '系名:', dp_nm, '系主任:', dp_drt

FROM dept

（6）查询列表中使用系统函数

操作6.7：显示所有学生的学号、姓名、性别和入学年份

SELECT st_id, st_nm, st_sex, DATEPART(yy,st_birth) AS 入学年份

FROM student

操作6.8：显示所有学生学号、姓名、性别和班级(学号前6位)

SELECT st_id, st_nm, st_sex, LEFT(st_id, 6) AS 班级

FROM student

（7）消除查询结果中的重复项

操作6.9：显示所有学生班级

SELECT DISTINCT LEFT(st_id,6) AS 班级 FROM student

（8）取得查询结果的部分行集

操作6.10：显示前5条学生记录信息

SELECT TOP 5 * FROM student

操作6.11：显示前25%条学生记录信息

SELECT TOP 25 PERCENT * FROM student

7．条件查询

（1）使用关系表达式表示查询条件

操作7.1：查询dept表中系号为11的院系信息

SELECT * FROM dept WHERE dp_id = '11'

操作7.2：查询student表中11系的学生学号、姓名、性别和所在系编号

SELECT st_id, st_nm, st_sex, st_dpid FROM student

WHERE st_dpid = '11'

操作7.3：查询student表中2008年及以后入学的学生信息

SELECT * FROM student

WHERE DATEPART( yy, st_date )>= 2008

操作7.4：在查询student表080808班学生的学号、姓名、性别和入学成绩

SELECT st_id, st_nm, st_sex, st_score FROM student

WHERE Left(st_id,6)='080808'

（2）使用逻辑表达式表示查询条件

操作7.5：查询student表中非11系的学生信息

SELECT * FROM student WHERE NOT (st_dpid = '11')

操作7.6：查询选修了1002号课程且成绩在60以下的学生学号

SELECT st_id FROM slt_couse

WHERE (cs_id='1002') AND (score<60)

操作7.7：查询2007年入学的11系所有男生信息

SELECT * FROM student

WHERE DATEPART(yy,st_date)=2007 AND st_dpid='11' AND st_sex='男'

操作7.8：查询11系和12系的学生信息

SELECT * FROM student

WHERE st_dpid='11' OR st_dpid='12'

操作7.9：查询11系和12系所有2007年入学的学生信息

SELECT * FROM  student

WHERE (st_dpid='11' OR st_dpid='12') AND DATEPART(yy,st_date)=2007

（3）使用LIKE关键字进行模糊查询

操作7.10：查询所有“计算机”开头的课程信息

SELECT * FROM couse WHERE cs_nm LIKE '计算机%'

操作7.11：查询所有由三个字组成的“王”姓学生信息

SELECT * FROM student WHERE st_nm LIKE '王__'

操作7.12：查询所有课程名中包含“信息”的课程信息

SELECT * FROM couse WHERE cs_nm LIKE '%信息%'

操作7.13：查询学生姓名介于王姓到张姓的信息

SELECT * FROM student

WHERE st_nm LIKE '[王-张]%'

（4）使用Between…And关键字进行查询

操作7.14：查询在19.7.1到1990.6.30之间出生的学生信息

SELECT st_id, st_nm, st_sex, st_birth FROM student

WHERE st_birth BETWEEN '1981.7.1' AND '1999.6.30'

操作7.15：查询选修了1001号课程且成绩在60到80之间的学生选课信息

SELECT * FROM slt_couse

WHERE cs_id='1001' AND (score BETWEEN 60 AND 80)

（5）使用IN关键字进行查询

操作7.16：查询11系、12系、13系的学生信息

SELECT * FROM student WHERE st_dpid IN ('11', '12','13')

操作7.17：查询所有张，王，李，赵姓的学生的学号、姓名、性别

SELECT st_id, st_nm, st_sex FROM student

WHERE Left(st_nm,1) IN ('张','王','李','赵')

（6）使用[NOT] NULL关键字进行查询

操作7.18：查询所有生源为非空的学生信息

SELECT * FROM  student WHERE  st_from IS NOT NULL

操作7.19：查询选修了1001号课程且成绩为空的学生选课信息

SELECT * FROM slt_couse 

WHERE cs_id='1001' AND score IS NULL

8.查询排序与查询结果存储

操作8.1：查询课程信息，按课程名称降序排序

SELECT * FROM couse ORDER BY cs_nm DESC

操作8.2：查询选修了1001号课程成绩非空的学生学号和成绩，并按成绩降序排序

SELECT st_id, score FROM slt_corse

WHERE cs_id='1001' AND score IS NOT NULL

ORDER BY score DESC

操作8.3：查询11系学生学号、姓名和年龄，按年龄升序排序

SELECT st_id,st_nm,DATEPART(yy,GETDATE( ))-DATEPART(yy,st_birth) AS age

FROM  student

ORDER BY age ASC

操作8.4：查询学生信息，按姓名升序排序，再按系号降序排序

SELECT * FROM student ORDER BY st_nm, st_dpid DESC

操作8.5：创建学生表副本student01，仅保留学生学号、姓名和性别

SELECT st_id, st_nm, st_sex INTO  student01  FROM  student

操作8.6：查询陕西籍学生，将结果保存在新表st_shanxi

SELECT * INTO st_shanxi

FROM  student

WHERE  st_from='陕西省'

操作8.7：查询选修了1001号课程学生的选课信息，按学号升序排序，将结果保存在新表slt1001

SELECT * INTO slt1001 FROM slt_corse

WHERE cs_id='1001' ORDER BY st_id

9.查询统计与汇总

操作9.1：查询课程总数

SELECT  COUNT( * ) FROM  couse

操作9.2：查询选修1001号课程的学生人数

SELECT  COUNT(st_id)

FROM  slt_couse

Where  cs_id = '1001'

操作9.3：查询被选修课程的数量

SELECT  COUNT( DISTINCT cs_id )  FROM  slt_couse

操作9.4：查询选修070101班学生的平均入学成绩

SELECT  AVG(st_score)

FROM  student

WHERE  LEFT(st_id,6)='070101'

操作9.5：查询070101001号学生选修课程的数量、总分以及平均分

SELECT  COUNT(cs_id) AS 课程数量,SUM(score) AS 总分,AVG(score) AS 平均分

FROM  slt_couse

WHERE  st_id='070101001'

操作9.6：查询选修1001号课程的学生人数、最高分、最低分和平均分

SELECT      COUNT(*) AS 学生人数,     MAX(score) AS 最高分, 

              MIN(score) AS 最低分,     AVG (score) AS 平均分

FROM  slt_couse

WHERE  cs_id='1001'

操作9.7：求各个课程号和相应的选课人数

SELECT  cs_id, COUNT(st_id)

FROM  slt_couse GROUP BY cs_id

操作9.8：统计各班人数

SELECT  LEFT(st_id,6) AS 班级, COUNT(st_id) AS 人数

FROM  student

GROUP  BY  LEFT(st_id,6)

操作9.9：依次按班级、系号对学生进行分类统计人数、入学平均分

SELECT  st_dpid AS 系号, LEFT(st_id,6) AS 班级, 

COUNT(st_nm) AS 人数, AVG(st_score) AS 均分

FROM  student  

GROUP  BY LEFT(st_id,6), st_dpid

操作9.10：查询选修了均分在75以上的课程号及均分

SELECT cs_id AS 课程编号, AVG(score) AS 均分

FROM  slt_couse

GROUP  BY cs_id HAVING AVG(score)>75

操作9.11：查询选修了2门以上课程的学生学号

SELECT  st_id FROM slt_couse

GROUP  BY st_id HAVING COUNT(*)>2

操作9.12：明细汇总年龄<20的学生，并汇总学生数量、平均年龄

SELECT  st_nm,DATEPART(yy,GETDATE( ))-DATEPART(yy,st_birth) AS 年龄

FROM  student

WHERE DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)<20

COMPUTE COUNT(st_nm),AVG(DATEPART(yy,GETDATE())-DATEPART(yy,st_birth))

操作9.13：按班级明细汇总成绩<85分的学生，汇总学生数、均分

SELECT st_nm, LEFT(st_id,6) AS 班级, st_score

FROM  student

WHERE st_score<85

ORDER  BY 班级

COMPUTE  COUNT(st_nm), AVG(st_score) BY 班级

10.连接查询

操作10.1：用SQL Server形式连接查询学生学号、姓名、性别及其所选课程编号

SELECT  a.st_id, st_nm, st_sex, cs_id

FROM  student a, slt_couse b

WHERE  a.st_id = b.st_id

ORDER  BY  a.st_id

操作10.2：用ANSI形式连接查询学生学号、姓名、性别及其所选课程编号

SELECT  a.st_id, st_nm, st_sex, cs_id

FROM  student a INNER JOIN slt_couse b

ON  a.st_id = b.st_id

ORDER  BY a.st_id

操作10.3：用SQL Server形式连接查询学生学号、姓名及其所选课程名称及成绩

SELECT  a.st_id, st_nm, cs_nm, score

FROM  student a, slt_couse b, couse c

WHERE  a.st_id = b.st_id AND b.cs_id = c.cs_id

ORDER  BY a.st_id

操作10.4：用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩

SELECT  a.st_id, st_nm, cs_nm, score

FROM  slt_couse a INNER JOIN student b ON a.st_id = b.st_id

INNER  JOIN couse c ON a.cs_id = c.cs_id

ORDER  BY  b.st_id

操作10.5：查询选修了1002课程的学生学号、姓名及1001课程成绩

SELECT  a.st_id, st_nm, score

FROM  student a，slt_couse b

WHERE  a.st_id = b.st_id AND b.cs_id = '1002'

ORDER  BY b.st_id

操作10.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩

SELECT  a.st_id, st_nm, score

FROM  student a, slt_couse b, couse c

WHERE  a.st_id=b.st_id AND b.cs_id=c.cs_id AND c.cs_nm='数据结构'

ORDER  BY  a.st_id

操作10.7：用左外连接查询没有选修任何课程的学生学号、姓名

SELECT  a.st_id, st_nm, score

FROM  student a LEFT OUTER JOIN slt_couse b ON a.st_id = b.st_id

WHERE  b.cs_id IS NULL

ORDER  BY b.st_id

操作10.8：用右外连接查询选修各个课程的学生学号

SELECT  b.cs_id, a.st_id

FROM  slt_couse a Right OUTER JOIN couse b ON a.cs_id = b.cs_id

ORDER  BY  b.cs_id