第五章例题

5.1:将Student表中的Sno属性定义为码

create table student1
(sno char(9)primary key,
sname char(20) not null,
ssex char(2),
sage smallint,
sdept char(20)
);

在这里插入图片描述

5.2:将SC表中的Sno,Cno属性组定义为码

create table SC
(sno char(9) not null,
cno char(4) not null,
grade smallint,
primary key (Sno,Cno)
);

在这里插入图片描述

5.3: 定义SC中的参照完整性

create table sc
(sno char(9) not null,
cno char(4) not null,
grade smallint,
primary key (sno,cno)foreign key(sno)references student(sno),
foreign key(cno)references course(cno)
)

在这里插入图片描述

5.4: 显示说明参照完整性的违约处理示例

create table sc
(sno char(9) not null,
cno char(4) not null,
grade smallint,
primary key (sno,cno)foreign key(sno)references student(sno)
	on delete cascade
	on update cascade
foreign key(cno)references course(cno)
	on delete no action
	on update cascade
)

5.5: 在定义SC表时,说明Sno,Cno,Grade属性不允许为空值

create table SC1
(sno char(9) not null,
cno char(4) not null,
grade smallint not null,
primary key (sno,cno)
);

在这里插入图片描述

5.6; 建立部门表DEPT,要求部门名称Dname列取值唯一,部门编号Deptno列为主码

create table dept
(deptno numeric(2),
 dname char(9) unique not null,      
 location char(10),
 primary key(Deptno)
);

在这里插入图片描述

5.7:Student表的Ssex只允许取“男”或“女”


create table student 
(sno char(9)primary key,
sname char(8) not null,
ssex char(2) check(ssex in('男','女')),
sage smallint,
sdept char(20)
);

在这里插入图片描述

:5.8:SC表的Grade的值应该在0到100之间

create table SC2
(sno char(9) not null,
cno char(4) not null,
grade smallint check (Grade>=10 and Grade <=100),
primary key (Sno,Cno),
foreign key(Sno)references Student(Sno),
foreign key(Cno)references Course(Cno)
);

在这里插入图片描述

:5.9:”当学生的性别是男时,其名字不能以Ms.开头。

create table Student4
( sno char(9),
  sname char(8) not null,
  ssex char(2),
  sage smallint,
  sdept char(20),
  primary key(Sno),
  check (Ssex='女'or Sname not like 'Ms.%')
  );

在这里插入图片描述

5.10:建立学生登记表Student,要求学号在90000~99999之间,姓名不能取空值,年龄小于30,性别只能是“男”或“女”

create table Student
( Sno numeric(6)
constraint C1 check(Sno between 90000 and 99999),
sname char(20)
constraint C2 not null,
sage numeric C3 check (Sage<30),
ssex char(2)
constraint C4 check(Ssex in('男','女')),
constraint StudentKey primary e=key(Sno)
);

:5.11:建立教师表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 Teacherfkey foreign key(Deptno)
 references Dept(Deptno),
 constraint C1 check(Sal+Deduct>=3000)
 );

5.:12:去掉例5.10 Student表中对性别的限制。

alter table student
drop constraint c4;

5.13: 修改表Student中的约束条件,要求学号改为在900000~999999之间,年龄由小于30改为小于40

alter table student
drop constraint c1;
alter table student
add constraint c1 check (Sno between 900000 and 99999);
alter table Student
drop constraint c3;
alter table student
add constraint c3 check (Sage<40);

5.18:限制数据库课程最多60名学生选修

create assertion ASSE_SC_DB_NUM
check (60 >= 
  		(
  		select count(*)
        from Course,SC
        where SC.Cno=Course.Cno and   Course.Cname ='数据库')
       );

:
5.20:限制每个学期每一门课程最多60名学生选修

alter table SC add term date;
create (60>=all(
				select count(*)
				from SC group by Cno,term)
				);

:5.21: 当对表SC的Grade属性进行修改时,若分数增加了10%则将此次操作记录到下面表中:SC_U(Sno,Cno,Oldgrade,Newgrade)
其中Oldgrade是修改前的分数,Newgrade是修改后的分数。

create trigger  SC_T		
after update of Grade on SC
referencing
	 old row  as  OldTuple,
	 new row  as  NewTuple
for each row
when(NewTuple.Grade >= 1.1 * OldTuple.Grade)
	 insert into SC_U(Sno,Cno,OldGrade,NewGrade) 				
	 values(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade);

5.23: 定义一个BEFORE行级触发器,为教师表Teacher定义完整性规则“教授的工资不得低于4000元,如果低于4000元,自动改为4000元”。

