几个比较实用的数据库问题和答案

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

--推荐第二条语句

 

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

--语句结构清晰

--将IN去掉变成下面语句

 

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

--效率高，并且可以处理多个字段，IN不能处理多个字段

 

 

 

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程序设计')

 

 

 

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