oracle查询例子,Oracle SQL语句查询例子

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