sqlsever练习题大全所有练习代码文字版程序可做见习报告用

2.修改student表（增加属性列“major”）

alter table student add major varchar(20)

3.修改student表（更改属性列“score”数据类型）

alter table student alter column score int

4.修改student表（删除属性列“class”）

alter table student drop column class

5.修改student表（更改属性列“subjects”名）

sp_rename "student.subjects","cno"

6.修改subsjects表（增加属性列“cname”约束条件）

alter table subsjects add unique(cname)

7.删除subsjects数据表

drop table subjects

8.查询各个课程号和相应的选课人数。

select cno,COUNT(sno)选课人数 from CS group by cno
9.查询选修三门或三门以上课程的学生学号。

select sno from CS group by sno having COUNT(*)>=3

10. 查询平均成绩大于等于90分的学生学号和平均成绩。

select sno,AVG(score) from CS group by sno having AVG(score)>=90

11.查询选修2号课程且成绩大于等于90分的学生姓名和学号。

select CS.sno,sname from CS,S where CS.sno=S.sno AND CS.cno='2' AND CS.score>=90

12.查询每个学生的学号、姓名、选修的课程名及成绩。

对应sql语句

select S.sno,sname,cname,CS.score from CS,S,C where S.sno=CS.sno AND CS.cno=C.cno

13.查询表中和赵伟在一个系学习的学生。

select * from s where sdept in (select sdept from S where sname='赵伟')

14.查询表中超过自己选修课程平均成绩的课程号。

对应sql语句

select sno,cno from CS x where score>= (select AVG(score) from CS y where x.sno=y.sno)

15.查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

对应sql语句

select sname,sage

from s

where sage<all

(select sage

from s

where sdept='计算机学院')

and sdept<>;

16.查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。

对应sql语句

select sname,sage

from s

where sage<any

(select sage

from s

where sdept=)

and sdept<>;

17.查询数学系或年龄大于等于19岁的学生。

对应sql语句

select *

from s

where sdept='数学学院'

union

select *

from s

where sage>=19;

18.查询数学系的学生与年龄不小于19岁学生的交集。

对应sql语句

select *

from s

where sdept=

intersect

select *

from s

where sage>=19;

19.查询数学系的学生与年龄不小于19岁学生的差集。

对应sql语句

select *

from s

where sdept=

except

select *

from s

where sage>=19;

20.在S表中插入如下学生信息：'数学学院',201811,'姗姗','女',18。
对应sql语句

INSERT

INTO S(sdept,sno,sname,ssex,sage)

VALUES('数学学院',201811,'姗姗','女',18);

INSERT

INTO S

VALUES('数学学院',201811,'姗姗','女',18);

21. 在S表中插入如下学生信息：'数学学院',201812,'曼曼'。
对应sql语句

INSERT

INTO S(sdept,sno,sname)

VALUES('数学学院',201812,'曼曼');

INSERT

INTO S(sdept,sno,sname)

VALUES('数学学院',201812,'曼曼',null,null);

22. 创建新表s_ascore包含两列sno,ascore，记录学生学号和平均成绩。

对应sql语句

CREATE TABLE s_ascore(

sno varchar(50),

ascore INT

);

INSERT

INTO s_ascore(sno,ascore)

select sno,AVG(score) from CS group by sno

23.将学号为201811的学生年龄改为28。

对应sql语句

UPDATE s SET sage=28 WHERE sno='201811';

24.计算机系学生年龄全部减1。

对应sql语句

UPDATE s

SET sage=sage-1

WHERE sno in

(select sno from S where sdept='计算机学院' )

25.删除201811的学生信息。

对应sql语句

DELETE FROM s WHERE sno=201811;

26.删除计算机学院学生信息。

对应sql语句

delete

from S

WHERE sno in

(select sno from S where sdept='计算机学院' )

27. 创建s1视图，包含所有男生的姓名、学号、年龄、院系，并要求进行修改删除操作时仍保证该视图只有男生。

对应SQL语句

create view s1

as

select sdept,sno,sname,sage

from s

where ssex='男'

with check option

28.创建s2视图，包含所有男生且选修了1号课程的姓名、学号、成绩。

对应SQL语句

create view s2(sno,sname,score)

as

select s.sno,sname,score

from s,cs

where ssex='男' and s.sno=CS.sno and CS.cno='1'

29.建立选修了一号课程的男生且分数在90分以上的人。

对应SQL语句

create view s3

as

select *

from s2

where score>=90

30.建立一个包含学生姓名和出生年月的视图。

对应SQL语句

create view s4(sname,b_d)

as

select sname,YEAR(GETDATE())-sage

from s

31.将每个学生的学号、姓名及平均成绩做一个视图。

对应SQL语句

create view s5(sno,avgscore)

as

select sno,avg(score)

from cs

group by sno

32.删除视图s4。

对应SQL语句

drop view s4

33.查询s1中所有化学学院的学生。

对应SQL语句

select *

from s1

where sdept='化学学院'

34.s1中学号为201806的学生姓名改为珊珊。

对应SQL语句

update s1

set sname='珊珊'

where sno=201806

35. s1中增加学生信息。院系：化学学院，学号：201817，姓名：阿丰，年龄：27。

对应SQL语句

create view s6

as

select sdept,sno,sname,sage,ssex

from s

where ssex='男'

with check option

insert into s6 values('化学学院','201817','阿丰','17','男')

36.删除记录

对应SQL语句

Delete from s1 where sno='201803'

37.将student表中的sno属性定义为主码。

SQL语句

create table Student(

sno char(20) primary key,

sname char(20) ,

ssex char(10) ,

sdeptNo char(20),

);

或者先定义再增加条件：（此刻必须先加上sno不为空）

create table Student1(

Sno char(11) not null ,

Sname char(20) ,

Ssex char(1) ,

SdeptNo char(4),

);

alter table student1 add primary key(sno)

38.在定义Course表时，Cno为主键,Cname属性不允许空值。

SQL语句

create table Course(

Cno char(5) primary key,

Cname varchar(20) not null,

Preno char(5),

credit int,

);

39.定义SC中的参照完整性。

SQL语句

create table SC(

SNo char(20),

CNo char(5),

Grade int

foreign key(Sno) references Student(Sno)

on delete cascade

on update cascade,

foreign key(Cno) references Course(Cno)

on delete cascade

on update cascade,

);

操作：

DELETE FROM sc WHERE sno=201811;

40.建立SD表，要求部门名称Sdept列取值唯一。

SQL语句

create table SD(

SdeptNo char(4) primary key ,

Sdept varchar(20) unique,

Mname char(10),

);

41. 用CHECK短语指定性别只能为男或女。

SQL语句

create table Student1(

Sno char(20) primary key,

Sname char(20) not null,

Ssex char(20) CHECK (Ssex='男' or Ssex='女'),

SdeptNo char(20),

);

42.建立student2表，要求当学生的性别是男时，不能姓李。

SQL语句

create table student2

(sno char(20)primary key,

sname char(20)not null,

ssex char(20),

sage smallint,

sdept char(20),

check (ssex='女'OR sname not like '李%')

);

自己练习规则与约束。

（1）规则

SQL语句

Create rule rssex as @Ssex in ('男','女')

Exec sp_bindrule 'rssex','Student.ssex'

执行规则后不会对前面已经输入的数据再次判断检验，而是对之后的操作开始检验。

（2）约束

SQL语句

alter table student add CHECK (Ssex='男' or Ssex='女')

因为前面有已经录入的错误数据，所以拒绝执行。


代码速查
创建数据库表：student、course create table student(sno nchar(10),sname nchar(10),

ssex nchar(1),score int,sdept nchar(10),primary key(sno,sname));

create table course (cno nchar(10),cname nchar(10),ccredit nchar(10),primary key(cno));

查询学生表的总人数select COUNT (*) 总人数 from student

查询学生表的总人数select COUNT (*) 总人数 from student

查询计算机科学系全体学生的名单select * from student where sdept ='CS'

查询所有学生的出生年份select * ,year(getdate())-sage 出生年份 from student

查询学号为“2018503”的数学分析成绩

select grade from score where sno='2018503' and cno='2'

查询所有王姓学生信息select * from student where sname like '王%'

查询那些学生的学科成绩还没有录入select sno，cno from score where grade is null

查询学分为2，3学分的课程名select cname from course where ccredit in(2,3)

查询计算机系学生及其课程得分的情况 select student.*,score.*from student,score where sdept='CS' AND student.sno=score.sno

查询王敏各个课程的成绩select student.*,score.*,cname

from student,score,course where sname='王敏' and student.sno=score.sno and course.cno=score.cno

查询选修3号课程且成绩在80分以上的所有学生的学号和姓名：select student.sno,sname

from student,score where student.sno=score.sno and score.cno='2' and score.grade>80

查询选修了课程名为‘数学分析’的学生学号和姓名：select sno,sname from student where sno in (select sno from score where cno in (select cno from course where cname='数学分析'))

查询非数学系中比数学系任意一个学生年龄小的学生信息：select student.* from student where sage<any (select sage from student where sdept='MA') AND sdept<>'MA'

查询没有选修2号课程的学生姓名select sname,sno,sdept from student where not exists (select* from score where sno=student.sno and cno ='2')

查询选修了全部课程的学生信息select sname from student where not exists

(select* from course where not exists (select* from score where cno =course.cno and sno=student.sno))

查询数学系的学生与年龄不大于20岁的学生的差集select* from student where sdept = 'MA'

except select* from student where sage<=20

查询既选修了课程1又选修了课程2的学生select score.sno,sname from student,score

where cno = '1' and score.sno=student.sno intersect select score.sno,sname from student,score where cno = '2' and score.sno=student.sno


对每个系，求学生的平均年龄，并把结果存入数据库

create table Dept_age (dept NCHAR(10) avg_age smallint);

INSERT INTO Dept_age(dept,avg_age)select sdept,AVG(sage)from student

group by sdept;


删除学号为2018501的学生信息DELETE FROM student where sno='2018501'

删除数学系所有学生的选课记录DELETE FROM score where sno in (select sno from student where sdept='MA');

建立计算机系学生的视图CREATE VIEW CS_Stuent AS SELECT sno,sname,sage from student


建立数学系选修1号课程且成绩在90分以上的学生学生视图

CREATE VIEW MA_S2 AS SELECT sno,sname,grade from MA_S1

where grade >=90

定义一个反映学生出生年份的视图

CREATE VIEW BIRTH(sno,sname,sbirth) AS SELECT sno,sname,2021-sage

from student


将student表中所有男生记录定义为一个视图

CREATE VIEW B_STU(B_no,name,sex,age,dept) AS SELECT* from student

where ssex='男'

删除视图B_STU、BIRTH drop view BIRTH; drop view B_STU;

查询avg_student视图中平均成绩在90分以下的学生学号和平均成绩select*from avg_student

where savg<=90



将student表中的sno属性定义为码

create table student

(sno char(9)primary key,

sname char(20)not null,ssex char(2),

sage smallint,sdept char(20));

定义SC中的参照完整性create table sc

(sno char(9)not null,

cno nchar(5) not null,

grade smallint,

primary key(sno,cno),

foreign key (sno) references student(sno),

foreign key (cno) references course(cno),);

在定义sc表时，说明sno、cno、grade属性不允许空值

create table sc

(sno char(9)not null,

cno nchar(4) not null,

grade smallint not null

,primary key(sno,cno),);

建立部门表dept，要求部门名称dname列取值唯一，部门编号deptno列为主码

create table dept

(deptno numeric(2),

dname char(9) unique not null,

location char(10),

primary key(deptno),);

用CHECK短语指定列表值应该满足的条件create table student

(sno char(9)primary key,

sname char(8)not null,

ssex char(2) check(ssex in('男','女')),

sage smallint,

sdept char(20)

);

将student表中所有男生记录定义为一个视图

CREATE VIEW B_STU(B_no,name,sex,age,dept)

AS

SELECT*

from student

where ssex='男'

当学生的性别是男时，其名字不能以MS.打头

create table student

(sno char(9)primary key,

sname char(8)not null,

ssex char(2),

sage smallint,

sdept char(20),

check (ssex='女'OR sname not like 'MS.%')

);

建立教师表teacher，要求每个教师的应发工资不低于3000元，应发工资是工资列sal与扣除列deduct之和

create table teacher

(eno numeric(4)primary key,

ename char(10),

job char(8),

sal numeric(7,2),

deduct numeric(7,2),

deptno numeric(2),

constraint teacherkey foreign key(deptno)

references dept(deptno),

constraint c1 check(sal+deduct>=3000));