自己看的 sql -2

--Student(SID,Sname,unit,Sage) 学生表(学号,学员姓名,所属单位,学员年龄)
create table student
(
 sid varchar(20) not null primary key,
 sname varchar(20) not null,
    unit varchar(20) null,
 sage datetime  null
 
)
drop table student
select * from student
delete from student
insert into student values('1001','凤来','华为','1989.1.1')
insert into student values('1002','李响','思创','1980.1.25')
insert into student values('1003','王为','江西移动','1982.5.3')
insert into student values('1004','王吉','铜业','1981.2.7')
insert into student values('1005','林欣','中笠','1985.11.12')

--Course(CID,Cname) 课程表(分别代表课程编号，课程名称)
create table course 
(
 cid varchar(20) not null primary key,
 cname varchar(20) not null
)
drop table course
select * from course
delete from course
insert into course values('C1','企业管理')
insert into course values('C2','税收基础')
insert into course values('C3','UML')
insert into course values('C4','数据库')
insert into course values('C5','JAVA')
--SC(SID,CID,score) 成绩表（学号，所选修的课程编号，学习成绩）
create table sc
(
 sid varchar(20)  null ,
 cid varchar(20) null,
 score float(2) check(score between 0 and 100) null
)
drop table sc
select * from sc
delete from sc
insert into sc values('1001','C1','85')
insert into sc(sid,cid) select sid ,cid from student,course
--1.使用标准SQL嵌套语句查询选修课程名称为‘税收基础’的学员学号和姓名;
select sid as'学员学号',sname as'姓名' 
from student
where sid in(select sid from sc 
   where cid in(select cid from course 
      where cname='税收基础'
      )
   )
--2.使用标准SQL嵌套语句查询选修课程编号为‘C2’的学员学号和所属单位;
select sid as'学员学号',unit as'所属单位' 
from student
where sid in(select sid from sc 
   where cid='C2'
   )

--3.使用标准SQL嵌套语句查询不选修课程编号为‘C5’的学员学号和所属单位;
select sid as'学员学号',unit as'所属单位' 
from student
where sid in(select sid from sc 
   where cid!='C5'
   )

--3.使用标准SQL嵌套语句查询选修全部课程的学员学号和所属单位;
select sid as'学员学号',unit as'所属单位' 
from student
where exists(select sid from sc 
   group by sid 
   having count(cid)=(select count(*) 
        from course
        )
   )

--5查询选修了课程的学员数;
select count(sid) as'学员数' from student
where sid in(select sid from sc 
   where cid is not null
   )

--6.查询选修课程超过5门的学员学号和所属单位;
select sid as'学员学号',unit as'所属单位' 
from student
where exists(select sid from sc 
   group by sid 
   having count(cid)>5
   )
select * from sc

--Student(SID,Sname,unit,Sage) 学生表(学号,学员姓名,所属单位,学员年龄)
create table student
(
 sid varchar(20) not null primary key,
 sname varchar(20) not null,
    unit varchar(20) null,
 sage datetime  null
 
)
drop table student
select * from student
delete from student
insert into student values('1001','凤来','华为','1989.1.1')
insert into student values('1002','李响','思创','1980.1.25')
insert into student values('1003','王为','江西移动','1982.5.3')
insert into student values('1004','王吉','铜业','1981.2.7')
insert into student values('1005','林欣','中笠','1985.11.12')

--Course(CID,Cname) 课程表(分别代表课程编号，课程名称)
create table course
(
 cid varchar(20) not null primary key,
 cname varchar(20) not null
)
drop table course
select * from course
delete from course
insert into course values('C1','企业管理')
insert into course values('C2','税收基础')
insert into course values('C3','UML')
insert into course values('C4','数据库')
insert into course values('C5','JAVA')
--SC(SID,CID,score) 成绩表（学号，所选修的课程编号，学习成绩）
create table sc
(
 sid varchar(20)  null ,
 cid varchar(20) null,
 score float(2) check(score between 0 and 100) null
)
drop table sc
select * from sc
delete from sc
insert into sc values('1001','C1','85')
insert into sc(sid,cid) select sid ,cid from student,course
--1.使用标准SQL嵌套语句查询选修课程名称为‘税收基础’的学员学号和姓名;
select sid as'学员学号',sname as'姓名'
from student
where sid in(select sid from sc
   where cid in(select cid from course
      where cname='税收基础'
      )
   )
