山东大学数据库实验1(2021年最新版)
creat table test1_student
( sid char ( 12 ) not null ,
name varchar ( 10 ) not null ,
sex char ( 2 ) ,
age int ,
birthday date ,
dname varchar ( 30 ) ,
class varchar ( 10 ) )
create table test1_course
( cid char ( 6 ) not null ,
name varchar ( 40 ) not null ,
fcid char ( 6 ) ,
credit numeric ( 4 , 1 ) )
create table test1_student_course
( sid char ( 20 ) not null ,
cid char ( 6 ) not null ,
score numeric ( 5 , 1 ) ,
tid char ( 6 ) ,
sctime date )
insert into test1_student values ( ‘200800020101 ’, ’王欣’, ’女’, 19 , date ’1994 - 02 - 02 ’, ’计算机学院’, ’2010 ’) ;
insert into test1_student values ( ‘200800020102 ’, ’李华’, ’女’, 20 , date ’1995 - 03 - 03 ’, ’软件学院’, ’2009 ’) ;
insert into test1_course values ( 300001 , '数据结构' , null , 2 ) ;
insert into test1_course values ( 300002 , '数据库' , 300001 , 2.5 ) ;
Insert into test1_student_course values ( 200800020101 , 300001 , 91.5 , 100101 ) ;
Insert into test1_student_course values ( 200800020101 , 300002 , 92.6 , 100102 ) ;
create table a as
select distinct sid
from test1_student_course;
create table b as
Select distinct sid
from test1_student
minus
select sid
from a;
create view test2_01 as
select test1_student. sid, name
from test1_student cross join b
where ( test1_student. sid = b. sid) ;
create view test2_01 as
select test1_student. sid, name
from test1_student CROSS JOIN b
where ( test1_student. sid = b. sid) ;