クラシック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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: