路人乙

心有多大,舞台就有多大。

统计

留言簿(1)

个人blog

技术网站

阅读排行榜

评论排行榜

Oracle SQL语句查询例子[转]

 

原创 Oracle SQL语句查询例子收藏

新一篇: Eclipse快捷键大全(转载)  | 旧一篇: “达内”JAVA技术培训有感(二)

 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

posted on 2009-02-22 21:38 上帝也犯困 阅读(252) 评论(0)  编辑  收藏 所属分类: oracle


只有注册用户登录后才能发表评论。


网站导航: