GROUPBYとHAVINGを使ったデータ抽出
最近OracleDBを触る機会が増えたので備忘録程度に。
早速行ってみよう!
問題文:
empテーブルからデータを検索しなさい。同じ職種が2人以上存在するデータを検索するものとし、下記の実行結果を参考にすること。
<実行結果_empテーブル> | EMPNO | ENAME | JOB ---------- ----------------- ---------- | 001 | 鈴木 仁 | PL | 003 | 高橋 ひとみ | PL | 004 | 大前 貢 | SE | 005 | 真田 博之 | SE | 007 | 神城 陣 | PG | 008 | 結城 陣 | SE | 009 | 博多 弁 | PG
GROUP BYとHAVING使うのは見えてたけど、サブクエリを定義する場所が分からず苦戦した。
ということで定番のテーブル作成から。実行結果を見たい方は、4番へ。
1.テーブル作成
empテーブル作成:
CREATE TABLE emp ( empno varchar2(3) PRIMARY KEY, ename varchar2(20) NOT NULL, superior varchar2(20), hiredate date NOT NULL, sal number NOT NULL, job varchar2(30), deptno varchar2(2), FOREIGN KEY (deptno) REFERENCES dept (deptno) );
2.論理名付与
COMMENT ON COLUMN emp.empno IS '任意の論理名';
※empno=部署NO、empname=社員名、superior=上司、hiredate=入社日、sal=給与、job=職種、deptno=部署名
3.レコード挿入
INSERT INTO emp VALUES ('001', '鈴木 仁', NULL, '2000/01/01', 600000, 'PL', '01'); INSERT INTO emp VALUES ('002', '佐藤 幸助', '鈴木 仁', '2000/03/22', 350000, 'ブリッジSE', '01'); INSERT INTO emp VALUES ('003', '高橋 ひとみ', NULL, '2000/01/01', 500000, 'PL', '02'); INSERT INTO emp VALUES ('004', '大前 貢', '鈴木 仁', '2000/04/01', 320000, 'SE', '01'); INSERT INTO emp VALUES ('005', '真田 博之', '鈴木 仁', '2001/04/01', 300000, 'SE', '01'); INSERT INTO emp VALUES ('006', '大城 勉', '鈴木 仁', '2001/04/01', 300000, 'SE', '01'); INSERT INTO emp VALUES ('007', '神城 陣', '高橋 ひとみ', '2001/04/01', 280000, 'PG', '02'); INSERT INTO emp VALUES ('008', '結城 瞳', '高橋 ひとみ', '2002/04/01', 280000, 'PG', '02'); INSERT INTO emp VALUES ('009', '博多 弁', '高橋 ひとみ', '2003/04/01', 260000, 'PG', '02'); INSERT INTO emp VALUES ('010', '小山 浩', '高橋 ひとみ', '2006/04/01', 180000, 'デザイナー', '02');
4.クエリ定義
SELECT empno, ename, job FROM emp WHERE (job) IN ( SELECT job FROM emp GROUP BY job HAVING count(*) > 1 );