SQL: Menggunakan Subquery

SELECT ename, hiredate
FROM emp
WHERE LOWER(ename) != 'blake'
AND deptno = (SELECT deptno FROM emp WHERE LOWER(ename) = 'blake');
SELECT empno, ename
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp) ORDER BY sal DESC;
SELECT empno, ename
FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE ename LIKE '%T%');
SELECT ename, sal
FROM emp
WHERE empno IN (SELECT e.empno FROM emp e, emp m WHERE e.mgr=m.empno AND
LOWER(m.ename) LIKE 'king');
SELECT deptno, ename, job
FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE LOWER(dname) = 'sales');
SELECT empno, ename
FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE ename LIKE '%T%') AND sal >
(SELECT AVG(sal) FROM emp);
Posted in SQL

SQL: Menampilakan Data dari Banyak Tabel

SELECT emp.ename, dept.deptno, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;
SELECT DISTINCT emp.job, dept.loc
FROM emp, dept
WHERE emp.deptno = dept.deptno AND dept.deptno = 30;
SELECT emp.ename, dept.dname, dept.loc
FROM emp, dept
WHERE emp.deptno = dept.deptno AND emp.comm IS NOT NULL;
SELECT emp.ename, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno AND emp.ename LIKE '%A%';
SELECT emp.ename, emp.job, dept.deptno, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno AND LOWER(dept.loc) = 'dallas';
SELECT e.empno, e.ename AS pegawai, m.empno, m.ename AS manager
FROM emp e, emp m
WHERE e.mgr = m.empno;
Posted in SQL