投稿2018/02/13 21:43
従業員、従業員の部署、給料のグレードを示す三つのテーブルが与えられた中で
部署名とその部署の給料グレード、部署にいる従業員の最低給料、平均のコミッションを紐ずけた形で
表示させるSQL文を書くことを求められています。
SQL> select * 2 from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 training San Francisco
SQL> select * 2 from salgrade; GRADE LOSAL HISAL ---------- ---------- ---------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999
SQL> select * 2 from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7839 KING PRESIDENT 17-NOV-81 5000 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-APR-81 3000 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 1456 John Smith Analyst 7566 01-JAN-02 3000 20 15 rows selected.
正解のSQL文とテーブルは下記のようになるようなのですが
わからない点が４点ほどあります。
ご存知でしたらお伺いできますと幸いです。
① どのように給料グレード （grade）を部署のテーブルに結合して
表示しているのか。
where句では従業員テーブルと部署テーブルのみが内部結合で
紐づけられているように見えます。
WHERE d.deptno=e.deptno
②そもそもこの方法は結合なのでしょうか。
③having句がこの時、どのような操作の為にあるのかよくわかりません。。
④group by句に以下の５つのカラムを含めないといけないことは
文法上の制約なのでわかるのですが
これがどのような動作を与えるのか
イメージがつきません。
GROUP BY d.deptno,d.dname,s.grade,s.losal,s.hisal
SQL> SELECT d.dname,s.grade,MIN(e.sal) "Min Sal",AVG(NVL(e.comm,0)) "AVG Comm" FROM emp e,dept d,salgrade s WHERE d.deptno=e.deptno GROUP BY d.deptno,d.dname,s.grade,s.losal,s.hisal HAVING MIN(e.sal) BETWEEN s.losal AND s.hisal; DNAME GRADE Min Sal AVG Comm -------------- ---------- ---------- ---------- SALES 1 950 366.666667 ACCOUNTING 2 1300 0 RESEARCH 1 800 0
テーブルの表がずれてしまっていて申し訳ございません。
回答2件
0
① どのように給料グレード （grade）を部署のテーブルに結合して表示しているのか。
②そもそもこの方法は結合なのでしょうか。
from句で,で区切られているものは直積(クロス結合)と言い総当たりで結合されます。
DBMSによってはcross join として記述できるものもあります。
③having句がこの時、どのような操作の為にあるのかよくわかりません。。
where → group by → having → select という実行順序で処理が行われると考えてください。
havingはgroup by 後の条件節です。
min()を条件にしているので、group by の後の条件である必要があり、havingで指定しています。
④group by句に以下の５つのカラムを含めないといけないことは文法上の制約なのでわかるのですが
これがどのような動作を与えるのかイメージがつきません。
文法上の制約ではありません。明確な集計の単位です。
salgradeの項目は集計結果には関係していませんが、havingの条件に含まれていますから。
投稿2018/02/14 04:32
総合スコア25173
0
① どのように給料グレード （grade）を部署のテーブルに結合して
表示しているのか。
グレード表は結合句がないので部署表と従業員表にすべての組み合わせで結合されています。
部署表行数5*グレード表行数5=25行
②そもそもこの方法は結合なのでしょうか。
はい。たとえばカスタマイズTシャツのオーダーで「色」「スタイル(Tシャツ？タンクトップ？)」「サイズ」が選べるとして「select * from col, style, size」とやればすべての組み合わせを抽出できます。
③having句がこの時、どのような操作の為
前述①の際に給料のレンジを無視して全組み合わせ結合したのでここで絞り込みをしています。グレード表の最低から最高の中に含まれる従業員表の給料の行を抽出しています。
④group by句に以下の５つのカラムを含めないといけない
そんなことないです。SQLはd.dname,s.gradeのみで通ると思います。
ここからはテーブル定義見ていないのでd.deptnoが部署の主キーと仮定します。
部署表と従業員表はd.deptnoで結合されています。それに対してグレード表を給料レンジにかかわらず従業員人数分も含めて全組み合わせ結合しています。
例：ACCOUNTING(10)は従業員表で3人確認できました。
ACCOUNTING(10)-KING
-CLARK
-MILLER
Group byの前ではACCOUNTINGのlosalとhisalが3人*5グレードで15行発生しています。
グレード表のs.losal,s.hisalをGroup byに含めることで一意の組み合わせになります。
それでもって前述のhavingで正しいレンジの行のみ残す、という手法っぽいです。
いかがでしょうか。
投稿2018/02/14 00:17
総合スコア16
あなたの回答
tips
プレビュー
Q&A
