落書きノート

ふと自分が気になった事を書いてます

SQLで問題を解く PostgreSQL

Oracle12cからPostgreSQLに移行して練習してみてます。近いうち新しい本でも買おうかなと。Oracle独自の仕様のもの以外は大体今の本でもいけます。

-- 第0部
-- 問題1
oracleoit=> select * from account
oracleoit-> where account_id <= 6;
 account_id | slip_id | goods_id | qty 
------------+---------+----------+-----
          1 |       1 |        2 |  10
          2 |       1 |        3 |   5
          3 |       1 |        1 |  15
          4 |       2 |        1 |  20
          5 |       3 |        3 |   5
          6 |       3 |        4 |   5

-- 問題1-1
oracleoit=> select * from account
oracleoit-> where slip_id between 2 and 5;
 account_id | slip_id | goods_id | qty 
------------+---------+----------+-----
          4 |       2 |        1 |  20
          5 |       3 |        3 |   5
          6 |       3 |        4 |   5
          7 |       4 |        1 |  15
          8 |       4 |        2 |  10
          9 |       5 |        3 |   7

-- 問題1-2
oracleoit=> select account_id 受注明細番号
oracleoit-> from account
oracleoit-> where goods_id in (3,4);
 受注明細番号 
--------------
            2
            5
            6
            9
           12
           13

-- 問題1-3
oracleoit=> select * from account                                           
oracleoit-> order by goods_id desc, slip_id;
 account_id | slip_id | goods_id | qty 
------------+---------+----------+-----
          6 |       3 |        4 |   5
         13 |       6 |        4 |   5
          2 |       1 |        3 |   5
          5 |       3 |        3 |   5
          9 |       5 |        3 |   7
         12 |       6 |        3 |   5
          1 |       1 |        2 |  10
          8 |       4 |        2 |  10
         11 |       6 |        2 |  10
         15 |       7 |        2 |   5
          3 |       1 |        1 |  15
          4 |       2 |        1 |  20
          7 |       4 |        1 |  15
         10 |       6 |        1 |  15
         14 |       7 |        1 |   8
         16 |       8 |        1 |  20

-- 問題1-4
oracleoit=> select goods_id 商品番号, sum(qty) 受注総数
oracleoit-> from account
oracleoit-> group by goods_id
oracleoit-> having sum(qty) >= 30;
 商品番号 | 受注総数 
----------+----------
        1 |       93
        2 |       35

-- 第1部
-- 問題1
oracleoit=> select grade,losal,hisal, hisal - losal "hisal - losal"
oracleoit-> from salgrade;
 grade | losal | hisal | hisal - losal 
-------+-------+-------+---------------
     1 |   700 |  1200 |           500
     2 |  1201 |  1400 |           199
     3 |  1401 |  2000 |           599
     4 |  2001 |  3000 |           999
     5 |  3001 |  9999 |          6998

-- 問題2
oracleoit=> select count(*) 社員数, sum(sal) 給与総額
oracleoit-> from emp;
 社員数 | 給与総額 
--------+----------
     14 |    29025

-- 問題3
oracleoit=> select ename,job,sal from emp
oracleoit-> where sal >= 2900;
 ename |    job    | sal  
-------+-----------+------
 JONES | MANAGER   | 2975
 SCOTT | ANALYST   | 3000
 KING  | PRESIDENT | 5000
 FORD  | ANALYST   | 3000

-- 問題4
oracleoit=> select grade,losal - losal * 0.1 n_losal,hisal * 1.1 n_hisal
oracleoit-> from salgrade;
 grade | n_losal | n_hisal 
-------+---------+---------
     1 |   630.0 |  1320.0
     2 |  1080.9 |  1540.0
     3 |  1260.9 |  2200.0
     4 |  1800.9 |  3300.0
     5 |  2700.9 | 10998.9

-- 問題5
oracleoit=> select ename,sal from emp
oracleoit-> where sal between 1300 and 2000
oracleoit-> order by sal;
 ename  | sal  
--------+------
 MILLER | 1300
 TURNER | 1500
 ALLEN  | 1600

oracleoit=> select ename,sal from emp
oracleoit-> where sal >= 1300 and sal <= 2000
oracleoit-> order by sal;
 ename  | sal  
--------+------
 MILLER | 1300
 TURNER | 1500
 ALLEN  | 1600

-- 問題6
oracleoit=> select dname,loc from dept
oracleoit-> where loc not in ('NEW YORK','DALLAS');
   dname    |   loc   
------------+---------
 SALES      | CHICAGO
 OPERATIONS | BOSTON

-- 問題7
oracleoit=> select count(*) "count(*)", avg(sal) "avg(sal)"
oracleoit-> from emp;
 count(*) |     avg(sal)     
----------+------------------
       14 | 2073.21428571429

-- 問題8
oracleoit=> select job from emp
oracleoit-> where job not like 'S%';
    job    
-----------
 CLERK
 MANAGER
 MANAGER
 MANAGER
 ANALYST
 PRESIDENT
 CLERK
 CLERK
 ANALYST
 CLERK

-- 問題9
oracleoit=> select deptno,to_char(avg(sal), '999,999') 平均給与
oracleoit-> from emp
oracleoit-> group by deptno
oracleoit-> having count(deptno) >= 4;
 deptno | 平均給与 
--------+----------
     30 |    1,567
     20 |    2,175

-- 問題10
oracleoit=> select job,min(sal) "min(sal)",max(sal) "max(sal)"
oracleoit-> from emp
oracleoit-> group by job;
    job    | min(sal) | max(sal) 
-----------+----------+----------
 CLERK     |      800 |     1300
 SALESMAN  |     1250 |     1600
 MANAGER   |     2450 |     2975
 PRESIDENT |     5000 |     5000
 ANALYST   |     3000 |     3000