数据库

create database XK04131028
 
 
create table S25
(
     S# char(8) primary key,
    Sname char(20) not null,
    Age int not null,
    Sex char(2) not null,
    Sdept char(20) not null,
        check(Sex in('男','女')),
    check(Age between 15 and 29)
    
)
 
create table C25
(
    C# char(15) primary key,
    Cname char(20) not null,
    Credit smallint not null,
    Teacher char(20) not null,
)
 
create table SC25
(
    S# char(8),
    C# char(15),
         primary key(S#,C#),
    Grade int not null,
    foreign key(S#) references S25(S#),
    foreign key(C#) references C25(C#),
)
 
 
 
insert
into S25
values('04131028','杜帅','20','男','计算机')
insert
into S25
values('04131022','闭号','21','男','计算机')
insert
into S25
values('04133037','许博东','22','女','计算机')
insert
into S25
values('04131027','肖遥','20','男','通信工程')
insert
into S25
values('04131029','曹延琴','20','女','通信工程')
 
 
 
insert
into C25
values('1001','English','2','王五')
insert
into C25
values('1002','Math','2','马六')
insert
into C25
values('1003','数据库原理及应用A','2','孟彩霞')
insert
into C25
values('1004','Chinese','2','Wang')
 
 
insert
into SC25
values('04131028','1001',99)
insert
into SC25
values('04131028','1002',95)
insert
into SC25
values('04131028','1003',97)
insert
into SC25
values('04131027','1003',96)
insert
into SC25
values('04131022','1002',98)
 
 
 
 
/*查询年龄在19到21岁的女同学的学号和姓名*/
select S#,Sname
from S25
where Age>19 and Age<21 and Sex ='女'  
 
 
 
/*查询选修了“数据库原理及应用A”课程的学生学号和姓名*/
select S25.S#,Sname
from S25,C25,SC25
where C25.C#=SC25.C# and S25.S#=SC25.S# and Cname='数据库原理及应用'
 
 
/*查询既选修了课程English和Math的学生学号*/
select S#
from C25,SC25
where SC25.C#=C25.C# and Cname='English'and Cname='Math'
 
/*WANG老师所受课程的课程号和课程名*/
select C#,Cname
from C25
where Teacher = 'WANG'
 
 
/*查询没有选修课程的学生学号和姓名*/
select S#,Sname
from S25
where exists
      (
    select *
    from C25
    where exists
       (
          select *
          from SC25
          where S#=S25.S# and C#=C25.C#));  
 
 
 
 
/*修改本人（自己学号）的年龄是年龄增加1*/
update S25
set Age = Age +1
where S#='04131028';
 
 
 
/*删除本人（自己学号）的选课记录*/
delete
from SC25
where S#='04131028'