🚀 go-pugleaf

û��������ģ���������Լ���ģ��������
SQL> create table stu(sid number,sname char(4));
Table created.
SQL>  create table grade(cid number,sid number,mark number);
Table created.
SQL> create table grade(cid number,sid number,mark number);
Table created.
SQL> begin
  2  for i in 1..50 loop
  3    insert into stu values(i,chr(97+mod(i,15)));
  4    insert into class values(mod(i,5),chr(65+i),i);
  5    insert into grade values(mod(i,3),i,decode(i,20,null,80+mod(i,15)));
  6  end loop;
  7  end;
  8  /
PL/SQL procedure successfully completed.
SQL> select * from stu where rownum < 21;
       SID SNAM
---------- ----
         1 b
         2 c
         3 d
         4 e
         5 f
         6 g
         7 h
         8 i
         9 j
        10 k
        11 l
       SID SNAM
---------- ----
        12 m
        13 n
        14 o
        15 a
        16 b
        17 c
        18 d
        19 e
        20 f
20 rows selected.
SQL> select * from class where rownum < 21;
       CID CNAM        SID
---------- ---- ----------
         1 B             1
         2 C             2
         3 D             3
         4 E             4
         0 F             5
         1 G             6
         2 H             7
         3 I             8
         4 J             9
         0 K            10
         1 L            11
       CID CNAM        SID
---------- ---- ----------
         2 M            12
         3 N            13
         4 O            14
         0 P            15
         1 Q            16
         2 R            17
         3 S            18
         4 T            19
         0 U            20
20 rows selected.
SQL> c/class/grade/
  1* select * from grade where rownum < 21
SQL> /
       CID        SID       MARK
---------- ---------- ----------
         1          1         81
         2          2         82
         0          3         83
         1          4         84
         2          5         85
         0          6         86
         1          7         87
         2          8         88
         0          9         89
         1         10         90
         2         11         91
       CID        SID       MARK
---------- ---------- ----------
         0         12         92
         1         13         93
         2         14         94
         0         15         80
         1         16         81
         2         17         82
         0         18         83
         1         19         84
         2         20
20 rows selected.
SQL> break on cid on mark skip 1
SQL> select cid,mark,sname,count(sname) cnt
  2  from grade g, stu s
  3  where g.sid = s.sid
  4  group by cid,mark,sname
  5  having count(sname) > 2;
       CID       MARK SNAM        CNT
---------- ---------- ---- ----------
         0         80 a             3
                   83 d             4
                   86 g             3
                   89 j             3
                   92 m             3
         1         81 b             4
       CID       MARK SNAM        CNT
---------- ---------- ---- ----------
         1         84 e             4
                   87 h             3
                   90 k             3
                   93 n             3
         2         82 c             4
       CID       MARK SNAM        CNT
---------- ---------- ---- ----------
         2         85 f             3
                   88 i             3
                   91 l             3
                   94 o             3
15 rows selected.
SQL> select cid,s.sid
  2  from stu s,grade g
  3  where s.sid = g.sid
  4  and g.mark is null;
       CID        SID
---------- ----------
         2         20
SQL> select sname,count(sname) cnt
  2  from stu
  3  group by sname
  4  having count(sname) > 1;
SNAM        CNT
---- ----------
a             3
b             4
c             4
d             4
e             4
f             4
g             3
h             3
i             3
j             3
k             3
SNAM        CNT
---- ----------
l             3
m             3
n             3
o             3
15 rows selected.
SQL> 

�� �� yhangw.bbs@bbs.sjtu.edu.cn (ľ��) �Ĵ������ᵽ: ��
: STUDENT(����ѧ�ţ��Ա𣬳������£���ѧ���£����ᣬϵ�𣬣���
: COURSE���γ���γ̺ţ�ѧ�ţ�����ʱ�䣬���޿κţ���
: GRADE��ѧ�ţ��ɼ����γ̺ţ���
: �����¹�ϵ����
: ͬһ�ſγɼ���ͬ��ѧ�����ֶԣ�
: ÿ��ѧ���޳ɼ��Ŀγ̣��޳ɼ�ʱֵΪnull)
: ͬ��ѧ��ż�ԣ�


--
�� ��Դ:������δ��վ bbs.pku.edu.cn��[FROM: 61.149.128.177]