sql脚本2.txt

sql 数据库建立一个学生信息管理系统

create table students
(
  stu_no    char(12)  not null,
  stu_name  char(20)  not null,
  stu_sex   char(6),
  stu_age   char(4),
  stu_dept  char(20)  not null,
  remark    varchar(100),
  primary key(stu_no)
)
select *from students
drop table students
drop table courses
drop table teachers
drop table choice
drop table teaching
select *from teaching



create table courses
(
  cou_no              char(5)   not null,
  cou_name            char(20)  not null,
  cou_hour            int       not null,
  cou_point           float     not null,
  cou_premiss_cou_no  char(10),
  remark              varchar(100),
  primary key(cou_no)
)
select *from courses

create table teachers
(
  teacher_no        char(10)  not null,
  teacher_name      char(20)  not null,
  teacher_sex       char(6)   not null,
  teacher_age       char(4)   not null,
  teacher_position  char(10)  not null,
  remark            varchar(100),
  primary key(teacher_no)
)
select *from teachers

create table choice
(
  stu_no      char(12)  not null,
  cou_no      char(5)   not null,
  teacher_no  char(10)  not null,
  cho_time    date      not null,
  mark        float,
  remark      varchar(100),
  primary key(stu_no,cou_no)
)
select *from choice

create table teaching
(
  teacher_no  char(10)  not null,
  cou_no      char(5)   not null,
  remark      varchar(100),
  primary key(teacher_no,cou_no)
)
select *from teaching

select *from students

insert
into students
values('060544310','肖华飞','男','20','自动化','')
update students
set stu_no='t00004'
where stu_no='060544310'

insert
into students
values('060544313','徐梁','男','18','自动化','')
update students
set stu_no='t00005'
where stu_no='060544313'

insert
into students
values('060743311','刘海龙','女','20','金融管理','')

insert
into students
values('060743318','谭毅','女','20','临床医学','')

select *from students

update students
set stu_dept='信息工程'
where stu_name='刘海龙'

insert
into students
values('060145310','谭尧','女','18','工业设计','')

update students
set stu_no='t00001'
where stu_no='060145310'

insert
into students
values('060145316','小黄鹂','男','20','工业设计','')

update students
set stu_no='t00002'
where stu_no='060145316'

insert
into students
values('060546317','王文韬','男','20','计算机技术','')

insert
into students
values('060546315','狐狸霞','女','19','计算机技术','')

insert
into students
values('060547307','唐娅婷','女','20','通信工程','')

insert
into students
values('060547310','唐强','男','20','通信工程','')

insert
into students
values('060145317','艾薇儿','女','20','工业设计','')
update students
set stu_no='t00003'
where stu_no='060145317'


insert
into students
values('060544324','迈克尔','男','20','自动化','')

insert
into students
values('060546316','小虎队','女','20','','计算机技术')

insert
into students
values('060547327','陈楚生','男','20','','通信工程')

insert
into students
values('060743312','刘德华','男','20','','信息工程')

select *from students

update students
set stu_dept='计算机技术'
where stu_name='小虎队'

update students
set stu_dept='通信工程'
where stu_name='陈楚生'

update students
set stu_dept='信息工程'
where stu_name='刘德华'

select *from students

select *from courses

insert
into courses
values('c001','电路原理','60','3','','')

insert
into courses
values('c002','模拟电路','70','3.5','电路原理','')

insert
into courses
values('c003','程序设计基础','60','3','','')

insert
into courses
values('c004','数据库技术','60','3','程序设计基础','')

insert
into courses
values('c005','大学英语','80','5','','')

insert
into courses
values('c006','CAD绘图','40','2','','')

select *from courses

select *from teachers

insert
into teachers
values('t0001','赵本山','男','57','教授','')

insert
into teachers
values('t0002','冯巩','男','46','教授','')

insert
into teachers
values('t0003','秦始皇','男','5500','导师','')

insert
into teachers
values('t0004','腾格尔','女','45','助教','')

