面试题案例

[sql]代码库

问题描述：有三个表：
	S（s#,sn,sd,sa）代表学号，姓名，单位，年龄
	c（c#，cn）代表课程编号，课程名称
	SC（s#,c#，g） 学员，课程号，成绩
--表一：学员信息表
create  table s(
  s# varchar(40),
  sn varchar(40),
  sd varchar(40),
  sa varchar(40)
);

insert into s values('0212','lily','account','23');
insert into s values('0215','lucy','market','26');
insert into s values('0213','jack','sales','20');
insert into s values('0214','donna','manager','25');
insert into s values('0216','rose','manager','28');
insert into s values('0217','ann','manager','28');
select * from s; 

--表二：课程信息表
create  table c(
  c# varchar(20),
  cn varchar(20)
);

insert into c values('002','税法');
insert into c values('001','物理');
insert into c values('003','财经法规');
insert into c values('C2','经济法');
insert into c values('C5','行政法');
select * from c;

--表三：学员成绩表
create  table sc(
  s# varchar(20),
  c# varchar(20),
  g varchar(20)
);
insert into sc values('0212','002','80');
insert into sc values('0215','003','85');
insert into sc values('0213','002','70');
insert into sc values('0213','C2','90');
insert into sc values('0213','C5','100');
insert into sc values('0213','003','100');
insert into sc values('0213','001','100');
insert into sc values('0214','001','100');
insert into sc values('0216','001','100');

--1.查询选修了‘税法’这门课程的学员学号和姓名
select s1.s#,s1.sn from s s1,c c1,sc sc1 
  where s1.s#=sc1.s# and c1.c#=sc1.c# and c1.cn='税法';

--2.查询课程编号为C2的学员姓名和单位
select sn,sd from s where s# in(
  select s# from sc where c#='C2');

--3.查询不选修c5的学员姓名和单位
select sn,sd from s where s# not in(
  select s# from sc where c# ='C5');

--4.查询选修了全部课程的学员姓名和单位
select sn,sd from s where s# in(  
  select ss.s# from  
  (select s# ,count(distinct c#) cc 
  from sc group by s#) ss where ss.cc= (select count(*) from c));

--5.查询选修了课程的学员人数
SELECT COUNT(*)
  FROM S 
  WHERE EXISTS( SELECT * FROM SC WHERE SC.s# = s.s#);