数据查询

查询所在系为 “CS” 的学生学号和姓名；

select sno,sname

from student

where sdept='CS';

1. 查询选修了3号课程的学生学号；

select sno

from sc

where cno='3';

1. 查询选修1号 课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列；

select sno,grade

from sc

where cno='1' 

order by grade desc,sno asc;

1. 查询选修课程1号课程且成绩在80－90 之间的学生学号和成绩，并将成绩乘以系数0.75 输出；

select sno,0.75*grade

from sc

where cno='1' and grade between 80 and 90;

1. 查询所在系为 “CS”或者“MA”的姓张的学生信息；

select *

from student

where (sdept='cs' or sdept='ma') and sname='张%';

1. 查询缺少了成绩的学生的学号和课程号。

select sno,cno

from sc

where grade is null;

1. 查询学生的学号、姓名、选修的课程名及成绩；

select student.sno,sname,grade,cname

from  student,sc,course

where student.sno=sc.sno and sc.cno=course.cno;

                                  

1. 查询选修1号课程且成绩在90 分以上的学生学号、姓名及成绩；

select student.sno,sname,grade

from  student,sc

where student.sno=sc.sno and cno='1' and grade>90 ;

 

1. 查询每门课程的先行课程的课程名称，学分；

select cname,cpno,ccredit

from  course

where cpno is not null ;

 

1. 查询每一门课的间接先行课的课程编号

select first.cno,second.cpno

from  course first,course second

where first.cpno=second.cno and  second.cpno is not null;

 

1. 查询每一门课的间接先行课的课程名称；

select first.cno,second.cpno,second.cname

from  course first,course second

where first.cpno=second.cno and  second.cpno is not null;

                                  

1. 查询所在系部为“MA”且选修了高等数学课程的学生姓名，年龄，性别；

select sname,sage,ssex

from  course,student,sc

where student.sno=sc.sno and course.cno=sc.cno and sdept='MA' and cname='高等数学';

 

1. 查询选修了数据结构课程，且成绩在90分以上的学生学号；

select sno

from  course,sc

where course.cno=sc.cno and grade>90 and cname='数据结构';

 

1. 查询选修了数据结构课程，且成绩在90分以上的学生姓名，年龄；

select sc.sno,sage

from  course,sc,student

where course.cno=sc.cno and student.sno=sc.sno and grade>90 and cname='数据结构';

 

1. 查询选修了数据结构课程的学生学号，姓名，成绩；

select sc.sno,sname,grade

from  course,sc,student

where course.cno=sc.cno and student.sno=sc.sno  and cname='数据结构';

1. 查询所在系部为“MA”的女生人数；

select COUNT(ssex)

from  student

where sdept='MA' and ssex='女'

group by ssex;

1. 查询选修了2号课程的学生姓名；

select sname

from  student,sc

where student.sno=sc.sno and cno='2';

1. 查询没有选修2号课程的学生姓名；

select sname

from  student,sc

where student.sno=sc.sno and cno!='2';

 

1. 查询选修了全部课程的学生的姓名；

select sname

from  student

where sno in 

      (select sno

       from sc

       group by sno

       having COUNT(*)=(select COUNT(*)from course));

1. 查询至少选修了学号为“201215121”的学生所选修的全部课程的学生学号和姓名；

select distinct sno,sname

from  student

where sno in 

      (select sno

       from sc x

       where not exists

       (select *

       from sc y

       where y.sno='201215121' and not exists

       (select *

       from sc z

       where z.sno=x.sno and z.cno=y.cno)));

1. 查询学生的总人数；

select COUNT(sno)

from  student;

1. 查询每个系部的学生人数；

select sdept,COUNT(sdept)

from  student

group by sdept ;

1. 查询选修了1号课程的学生人数；

select COUNT(sc.sno)

from  student,sc

where student.sno=sc.sno and cno='1' ;

1. 查询选修了操作系统课程的学生人数；

select COUNT(sc.sno)

from  sc,course

where  course.cno=sc.cno and cname='操作系统' ;

1. 查询课程的课程号和选修该课程的人数；

select course.cno,COUNT(sc.sno)

from  sc,course

where  course.cno=sc.cno 

group by course.cno ;

1. 查询选修课超过3 门课的学生学号；

select sno

from  sc

group by sno

having count(cno)>3 ;

1. 查询选修1号课程的最高分，平均分；

select max(grade),avg(grade)

from  sc

where cno='1' ;

1. 查询选修了数据库课程的最高分，平均分；

select max(grade),avg(grade)

from  sc,course

where sc.cno=course.cno and cname='数据库';

1. 查询系别为“CS“，选修2号课程的平均分；

