sql 多表关联查询

 create database example
use example
create table major
(
 spno varchar(10) primary key not null,
 spname varchar(20) not null
 )
 create table student
(
 sno varchar(10) primary key not null,
 sname varchar(10) not null,
 ssex varchar(2) not null,
 spno varchar(10) foreign key references major(spno)
)
create table course
( cno varchar(10) primary key not null,
  cname varchar(10) not null
 )
create table  scourse
(
 sno varchar(10) foreign key references student(sno),
 cno varchar(10) foreign key references course(cno),
 gmark int
)
insert into major values('001','英语')
insert into major values('002','语文')
insert into major values('003','数学')
insert into major values('004','生物')
insert into major values('005','物理')
insert into major values('006','化学')

insert into student values('2007151301','bobo','男','001')
insert into student values('2007151302','roy','男','002')
insert into student values('2007151303','max','男','004')
insert into student values('2007151304','tony','男','004')
insert into student values('2007151305','jaims','男','005')
insert into student values('2007151306','wing','男','006')
insert into student values('2007151307','allen','男','001')
insert into student values('2007151308','heng','男','002')
insert into student values('2007151309','chart','男','003')

insert into course values('001','乒乓球')
insert into course values('002','篮球')
insert into course values('003','足球')
insert into course values('004','网球球')
insert into course values('005','舞蹈')
insert into course values('006','瑜伽')
insert into course values('007','游泳')

insert into scourse values('2007151301','001','88')
insert into scourse values('2007151302','002','88')
insert into scourse values('2007151303','003','88')
insert into scourse values('2007151304','004','88')
insert into scourse values('2007151305','005','88')
insert into scourse values('2007151306','006','88')
insert into scourse values('2007151307','001','88')
insert into scourse values('2007151308','002','88')
insert into scourse values('2007151309','003','88')
insert into scourse values('2007151301','004','88')
insert into scourse values('2007151302','005','88')
insert into scourse values('2007151303','006','88')
insert into scourse values('2007151304','001','88')
insert into scourse values('2007151305','002','88')
insert into scourse values('2007151306','003','88')
insert into scourse values('2007151307','004','88')
insert into scourse values('2007151308','005','88')
insert into scourse values('2007151309','006','88')
insert into scourse values('2007151301','001','88')
insert into scourse values('2007151301','002','88')
insert into scourse values('2007151302','001','88')

//选修课目大于等于3的人的姓名
select sname from student where sno in(select sno from scourse group by sno having count(*)>=3)
//与bobo选了相同专业的人数总数

select sname from student where spno=(select spno  from student where sname='bobo')
//选课学生的总人数

 select count(*) from scourse
 select distinct sno from scourse
 select count(*) from student where sno in (select sno from scourse)
//与bobo选相同专业人的信息
select * from student where spno=(select spno  from student where sname='bobo')

//与bobo至少有一个选修课一样的学生信息
select * from student where sno in (select sno  from scourse where cno in( select cno from scourse where sno=(select sno from student where sname='bobo')))

 

select * from student where sno in (select distinct sno from scourse where cno in(select cno from scourse where sno=(select sno from student where sname='bobo')))