要求：–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中 两个查询的主要区别。