1 Oracle
2
2.1
1
2
1 MLDN 010-51283346
2 MLDN 010-51283346
COUNT() AVG() SUM() MAX() MIN()
GROUP BY
HAVING HAVING
WHERE
3
SELECT FROM WHERE
4
CREATE TABLE AS ( ) Oracle
INSERT INTO ,… VALUES ( ) ;
|- '
|- TO_DATE
yyyy-mm-dd
|- INSERT
UPDATE SET = ,…. WHERE
|-
DELETE FROM WHERE
5
Oracle session session
commit rollback
Oracle
2.2
7 1500
1500
SELECT job,MIN(sal)
FROM emp
GROUP BY job HAVING MIN(sal)>1500 ;
SELECT e.job,COUNT(e.empno)
FROM emp e
WHERE e.job IN(
SELECT job
FROM emp
GROUP BY job HAVING MIN(sal)>1500)
GROUP BY e.job ;
8 SALES
dept
SELECT deptno FROM dept WHERE dname='SALES' ;
3 MLDN 010-51283346
SELECT ename
FROM emp WHERE deptno=(
SELECT deptno FROM dept WHERE dname='SALES') ;
9
SELECT AVG(sal) FROM emp ;
SELECT * FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp) ;
4 MLDN 010-51283346
SELECT e.*,d.dname,d.loc FROM emp e,dept d
WHERE sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno ;
SELECT e.empno,e.ename,m.empno,m.ename,d.deptno,d.dname,d.loc
FROM emp e,dept d,emp m
WHERE e.sal>(SELECT AVG(sal) FROM emp)
AND e.deptno=d.deptno
AND e.mgr=m.empno(+) ;
SELECT e.empno,e.ename,s.grade,m.empno,m.ename,d.deptno,d.dname,d.loc
FROM emp e,dept d,emp m,salgrade s
WHERE e.sal>(SELECT AVG(sal) FROM emp)
AND e.deptno=d.deptno
AND e.mgr=m.empno(+)
AND e.sal BETWEEN s.losal AND s.hisal;
5 MLDN 010-51283346
- 1
- 2
- 3
前往页