select avg(grade)

from  sc,student

where sc.sno=student.sno and sdept='CS' and Cno=’2’;

1. 查询每个学生的学号，姓名，所修课程的平均分；

select student.sno,sname,avg(grade)

from  sc,student

where sc.sno=student.sno

group by student.sname,student.sno;

1. 查询指定学号的学生所获得的总学分。（成绩大于等于60，则获得该门课程的学分；学号自己给定）；

select sum(Ccredit)

from  Course

where Cno in

(select Cno

from SC 

where Sno='201215121' and grade>=60

);

32.查询每个学生的学号，姓名，所获得的总学分（成绩大于等于60，则获得该门课程的学分）；

select student.sno,sname,sum(Ccredit)zxf

from  sc,student,course

where sc.sno=student.sno and sc.cno=course.cno and grade>=60

group by student.sname,student.sno;

33.查询每门课程的平均分，最高分

select max(grade),avg(grade)

from  sc

group by Cno;

1） 找出工程项目J2使用的各种零件的名称及其数量

select pname,qiy

from p,spj

where jno='j2' and p.pno=spj.pno

2）  找出使用上海产的零件的工程名称

select distinct p.pno

from s,p,spj

where city='上海' and s.sno=spj.sno 

and p.pno=spj.pno

3） 找出没有使用天津供应商生产的红色零件的工程号码

select jno

from j

where jno not in

(select jno

 from spj 

 where sno in

 (select sno

 from s

 where city='天津' and sno in

 (select sno

 from spj

 where pno in

 (select pno

 from p

where color='红'))

))

4） 找出至少使用了供应商S1提供的所有零件的工程号码 

①　select distinct jno

from spj x

where not exists(

select *

from spj y

where sno='s1'and not exists

(select*

from spj z

where x.pno=z.pno and y.jno=z.jno and y.sno=z.sno) 

)

②　select distinct jno

from spj x

where not exists(

select *

from spj y

where sno='s1'and not exists

(select*

from spj z

where x.pno=z.pno and y.jno=z.jno) 

)

5） 找出至少使用了供应商S1提供的所有零件的工程

select distinct jno

from spj x

where not exists(

select *

from spj y

where sno='s1'and not exists

(select*

from spj z

where  x.jno=z.jno and y.pno=z.pno and y.sno=z.sno) 

)

对于course表，插入两条记录，记录的值自己给定。

insert

into course(cno,cname,cpno,ccredit)

values(8,'java语言',7,4)

insert

into course(cno,cname,cpno,ccredit)

values(9,'线性代数',null,3);

对于SC表，将课程编号为2号的最低分改为在原分数*1.1

update sc

set grade=grade*1.1

where grade in

(select min(grade)

from sc

where cno=2) ;

对于SC表，将课程名为数据库的最低分改为在原分数*1.1

update sc

set grade=grade*1.1

where grade in

(select min(grade)

from sc

where cno in

(select cno

from course

where cname='数据库'));

3. 将各系学生人数，平均年龄定义为视图V_NUM_AVG

create view V_NUM_AVG(dno,sums,asage)

as

select sdept dno,count(*),avg(sage)

from student

group by sdept

1. 将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G

create view V_AVG_S_G(SNO,C_SUM,AVG_GRADE)

AS

SELECT SNO,COUNT(CNO),AVG(GRADE)

FROM SC

GROUP BY SNO

查询平均成绩为90分以上的学生学号、姓名和成绩；

参考SQL语句：

Select distinct V_AVG_S_G .sno, V_S_C_G .sname,ascore

From V_AVG_S_G, V_S_C_G

Where V_S_C_G.sno = V_AVG_S_G .sno and ascore > 90;

查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩；

create view V_AVG

as

select SC.Cno,avg(Grade) AvgGrade

from C,SC

where C.Cno=SC.Cno

group by SC.Cno;

select

distinct S.Sno,Sname,C.Cno,Grade

from S,SC,C,V_AVG

  where S.Sno=SC.Sno and C.Cno=V_AVG.Cno and Grade >V_AVG.AvgGrade

按系统计各系平均成绩在80分以上的人数，结果按降序排列；

select distinct student.dno,count(*)sum

 from student,V_AVG_S_G

where student.sno=V_AVG_S_G.sno and ascore>80

group by dno

order by sum desc;

 通过视图V_Computer，新增加一个学生记录 ('201215201','YAN XI',19,'IS')，并查询结果

insert into V_Computer(Sno,Sname,Sage,Sdept) values('201215201','YAN XI',19,'CS');  

select *

from  V_Computer ;

Select * from student; 

