4.18日数据库上机

CREATE DATABASE jxgl
GO
USE jxgl
Go
Create Table Student
(Sno CHAR(5) NOT NULL PRIMARY KEY(Sno),
 Sname VARCHAR(20),
 Sage SMALLINT CHECK(Sage>=15 AND Sage<=45),
 Ssex CHAR(2) DEFAULT'男' CHECK(Ssex='男' OR Ssex='女'),
 Sdept CHAR(2));
Create Table Course
(Cno CHAR(2) NOT NULL PRIMARY KEY(Cno),
 Cname VARCHAR(20),
 Cpno CHAR(2),
 Ccredit SMALLINT);
 create table sc
(sno char(5) not null constraint s_f foreign key references student(sno),
 cno char(2) not null,
 grade smallint check((grade is null)or(grade between 0 and 100)),
 primary key(sno,cno),
 foreign key (cno) references course(cno));
 insert into student values('98001','钱横',18,'男','CS');
 insert into student values('98002','王林',19,'女','CS');
 insert into student values('98003','李民',20,'男','IS');
 insert into student values('98004','赵三',16,'女','MA');
 insert into Course values('1','数据库系统','5',4);
 insert into Course values('2','数学分析',null,2);
 insert into Course values('3','信息系统导论','1',3);
 insert into Course values('4','操作系统_原理','6',3);
 insert into Course values('5','数据结构','7',4);
 insert into Course values('6','数据处理基础',null,4);
 insert into Course values('7','C语言','6',3);
 insert into sc values('98001','1',87);
 insert into sc values('98001','2',67);
 insert into sc values('98001','3',90);
 insert into sc values('98002','2',95);
 insert into sc values('98002','3',88);

 insert into Student values('98011','张静',27,'女','CS');
  insert into Student values('99201','石科',21,'男','CS')
  insert into Student values('99202','宋笑',19,'女','CS')
  insert into Student values('99203','王欢',20,'女','IS')
  insert into Student values('99204','彭来',18,'男','MA')
  insert into Student values('99205','李晓',22,'女','CS')

 insert into Student(Sno,Sname,Sage) values('98012','李四',16);

 insert into Student values('99010','赵青江',18,'男','CS')
 insert into Student values('99011','张丽萍',19,'女','CH')
 insert into Student values('99012','陈景欢',20,'男','IS')
 insert into Student values('99013','陈婷婷',16,'女','PH')
 insert into Student values('99014','李军',16,'女','EH')


insert into SC
    select sno,cno,null
    from Student,Course
    where Sdept = 'CS' and cno='5';

insert into Student
    select cast(cast(sno as integer)+1 as char(5)),sname+'2',sage,ssex,sdept
    from Student where Sname='赵三';
    go
    select *
    from Student;

    select *from Student

    update Student set sname='李明',Sage=23 where sno='98003'

    select *from Student

    update student set student.Sage = Student.Sage+1
    from (select top(3) * from student order by sno) as stu3
    where stu3.sno=Student.sno;


    select *from Student
    update top(3) percent Student set student.Sage=Student.Sage+1;
    select *from Student

    select *from sc
    update sc 
    set grade=(select avg(grade) from sc where cno='3')
    where sno = '98001' and cno='3'
    select *from sc
    select *from sc
    update sc set grade=0
    where cno='2' and sno in(select sno from Student where sname = '王林')
    select *from sc


    select * into TSC from sc

    select * from TSC


    select *from sc
    delete from sc
    where 'CS'=(select sdept from Student
                where Student.Sno=sc.sno)

    select *from sc

    insert into sc select * from TSC

    delete from sc

    truncate table sc

insert into sc values('99010','1',87)
insert into sc values('99010','3',80)
insert into sc values('99010','4',87)
insert into sc values('99010','6',85)
insert into sc values('99011','1',52)
insert into sc values('99011','2',47)
insert into sc values('99011','3',53)

insert into sc values('99011','5',45)
insert into sc values('99012','1',84)
insert into sc values('99012','4',67)
insert into sc values('99012','5',81)

insert into sc(sno,cno)values('99010','2')
insert into sc(sno,cno)values('99012','3')

select * from sc
--2
select * into TS from Student

delete from TS

select * from TS

--3
insert into SC
    select sno,cno,60
    from Student
    where Sdept = 'IS' and cno='7';
    // 如果from student course 会进行广义笛卡尔积，会导致重复出现；或者使用distinct 来约束
--4 
select * from SC
select * from Student
insert into TS 
        select * 
        from Student
        where Ssex='女' and Sage<=16;


--5 
INSERT INTO TS
SELECT *
FROM STUDENT
WHERE SNO IN
        (SELECT SNO
        FROM SC
        GROUP BY SNO
        HAVING MAX(GRADE)<60
        )
--6
update Student set sname='刘华',Sage=sage+1 where sno='99011'


select * from SC
--7
update sc
set grade =null
where grade<60 and cno in(select cno
                            from Course
                            where Cname='数据库系统')


select * from SC


    --8
    update student set student.Sage = Student.Sage+1
    from (select top(4) * from student order by sno) as stu3
    where stu3.sno=Student.sno;
--9
    update sc set grade=null
    where cno='3' and sno in(select sno from Student where sname = '王林')
    select *from sc
--10
    update sc 
    set grade=grade*1.05
    where grade<(select avg(grade) from sc) and sno in(select sno from Student where ssex = '女')


    select *from sc
--11
    update sc
    set grade = grade*0.98
    where grade<=80 and cno='2'

    update sc
    set grade = grade * 0.99
    where grade >80 and cno='2'
    --12
    select * into t11 from Student
    select * into t12 from Course
    select * into t13 from SC

    select * from sc
    --13
    delete from sc
    where grade IS null

    select * from sc

--14
delete from sc
where sno in (select sno
              from Student
              where Sname='钱横')

--15
delete from sc
where sno ='98005'

delete 
from Student
where sno='98005'

--16
delete from sc
where sno in(select sno
            from Student
            where Sname like '张%')

select * from Student
delete from Student
where Sname like '张%'

--17
delete from student
delete from course

--18
insert into student  select * from t1
insert into sc select * from t2
insert into course  select * from t3