Microsoft Office Access(前名 Microsoft Access)是由微软发布的关联式数据库管理系统。它结合了 Microsoft Jet Database Engine 和 图形用户界面两项特点,是 Microsoft Office的成员之一。
1
create view view_1
as
select a,b,cgrade,cxklb
from Student a,course b,sc c
where asno=csno and bcno=ccno
2
create view view_2
as
select sname,convert(varchar(4),dateadd(year,-Sage,getdate()),120) birth_year
from student
3
create view view_3
as
select sno 学号,sname 姓名,ssex 性别,sage 年龄,sdept 系别
from student
4
create view view_4
as
select sno,sname,ssex,sdept from student where sdept='计算机系'
赋权
grant select on view_4 to 用户名
5
create view view_5
as
select cno,avg(grade) avg_grade from sc
group by cno
查询
select asno,acno,agrade from sc a,view_5 b
where acno=bcno and agrade>bavg_grade
-- Create table
create table Student
(
S_No varchar2(20),
S_Name varchar2(20) not null,
S_Sex varchar2(2) default '男',
S_Birthday date
)
;
-- Add comments to the table
comment on table Student
is '学生';
-- Create/Recreate primary, unique and foreign key constraints
alter table Student
add constraint Pri_S_No primary key (S_NO);
-- Create table
create table Course
(
C_No varchar2(20),
C_Name varchar2(40),
Grade number(4,1)
)
;
-- Add comments to the table
comment on table Course
is '课程';
-- Add comments to the columns
comment on column CourseGrade
is '分数';
-- Create/Recreate primary, unique and foreign key constraints
alter table Course
add constraint Pri_C_No primary key (C_NO);
-- Create table
create table T_Course
(
S_no varchar2(20),
C_no varchar2(20),
Grade number(4,1)
)
;
-- Add comments to the table
comment on table T_Course
is 'take the course选课信息';
-- Add comments to the columns
comment on column T_CourseGrade
is '成绩';
-- Create/Recreate primary, unique and foreign key constraints
alter table MEAT_Course
add constraint For_S_No foreign key (S_NO)
references Student (S_No);
alter table MEAT_Course
add constraint For_C_No foreign key (C_NO)
references Course (C_No);
3:
-- Add/modify columns
alter table Student add S_Tel varchar2(20);
-- Add comments to the columns
comment on column Student S_Tel
is '电话';
4:
-- Add/modify columns
alter table Course modify C_Name CVARCHAR2(20);
5:
INSERT INTO Student (S_No, S_Name,S_Sex, S_Birthday ) VALUES ('A001','日月哥','女',to_date('20110515','yyyyMMdd'))
其他你自己来
6:
update Course set Grade =Grade +1
7:
-- Create/Recreate indexes
create unique index PK_oo on Student(S_Birthday order by desc);
8:
select sS_No, avg(tcGrade) from Student s,T_Course tc where sS_No=tcS_No group by sS_No
9:
delete T_Course tc where tcS_No='A000';
delete Student s where sS_No='A000';
思考题自己来,我用的orcal,这都不给分的话,爆你菊花啦
我也是菜鸟,写了一个你瞧瞧。
select from aaa x ;银行ys 贷款金额je rq贷款日期 贷款年限nx gs公司名称
工行 3 2017-5-8 15 塞德斯公司
建行 5 2018-6-9 12 塞德斯公司
中国银行 6 2016-6-8 5 塞德斯公司
中国银行 66 2016-6-8 77 dfgfdgh
建行 34 2016-6-8 55 dfgfdghdeclare
v_yh varchar2(100);
v_je number;
v_rq varchar2(100);
v_nx varchar2(100);
v_gs varchar2(100);
vv_gs varchar2(100);
vv_sum number ;
cursor emp_cursor is select from aaa;
cursor emp_cursor2 is select gs,sum(je) from aaa group by gs;
begin dbms_outputenable(500000);
open emp_cursor2 ; loop fetch emp_cursor2 into vv_gs,vv_sum; exit when emp_cursor2%notfound;
DBMS_OUTPUTPUT_LINE(vv_gs||'总贷款金额为'||vv_sum||'万元,具体情况为如下:');
open emp_cursor;
loop
fetch emp_cursor into v_yh,v_je,v_rq,v_nx,v_gs;
exit when emp_cursor%notfound;
if vv_gs=v_gs then
DBMS_OUTPUTPUT_LINE(v_yh||','||v_rq); end if;
end loop;
close emp_cursor; end loop; close emp_cursor2;
end;
效果
db2stop force
db2start
db2 drop db EXAMDB
db2 create db EXAMDB using codeset GBK territory CN
mkdir /home/db2inst1/db2inst1/NODE0000/EXAMDB/archive_log
mkdir /home/db2inst1/db2inst1/NODE0000/EXAMDB/mirrorlog
db2 update db cfg for EXAMDB using LOGFILSIZ 1024 LOGPRIMARY 10 MIRRORLOGPATH "/home/db2inst1/db2inst1/NODE0000/EXAMDB/mirrorlog" LOGARCHMETH1 "disk:/home/db2inst1/db2inst1/NODE0000/EXAMDB/archive_log"
db2stop force
db2start
db2 backup db EXAMDB
db2 connect to EXAMDB
db2 create bufferpool BP_TMP size 1600 pagesize 32 K
db2 create bufferpool BP_DATA size 1600 pagesize 32 K
db2 "create tablespace TBS_USRTMP pagesize 32 K managed by system using ('/home/db2inst1/db2inst1/EXAMDB/TBS_USRTMP') bufferpool BP_TMP "
db2 "create tablespace TBS_USRDAT1 pagesize 32 K managed by database using (file 'con0' 100 M) bufferpool BP_DATA"
db2 "create tablespace TBS_USRIDX1 pagesize 32 K managed by database using (file 'con1' 50 M) bufferpool BP_DATA"
db2 terminate
求给分~
以上就是关于数据库方面的实验题全部的内容,包括:数据库方面的实验题、数据库实验,要交了,真的没有分了,但希望好心人求教、数据库实验要SQL语言编写的等相关内容解答,如果想了解更多相关内容,可以关注我们,你们的支持是我们更新的动力!
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)