insert
into teachers
values('t0005','龙宝宝','女','26','教授','')

select *from teachers
select *from courses
select *from choice
select *from students


update students
set stu_no='t000015'
where stu_no='060743318'

update courses
set cou_premiss_cou_no='c003'
where cou_no='c004'

insert 
into choice
values('t00001','c006','t0001','2007-9-1','98','')

insert 
into choice
values('t00001','c005','t0002','2007-9-1','78','')

insert 
into choice
values('t00002','c006','t0001','2007-9-1','84','')

insert 
into choice
values('t00002','c005','t0002','2007-9-1','66','')

insert 
into choice
values('t00003','c001','t0004','2007-3-1','90','')

insert 
into choice
values('t00003','c002','t0001','2007-9-1','83','')

insert 
into choice
values('t00004','c005','t0004','2007-9-1','97','')

insert 
into choice
values('t00004','c001','t0003','2007-9-1','87','')

insert 
into choice
values('t00004','c003','t0001','2007-9-1','82','')

insert 
into choice
values('t00007','c003','t0003','2007-9-1','88','')

insert 
into choice
values('t00007','c002','t0001','2007-9-1','92','')

select  cou_no
from choice
where teacher_no='t0001'

update choice
set teacher_no='t0005'
where mark='84.0'

select cou_no
from choice
where stu_no='t00004'

insert into choice
values('t00001','c004','t0005','2007-9-1','86','')

insert into choice
values('t00002','c004','t0005','2007-9-1','89','')

insert into choice
values('t00003','c004','t0005','2007-9-1','96','')

select teacher_no,teacher_name
from teachers

select cou_no
from choice
where teacher_no='t0004'

insert into choice
values('t00004','c002','t0004','2007-9-1','77','')

insert into choice
values('t00005','c002','t0004','2007-9-1','76','')

insert into choice
values('t00006','c002','t0004','2007-9-1','91','')

select *from choice

update choice
set cho_time='2007-3-1'
where cou_no='c001'

select cou_no,stu_no
from choice
where stu_no in
(
select stu_no
from choice
where cou_no='c004'
)

insert into choice
values('t00001','c003','t0005','2007-3-1','85','')

insert into choice
values('t00002','c003','t0005','2007-3-1','87','')

insert into choice
values('t00003','c003','t0005','2007-3-1','94','')

select cou_no
from choice
where teacher_no='t0001'

insert into teaching
values('t0001','c006','')

insert into teaching
values('t0001','c002','')

insert into teaching
values('t0001','c003','')

select cou_no
from choice
where teacher_no='t0002'

insert into teaching
values('t0002','c005','')

select cou_no
from choice
where teacher_no='t0003'

insert into teaching
values('t0003','c001','')

insert into teaching
values('t0003','c003','')

select cou_no
from choice
where teacher_no='t0004'

insert into teaching
values('t0004','c001','')

insert into teaching
values('t0004','c002','')

select cou_no
from choice
where teacher_no='t0005'

insert into teaching
values('t0005','c003','')

insert into teaching
values('t0005','c004','')

insert into teaching
values('t0005','c006','')

select *from teaching


select *from students
select *from choice
select *from teachers
select *from courses

select choice.stu_no,students.stu_name,choice.cou_no,max(choice.mark) max_mark
from choice,students
where cou_no='c001'
group by cou_no


select choice.stu_no,students.stu_name,stu_dept,choice.cou_no,courses.cou_name,teachers.teacher_name,max(mark) max_mark
from choice,students,courses,teachers
where choice.cou_no='c005'
group by choice.cou_no


select students.stu_name,courses.cou_name,students.stu_dept,teachers.teacher_name
from students,courses,teachers,(
  select stu_no,cou_no,teacher_no,max(mark)
  from choice
  group by cou_no
  ) m
where m.stu_no=students.stu_no and m.cou_no=courses.cou_no and m.teacher_no=teachers.teacher_no