shiyan5.txt

北@大 03级数据库上机实验内容和习题解答,很有代表性的题目,供大家参考

use master
go
drop database school

create database school
go

use school
go

create table student
(
  Sno char(5) Primary Key,
  Sname varchar(8),
  Ssex char(2),
  Sage tinyint,
  Sdept char(2)
)
go

create table sc
(sno char(5),
 Cno tinyint,
 grade tinyint,
 primary key(sno,cno)
)
go

create table course
( Cno tinyint primary key,
  cname varchar(12),
  cpo tinyint,
  Ccredit tinyint
)
go


exec sp_helpdb school
select * from student
select * from course
select * from sc

insert into student values('95001','李勇','男',20,'CS')
go
insert into student values('95002','刘辰','女',19,'IS')
go
insert into student values('95003','王敏','女',18,'MA')
go
insert into student values('95004','张立','男',19,'IS')
go

insert into course values(1,'数据库',5,4)
go
insert into course values(2,'数学',null,2)
go
insert into course values(3,'信息系统',5,4)
go
insert into course values(4,'操作系统',6,3)
go
insert into course values(5,'数据结构',7,4)
go
insert into course values(6,'数据处理',null,2)
go
insert into course values(7,'PASCAL语言',6,4)
go

insert into sc values('95001',1,92)
go
insert into sc values('95001',2,85)
go
insert into sc values('95001',3,88)
go
insert into sc values('95002',4,90)
go
insert into sc values('95002',5,80)
go


delete
from sc
where sno='95002'
select *from sc
insert into sc values('95002',2,90)
insert into sc values('95002',3,80)
insert into sc values('95003',1,87)
insert into sc values('95003',2,87)
insert into sc values('95004',3,86)
insert into sc values('95005',1,87)
insert into sc values('95005',4,87)
select *from sc
(1)
select distinct sno
from sc scx
where not exists
(select *
from sc scy
where scy.sno='95002' and
not exists
(select *
from sc scz
where scz.sno=scx.sno and
scz.cno=scy.cno)
)
(2)
select distinct sno
from sc
where cno in
(select cno
from sc
where sno='95002'
)
(3)
select sno
from sc
where cno=1 and sno in
(select sno
from sc
where cno=2)
(4)
select distinct sno
from sc
where cno=1 or 
 cno=2
(5)
select sno
from sc
where not exists
(select * 
from sc
where cno=1 or  cno=2
)
(6)//特别++
select *from sc

select distinct cno
from sc 
where  sno='95002' and  cno not in
(select cno
from sc 
where sno='95003')
(7)
select distinct cno
from sc
where sno='95003' and cno not in
(select cno
from sc
where sno='95002')
(8)
select distinct cno
from sc
where cno!=all
(select cno
from sc
where sno='95002')
and sno!='95002'

9:
select distinct sno
from sc
where cno!=any
(select cno
from sc
where sno='95002')
and sno!='95002'

insert into sc values('95001',4,89)

10:
//超级特别与P113的例44相似
select distinct sno
from sc sc1
where not exists
(select *
from sc sc2
where sc2.sno =
(select sno
from student
where sname='刘辰') and not exists
(select *
from sc sc3
where sc3.sno=sc1.sno and sc3.cno=sc2.cno))
and  sc1.sno!=(select sno
from student
where sname='刘辰')



(1);
select distinct sno
from sc
where cno= any
(select cno 
from sc
where sno='95002')and sno!='95002'

(2)注意这里就不能用!=any
select distinct sno
from sc
where cno not in
(select cno 
from sc
where sno='95002')and sno!='95002'





use spj
go
select * from s

select *from spj
select jno,sno,pno
from spj
where pno!=all
(select pno
from spj
where sno in(
select sno
from s
where city='天津' and s.sno=spj.sno))