数据库SQLServer实验前言

1 创建学生信息表Student

Create table Student
    (Sno char(10) primary key,
     Sname char(20) unique,
     Ssex char(2),
     Sage smallint,
     Sdept char(30)
    )

2 创建课程信息表Course

Create table Course
    (Cno char(4) primary key,
     Cname char(40) NOT NULL,
     Cpno char(4),
     Credit smallint,
     foreign key(Cpno) references Course(Cno)
    )

3 创建选课信息表SC

Create table SC
    (Sno char(10),
     Cno char(4),
     Grade smallint,
     primary key(Sno,Cno),
     foreign key(Sno) references Student(Sno),
     foreign key(Cno) references Course(Cno) 
    )

4 删除选课信息表SC

drop table SC

5 录入学生信息表Student

insert
into Student
Values  ('202001001','张小玲','女',19,'电子信息'),
        ('202001008','张玲','女',20,'电子信息'),
        ('202002001','张强','男',21,'计算机'),
        ('202002003','张峰','男',21,'电子信息'),
        ('202002005','刘梅','女',17,'计算机'),
        ('201902001','王一','男',22,'计算机'),
        ('201903005','郑丽','女',18,'网络工程'),
        ('202101003','沈艳','女',19,'电子信息'),
        ('202103001','赵丽红','女',20,'网络工程'),
        ('202104001','李宏伟','男',19,'通信工程'),
        ('202104005','刘景鹏','男',21,'通信工程')

6 录入课程信息表Course

insert
into Course
Values   ('1','数据库','5',4),
         ('2','高等数学',NULL,2),
         ('3','信息系统','1',4),
         ('4','操作系统','6',3),
         ('5','数据结构','7',4),
         ('6','数据处理',NULL,2),
         ('7','C语言','6',5)

7 录入选课信息表SC

insert
into SC
Values   ('202001001','1',NULL),
         ('202001001','2',95),
         ('202001001','4',NULL),
         ('202001001','7',87),
         ('202001008','4',65),
         ('202001008','2',59),
         ('202002001','3',63),
         ('202002001','7',74),
         ('202002001','2',84),
         ('202002003','4',58),
         ('202002003','2',76),
         ('202002003','1',89),
         ('202002005','3',77),
         ('202002005','2',69),
         ('201902001','5',83),
         ('201902001','1',62),
         ('201903005','3',43),
         ('201903005','7',64),
         ('202101003','4',87),
         ('202104001','3',97),
         ('202104001','5',65)