1.现有一个简化的学生选课系统，对象及其描述如下：学生(学号,姓名),课程(课程代号,课程名称)，学生可以不选，或者至多选6门课。
create table C (
ID NUMBER(10) not null,
CODE VARCHAR2(50),
NAME VARCHAR2(50),
constraint PK_C primary key (ID)
);
create table S (
ID NUMBER(10) not null,
CODE VARCHAR2(50),
NAME VARCHAR2(50),
constraint PK_S primary key (ID)
);
create table SC (
S_ID Number(10) not null,
C_ID NUMBER(10) not null,
constraint PK_S_C primary key (S_ID, C_ID),
constraint FK_C_SC foreign key (C_ID)
references C(ID),
constraint FK_S_SC foreign key (S_ID)
references S (ID)
);
A)选了 “Java程序设计” 的学生列表（按学号生序）。
select S.* from S, SC, C
WHERE S.ID=SC.S_ID AND C.ID=SC.C_ID AND C.NAME = 'Java程序设计'
ORDER BY S.CODE
B)选了3门以上课程的学生列表和选课数（按学号生序）。
SELECT S.ID,S.CODE,S.NAME,COUNT(*)
FROM S, SC
WHERE S.ID = SC.S_ID
GROUP BY S.ID,S.CODE,S.NAME HAVING COUNT(*) >= 3 ORDER BY S.CODE
C)选了0门课程的学生列表（按学号生序）。
SELECT S.* FROM S, SC
WHERE S.ID=SC.S_ID(+) AND SC.S_ID IS NULL ORDER BY S.CODE
SELECT S.* FROM S LEFT JOIN SC
ON S.ID=SC.S_ID WHERE SC.S_ID IS NULL ORDER BY S.CODE
select s.code,s.name
from s
where s.id not in(select s_id from sc)
order by s.code asc
D)选课最多的学生列表（按学号生序）。
SELECT S.ID,S.CODE,S.NAME FROM S, SC
WHERE S.ID = SC.S_ID GROUP BY S.ID,S.CODE,S.NAME
HAVING COUNT(*) = (
SELECT MAX(COUNT(SC.S_ID)) FROM SC
GROUP BY SC.S_ID)
ORDER BY S.CODE
E)仅仅选了“Java程序设计”一门课的学生列表（按学号生序）。
select S.* from S, SC, C
WHERE S.ID=SC.S_ID AND C.ID=SC.C_ID AND C.NAME = 'Java程序设计'
AND S.ID in (
SELECT S_ID from SC
GROUP BY SC.S_ID HAVING COUNT(SC.S_ID) = 1)
order by s.code
select S.* from S, SC, C,
(select SC.S_ID, count(SC.S_ID) count from SC
group by SC.S_ID) N
WHERE S.ID=SC.S_ID AND C.ID=SC.C_ID AND C.NAME = 'Java程序设计'
AND S.ID = N. S_ID AND N.COUNT = 1
order by s.code
F)选了3门课，并且其中一门为“Java程序设计”的学生列表（按学号生序）。
SELECT S.* FROM S, SC, C
WHERE S.ID=SC.S_ID AND C.ID=SC.C_ID AND C.NAME = 'Java程序设计'
AND S.ID IN (
SELECT S_ID from SC
GROUP BY SC.S_ID HAVING COUNT(SC.S_ID) = 3)
ORDER BY S.CODE
G)即选了“Java程序设计” 又选了“Delphi程序设计”的学生列表（按学号生序）。
(select s.* from s,c,sc where s.id = sc.S_ID and sc.C_ID = c.id and c.name = 'Java程序设计')
intersect
(select s.* from s,c,sc where s.id = sc.S_ID and sc.C_ID = c.id and c.name = 'Delphi程序设计')
select s.* from s,c,sc where s.id = sc.S_ID and sc. C_ID = c.id and c.name = 'Java程序设计' and s.id in (
select s.id from s,c,sc where s.id = sc.S_ID and sc.C_ID = c.id and c.name = 'Delphi程序设计')
select S.* from S, SC, C, SC SC2, C C2
WHERE S.ID=SC.S_ID AND C.ID=SC.C_ID AND C.NAME = 'Java程序设计'
AND S.ID= SC2.S_ID AND C2.ID= SC2.C_ID AND C2.NAME = 'Delphi程序设计'
order by s.code
H)选了“Java程序设计”没选“Delphi程序设计”的学生列表（按学号生序）。
select S.* from S, SC, C,
(select SC.S_ID from SC, C
WHERE C.ID=SC.C_ID AND C.NAME = 'Delphi程序设计') X
WHERE S.ID=SC.S_ID AND C.ID=SC.C_ID AND C.NAME = 'Java程序设计'
AND S.ID = X. S_ID (+) AND X. S_ID IS NULL
order by s.code
(select s.* from s,c,sc where s.id = sc.S_ID and sc.C_ID = c.id and c.name = 'Java程序设计')
minus
(select s.* from s,c,sc where s.id = sc.S_ID and sc.C_ID = c.id and c.name = 'Delphi程序设计')
select s.* from s,c,sc where s.id = sc.S_ID and sc. C_ID = c.id and c.name = 'Java程序设计' and
s.id not in (select s.id from s,c,sc where s.id = sc.S_ID and sc.C_ID = c.id and c.name = 'Delphi程序设计')
2.请实现一个基于Oracle的数据库设计，完成某个磁盘的所有目录及文件信息（包括目录结构）的存储。
目录信息包括目录名，创建时间
文件信息包括文件名，创建时间，文件大小
并用sql语句实现。
create table P (
ID NUMBER(10) not null,
PARENTID NUMBER(10),
NAME VARCHAR2(100),
CREATETIME DATE,
constraint PK_P primary key (ID),
constraint FK_P_PARENT foreign key (PARENTID) references P (ID)
);
create table F (
ID NUMBER(10) not null,
PATHID NUMBER(10),
NAME VARCHAR2(100),
CREATETIME DATE,
FILESIZE NUMBER(10),
constraint PK_F primary key (ID),
constraint FK_P_F foreign key (PATHID) references P (ID)
);
A) 列出某个目录下的所有子目录列表，同级目录按名称升序排列。
SELECT * FROM P START WITH P.NAME='P1'
CONNECT BY PRIOR P.ID = P.PARENTID
ORDER SIBLINGS BY P.NAME
B) 列出某个目录下的所有文件列表，同级目录中文件按名称升序排列。
SELECT F.* FROM F,
(SELECT P.ID, ROWNUM RN FROM P START WITH P.NAME='P1'
CONNECT BY PRIOR P.ID = P.PARENTID
ORDER SIBLINGS BY P.NAME) R
WHERE F.PATHID = R.ID ORDER BY R.RN, F.NAME
C) 列出某个目录下的所有doc文件。
SELECT F.* FROM F,
(SELECT P.ID, ROWNUM RN FROM P
START WITH P.NAME='P1' CONNECT BY PRIOR P.ID = P.PARENTID
ORDER SIBLINGS BY P.NAME) R
WHERE F.PATHID = R.ID AND UPPER(F.NAME) LIKE '%.DOC'
ORDER BY R.RN, F.NAME
D) 列出某个目录下的所有的空目录。
SELECT R.* FROM
(SELECT P.*, ROWNUM RN FROM P START WITH P.NAME='P1'
CONNECT BY PRIOR P.ID = P.PARENTID
ORDER SIBLINGS BY P.NAME) R, F
WHERE R.ID=F.PATHID(+) and F.PATHID IS NULL ORDER BY R.RN