数据库原理期中实验报告

一、建表语句

create table student

 

(

 sno char(20) primary key,

 sname char(20) not null,

 ssex char(20),

 sage int,

 sdept char(20)

);

 

create table course(

 cno char(20) primary key,

 cname char(20) not null,

 cpno char(20),

 ccredit int ,

 foreign key(cpno) references course(cno)

 

);

 

 create table sc(

 sno char(20) not null,

 cno char(20) not null,

 grade int,

 

 primary key(sno,cno),

 foreign key(sno) references student(sno),

 foreign key(cno) references course(cno)

);

 

 

insert into student values('201215121','李勇','男',20,'cs');

 

insert into student values('201215122','李晨','女',19,'cs');

 

insert into student values('201215123','王敏','女',18,'ma');

 

insert into student values('201215125','张立','男',19,'is');

 

 

insert into course values('1','数据库','5',4);

insert into course values('2','数学',null,2);

insert into course values('3','信息系统','1',4);

insert into course values('4','操作系统','6',3);

insert into course values('5','数据结构','7',4);

insert into course values('6','数据处理',null,2);

insert into course values('7','PASCAL语音','6',4);

 

insert into sc values('201215121','1',92);

insert into sc values('201215121','2',85);

insert into sc values('201215121','3',88);

insert into sc values('201215122','2',90);

insert into sc values('201215122','3',80);

 

select * from student;

select * from course;

select * from sc;

二、Sql查询语句

 

一

1 查询所有年龄在岁以下的学生姓名及其年龄

select sname,sage from student where sage<20;

2 查询考试成绩不及格的学生的学号

select distinct sno from sc where grade<60;