2014-12-10 怒放的生命   Leave a comment

多年来,断断续续,一次又一次的开始,一次又一次的结束。

坚持下去吧!

怒放的生命!

不管做什么,你都义无反顾,勇往直前!

想念远方慢慢衰老的爸爸妈妈,还有不知道过得幸福不幸福的小妹了。

 

为了谁,活着!

一群人看着你,

关注你,

你却离得远远的。

这样,

真的好吗

活着,为了什么!

应该好好思考思考了!

クラシックORACLE SQL検索練習第四篇   Leave a comment

                                                    –知識共有を通じて個人ブランドを立

こんにちは、Linouです。今日もSQL検索練習のブログを書きます。

皆さんと一緒に良く使うSQLを共有します、良いSQLがあったら、共有してください。

それから、本文中の問題について、良い解決方法があれば、ぜひコメントしてくいただければ。

第一篇:http://orafox.blog.fc2.com/blog-entry-11.html

第二篇:http://orafox.blog.fc2.com/blog-entry-13.html

第三篇:http://orafox.blog.fc2.com/blog-entry-15.html

今日の練習は複雑な業務です、難しいと思って、よく頑張ってください

本文中に、使っている表とデータは:

hr@ORCL> desc regions
Name                                                  Null?    Type
—————————————————– ——– ————————————
REGION_ID                                             NOT NULL NUMBER
REGION_NAME                                                    VARCHAR2(25)

hr@ORCL> desc COUNTRIES
Name                                                  Null?    Type
—————————————————– ——– ————————————
COUNTRY_ID                                            NOT NULL CHAR(2)
COUNTRY_NAME                                                   VARCHAR2(40)
REGION_ID                                                      NUMBER

hr@ORCL> desc LOCATIONS
Name                                                  Null?    Type
—————————————————– ——– ————————————
LOCATION_ID                                           NOT NULL NUMBER(4)
STREET_ADDRESS                                                 VARCHAR2(40)
POSTAL_CODE                                                    VARCHAR2(12)
CITY                                                  NOT NULL VARCHAR2(30)
STATE_PROVINCE                                                 VARCHAR2(25)
COUNTRY_ID                                                     CHAR(2)

hr@ORCL> desc EMPLOYEES
Name                                                  Null?    Type
—————————————————– ——– ————————————
EMPLOYEE_ID                                           NOT NULL NUMBER(6)
FIRST_NAME                                                     VARCHAR2(20)
LAST_NAME                                             NOT NULL VARCHAR2(25)
EMAIL                                                 NOT NULL VARCHAR2(25)
PHONE_NUMBER                                                   VARCHAR2(20)
HIRE_DATE                                             NOT NULL DATE
JOB_ID                                                NOT NULL VARCHAR2(10)
SALARY                                                         NUMBER(8,2)
COMMISSION_PCT                                                 NUMBER(2,2)
MANAGER_ID                                                     NUMBER(6)
DEPARTMENT_ID                                                  NUMBER(4)

ER図:

 

今日の問題:

1. 各部門の平均、最大、最小給与、人数を部門番号昇順並んでリストする
2. 各部門で、給与は5000より多いの従業員人数を検索する
3. 各部門で、平均給与と人数を検索する、部門名前で昇順並んでください
4. 部門で同じ給与の従業員統計情報を検索し、その部門番号、給与、人数をリストする
5. 同じ部門で、給与は1000より高い人数は二人超えた部門をリストする、部門名前、地域を表示する
6. 会社の平均給与yおり高い従業員を検索し、その名前と給与をリストする
7. 誰なの給与は50番部門平均給与と80番部門の平均給与の間に
8. 給与は5000より高い従業員をリストする
9. 各部門で、最高給与の従業員をリストする
10. 平均給与は一番高い部門の平均給与を検索する

 

 

◆◆◆◆1. 各部門の平均、最大、最小給与、人数を部門番号昇順並んでリストする◆◆◆◆

SELECT DEPARTMENT_ID AS DEPARTMENT,AVG(SALARY) AS AVG_SALARY
,MAX(SALARY) AS MAX_SALARY,MIN(SALARY)  AS MIN_SALARY
,COUNT(*) AS COUNT
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID ASC;

DEPARTMENT AVG_SALARY MAX_SALARY MIN_SALARY      COUNT
———- ———- ———- ———- ———-
        10       4400       4400       4400          1
        20       9500      13000       6000          2
        30       4150      11000       2500          6
        40       6500       6500       6500          1
        50 3475.55556       8200       2100         45
        60       5760       9000       4200          5
        70      10000      10000      10000          1
        80 8955.88235      14000       6100         34
        90 19333.3333      24000      17000          3
       100 8601.33333      12008       6900          6
       110      10154      12008       8300          2
                 7000       7000       7000          1

12 rows selected.

◆◆◆◆2. 各部門で、給与は5000より多いの従業員人数を検索する◆◆◆◆

SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES
WHERE SALARY > 5000
GROUP BY DEPARTMENT_ID;

DEPARTMENT_ID   COUNT(*)
————- ———-
          100          6
           30          1
                       1
           20          2
           70          1
           90          3
          110          2
           40          1
           50          5
           80         34
           60          2

11 rows selected.

◆◆◆◆3. 各部門で、平均給与と人数を検索する、部門名前で昇順並んでください◆◆◆◆

SELECT DPTNAME,AVG(SALARY),COUNT(*) FROM
   (SELECT
   (SELECT DEPT.DEPARTMENT_NAME FROM DEPARTMENTS DEPT
   WHERE DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID) DPTNAME,
   EMP.SALARY
   FROM EMPLOYEES EMP)
   GROUP BY DPTNAME
ORDER BY DPTNAME;

DPTNAME                        AVG(SALARY)   COUNT(*)
—————————— ———– ———-
Accounting                           10154          2
Administration                        4400          1
Executive                       19333.3333          3
Finance                         8601.33333          6
Human Resources                       6500          1
IT                                    5760          5
Marketing                             9500          2
Public Relations                     10000          1
Purchasing                            4150          6
Sales                           8955.88235         34
Shipping                        3475.55556         45
                                      7000          1

12 rows selected.

 

SELECT DEPT.DEPARTMENT_NAME,AVG(EMP.SALARY),COUNT(*)
    FROM EMPLOYEES EMP,DEPARTMENTS DEPT
    WHERE EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID
    GROUP BY DEPT.DEPARTMENT_NAME
  ORDER BY DEPT.DEPARTMENT_NAME;

DEPARTMENT_NAME                AVG(EMP.SALARY)   COUNT(*)
—————————— ————— ———-
Accounting                               10154          2
Administration                            4400          1
Executive                           19333.3333          3
Finance                             8601.33333          6
Human Resources                           6500          1
IT                                        5760          5
Marketing                                 9500          2
Public Relations                         10000          1
Purchasing                                4150          6
Sales                               8955.88235         34
Shipping                            3475.55556         45

11 rows selected.

部門番号はNULLのはリストしなかった

◆◆◆◆4. 部門で同じ給与の従業員統計情報を検索し、その部門番号、給与、人数をリストする◆◆◆◆

SELECT EMP1.DEPARTMENT_ID,EMP1.SALARY,COUNT(*) CNT
    FROM   EMPLOYEES EMP1,EMPLOYEES EMP2
    WHERE  EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID AND
    EMP1.SALARY = EMP2.SALARY
    AND EMP1.EMPLOYEE_ID <> EMP2.EMPLOYEE_ID
GROUP BY EMP1.DEPARTMENT_ID,EMP1.SALARY;

DEPARTMENT_ID     SALARY        CNT
————- ———- ———-
           90      17000          2
           50       3200         12
           50       2200          2
           50       3600          2
           80      10500          2
           80       9000          2
           50       2700          2
           50       3100          6
           80      10000          6
           50       3000          2
           60       4800          2
           50       3300          2
           80       6200          2
           50       2600          6
           50       2800          6
           50       2500         20
           50       2400          2
           80       9500          6
           80       7500          2
           80      11000          2
           80       7000          2
           50       2900          2
           80       8000          2

23 rows selected.

◆◆◆◆5. 同じ部門で、給与は1000より高い人数は二人超えた部門をリストする、部門名前、地域を表示する

◆◆◆◆

SELECT D.DEPARTMENT_NAME,L.CITY,COUNT(*)
    FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L
    WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
    D.LOCATION_ID   = L.LOCATION_ID    AND
   E.SALARY > 1000
    GROUP BY D.DEPARTMENT_NAME,L.CITY
  HAVING COUNT(*) > 2;

DEPARTMENT_NAME                CITY                             COUNT(*)
—————————— —————————— ———-
IT                             Southlake                               5
Sales                          Oxford                                 34
Shipping                       South San Francisco                    45
Purchasing                     Seattle                                 6
Executive                      Seattle                                 3
Finance                        Seattle                                 6

6 rows selected.

◆◆◆◆6. 会社の平均給与yおり高い従業員を検索し、その名前と給与をリストする◆◆◆◆

SELECT FIRST_NAME || ‘ ‘ || LAST_NAME,SALARY
     FROM EMPLOYEES
    WHERE SALARY > (
    SELECT AVG(SALARY)
    FROM EMPLOYEES
    )
  ORDER BY SALARY DESC;

FIRST_NAME||”||LAST_NAME                          SALARY
———————————————- ———-
Steven King                                         24000
Neena Kochhar                                       17000
Lex De Haan                                         17000
John Russell                                        14000
Karen Partners                                      13500
Michael Hartstein                                   13000
Shelley Higgins                                     12008
Nancy Greenberg                                     12008
Alberto Errazuriz                                   12000
Lisa Ozer                                           11500
Gerald Cambrault                                    11000
Ellen Abel                                          11000
Den Raphaely                                        11000
Clara Vishney                                       10500
Eleni Zlotkey                                       10500
Peter Tucker                                        10000
Janette King                                        10000
Hermann Baer                                        10000
Harrison Bloom                                      10000
Tayler Fox                                           9600
Danielle Greene                                      9500
Patrick Sully                                        9500
David Bernstein                                      9500
Allan McEwen                                         9000
Daniel Faviet                                        9000
Alexander Hunold                                     9000
Peter Hall                                           9000
Alyssa Hutton                                        8800
Jonathon Taylor                                      8600
Jack Livingston                                      8400
William Gietz                                        8300
John Chen                                            8200
Adam Fripp                                           8200
Christopher Olsen                                    8000
Matthew Weiss                                        8000
Lindsey Smith                                        8000
Payam Kaufling                                       7900
Jose Manuel Urman                                    7800
Ismael Sciarra                                       7700
Nanette Cambrault                                    7500
Louise Doran                                         7500
William Smith                                        7400
Elizabeth Bates                                      7300
Mattea Marvins                                       7200
Sarath Sewall                                        7000
Oliver Tuvault                                       7000
Kimberely Grant                                      7000
Luis Popp                                            6900
David Lee                                            6800
Susan Mavris                                         6500
Shanta Vollman                                       6500

51 rows selected.

◆◆◆◆7. 誰なの給与は50番部門平均給与と80番部門の平均給与の間に◆◆◆◆

SELECT FIRST_NAME || ‘ ‘ || LAST_NAME AS NAME,SALARY
    FROM EMPLOYEES
   WHERE SALARY
   BETWEEN
   (SELECT AVG(SALARY) FROM EMPLOYEES
   WHERE DEPARTMENT_ID = 50)
   AND (SELECT AVG(SALARY) FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 80);

NAME                                               SALARY
———————————————- ———-
Jennifer Whalen                                      4400
Pat Fay                                              6000
Susan Mavris                                         6500
William Gietz                                        8300
Bruce Ernst                                          6000
David Austin                                         4800
Valli Pataballa                                      4800
Diana Lorentz                                        4200
John Chen                                            8200
Ismael Sciarra                                       7700
Jose Manuel Urman                                    7800
Luis Popp                                            6900
Matthew Weiss                                        8000
Adam Fripp                                           8200
Payam Kaufling                                       7900
Shanta Vollman                                       6500
Kevin Mourgos                                        5800
Renske Ladwig                                        3600
Trenna Rajs                                          3500
Christopher Olsen                                    8000
Nanette Cambrault                                    7500
Oliver Tuvault                                       7000
Lindsey Smith                                        8000
Louise Doran                                         7500
Sarath Sewall                                        7000
Mattea Marvins                                       7200
David Lee                                            6800
Sundar Ande                                          6400
Amit Banda                                           6200
William Smith                                        7400
Elizabeth Bates                                      7300
Sundita Kumar                                        6100
Alyssa Hutton                                        8800
Jonathon Taylor                                      8600
Jack Livingston                                      8400
Kimberely Grant                                      7000
Charles Johnson                                      6200
Nandita Sarchand                                     4200
Alexis Bull                                          4100
Kelly Chung                                          3800
Jennifer Dilly                                       3600
Sarah Bell                                           4000
Britney Everett                                      3900

43 rows selected.

◆◆◆◆8. 給与は5000より高い従業員をリストする◆◆◆◆

SELECT FIRST_NAME || ‘ ‘ || LAST_NAME AS NAME,SALARY
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID IN
    (SELECT DEPARTMENT_ID FROM EMPLOYEES
    GROUP BY DEPARTMENT_ID
  HAVING AVG(SALARY) > 5000);

NAME                                               SALARY
———————————————- ———-
Nancy Greenberg                                     12008
Daniel Faviet                                        9000
John Chen                                            8200
Ismael Sciarra                                       7700
Jose Manuel Urman                                    7800
Luis Popp                                            6900
Michael Hartstein                                   13000
Pat Fay                                              6000
Hermann Baer                                        10000
Steven King                                         24000
Neena Kochhar                                       17000
Lex De Haan                                         17000
Shelley Higgins                                     12008
William Gietz                                        8300
Susan Mavris                                         6500
John Russell                                        14000
Karen Partners                                      13500
Alberto Errazuriz                                   12000
Gerald Cambrault                                    11000
Eleni Zlotkey                                       10500
Peter Tucker                                        10000
David Bernstein                                      9500
Peter Hall                                           9000
Christopher Olsen                                    8000
Nanette Cambrault                                    7500
Oliver Tuvault                                       7000
Janette King                                        10000
Patrick Sully                                        9500
Allan McEwen                                         9000
Lindsey Smith                                        8000
Louise Doran                                         7500
Sarath Sewall                                        7000
Clara Vishney                                       10500
Danielle Greene                                      9500
Mattea Marvins                                       7200
David Lee                                            6800
Sundar Ande                                          6400
Amit Banda                                           6200
Lisa Ozer                                           11500
Harrison Bloom                                      10000
Tayler Fox                                           9600
William Smith                                        7400
Elizabeth Bates                                      7300
Sundita Kumar                                        6100
Ellen Abel                                          11000
Alyssa Hutton                                        8800
Jonathon Taylor                                      8600
Jack Livingston                                      8400
Charles Johnson                                      6200
Alexander Hunold                                     9000
Bruce Ernst                                          6000
David Austin                                         4800
Valli Pataballa                                      4800
Diana Lorentz                                        4200

54 rows selected.

◆◆◆◆9. 各部門で、最高給与の従業員をリストする◆◆◆◆

SELECT FIRST_NAME || ‘ ‘ || LAST_NAME AS NAME
    ,SALARY,DEPARTMENT_ID
    FROM EMPLOYEES
    WHERE (DEPARTMENT_ID,SALARY) IN
    (SELECT DEPARTMENT_ID,MAX(SALARY)
    FROM EMPLOYEES
  GROUP BY DEPARTMENT_ID);

NAME                                               SALARY DEPARTMENT_ID
———————————————- ———- ————-
Nancy Greenberg                                     12008           100
Den Raphaely                                        11000            30
Michael Hartstein                                   13000            20
Hermann Baer                                        10000            70
Steven King                                         24000            90
Shelley Higgins                                     12008           110
Adam Fripp                                           8200            50
Susan Mavris                                         6500            40
John Russell                                        14000            80
Jennifer Whalen                                      4400            10
Alexander Hunold                                     9000            60

11 rows selected.

◆◆◆◆10. 平均給与は一番高い部門の平均給与を検索する◆◆◆◆

SELECT MAX(AVGSALARY)
   FROM(SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY
   FROM EMPLOYEES
  GROUP BY DEPARTMENT_ID);

MAX(AVGSALARY)
————–
    19333.3333

Posted 2013/05/05 by linou518 in Old Memory

クラシックORACLE SQL検索練習第二篇   Leave a comment

こんにちは、Linouです。今日もSQL検索練習のブログを書きます。

皆さんと一緒に良く使うSQLを共有します、良いSQLがあったら、共有してください。

それから、本文中の問題について、良い解決方法があれば、ぜひコメントしてくいただければ。

第一篇:http://orafox.blog.fc2.com/blog-entry-11.html

今日の練習は第一篇より簡単たと思います、もし誰か出来ない場合、良くSQL基礎をちゃんと勉強しなきゃ。

本文中に、使っている表とデータは同じ:

sys@ORCL> conn scott/tiger
Connected.
scott@ORCL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
BONUS                          TABLE
DEPT                           TABLE
DUMMY                          TABLE
EMP                            TABLE
SALGRADE                       TABLE

scott@ORCL> desc emp
Name                                                  Null?    Type
—————————————————– ——– ————————————
EMPNO                                                 NOT NULL NUMBER(4) 従業員番号
ENAME                                                          VARCHAR2(10) 従業員名前
JOB                                                            VARCHAR2(9) 作業
MGR                                                            NUMBER(4) 上司番号
HIREDATE                                                       DATE 入社日付
SAL                                                            NUMBER(7,2) 給与
COMM                                                           NUMBER(7,2) ボラス
DEPTNO                                                         NUMBER(2) 部門番号

scott@ORCL> desc dept
Name                                                  Null?    Type
—————————————————– ——– ————————————
DEPTNO                                                         NUMBER(2) 部門番号
DNAME                                                          VARCHAR2(14) 部門名前
LOC                                                            VARCHAR2(13) ロケーション

Tips:
total salary=SAL+COMM
emp表でのデータ:

scott@ORCL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

dept表でのデータ:

scott@ORCL> select * from dept;

    DEPTNO DNAME          LOC
———- ————– ————-
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

 

練習問題は:

1. EMP表に、名前(ENAME)第三文字は「A」の従業員を検索する。
2. EMP表に、名前の中に、AとNが含まれている従業員を検索する。
3. ボラス―がある従業員を検索し、その名前、給与、ボラス―をリストし、給与はローからハイまで、それから、ボラス―はハイからローまで並んでください。
4. 部門番号は20のJOBをリストする。
5. SALES以外の部門を検索する。
6. 給与は1000-1500以外の従業員を検索し、その名前、給与をリスト、給与はハイからローまで。
7. ManagerとSALESMANのJOBを検索し、条件:年収は15,000~20,000、名前、JOB、年収をリストする。
8. 次の2つのSQL文の出力を説明:
SELECT EMPNO、COMMがNULL IS EMP FROM COMM;
EMP FROM SELECT EMPNO、COMM WHERE COMM= NULL;
9. SELECT ENAME,SAL FROM EMP WHERE SAL > ‘1500’ エラーになるでしょうか、なぜですか。

 

◆◆◆◆◆1. EMP表に、名前(ENAME)第三文字は「A」の従業員を検索する。◆◆◆◆◆

scott@ORCL> select * from emp where ename like ‘__A%';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20

◆◆◆◆◆2. EMP表に、名前の中に、AとNが含まれている従業員を検索する。◆◆◆◆◆

scott@ORCL>  SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE ‘%A%’ AND ENAME LIKE ‘%N%';

ENAME
———-
ALLEN
MARTIN

 

◆◆◆◆◆3. ボラス―がある従業員を検索し、その名前、給与、ボラス―をリストし、給与はローからハイまで、それから、ボラス―はハイからローまで並んでください。◆◆◆◆◆

scott@ORCL> select ename,sal,comm from emp where comm is not null and comm <> 0 order by comm desc;

ENAME             SAL       COMM
———- ———- ———-
MARTIN           1250       1400
WARD             1250        500
ALLEN            1600        300

 

◆◆◆◆◆4. 部門番号は20のJOBをリストする。◆◆◆◆◆

scott@ORCL> select distinct job from emp where deptno=20;

JOB
———
CLERK
MANAGER
ANALYST

 

◆◆◆◆◆5. SALES以外の部門を検索する。◆◆◆◆◆

scott@ORCL> select * from dept where dname <> ‘SALES';

    DEPTNO DNAME          LOC
———- ————– ————-
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        40 OPERATIONS     BOSTON

 

◆◆◆◆◆6. 給与は1000-1500以外の従業員を検索し、その名前、給与をリスト、給与はハイからローまで。◆◆◆◆◆

scott@ORCL> select ename,sal from emp where sal<1000 or sal>1500 order by sal desc;

ENAME             SAL
———- ———-
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850
CLARK            2450
ALLEN            1600
JAMES             950
SMITH             800

9 rows selected.

 

◆◆◆◆◆7. ManagerとSALESMANのJOBを検索し、条件:年収は15,000~20,000、名前、JOB、年収をリストする。◆◆◆◆◆

scott@ORCL> select ename,job,(sal+nvl(comm,0))*12 as salary from emp where job=’MANAGER’ or job =’SALESMAN’ order by salary desc;

ENAME      JOB           SALARY
———- ——— ———-
JONES      MANAGER        35700
BLAKE      MANAGER        34200
MARTIN     SALESMAN       31800
CLARK      MANAGER        29400
ALLEN      SALESMAN       22800
WARD       SALESMAN       21000
TURNER     SALESMAN       18000

7 rows selected.

 

◆◆◆◆◆8. 次の2つのSQL文の出力を説明:
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;

◆◆◆◆◆

scott@ORCL> SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;

     EMPNO       COMM
———- ———-
      7369
      7566
      7698
      7782
      7788
      7839
      7876
      7900
      7902
      7934

10 rows selected.

scott@ORCL> SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;

no rows selected

 

◆◆◆◆◆9. SELECT ENAME,SAL FROM EMP WHERE SAL > ‘1500’ エラーになるでしょうか、なぜですか。◆◆◆◆◆

scott@ORCL> SELECT ENAME,SAL FROM EMP WHERE SAL > 1500;

ENAME             SAL
———- ———-
ALLEN            1600
JONES            2975
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
FORD             3000

7 rows selected.

Posted 2013/05/01 by linou518 in Oracle Base

日本での4年とその話 第 0 話   Leave a comment

メニュー

1.日本に行く前
2.始めての飛行機
3.成田空港
4.始めての電車
5.日本での第一夜
6.外国人登録書
7.日本の初印象
8.社長との初面
9.日本語は話せない
10.始めての買い物
11.日本語の勉強
12.面接(1)
13.面接(2)
14.512四川地震
15.霍さん
16.バスケットボール
17.走る
18.日本語教室
19.外食(焼肉)
20.外食(カレー)
21.仕事を探す
22.会社との交渉
23.始めての転職
24.李社長
25.アルファ新洋の面接
26.アルファ新洋入社
27.日本の食事
28.日本の食事中華料理
29.日本の食事蕎麦
30.日本の食事吉野家
31.日本の食事松屋
32.日本の食事寿司
33.日本の食事すき焼
34.日本の食事天麩羅
35.日本の食事刺身
36.日本での旅行
37.日本での旅行秋葉原
38.日本での旅行上野
39.日本での旅行東京
40.日本での旅行東京スカイツリー
41.日本での旅行東京タワー
42.日本での旅行東京駅
43.日本での旅行東京ドーム
44.日本での旅行皇居外苑
45.アルファ新洋の初出社
46.アルファ新洋の人物
47.アルファ新洋の人物西川さん
48.アルファ新洋の人物長神さん
49.アルファ新洋の人物伊藤さん
50.アルファ新洋の人物大西さん
51.アルファ新洋の人物町井さん
52.アルファ新洋の人物浅井さん
53.アルファ新洋の人物尾野さん
54.アルファ新洋の人物瀬戸さん
55.アルファ新洋の人物小寺さん
56.アルファ新洋の人物池添さん
57.アルファ新洋の人物吉村さん
58.アルファ新洋の人物石橋さん
59.アルファ新洋の人物上脇さん
60.アルファ新洋の人物高木さん
61.アルファ新洋の人物ニャンさん
62.アルファ新洋の人物ヒンさん
63.歓迎会
64.引っ越する
65.北松戸
66.松戸
67.川越市
68.川越市の生活
69.結婚
70.彼女の初面
71.東京ディズニー
72.渋谷
73.北松戸からの通勤
74.川越市からの通勤
75.オラクルBronze
76.オラクルSilver
77.始めて日本人の長神さんのお宅へ
78.長神さんのパソコン
79.長神さんの奥さん
80.妻と一緒に長神さんのお宅へ
81.長神さん夫婦と一緒に横浜中華街へ
82.正社員の悩み
83.ルール違反
84.妻の仕事
85.突然の退職
86.家を探す
87.小菅へ引越し
88.財布は盗まれ
89.甲府での通勤
90.田町での通勤
91.311大地震
92.羽田空港での通勤
93.梅田へ引越
94.家を造る
95.梅田での生活
96.妻の病気
97.中国に帰る

 

毎日少なくとも一つを書くつもりなので、頑張りましょう。

Posted 2013/05/01 by linou518 in Old Memory

クラシックORACLE SQL検索練習第一篇   Leave a comment

                                                     –知識共有を通じて個人ブランドを立

こんにちは、Linouです。今日から、SQL検索練習のブログを書きましょう。

皆さんと一緒に良く使うSQLを共有します、良いSQLがあったら、共有してください。

それから、本文中の問題について、良い解決方法があれば、ぜひコメントしてくいただければ。

皆さんと一緒に上達になりましょう。

本文中に、使っている表とデータ:

sys@ORCL> conn scott/tiger
Connected.
scott@ORCL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
BONUS                          TABLE
DEPT                           TABLE
DUMMY                          TABLE
EMP                            TABLE
SALGRADE                       TABLE

scott@ORCL> desc emp
Name                                                  Null?    Type
—————————————————– ——– ————————————
EMPNO                                                 NOT NULL NUMBER(4) 従業員番号
ENAME                                                          VARCHAR2(10) 従業員名前
JOB                                                            VARCHAR2(9) 作業
MGR                                                            NUMBER(4) 上司番号
HIREDATE                                                       DATE 入社日付
SAL                                                            NUMBER(7,2) 給与
COMM                                                           NUMBER(7,2) ボラス
DEPTNO                                                         NUMBER(2) 部門番号

 

scott@ORCL> desc dept
Name                                                  Null?    Type
—————————————————– ——– ————————————
DEPTNO                                                         NUMBER(2) 部門番号
DNAME                                                          VARCHAR2(14) 部門名前
LOC                                                            VARCHAR2(13) ロケーション

Tips:
total salary=SAL+COMM

emp表でのデータ:

scott@ORCL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

dept表でのデータ:

scott@ORCL> select * from dept;

    DEPTNO DNAME          LOC
———- ————– ————-
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

 

SQLを使って、以下の問題を答えでください

1、スタッフを持っているすべての部門をリストする
2、給与は "SMITH"より多いスタッフをリストする
3、従業員とその直属の上司の名前をリストする
4、雇用日付は直接上司より早い従業員をリストする
5、部門と部門の従業員情報リストする、従業員はない部門もリストする
6、"CLERK″従業員の名前と部門名をリストする
7、給与が1500より大きい仕事名をリストする
8、SALES部門の従業員名前をリストする、SALES部門番号は分からない場合
9、平均給与より高い従業員をリストする
10、"SCOTT"と同じ作業する従業員をリストする
11、部門30の従業員と同じ給与の従業員名前と給与をリストする
12、部門30の従業員の給与より高い従業員名前と給与をリストする
13、それぞれの部門で働く従業員数、平均給与、平均期間
14、全従業員の名前、部門名と給与をリストする
15、全部門情報と部門従業員数をリストする
16、作業の最低給与をリストする

17、各部門のマネージャー(マネージャー)最低給与をリストする
18、従業員の年俸をリストする、ローからハイに並べる

答えは以下になります、違う答えがWelcome~~~~

◆◆◆◆◆1、スタッフを持っているすべての部門をリストする◆◆◆◆◆

scott@ORCL> select dname from dept where deptno in(select deptno from emp);

DNAME
————–
RESEARCH
SALES
ACCOUNTING

◆◆◆◆◆2、給与は "SMITH"より多いスタッフをリストする◆◆◆◆◆

scott@ORCL> select * from emp where sal > (select sal from emp where ename=’SMITH’);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

13 rows selected.

Execution Plan
———————————————————-
Plan hash value: 587534197

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |     1 |    38 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL | EMP  |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    10 |     2   (0)| 00:00:01 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

   1 – filter("SAL"> (SELECT "SAL" FROM "EMP" "EMP" WHERE
              "ENAME"=’SMITH’))
   2 – filter("ENAME"=’SMITH’)

Statistics
———————————————————-
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1547  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         13  rows processed

◆◆◆◆◆3、従業員とその直属の上司の名前をリストする◆◆◆◆◆

scott@ORCL> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;

ENAME      BOSS_NAME
———- ———-
SMITH      FORD
ALLEN      BLAKE
WARD       BLAKE
JONES      KING
MARTIN     BLAKE
BLAKE      KING
CLARK      KING
SCOTT      JONES
KING
TURNER     BLAKE
ADAMS      SCOTT
JAMES      BLAKE
FORD       JONES
MILLER     CLARK

14 rows selected.

Execution Plan
———————————————————-
Plan hash value: 102146803

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |    14 |   140 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    10 |     2   (0)| 00:00:01 |
|   2 |  TABLE ACCESS FULL| EMP  |    14 |   140 |     2   (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

   1 – filter("B"."EMPNO"=:B1)

Statistics
———————————————————-
          1  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
        850  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

◆◆◆◆◆4、雇用日付は直接上司より早い従業員をリストする◆◆◆◆◆

scott@ORCL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);

ENAME
———-
SMITH
ALLEN
WARD
JONES
BLAKE
CLARK

6 rows selected.

Execution Plan
———————————————————-
Plan hash value: 2561671593

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |     1 |    18 |    14   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   252 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     1 |    12 |     2   (0)| 00:00:01 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

   1 – filter("A"."HIREDATE"< (SELECT "HIREDATE" FROM "EMP" "B" WHERE
              "B"."EMPNO"=:B1))
   3 – filter("B"."EMPNO"=:B1)

Statistics
———————————————————-
          1  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
        626  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

◆◆◆◆◆5、部門と部門の従業員情報リストする、従業員はない部門もリストする◆◆◆◆◆

scott@ORCL> select a.dname, b.empno, b.ename, b.job, b.hiredate, b.sal from dept a, emp b where a.deptno=b.deptno(+);

DNAME               EMPNO ENAME      JOB       HIREDATE         SAL
————– ———- ———- ——— ——— ———-
RESEARCH             7369 SMITH      CLERK     17-DEC-80        800
SALES                7499 ALLEN      SALESMAN  20-FEB-81       1600
SALES                7521 WARD       SALESMAN  22-FEB-81       1250
RESEARCH             7566 JONES      MANAGER   02-APR-81       2975
SALES                7654 MARTIN     SALESMAN  28-SEP-81       1250
SALES                7698 BLAKE      MANAGER   01-MAY-81       2850
ACCOUNTING           7782 CLARK      MANAGER   09-JUN-81       2450
RESEARCH             7788 SCOTT      ANALYST   09-DEC-82       3000
ACCOUNTING           7839 KING       PRESIDENT 17-NOV-81       5000
SALES                7844 TURNER     SALESMAN  08-SEP-81       1500
RESEARCH             7876 ADAMS      CLERK     12-JAN-83       1100
SALES                7900 JAMES      CLERK     03-DEC-81        950
RESEARCH             7902 FORD       ANALYST   03-DEC-81       3000
ACCOUNTING           7934 MILLER     CLERK     23-JAN-82       1300
OPERATIONS

15 rows selected.

方法二:

scott@ORCL> select a.dname,b.empno,b.ename,b.job,b.hiredate,b.sal,b.deptno from dept a left join emp b on a.deptno=b.deptno;

DNAME               EMPNO ENAME      JOB       HIREDATE         SAL     DEPTNO
————– ———- ———- ——— ——— ———- ———-
RESEARCH             7369 SMITH      CLERK     17-DEC-80        800         20
SALES                7499 ALLEN      SALESMAN  20-FEB-81       1600         30
SALES                7521 WARD       SALESMAN  22-FEB-81       1250         30
RESEARCH             7566 JONES      MANAGER   02-APR-81       2975         20
SALES                7654 MARTIN     SALESMAN  28-SEP-81       1250         30
SALES                7698 BLAKE      MANAGER   01-MAY-81       2850         30
ACCOUNTING           7782 CLARK      MANAGER   09-JUN-81       2450         10
RESEARCH             7788 SCOTT      ANALYST   09-DEC-82       3000         20
ACCOUNTING           7839 KING       PRESIDENT 17-NOV-81       5000         10
SALES                7844 TURNER     SALESMAN  08-SEP-81       1500         30
RESEARCH             7876 ADAMS      CLERK     12-JAN-83       1100         20
SALES                7900 JAMES      CLERK     03-DEC-81        950         30
RESEARCH             7902 FORD       ANALYST   03-DEC-81       3000         20
ACCOUNTING           7934 MILLER     CLERK     23-JAN-82       1300         10
OPERATIONS

15 rows selected.

◆◆◆◆◆6、"CLERK″作業をする従業員名前と部門名をリストする◆◆◆◆◆

scott@ORCL> select a.ename, b.deptno from emp a, dept b where a.deptno = b.deptno and a.job=’CLERK';

ENAME          DEPTNO
———- ———-
MILLER             10
ADAMS              20
SMITH              20
JAMES              30

◆◆◆◆◆7、給与が1500より多い仕事名をリストする◆◆◆◆◆

scott@ORCL> select distinct job from emp group by job having min(sal)>1500;

JOB
———
PRESIDENT
MANAGER
ANALYST

◆◆◆◆◆8、SALES部門の従業員名前をリストする、SALES部門番号は分からない場合◆◆◆◆◆

scott@ORCL> select ename from emp where deptno=(select deptno from dept where dname=’SALES’);

ENAME
———-
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

6 rows selected.

◆◆◆◆◆9、平均給与より高い従業員をリストする◆◆◆◆◆

良い:

scott@ORCL> select a.ename from emp a, (select avg(sal) as sal from emp) b where a.sal>b.sal;

ENAME
———-
JONES
BLAKE
CLARK
SCOTT
KING
FORD

6 rows selected.

悪い:

scott@ORCL> select ename from emp where sal>(select avg(sal) from emp);

ENAME
———-
JONES
BLAKE
CLARK
SCOTT
KING
FORD

