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

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: