数据库实验要SQL语言编写的

-- 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 Course.Grade

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_Course.Grade

is '成绩'

-- Create/Recreate primary, unique and foreign key constraints

alter table MEA.T_Course

add constraint For_S_No foreign key (S_NO)

references Student (S_No)

alter table MEA.T_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 s.S_No, avg(tc.Grade) from Student s,T_Course tc where s.S_No=tc.S_No group by s.S_No

9:

delete T_Course tc where tc.S_No='A000'

delete Student s where s.S_No='A000'

思考题自己来，我用的orcal，这都不给分的话，爆你菊花啦

CREATE PROCEDURE P1

@XH VARCHAR(10) AS

IF @XH NOT IN(SELECT 学号 FROM 选课) INSERT 选课 VALUES(@XH,'1',0)

ELSE IF NOT EXISTS(SELECT * FROM 选课 WHERE 学号=@XH AND 成绩>=60)

DELETE FROM 选课 WHERE 学号=@XH

ELSE SELECT * FROM 选课 WHERE 学号=@XH

---