(2)A#
(3)第二范式,因为A#、A1、A3存在传递函数依赖
(4)不一定。第三范式是指不存在部分函数依赖和传递函数依赖
但是,该模式不能保证B1、B2之间是否存在函数依赖关系,同理也不能保证R1内的各属性之间的函数依赖关系
1.(5)select 学号,姓名,专业
from 学生
where 学号 not in(
select 学号
from 学习
where 课程号=‘C135’);
(6)select 学号,姓名,专业
from 学生
where 学号 in(
select 学号
from 学习 X,学习 Y
where X.学号=Y.学号 and X.课程号=C‘135’ and Y.课程号=‘C219’);
(7)delete
from 学生
where 学号 in(
select 学号
from 学习
where 分数=0);
(8)create view AAA
AS
select 学号,姓名,课程号,分数
from 学生,学习
where 学生.学号=学习.学号 and 专业=‘英语’;
(1)、create table CJB(sno char(6) not null,
cno char(3) not null,
grade int default '0',
primary key(sno,cno),
foreign key(sno) references XSB(sno),
foreign key(cno) references KCB(cno)
)
(2)、alter table XSB add sumcredit int default '0'
(3)、insert into XSB values('081101','王英','女',to_date('1990-02-07','yyyy-mm-dd'),'计算机','55')
(4)、select sno "学号",sname "姓名" from XSB where sumcredit>50 and rownum<10
3、select XSB.sno,sname,CJB.grade from XSB,KCB,CJB where CJB.sno=XSB.sno and CJB.cno=KCB.cno XSB.专业(图片看不清)='计算机专业' and KCB.cname='C语言程序设计' group by XSB.sno order by grade desc
borrower:create table borrower(
姓名 char[20] not null,
系名 char[10],
班级 char[10])
create table loans(
借书证号 char[5],
图书登记号 char[6],
结束日期 DATE,
primary key(借书证号,图书登记号),
foreign key(借书证号) reference borrower(借书证号),
foreign key(图书登记号 reference books(图书登记号))
create table books(
索书号 char[10],
书名 char[20],
图书登记号 char[6] primary key,
出版社 char[20],
价格 smallint))
(1)
select borrower.借书证号,姓名,系名,temp.total as 借书数量
from borrower,(select 借书证号,count(图书登记号) as total
from loans group by 借书证号
where tatal>5 as temp(借书证号,total))
(2)
select borrower.姓名,系名,书名,结束日期
from borrower,loans,books
where borrower.借书证号=loans.借书证号
and books.图书登记号=loans.图书登记号
and 书名 in(selcet 书名
from borrower,loans,books
where borrower.借书证号=loans.借书证号
and books.图书登记号=loans.图书登记号
and 姓名='赵垒')
(3)
create view SB
as
select borrower.借书证号,姓名,班级,books.图书登记号,书名,出版社
,借书日期
from borrower,book,loans
where borrower.借书证号=loans.借书证号
and books.图书登记号=loans.图书登记号
and 系名='信息系'
1
create table student(
sno char[10] primary key,
sname char[20] not null,
ssex char[2],
sage smallint check( sage between 16 and 30),
sdept char[4])
create table course(
cno char[10] primary key,
cname char[20] not null,
cteacher char[20])
create table sc(
sno char[10],
cno char[10],
grade smallint check(grade is null or grade between 0 and
100),
primary key(sno,cno)
foreign key sno reference student(sno),
foreign key cno reference course(cno))
2
insert into student values('102','李四','男',16,'数学')
下同
insert into course values('203',' *** 作系统','程羽')
下同
insert into sc values('101','203',82)
下同
3
(1) select cname,grade
from sc,student,course
where student.sno=sc.sno and course.cno=sc.cno
and sname='张三'
(2) select sname from student
where sno in(select sno from sc x
where not exists(
select * from sc y
where x.sno=y.sno
and y.grade<60))
(3) select cname,sname,grade
from student,course sc,(select cno,max(grade) from sc group by cno
as temp(cno,max))
where student.sno=sc.sno and course.cno=sc.cno
and grade=max and course.cno=temp.cno
4
delete * from sc where grade<60
5
update sc
set grade=(select avg(grade) from sc where cno='203')
where sno=105
仅供参考
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)