1. 在前几次实验所使用的数据库中新建一个教师信息表，表名为Teacher，字段包括Tno(教师编号)，Tname(姓名)，Tsex(性别)，Ttitle(职称)，完整性包括：设定Tno为主键，Tname非空，Tsex的取值范围为“男”或“女”。

create table Teacher

(Tno char(9) primary key,

Tname char(20) not null,

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

Ttitle char(9)

)

2. 用insert语句插入如下记录，观察实验结果：

   insert into Teacher values('2016001','王明','男','讲师')

   insert into Teacher values('2016001','王英','女','助教')

   insert into Teacher values('2016002','张方','a','讲师')

消息2627，级别14，状态1，第2 行

违反了PRIMARY KEY 约束'PK__Teacher__C450026D300424B4'。不能在对象'dbo.Teacher' 中插入重复键。

语句已终止。

消息547，级别16，状态0，第3 行

INSERT 语句与CHECK 约束"CK__Teacher__Tsex__31EC6D26"冲突。该冲突发生于数据库"sc"，表"dbo.Teacher", column 'Tsex'。

语句已终止。

1. 对Teacher表增加一个约束，Ttitle属性的取值范围为（'助教','讲师','副教授','教授'），并插入一条记录：

alter table teacher

add check(Ttitle in('助教','讲师','副教授','教授'));

 insert into Teacher values('2016003','刘阳','男','研究员')，观察实验结果。

消息547，级别16，状态0，第1 行

INSERT 语句与CHECK 约束"CK__Teacher__Ttitle__32E0915F"冲突。该冲突发生于数据库"sc"，表"dbo.Teacher", column 'Ttitle'。

语句已终止。

1. 为学生表Student增加一个约束，Ssex的取值范围为“男”或“女”。

alter table Student

add check(Ssex in('男','女'));

1. 为选课表Sc增加一个约束，Grade的取值在0至100之间。

（提示，3,4,5是对表的结构增加约束定义，使用命令为：Alter table）

alter table Sc

add check(grade between 0 and 100);

1. 为选课表Sc增加参照完整性及违约处理，定义sno为外键，与student表中的sno关联，当删除、更新student中的元组时，级联删除、更新Sc表中的相应元组，命令为：

  alter table sc

   add foreign key(sno) references student(sno)

   on delete cascade

   on update cascade

使用SQL语句，将Student表中姓名为”王敏”的元组，学号更改为“201315123”，然后查看SC表中的元组是否也做了相应的更改。

update Student

set sno='201315123'

where sname='王敏';

select *

from student

where sno='201315123';

1. 为选课表Sc增加参照完整性及违约处理，定义cno为外键，与course表中的cno关联，当删除、更新course中的元组时，级联删除、更新Sc表中的相应元组，并进行验证。

  alter table sc

   add foreign key(cno) references course(cno)

   on delete cascade

   on update cascade

8. 建立一个触发器，当向sc表中添加数据时，如果添加的数据与student表中的数据不匹配（没有对应的学号），则将此数据删除。

create trigger sc_in on sc for insert

as

begin

declare @bh char(5)

select @bh=Inserted.sno

from inserted

if not exists(select sno from student where student.sno=@bh)

  delete from sc where sno=@bh

  End

或

CREATE TRIGGER tr_insert_sc ON sc 

FOR INSERT 

AS

BEGIN

  if (select sno from inserted) 

    not in   (select sno from student)

      begin

        print 'insertion is stopped!'

        rollback

       end

END 

9. 创建一个修改触发器，当student表中的sno信息修改时，自动将SC表中的相应信息也修改。

（简单要求：修改仅为一条记录）

（注：8，9的操作使用create trigger命令，触发器创建成功后，自己使用insert语句，或者update语句进行验证，验证完毕，可以将触发器删除）

create trigger student_up on student for update

as

begin

declare @bh char(5)

select @bh=updated.sno

from updated

select sno from sc where sno=@bh

  End

或

create trigger tri_student_upd

on student

for update

as 

begin

Declare @oldid char(9),@newid char(9)

Select @oldid=sno from deleted

Select @newid=sno from inserted

Update sc set sno=@newid where sno=@oldid

end 

1. 用触发器实现教材例题5.21,5.22的功能。

5.21

create trigger sc_t on sc

after update

as

begin

   create table temp(sno char(9),cno char(4),oldgrade smallint, newgrade smallint)

   insert into temp

   select deleted.sno as sno, deleted.cno as cno, deleted.grade as oldgrade, inserted.grade as newgrade

   from deleted,inserted

   where  deleted.sno=inserted.sno and deleted.cno=inserted.cno

  insert into sc_u

  select * from temp

  where newgrade>=oldgrade*1.1

  drop table temp

End