1 Star 0 Fork 48

吕宇鹏/SQL Server作业仓库

加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
文件
该仓库未声明开源许可证文件(LICENSE),使用请关注具体项目描述及其代码上游依赖。
克隆/下载
第七次.sql 4.88 KB
一键复制 编辑 原始数据 按行查看 历史
邓德城 提交于 2021-03-18 13:10 . 邓德城第七次课作业
create database School
use School
--创建班级信息表
create table Class
(
ClassID int identity(1,1) primary key,
ClassName nvarchar(50) unique not null
)
insert into Class(ClassName)
values('软件1班'),('软件2班'),('软件3班'),('软件4班'),('软件5班'),('软件6班'),('软件7班'),('软件8班'),('软件9班'),('软件10班')
select * from Class
update Class set ClassName='软件一班'where ClassID=1
delete Class where ClassID=1
--创建学生信息表
create table Student
(
StuID int identity(1,1) primary key,
ClassID int references Class(ClassID),
StuName nvarchar(20) not null,
StuSex nvarchar(1) Check(StuSex='男'or StuSex='女') default('男'),
StuBirthday date,
StuPhone nvarchar(11),
StuAddress nvarchar(200) unique,
CreateDate datetime default (getdate())
)
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone)
values(5,'刘1','男','2001.3.1',1131703355),
(6,'刘2','男','2001.3.2',1131603455),
(7,'刘3','男','2001.3.3',1131603555),
(7,'刘4','男','2001.3.4',1131603855),
(5,'刘5','男','2001.3.5',1131603755),
(2,'刘6','男','2001.3.6',1131605355),
(3,'刘7','男','2001.3.7',1131613355),
(4,'刘8','男','2001.3.8',1131633355),
(6,'刘9','男','2001.3.9',1131609355),
(8,'刘10','男','2001.3.10',1131612355),
(9,'刘11','男','2001.3.11',1131634355),
(2,'刘12','男','2001.3.12',1131563355),
(6,'刘13','男','2001.3.13',1131783355),
(7,'刘14','男','2001.3.14',1131903355),
(7,'刘15','男','2001.3.15',1131113355),
(5,'刘16','男','2001.3.16',1131903355),
(6,'刘17','男','2001.3.17',7131603355),
(5,'刘18','男','2001.3.18',9131603355),
(4,'刘19','男','2001.3.19',5131603355),
(5,'刘20','男','2001.3.20',3131603355)
delete Student where ClassID=2
--创建课程信息表
create table Course
(
Courseid int identity(1,1) primary key,
CourseName nvarchar(50) unique not null,
CourseCredit1 int default('1') check(CourseCredit1>=1and CourseCredit1<=5) not null,
CourseCredit nvarchar(10) check(CourseCredit='专业课'or CourseCredit='公共课' )
)
insert into
Course(CourseName,CourseCredit1) values('计算机基础',3),
('HTML+CSS网页制作',5),
('JAVA编程基础',5),
('SQL Server数据库基础',4),
('C#面向对象编程',5),
('Winform桌面应用程序设计',5)
select * from Course
update Course set CourseCredit1='5' where Courseid=5
--创建成绩表
create table Scores
(
Scoreid int identity(1,1) primary key,
StuId int references Student(StuID),
CourseId int references Course(Courseid),
Score decimal unique not null default('0') check(Score>=0 and Score<=100)
)
insert into Scores (StuId, CourseId, Score) values (1, 1, 80);
insert into Scores (StuId, CourseId, Score) values (1, 2, 78);
insert into Scores (StuId, CourseId, Score) values (1, 3, 65);
insert into Scores (StuId, CourseId, Score) values (1, 4, 90);
insert into Scores (StuId, CourseId, Score) values (2, 1, 60);
insert into Scores (StuId, CourseId, Score) values (2, 2, 77);
insert into Scores (StuId, CourseId, Score) values (2, 3, 68);
insert into Scores (StuId, CourseId, Score) values (2, 4, 88);
insert into Scores (StuId, CourseId, Score) values (3, 1, 88);
insert into Scores (StuId, CourseId, Score) values (3, 2, 45);
insert into Scores (StuId, CourseId, Score) values (3, 3, 66);
insert into Scores (StuId, CourseId, Score) values (3, 4, 75);
insert into Scores (StuId, CourseId, Score) values (4, 1, 56);
insert into Scores (StuId, CourseId, Score) values (4, 2, 80);
insert into Scores (StuId, CourseId, Score) values (4, 3, 75);
insert into Scores (StuId, CourseId, Score) values (4, 4, 66);
insert into Scores (StuId, CourseId, Score) values (5, 1, 88);
insert into Scores (StuId, CourseId, Score) values (5, 2, 79);
insert into Scores (StuId, CourseId, Score) values (5, 3, 72);
insert into Scores (StuId, CourseId, Score) values (5, 4, 85);
insert into Scores (StuId, CourseId, Score) values (6, 1, 68);
insert into Scores (StuId, CourseId, Score) values (6, 2, 88);
insert into Scores (StuId, CourseId, Score) values (6, 3, 73);
insert into Scores (StuId, CourseId, Score) values (6, 5, 63);
insert into Scores (StuId, CourseId, Score) values (7, 1, 84);
insert into Scores (StuId, CourseId, Score) values (7, 2, 90);
insert into Scores (StuId, CourseId, Score) values (7, 3, 92);
insert into Scores (StuId, CourseId, Score) values (7, 5, 78);
insert into Scores (StuId, CourseId, Score) values (8, 1, 58);
insert into Scores (StuId, CourseId, Score) values (8, 2, 59);
insert into Scores (StuId, CourseId, Score) values (8, 3, 65);
insert into Scores (StuId, CourseId, Score) values (8, 5, 75);
insert into Scores (StuId, CourseId, Score) values (9, 1, 48);
insert into Scores (StuId, CourseId, Score) values (9, 2, 67);
insert into Scores (StuId, CourseId, Score) values (9, 3, 71);
insert into Scores (StuId, CourseId, Score) values (9, 5, 56);
insert into Scores (StuId, CourseId, Score) values (9, 5, 56);
update Course set CourseCredit1='5' where CourseName=3
delete Scores where StuId=1
delete scores where CourseId=1
马建仓 AI 助手
尝试更多
代码解读
代码找茬
代码优化
1
https://gitee.com/lv-yupeng/sql-server-job-warehouse.git
git@gitee.com:lv-yupeng/sql-server-job-warehouse.git
lv-yupeng
sql-server-job-warehouse
SQL Server作业仓库
master

搜索帮助

0d507c66 1850385 C8b1a773 1850385