50道SQL练习题及答案与详细分析
103 浏览量
2020-12-14
17:48:12
上传
评论 2
收藏 102KB PDF 举报
50道道SQL练习题及答案与详细分析练习题及答案与详细分析
50道道SQL练习题及答案与详细分析练习题及答案与详细分析
数据表介绍数据表介绍
–1.学生表
Student(SId,Sname,Sage,Ssex)
–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
–2.课程表
Course(CId,Cname,TId)
–CId 课程编号,Cname 课程名称,TId 教师编号
–3.教师表
Teacher(TId,Tname)
–TId 教师编号,Tname 教师姓名
–4.成绩表
SC(SId,CId,score)
–SId 学生编号,CId 课程编号,score 分数
学生表学生表 Student
CREATE TABLE Student
(
SId varchar(20) NOT NULL,-- 学号
Sname varchar(20) NOT NULL, -- 姓名
Sage datetime not null, --出生日期
Ssex char(2) not null CHECK(Ssex='男' OR Ssex='女'),-- 性别
)
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');
科目表科目表 Course
-- 科目
create table Course
(
CId varchar(20) not null,
Cname varchar(20) not null ,
TId INT not null
)
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
教师表教师表 Teacher
CREATE TABLE Teacher
(
TId varchar(20) not null,
Tname varchar(20) not null,
)
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
成绩表成绩表 SC
CREATE TABLE SC
(
SId varchar(20) NOT NULL,
CId varchar(20) NOT NULL,
score int null check(score=0) ,
)
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
weixin_38666232
- 粉丝: 3
- 资源: 923
评论0