--妳第壹個大問題 x-y根本沒關系 x-z有直接的關系 y只是課程名字啥子的 x可以算z的父表 y也是z的父表 x跟y 可以說是平級的也是 x和y就像 人的特征 和人會的技能 x+y=人
create table stu_dent(
stu_id int primary key identity(1000,1),
stu_xuehao int unique not null,
stu_name varchar(10) not null,
stu_sex varchar(2) check(stu_sex='男'or stu_sex='女'),
stu_age int check(stu_age>0 and stu_age<100),
stu_ximing varchar(20) not null,
stu_shengao int check(stu_shengao>0 and stu_shengao<200),
)--第壹張表
insert into stu_dent values(2001,'小黃','男',20,'英語',152)
insert into stu_dent values(2002,'小麗','男',20,'數學',152)
insert into stu_dent values(2003,'小王','男',20,'語文',152)
insert into stu_dent values(2004,'小張','男',20,'物理',152)
insert into stu_dent values(2005,'小幕','男',20,'生物',152)
insert into stu_dent values(2006,'張飛','男',20,'計算機',152)
insert into stu_dent values(2007,'小飛','男',20,'計算機',152)
insert into stu_dent values(2008,'張三','男',20,'計算機',152)--插入數據
select *from stu_dent
create table stu_course(
stu_id int primary key identity(1000,1),
--stu_xh int references stu_dent(stu_xuehao),
stu_kh int unique not null,--課號
stu_km varchar(20) ,--課名
stu_ks varchar(50)not null,--課時
stu_xkh varchar(20) not null,--先行課
)--鍵張表
insert into stu_course values (15,'英語','40','語文')
insert into stu_course values (12,'物理','40','數學')
insert into stu_course values (10,'生物','40','英語')
insert into stu_course values (4,'語文','40','物理')
insert into stu_course values (3,'計算機','40','英語')--插入數據
create table stu_xuanke(
stu_xuh int references stu_dent(stu_xuehao) not null,
stu_kehao int references stu_course(stu_kh) not null,
stu_chegnji int not null,
)--最後張表
insert into stu_xuanke values(2001,15,55)
insert into stu_xuanke values(2001,3,55)
insert into stu_xuanke values(2001,12,99)
insert into stu_xuanke values(2001,10,66)
insert into stu_xuanke values(2001,4,44)
insert into stu_xuanke values(2008,15,55)
insert into stu_xuanke values(2008,3,55)
insert into stu_xuanke values(2008,12,99)
insert into stu_xuanke values(2008,10,66)
insert into stu_xuanke values(2008,4,44)
insert into stu_xuanke values(2002,15,55)
insert into stu_xuanke values(2006,3,100)
insert into stu_xuanke values(2007,3,101)
insert into stu_xuanke values(2007,15,18)--插入數據
--/////////////////////////////////////////壹下就是 三個問題的查詢語句 ///////////////////////////////////////
select stu_kh,stu_km from stu_course join stu_xuanke on stu_kehao=stu_kh where stu_xuh in(select stu_xuh from stu_xuanke where stu_kehao=(select stu_kh from stu_course where stu_km='計算機'))--第壹個問題
select stu_name from stu_dent where stu_xuehao in(select stu_xuh from stu_xuanke where stu_kehao=(select stu_kh from stu_course where stu_km='英語'))--第二個問題
select count(*) ,stu_xuh from stu_xuanke join stu_dent on stu_xuehao=stu_xuh where stu_xuehao in(select stu_xuehao from stu_dent) group by stu_xuh having count(*)=5--最後個問題
--記得哦以後有這麽有趣的題目通知我哦 我也好久沒玩SQL