6 rows selected.

◆◆◆◆◆10、"SCOTT"と同じ作業する従業員をリストする◆◆◆◆◆

scott@ORCL> select a.ename from emp a, (select job from emp where ename=’SCOTT’) b where a.job =b.job;

ENAME
———-
SCOTT
FORD

◆◆◆◆◆11、部門30の従業員と同じ給与の従業員名前と給与をリストする◆◆◆◆◆

scott@ORCL> select a.ename,a.sal from emp a, (select b.sal from emp b where b.deptno=30) b where a.sal in (b.sal) and a.deptno<>30;

no rows selected

◆◆◆◆◆12、部門30の従業員の給与より高い従業員名前と給与をリストする◆◆◆◆◆

scott@ORCL> select a.ename,a.sal from emp a, (select max(b.sal) as sal from emp b where b.deptno=30) b where a.sal>b.sal;

ENAME             SAL
———- ———-
JONES            2975
SCOTT            3000
KING             5000
FORD             3000

 

◆◆◆◆◆13、それぞれの部門で働く従業員数、平均給与、平均期間◆◆◆◆◆

scott@ORCL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal from emp a group by deptno;

DEPTNAME        DEPTCOUNT DEPTAVGSAL
————– ———- ———-
SALES                   6 1566.66667
RESEARCH                5       2175
ACCOUNTING              3 2916.66667

◆◆◆◆◆14、全従業員の名前、部門名と給与をリストする◆◆◆◆◆

scott@ORCL> select a.ename,b.dname,a.sal from emp a, dept b where a.deptno=b.deptno;

ENAME      DNAME                 SAL
———- ————– ———-
SMITH      RESEARCH              800
ALLEN      SALES                1600
WARD       SALES                1250
JONES      RESEARCH             2975
MARTIN     SALES                1250
BLAKE      SALES                2850
CLARK      ACCOUNTING           2450
SCOTT      RESEARCH             3000
KING       ACCOUNTING           5000
TURNER     SALES                1500
ADAMS      RESEARCH             1100
JAMES      SALES                 950
FORD       RESEARCH             3000
MILLER     ACCOUNTING           1300

14 rows selected.

◆◆◆◆◆15、全部門情報と部門従業員数をリストする◆◆◆◆◆

scott@ORCL> select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a;

    DEPTNO DNAME          LOC            DEPTCOUNT
———- ————– ————- ———-
        10 ACCOUNTING     NEW YORK               3
        20 RESEARCH       DALLAS                 5
        30 SALES          CHICAGO                6
        40 OPERATIONS     BOSTON

◆◆◆◆◆16、作業の最低給与をリストする ◆◆◆◆◆

scott@ORCL> select job,min(sal) from emp group by job;

JOB         MIN(SAL)
——— ———-
CLERK            800
SALESMAN        1250
PRESIDENT       5000
MANAGER         2450
ANALYST         3000

 

◆◆◆◆◆17、各部門のマネージャー(マネージャー)最低給与をリストする◆◆◆◆◆

scott@ORCL> select deptno,min(sal) from emp where job=’MANAGER’ group by deptno;

    DEPTNO   MIN(SAL)
———- ———-
        30       2850
        20       2975
        10       2450

◆◆◆◆◆18、従業員の年俸をリストする、ローからハイに並べる◆◆◆◆◆

scott@ORCL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;

ENAME       SALPERSAL
———- ———-
SMITH            9600
JAMES           11400
ADAMS           13200
MILLER          15600
TURNER          18000
WARD            21000
ALLEN           22800
CLARK           29400
MARTIN          31800
BLAKE           34200
JONES           35700
FORD            36000
SCOTT           36000
KING            60000

14 rows selected.

 

答えは唯一ではないので、良い答えがあれば、共有して頂ければ~~

質問があれば、 linou518@hotmail.com まで、ご連絡をお待ちしております 。

Posted 2013/04/30 by linou518 in Old Memory

オラクル実験環境配置07   Leave a comment

                                               –知識共有を通じて個人ブランドを立つ

メニュ

1.Oracle VirtualBoxインストールと使う

2.OEL5.7のインストール

3.Oracle11gのインストール

4.DBCAでデータベースを作成

5.データベースに接続

6.簡単なテスト(ユーザを作成、表を作成など)

 

単なテスト(ユーザを作成、表を作成など

clip_image002
clip_image004

質問があれば、linou518@hotmail.com まで、ご連絡をお待ちしております。

Posted 2013/04/28 by linou518 in Old Memory

オラクル実験環境配置06   Leave a comment

                                           –知識共有を通じて個人ブランドを立つ

メニュ

1.Oracle VirtualBoxインストールと使う

2.OEL5.7のインストール

3.Oracle11gのインストール

4.DBCAでデータベースを作成

5.データベースに接続

6.簡単なテスト(ユーザを作成、表を作成など)

 

 

ータベースに接続

clip_image002[4]

 

質問があれば、linou518@hotmail.com まで、ご連絡をお待ちしております。

Posted 2013/04/28 by linou518 in Old Memory

关注

每发布一篇新博文的同时向您的邮箱发送备份。