学步园

笔记081110

2008-11-11 下午8:15 发布人 tiger chang

*************************************************
*    NAME    :ChangTiger            *
*    DATE     :19:28 2008-11-10        *
*    EMAIL    :o52tiger@yahoo.com.cn        *
*************************************************

**********************************************************************************
等值连接
SQL> SELECT A.NAME ,B.AGE FROM TIGER2 A ,TIGER B WHERE A.ID = B.ID;
**********************************************************************************

再建立一个张表用来存储课程信息
CREATE TABLE LESSION2(
LID NUMBER(3),
LNAME VARCHAR(20)
);
插入些许数据
INSERT INTO LESSION2 VALUES(110,'SHU_XUE');
INSERT INTO LESSION2 VALUES(111,'YU_WEN');
INSERT INTO LESSION2 VALUES(112,'HUA_XUE');

建立一张表存储学生选课信息
CREATE TABLE  S_L2(
SID NUMBER(8),
LID NUMBER(3),
SCORE NUMBER(3)
);
插入些许数据
INSERT INTO S_L2 VALUES(1001,112,16);
INSERT INTO S_L2 VALUES(1002,112,56);
INSERT INTO S_L2 VALUES(1003,112,96);
INSERT INTO S_L2 VALUES(1004,112,46);

查询学生的名字，所学课程的名字和成绩
SELECT S.SNAME , L.LNAME, SL.SCORE  FROM STUDENT2 S , LESSION2 L , S_L2 SL WHERE
L.LID = SL.LID AND  S.ID = SL.SID;


SNAME                LNAME                     SCORE
-------------------- -------------------- ----------
CHANGTIGER           SHU_XUE                      86
CHANGTIGER           YU_WEN                       86
CHANGTIGER           HUA_XUE                      16
NANCHANG             SHU_XUE                      86
NANCHANG             YU_WEN                       86
NANCHANG             HUA_XUE                      56
SHANGHAI             SHU_XUE                      56
SHANGHAI             YU_WEN                       56
SHANGHAI             HUA_XUE                      96
BEIJING              SHU_XUE                      46
BEIJING              YU_WEN                       46

SQL>

SELECT S.SNAME , L.LNAME ,S.AGE   FROM STUDENT2 S , LESSION2 L , S_L2 SL WHERE
L.LID = SL.LID AND  S.ID = SL.SID AND S.AGE > 23  ORDER BY L.LNAME;

**********************************************************************************
建表用来存储成绩的级别，比如80到100为A
CREATE TABLE GRADE2(
LEV VARCHAR2(2),
LOW NUMBER(3),
HIGH NUMBER(3)
);
插入些许数据
INSERT INTO GRADE2 VALUES('A',80,100);
INSERT INTO GRADE2 VALUES('B',60,80);
INSERT INTO GRADE2 VALUES('C',50,60);
INSERT INTO GRADE2 VALUES('D',0,50);

查询学生名字 所学课程 和成绩等级
SELECT S.SNAME ,L.LNAME , G.LEV
FROM STUDENT2 S, GRADE2 G ,S_L2 SL ,LESSION2 L
WHERE L.LID = SL.LID AND S.ID = SL.SID AND (SL.SCORE
BETWEEN G.LOW AND G.HIGH ) 

SNAME                LNAME                LE
-------------------- -------------------- --
CHANGTIGER           SHU_XUE              A
CHANGTIGER           YU_WEN               A
CHANGTIGER           HUA_XUE              D
NANCHANG             SHU_XUE              A
NANCHANG             YU_WEN               A
NANCHANG             HUA_XUE              C
SHANGHAI             SHU_XUE              C
SHANGHAI             YU_WEN               C
SHANGHAI             HUA_XUE              A
BEIJING              SHU_XUE              D
BEIJING              YU_WEN               D

SQL>

**********************************************************************************
SQL> SELECT A.SNAME,A.ID ,B.SNAME,B.ID FROM STUDENT2 A ,STUDENT2 B WHERE A.SNAME = B.SNAME
 AND A.ID != B.ID;

SQL> SELECT S.SNAME,S.AGE,C.NAME FROM STUDENT2 S,CLASS2 C WHERE S.CID(+) =  C.ID
;

SNAME                       AGE NAME
-------------------- ---------- --------------------
CHANGTIGER                   24 JAVA
BEIJING                      22 JAVA
NANCHANG                     24 JAVA
CHANGTIGER                   34 C++
SHANGHAI                     24 C++
BEIJING                      24 C++

6 rows selected.
SQL> SELECT S.SNAME,S.AGE,C.NAME FROM STUDENT2 S,CLASS2 C WHERE S.CID =  C.ID(+)
;
SNAME                       AGE NAME
-------------------- ---------- --------------------
CHANGTIGER                   24 JAVA
BEIJING                      22 JAVA
NANCHANG                     24 JAVA
CHANGTIGER                   34 C++
SHANGHAI                     24 C++
BEIJING                      24 C++
BEIJINDDD                    11

7 rows selected.

**********************************************************************************
CLEAR SCREEN; 清屏
**********************************************************************************
SQL> SELECT COUNT(* ) FROM STUDENT2;

  COUNT(*)
----------
         7

SQL> SELECT AGE , COUNT(CID) FROM STUDENT2 GROUP BY AGE ;

       AGE COUNT(CID)
---------- ----------
        11          0
        22          1
        24          4
        34          1

**********************************************************************************
SQL> SELECT TRUNC(AGE/10) ,COUNT(*) FROM STUDENT2 GROUP BY TRUNC(AGE/10);

TRUNC(AGE/10)   COUNT(*)
------------- ----------
            1          1
            2          5
            3          1

**********************************************************************************
**********************************************************************************
SQL> SELECT AVG(DISTINCT AGE) FROM STUDENT2;

AVG(DISTINCTAGE)
----------------
           22.75

********************************************************************************************