实验一
create database studentcourse
create table student (sno char(9) primary key,
sname char(20) unique,
ssex char(2),
sage smallint,
sdept char(20)
);
insert into student values ( '1222','张丽','女',21,'计算机')
insert into student values ( '1223','刘立','男',25,'生物')
insert into student values ( '1224','王晓华','女',22,'计算机')
insert into student values ( '1225','李佳','男',23,'计算机')
insert into student values ( '1226','张月','女',22,'建筑')
create table course( cno char(4) primary key,
cname char(40),
cpon char(4),
ccredit smallint,);
insert into course values ( '0001','数据库','2',4);
insert into course values ( '0002','数据结构','3',4);
insert into course values ( '0003','C语言',' ',4);
insert into course values ( '0004','数学',' ',4);
insert into course values ( '0005','生物学导论',' ',3);
create table sc ( sno char (4),
cno char (4),
grade int (4)
primary key (sno,cno)
);
insert into sc values ('1222','0001','80');
insert into sc values ('1222','0003','92');
insert into sc values ('1222','0004','90');
insert into sc values ('1223','0004','85');
insert into sc values ('1223','0002','90');
insert into sc values ('1224','0001','93');
insert into sc values ('1225','0001','87');
insert into sc values ('1226','0005','88');
insert into sc values ('1224','0002','89');
实验二
SELECT sno,sname
from student
where sdept='数学';
SELECT student.sno
from student,sc
where student.sno=sc.sno;
select sno,grade
from sc
where cno='0001'
order by grade desc ;
select sno,grade*0.8
from sc
where cno='0001' and grade between 80 and 90;
select *
from student
where sdept IN ('数学','计算机') and sname like '张%';
select sno,cno
from sc
where grade='';
select student.sno,sname,ssex,sage,sdept,cno
from student,sc
where student.sno=sc.sno;
select student.sno,sname,sc.cno,grade
from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno;
select student.sno,sname,grade
from student,sc
where cno='0001' and grade>90 and student.sno=sc.sno;
select first.sno,second.cpon
from course first,course second
where first.cpon=second.con;
select avg(grade)
from sc
group by cno;
select*
from student,sc
where grade in (
select grade
from sc
where grade>80
);
实验三
1、select student.sno,sname
from student,course,sc
where cname='数学' and student.sno=sc.sno and course.cno=sc.cno;
3、select *
from student
where sage< (
select max(sage)
from student
where sdept='计算机'
and sdept<>'计算机'
);
4、select *
from student
where sage< any(
select sage
from student
where sdept='计算机'
and sdept<>'计算机'
);
5、select sname
from student,sc
where student.sno=sc.sno and cno='0002' ;
6、select sname
from student
where not exists (
select*
from sc
where cno='0002'
and student.sno=sc.sno
);
7、select sname
from student
where not exists(
select *
from course
where not exists (
select *
from sc
where sno=student.sno
and course.cno=sc.cno
)
);
8、select distinct sname
from student
where not exists (
select *
from student
where sname='张立' and not exists (
select *
from sc
where student.sno=sc.sno
)
);
9、
10、select sno,cno
from sc x
where grade>(
select avg(grade)
from sc y
where y.sno=x.sno
);
实验五
insert into student
values('95020','陈冬','男','18','计算机');
insert into sc
values('95020','C1');
create table dept_age
(sdept char(20) ,
avg_age char(4));
insert into dept_age(sdept,avg_age)
select sdept,avg(sage)
from student
group by sdept;
update student
set sage='22'
where sno='1222';
update student
set sage=sage+1;
update sc
set grade='0'
where '计算机'=(
select sdept
from student
where student.sno=sc.sno
);
delete from student
where sno='1223';
delete from sc;
delete from sc
where '计算机'=
(select sdept
from student
where student.sno=sc.sno);
实验6
create table student1 (
sno char(9) primary key,
sname char(20) unique,
ssex char(2) check (ssex in ('男','女')),
sage smallint check (sage>0 and sage<40),
sdept char (20) );
create table sc1 ( sno char (9),
cno char (9),
grade int ,
primary key (sno,cno),
foreign key (sno) references student(sno),
foreign key (sno) references course(cno)
);
实验7
create clustered index stusno on student(sno);
create unique index coursecno on course(cno);
create view st_co_sc
as
select student.sno,sname,sdept,sc.cno,cname,grade
from student,sc,course
select sname
from st_co_sc
where cno='0001';
