数据库上机报告.docx

数据库SQL上机
实
验
报
告
书
姓名:
学号:
班级:
实验一:设计数据库、数据表并编程实现
建立student表
create table Student
(sno char(9) Primary key,
sname char(10)constraint c1 not null,
sbirthday DateTime,
ssex char(2)constraint c2 check(ssex in ('男','女')),
sclass char(20),
sremark char(100),
adress char(40),
zipcode char(6),
phone char(15),
email char(40)
);
建立course表:
create table Course
(cno char(6)primary key,
cname char(20),
cpno char(6),
ctime Numeric(2),
credit Numeric(2),
Foreign key(cpno)References o)
);
建立score表:
create table Score
(sno char(9),
cno char(6),
score Numeric(3),
Primary key(o),
Foreign key (sno)References Student(sno),
Foreign key (cno)References o)
);
建立Teacher表:
create table Teacher
(tno char(7)primary key,
tname char(10),
tsex char(2),
tbirthday DateTime,
position char(12),
department char(16),
tamount Numeric(7,2),
experience char(200)
);
建立Teachering 表
create table Teaching
(tno char(7),
cno char(6),
tdate Datetime,
classroom char(10),
sclass char(20),
Primary key(o),
Foreign key(tno)References Teacher(tno),
Foreign o)References o),
)
实验二:设计数据插入、修改、删除、查询和视图等操作并编程实现
(1)根据以下给定的部分数据表信息,分别对student, course, score, teacher, teching 表进行数据插入。
Student表插入语句:
Insert into
Student (sno,sname, sbirthday ,ssex,sclass, sremark, adress, zipcode, phone, email)
Values(011110101,章海潮,,信管系0101)
Course表插入语句:
Insert into
Course (cno, cname, cpno, ctime, credit)
Values (C001,数据库原理,C005,4,64, )
Score 表插入语句:
Insert into
Score (o,score, )
Values (0111101,C001,90)
Teacher 表插入语句:
Insert into
Teacher (tno,tname,tsex,tbirthday,position,department,tamount,experience )
Values (T001, 江承基,男,信息管理系,)
Teachering 表插入语句:
Insert into
Teachering (o,tdate,classroom,sclass)
Values (T001,C005,2012-01-09, 西二405, 信管系0101 )
(2)查询全体学生的详细记录;
select *
from Student left outer join Score on(=);

(3)查询全体学生的学号与姓名;
select sno,sname
from Student
(4)查询全体学生的学号、姓名、所属班级,相同班级列在一起;
select sno,sname,sc