要求：–1. 实训拓展训练【企业面试题】
–为岗位业务培训业务建立3个表:
–S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表工号、员工姓名、所属单位、员工年龄
–C (C#,CN ) C#,CN 分别代表课程编号、课程名称
–SC( S#,C#,G ) S#,C#,G 分别代表工号、所选修的课程编号、成绩
解题思路：下面是分步解析的 要仔细观察
CREATE TABLE S
(
S# VARCHAr2(10),
SN VARCHAr2(20),
SD VARCHAr2(20),
SA NUMBER(3)
)
–利用 begin end 可以向其中加入多条数据
BEGIN
insert into S values(‘001′,’Jack’,’IBM’,20);
insert into S values(‘002′,’Lucy’,’Microsoft’,22);
insert into S values(‘003′,’Tom’,’Sun’,30);
insert into S values(‘004′,’Jane’,’Asus’,31);
END;
SELECt * FROM S
DROp TABLE S
—
CREATE TABLE C
(
C# VARCHAr2(10),
CN VARCHAr2(20)
)
DROP TABLE C
–向其中插入数据
BEGIN
insert into C values(‘C001′,’C’);
insert into C values(‘C002′,’Java’);
insert into C values(‘C003′,’SQL’);
END;
SELECT * FROM C
CREATE TABLE SC
(
S# VARCHAr2(10),
C# VARCHAr2(10),
G NUMBER(3,1)
)
DROp TABLE SC
BEGIN
insert into SC values(‘001′,’C001’,50);
insert into SC values(‘001′,’C002’,50);
insert into SC values(‘002′,’C002’,70);
insert into SC values(‘003′,’C001’,99);
insert into SC values(‘003′,’C002’,52);
insert into SC values(‘003′,’C003’,44);
insert into SC values(‘004′,’C003′,54);
END;
SELECT * FROM SC
–【问题一】使用标准SQL嵌套语句查询选修全部课程的员工姓名和所属单位;
–方案一：
SELECt SN,SD
FROM S,(SELECt COUNT(C#)AS NUM,S# FROM SC GROUP BY S#) T
WHERe T.S#=S.S# AND T.NUM=(SELECt COUNT(*) FROM C);
–方案二：
–【问题二】列出有二门以上(含两门)不及格课程的员工姓名及其平均成绩;
–方案一 ：
SELECt * FROM SC
SELECt SN AS 姓名,AVG_G AS 平均成绩 FROM S
INNER JOIN
(SELECt COUNT(G)AS NUM,AVG(G)AS AVG_G,S# FROM SC WHERe G<60 GROUP BY S#) T
ON S.S#=T.S#
WHERe NUM>=2
–【问题三】列出既学过”C001″号课程，又学过”C002″号课程的所有员工姓名;
SELECt * FROM SC
–方案一：
SELECt SN,S# FROM S WHERe S# IN(
SELECt S# FROM SC WHERe C#=’C001′
INTERSECT
SELECt S# FROM SC WHERe C#=’C002′
)
–方案二：
SELECt S# AS 编号,SN 姓名 FROM S
WHERe S# IN
(
SELECt T1.S# FROM
(SELECt S# FROM SC WHERe C#=’C001′) T1
INNER JOIN
(SELECt S# FROM SC WHERe C#=’C002′) T2
ON T1.S#=T2.S#
)
–【问题四】列出”C001″号课成绩比”C002″号课成绩高的所有员工的工号
–及其”C001″号课和”C002″号课的成绩 .
SELECt * FROM SC
SELECt T1.S#,T1.G AS C001成绩,T2.G AS C002成绩
FROM (SELECt S#,G FROM SC WHERe C#=’C001′) T1 INNER JOIN
(SELECt S#,G FROM SC WHERe C#=’C002’) T2
ON T1.S#=T2.S#
WHERe T1.G>T2.G
总结：一般在面试的时候 问到的 “查询前几名的员工的信息 ” 比较多 也是高级查询中相对较难的 要区分开“查询前几名”和“查询前几个员工信息”的区别★
【注解】：要分清 在Oracle中和在SQL2005中 两个查询的主要区别。