--2.使用标准SQL嵌套语句查询选修课程编号为‘C2’的学员学号和所属单位;
select sid as'学员学号',unit as'所属单位'
from student
where sid in(select sid from sc
   where cid='C2'
   )

--3.使用标准SQL嵌套语句查询不选修课程编号为‘C5’的学员学号和所属单位;
select sid as'学员学号',unit as'所属单位'
from student
where sid in(select sid from sc
   where cid!='C5'
   )

--3.使用标准SQL嵌套语句查询选修全部课程的学员学号和所属单位;
select sid as'学员学号',unit as'所属单位'
from student
where exists(select sid from sc
   group by sid
   having count(cid)=(select count(*)
        from course
        )
   )

--5查询选修了课程的学员数;
select count(sid) as'学员数' from student
where sid in(select sid from sc
   where cid is not null
   )

--6.查询选修课程超过5门的学员学号和所属单位;
select sid as'学员学号',unit as'所属单位'
from student
where exists(select sid from sc
   group by sid
   having count(cid)>5
   )
select * from sc

--Student(SID,Sname,unit,Sage) 学生表(学号,学员姓名,所属单位,学员年龄)
create table student
(
 sid varchar(20) not null primary key,
 sname varchar(20) not null,
    unit varchar(20) null,
 sage datetime  null
 
)
drop table student
select * from student
delete from student

insert into student values('1001','凤来','华为','1989.1.1')
insert into student values('1002','李响','思创','1980.1.25')
insert into student values('1003','王为','江西移动','1982.5.3')
insert into student values('1004','王吉','铜业','1981.2.7')
insert into student values('1005','林欣','中笠','1985.11.12')

--Course(CID,Cname) 课程表(分别代表课程编号，课程名称)
create table course
(
 cid varchar(20) not null primary key,
 cname varchar(20) not null
)
drop table course
select * from course
delete from course

insert into course values('C1','企业管理')
insert into course values('C2','税收基础')
insert into course values('C3','UML')
insert into course values('C4','数据库')
insert into course values('C5','JAVA')

--SC(SID,CID,score) 成绩表（学号，所选修的课程编号，学习成绩）
create table sc
(
 sid varchar(20)  null ,
 cid varchar(20) null,
 score float(2) check(score between 0 and 100) null
)
drop table sc
select * from sc
delete from sc
insert into sc values('1001','C1','85')
insert into sc(sid,cid) select sid ,cid from student,course

--1.使用标准SQL嵌套语句查询选修课程名称为‘税收基础’的学员学号和姓名;
select sid as'学员学号',sname as'姓名'
from student
where sid in(select sid from sc
   where cid in(select cid from course
      where cname='税收基础'
      )
   )

--2.使用标准SQL嵌套语句查询选修课程编号为‘C2’的学员学号和所属单位;
select sid as'学员学号',unit as'所属单位'
from student
where sid in(select sid from sc
   where cid='C2'
   )

--3.使用标准SQL嵌套语句查询不选修课程编号为‘C5’的学员学号和所属单位;
select sid as'学员学号',unit as'所属单位'
from student
where sid in(select sid from sc
   where cid!='C5'
   )

--3.使用标准SQL嵌套语句查询选修全部课程的学员学号和所属单位;
select sid as'学员学号',unit as'所属单位'
from student
where exists(select sid from sc
   group by sid
   having count(cid)=(select count(*)
        from course
        )
   )

 

--5查询选修了课程的学员数;
select count(sid) as'学员数' from student
where sid in(select sid from sc
   where cid is not null
   )

--6.查询选修课程超过5门的学员学号和所属单位;
select sid as'学员学号',unit as'所属单位'
from student
where exists(select sid from sc
   group by sid
   having count(cid)>5
   )
select * from sc