Create trigger Insert_Or_Update_Sal 
before  insert or update on Teacher  --触发事件是插入或更新操作
for each row       
BEGIN              --定义触发动作体,这是一个PL/SQL过程块
  if(new.Job = '教授') and (new.Sal < 4000) 
     then  new.Sal := 4000;                
   	 end if;
end;  
  • 2
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
以下是第五版《代码答案形式的C语言程序设计》第八章的例题答案: 8.1 ```c #include <stdio.h> #define N 5 int main() { int a[N], max, i; printf("Enter array a:\n"); for (i = 0; i < N; i++) scanf("%d", &a[i]); max = a[0]; for (i = 1; i < N; i++) if (a[i] > max) max = a[i]; printf("max=%d\n", max); return 0; } ``` 8.2 ```c #include <stdio.h> #define N 5 int main() { int a[N], i; printf("Enter array a:\n"); for (i = 0; i < N; i++) scanf("%d", &a[i]); printf("Reverse array a:\n"); for (i = N - 1; i >= 0; i--) printf("%d ", a[i]); printf("\n"); return 0; } ``` 8.3 ```c #include <stdio.h> #define N 5 int main() { int a[N], i; printf("Enter array a:\n"); for (i = 0; i < N; i++) scanf("%d", &a[i]); printf("a[0]=%d, a[%d]=%d\n", a[0], N - 1, a[N - 1]); return 0; } ``` 8.4 ```c #include <stdio.h> #define N 5 int main() { int a[N], i, sum = 0; printf("Enter array a:\n"); for (i = 0; i < N; i++) scanf("%d", &a[i]); for (i = 0; i < N; i++) sum += a[i]; printf("sum=%d, average=%.2f\n", sum, (float)sum / N); return 0; } ``` 8.5 ```c #include <stdio.h> #define N 5 int main() { int a[N], i, temp; printf("Enter array a:\n"); for (i = 0; i < N; i++) scanf("%d", &a[i]); for (i = 0; i < N / 2; i++) { temp = a[i]; a[i] = a[N - i - 1]; a[N - i - 1] = temp; } printf("Reverse array a:\n"); for (i = 0; i < N; i++) printf("%d ", a[i]); printf("\n"); return 0; } ``` 8.6 ```c #include <stdio.h> #define N 5 int main() { int a[N], i, j, temp; printf("Enter array a:\n"); for (i = 0; i < N; i++) scanf("%d", &a[i]); for (i = 0; i < N - 1; i++) for (j = i + 1; j < N; j++) if (a[i] > a[j]) { temp = a[i]; a[i] = a[j]; a[j] = temp; } printf("Sort array a:\n"); for (i = 0; i < N; i++) printf("%d ", a[i]); printf("\n"); return 0; } ``` 8.7 ```c #include <stdio.h> #define N 5 int main() { int a[N], i, j, temp; printf("Enter array a:\n"); for (i = 0; i < N; i++) scanf("%d", &a[i]); for (i = 0; i < N - 1; i++) for (j = 0; j < N - i - 1; j++) if (a[j] > a[j + 1]) { temp = a[j]; a[j] = a[j + 1]; a[j + 1] = temp; } printf("Sort array a:\n"); for (i = 0; i < N; i++) printf("%d ", a[i]); printf("\n"); return 0; } ``` 8.8 ```c #include <stdio.h> #define N 5 int main() { int a[N], i; printf("Enter array a:\n"); for (i = 0; i < N; i++) scanf("%d", &a[i]); printf("Max 5 numbers in array a:\n"); for (i = 0; i < 5; i++) printf("%d ", a[i]); printf("\n"); return 0; } ``` 8.9 ```c #include <stdio.h> #define N 5 int main() { int a[N], i, j, temp; printf("Enter array a:\n"); for (i = 0; i < N; i++) scanf("%d", &a[i]); for (i = 0; i < N - 1; i++) for (j = 0; j < N - i - 1; j++) if (a[j] > a[j + 1]) { temp = a[j]; a[j] = a[j + 1]; a[j + 1] = temp; } printf("Median number in array a:\n"); printf("%d\n", a[N / 2]); return 0; } ``` 8.10 ```c #include <stdio.h> #define N 5 int main() { int a[N], i, j, temp; printf("Enter array a:\n"); for (i = 0; i < N; i++) scanf("%d", &a[i]); for (i = 0; i < N - 1; i++) for (j = 0; j < N - i - 1; j++) if (a[j] > a[j + 1]) { temp = a[j]; a[j] = a[j + 1]; a[j + 1] = temp; } printf("Mode number in array a:\n"); for (i = 0; i < N; i++) { int count = 0, k; for (j = i; j < N; j++) if (a[j] == a[i]) count++; for (k = i - 1; k >= 0; k--) if (a[k] == a[i]) break; if (k < 0 && count > 1) printf("%d ", a[i]); } printf("\n"); return 0; } ```

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值