1查询emp表中数据量 14条数据
Oracle
SQL> select count(*) from emp;
COUNT(*)
----------
14PPAS
scott=# select count(*) from emp;
count
-------
14
(1 row)2查询dept表中的数据库 4条数据
Oracle
SQL> select count(*) from dept;
COUNT(*)
----------
4PPAS
scott=# select count(*) from dept;
count
-------
4
(1 row)3查询所有的雇员和部门的全部详细信息
Oracle
SQL> select * from emp,dept;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-APR-81 2975 20 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 03-DEC-81 950 30 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-DEC-81 3000 20 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 20 RESEARCH DALLAS
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-DEC-80 800 20 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 30 SALES CHICAGO
7839 KING PRESIDENT 17-NOV-81 5000 10 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 30 SALES CHICAGO
7934 MILLER CLERK 7782 23-JAN-82 1300 10 30 SALES CHICAGO
7369 SMITH CLERK 7902 17-DEC-80 800 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 40 OPERATIONS BOSTON
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 40 OPERATIONS BOSTON
7566 JONES MANAGER 7839 02-APR-81 2975 20 40 OPERATIONS BOSTON
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 40 OPERATIONS BOSTON
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 40 OPERATIONS BOSTON
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 40 OPERATIONS BOSTON
7839 KING PRESIDENT 17-NOV-81 5000 10 40 OPERATIONS BOSTON
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 40 OPERATIONS BOSTON
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 40 OPERATIONS BOSTON
7900 JAMES CLERK 7698 03-DEC-81 950 30 40 OPERATIONS BOSTON
7902 FORD ANALYST 7566 03-DEC-81 3000 20 40 OPERATIONS BOSTON
7934 MILLER CLERK 7782 23-JAN-82 1300 10 40 OPERATIONS BOSTON
56 rows selected.PPAS
scott=# select * from emp,dept;
empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc
-------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 10 | ACCOUNTING | NEW YORK
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 20 | RESEARCH | DALLAS
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 30 | SALES | CHICAGO
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 40 | OPERATIONS | BOSTON
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 10 | ACCOUNTING | NEW YORK
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 20 | RESEARCH | DALLAS
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 40 | OPERATIONS | BOSTON
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 10 | ACCOUNTING | NEW YORK
30 | 20 | RESEARCH | DALLAS| 22-FEB-81 00:00:00 | 1250.00 | 500.00 | --More--
30 | 30 | SALES | CHICAGO 22-FEB-81 00:00:00 | 1250.00 | 500.00 | --More--
30 | 40 | OPERATIONS | BOSTON| 22-FEB-81 00:00:00 | 1250.00 | 500.00 | --More--
20 | 10 | ACCOUNTING | NEW YORK02-APR-81 00:00:00 | 2975.00 | | --More--
20 | 20 | RESEARCH | DALLAS| 02-APR-81 00:00:00 | 2975.00 | | --More--
20 | 30 | SALES | CHICAGO 02-APR-81 00:00:00 | 2975.00 | | --More--
20 | 40 | OPERATIONS | BOSTON| 02-APR-81 00:00:00 | 2975.00 | | --More--
30 | 10 | ACCOUNTING | NEW YORK28-SEP-81 00:00:00 | 1250.00 | 1400.00 | --More--
30 | 20 | RESEARCH | DALLAS| 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | --More--
30 | 30 | SALES | CHICAGO 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | --More--
30 | 40 | OPERATIONS | BOSTON| 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | --More--
30 | 10 | ACCOUNTING | NEW YORK01-MAY-81 00:00:00 | 2850.00 | | --More--
30 | 20 | RESEARCH | DALLAS| 01-MAY-81 00:00:00 | 2850.00 | | --More--
30 | 30 | SALES | CHICAGO 01-MAY-81 00:00:00 | 2850.00 | | --More--
30 | 40 | OPERATIONS | BOSTON| 01-MAY-81 00:00:00 | 2850.00 | | --More--
10 | 10 | ACCOUNTING | NEW YORK09-JUN-81 00:00:00 | 2450.00 | | --More--
10 | 20 | RESEARCH | DALLAS| 09-JUN-81 00:00:00 | 2450.00 | | --More--
10 | 30 | SALES | CHICAGO 09-JUN-81 00:00:00 | 2450.00 | | --More--
10 | 40 | OPERATIONS | BOSTON| 09-JUN-81 00:00:00 | 2450.00 | | --More--
20 | 10 | ACCOUNTING | NEW YORK19-APR-87 00:00:00 | 3000.00 | | --More--
20 | 20 | RESEARCH | DALLAS| 19-APR-87 00:00:00 | 3000.00 | | --More--
20 | 30 | SALES | CHICAGO 19-APR-87 00:00:00 | 3000.00 | | --More--
20 | 40 | OPERATIONS | BOSTON| 19-APR-87 00:00:00 | 3000.00 | | --More--
10 | 10 | ACCOUNTING | NEW YORK17-NOV-81 00:00:00 | 5000.00 | | --More--
10 | 20 | RESEARCH | DALLAS| 17-NOV-81 00:00:00 | 5000.00 | | --More--
10 | 30 | SALES | CHICAGO 17-NOV-81 00:00:00 | 5000.00 | | --More--
10 | 40 | OPERATIONS | BOSTON| 17-NOV-81 00:00:00 | 5000.00 | | --More--
30 | 10 | ACCOUNTING | NEW YORK08-SEP-81 00:00:00 | 1500.00 | 0.00 | --More--
30 | 20 | RESEARCH | DALLAS| 08-SEP-81 00:00:00 | 1500.00 | 0.00 | --More--
30 | 30 | SALES | CHICAGO 08-SEP-81 00:00:00 | 1500.00 | 0.00 | --More--
30 | 40 | OPERATIONS | BOSTON| 08-SEP-81 00:00:00 | 1500.00 | 0.00 | --More--
20 | 10 | ACCOUNTING | NEW YORK23-MAY-87 00:00:00 | 1100.00 | | --More--
20 | 20 | RESEARCH | DALLAS| 23-MAY-87 00:00:00 | 1100.00 | | --More--
20 | 30 | SALES | CHICAGO 23-MAY-87 00:00:00 | 1100.00 | | --More--
20 | 40 | OPERATIONS | BOSTON| 23-MAY-87 00:00:00 | 1100.00 | | --More--
30 | 10 | ACCOUNTING | NEW YORK03-DEC-81 00:00:00 | 950.00 | | --More--
30 | 20 | RESEARCH | DALLAS| 03-DEC-81 00:00:00 | 950.00 | | --More--
30 | 30 | SALES | CHICAGO 03-DEC-81 00:00:00 | 950.00 | | --More--
30 | 40 | OPERATIONS | BOSTON| 03-DEC-81 00:00:00 | 950.00 | | --More--
20 | 10 | ACCOUNTING | NEW YORK03-DEC-81 00:00:00 | 3000.00 | | --More--
20 | 20 | RESEARCH | DALLAS| 03-DEC-81 00:00:00 | 3000.00 | | --More--
20 | 30 | SALES | CHICAGO 03-DEC-81 00:00:00 | 3000.00 | | --More--
20 | 40 | OPERATIONS | BOSTON| 03-DEC-81 00:00:00 | 3000.00 | | --More--
10 | 10 | ACCOUNTING | NEW YORK23-JAN-82 00:00:00 | 1300.00 | | --More--
10 | 20 | RESEARCH | DALLAS| 23-JAN-82 00:00:00 | 1300.00 | | --More--
10 | 30 | SALES | CHICAGO 23-JAN-82 00:00:00 | 1300.00 | | --More--
10 | 40 | OPERATIONS | BOSTON| 23-JAN-82 00:00:00 | 1300.00 | | --More--
(56 rows)4消除查询结果中的笛卡尔积
Oracle
SQL> select *
2 from emp,dept
3 where emp.deptno=dept.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
14 rows selected.PPAS
scott=# select *
scott-# from emp,dept
scott-# where emp.deptno=dept.deptno;
empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc
-------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 20 | RESEARCH | DALLAS
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 | 20 | RESEARCH | DALLAS
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 | 30 | SALES | CHICAGO
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 | 10 | ACCOUNTING | NEW YORK
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 | 10 | ACCOUNTING | NEW YORK
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 | 20 | RESEARCH | DALLAS
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 | 30 | SALES | CHICAGO
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 | 10 | ACCOUNTING | NEW YORK
(14 rows)5查询每个雇员的编号、姓名、职位、基本工资、部门名称、部门位置信息
Oracle
SQL> select emp.empno,emp.ename,emp.job,emp.sal,dept.dname,dept.loc
2 from emp,dept
3 where emp.deptno=dept.deptno;
EMPNO ENAME JOB SAL DNAME LOC
---------- ---------- ---------- ---------- --------------- ----------
7782 CLARK MANAGER 2450 ACCOUNTING NEW YORK
7839 KING PRESIDENT 5000 ACCOUNTING NEW YORK
7934 MILLER CLERK 1300 ACCOUNTING NEW YORK
7566 JONES MANAGER 2975 RESEARCH DALLAS
7902 FORD ANALYST 3000 RESEARCH DALLAS
7876 ADAMS CLERK 1100 RESEARCH DALLAS
7369 SMITH CLERK 800 RESEARCH DALLAS
7788 SCOTT ANALYST 3000 RESEARCH DALLAS
7521 WARD SALESMAN 1250 SALES CHICAGO
7844 TURNER SALESMAN 1500 SALES CHICAGO
7499 ALLEN SALESMAN 1600 SALES CHICAGO
7900 JAMES CLERK 950 SALES CHICAGO
7698 BLAKE MANAGER 2850 SALES CHICAGO
7654 MARTIN SALESMAN 1250 SALES CHICAGO
14 rows selected.PPAS
scott=# select emp.empno,emp.ename,emp.job,emp.sal,dept.dname,dept.loc
scott-# from emp,dept
scott-# where emp.deptno=dept.deptno;
empno | ename | job | sal | dname | loc
-------+--------+-----------+---------+------------+----------
7369 | SMITH | CLERK | 800.00 | RESEARCH | DALLAS
7499 | ALLEN | SALESMAN | 1600.00 | SALES | CHICAGO
7521 | WARD | SALESMAN | 1250.00 | SALES | CHICAGO
7566 | JONES | MANAGER | 2975.00 | RESEARCH | DALLAS
7654 | MARTIN | SALESMAN | 1250.00 | SALES | CHICAGO
7698 | BLAKE | MANAGER | 2850.00 | SALES | CHICAGO
7782 | CLARK | MANAGER | 2450.00 | ACCOUNTING | NEW YORK
7788 | SCOTT | ANALYST | 3000.00 | RESEARCH | DALLAS
7839 | KING | PRESIDENT | 5000.00 | ACCOUNTING | NEW YORK
7844 | TURNER | SALESMAN | 1500.00 | SALES | CHICAGO
7876 | ADAMS | CLERK | 1100.00 | RESEARCH | DALLAS
7900 | JAMES | CLERK | 950.00 | SALES | CHICAGO
7902 | FORD | ANALYST | 3000.00 | RESEARCH | DALLAS
7934 | MILLER | CLERK | 1300.00 | ACCOUNTING | NEW YORK
(14 rows)6通过别名查询雇员的编号、姓名、职位、基本工资、部门名称、部门位置
Oracle
SQL> select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
2 from emp e,dept d
3 where e.deptno=d.deptno;
EMPNO ENAME JOB SAL DNAME LOC
---------- ---------- ---------- ---------- --------------- ----------
7782 CLARK MANAGER 2450 ACCOUNTING NEW YORK
7839 KING PRESIDENT 5000 ACCOUNTING NEW YORK
7934 MILLER CLERK 1300 ACCOUNTING NEW YORK
7566 JONES MANAGER 2975 RESEARCH DALLAS
7902 FORD ANALYST 3000 RESEARCH DALLAS
7876 ADAMS CLERK 1100 RESEARCH DALLAS
7369 SMITH CLERK 800 RESEARCH DALLAS
7788 SCOTT ANALYST 3000 RESEARCH DALLAS
7521 WARD SALESMAN 1250 SALES CHICAGO
7844 TURNER SALESMAN 1500 SALES CHICAGO
7499 ALLEN SALESMAN 1600 SALES CHICAGO
7900 JAMES CLERK 950 SALES CHICAGO
7698 BLAKE MANAGER 2850 SALES CHICAGO
7654 MARTIN SALESMAN 1250 SALES CHICAGO
14 rows selected.PPAS
scott=# select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
scott-# from emp e,dept d
scott-# where e.deptno=d.deptno;
empno | ename | job | sal | dname | loc
-------+--------+-----------+---------+------------+----------
7369 | SMITH | CLERK | 800.00 | RESEARCH | DALLAS
7499 | ALLEN | SALESMAN | 1600.00 | SALES | CHICAGO
7521 | WARD | SALESMAN | 1250.00 | SALES | CHICAGO
7566 | JONES | MANAGER | 2975.00 | RESEARCH | DALLAS
7654 | MARTIN | SALESMAN | 1250.00 | SALES | CHICAGO
7698 | BLAKE | MANAGER | 2850.00 | SALES | CHICAGO
7782 | CLARK | MANAGER | 2450.00 | ACCOUNTING | NEW YORK
7788 | SCOTT | ANALYST | 3000.00 | RESEARCH | DALLAS
7839 | KING | PRESIDENT | 5000.00 | ACCOUNTING | NEW YORK
7844 | TURNER | SALESMAN | 1500.00 | SALES | CHICAGO
7876 | ADAMS | CLERK | 1100.00 | RESEARCH | DALLAS
7900 | JAMES | CLERK | 950.00 | SALES | CHICAGO
7902 | FORD | ANALYST | 3000.00 | RESEARCH | DALLAS
7934 | MILLER | CLERK | 1300.00 | ACCOUNTING | NEW YORK
(14 rows)7查询出每个雇员的编号、姓名、雇用日期、基本工资、工资等级
Oracle
SQL> select e.empno,e.ename,e.hiredate,e.sal,s.grade
2 from emp e,salgrade s
3 where e.sal between s.losal and s.hisal;
EMPNO ENAME HIREDATE SAL GRADE
---------- ---------- ------------ ---------- ----------
7369 SMITH 17-DEC-80 800 1
7900 JAMES 03-DEC-81 950 1
7876 ADAMS 23-MAY-87 1100 1
7521 WARD 22-FEB-81 1250 2
7654 MARTIN 28-SEP-81 1250 2
7934 MILLER 23-JAN-82 1300 2
7844 TURNER 08-SEP-81 1500 3
7499 ALLEN 20-FEB-81 1600 3
7782 CLARK 09-JUN-81 2450 4
7698 BLAKE 01-MAY-81 2850 4
7566 JONES 02-APR-81 2975 4
7788 SCOTT 19-APR-87 3000 4
7902 FORD 03-DEC-81 3000 4
7839 KING 17-NOV-81 5000 5
14 rows selected.PPAS
scott=# select e.empno,e.ename,e.hiredate,e.sal,s.grade
scott-# from emp e,salgrade s
scott-# where e.sal between s.losal and s.hisal;
empno | ename | hiredate | sal | grade
-------+--------+--------------------+---------+-------
7369 | SMITH | 17-DEC-80 00:00:00 | 800.00 | 1
7876 | ADAMS | 23-MAY-87 00:00:00 | 1100.00 | 1
7900 | JAMES | 03-DEC-81 00:00:00 | 950.00 | 1
7521 | WARD | 22-FEB-81 00:00:00 | 1250.00 | 2
7654 | MARTIN | 28-SEP-81 00:00:00 | 1250.00 | 2
7934 | MILLER | 23-JAN-82 00:00:00 | 1300.00 | 2
7499 | ALLEN | 20-FEB-81 00:00:00 | 1600.00 | 3
7844 | TURNER | 08-SEP-81 00:00:00 | 1500.00 | 3
7566 | JONES | 02-APR-81 00:00:00 | 2975.00 | 4
7698 | BLAKE | 01-MAY-81 00:00:00 | 2850.00 | 4
7782 | CLARK | 09-JUN-81 00:00:00 | 2450.00 | 4
7788 | SCOTT | 19-APR-87 00:00:00 | 3000.00 | 4
7902 | FORD | 03-DEC-81 00:00:00 | 3000.00 | 4
7839 | KING | 17-NOV-81 00:00:00 | 5000.00 | 5
(14 rows)8使用DECODE()函数完成查询出每个雇员的编号、姓名、雇用日期、基本工资、工资等级
Oracle
SQL> select e.empno,e.ename,e.hiredate,e.sal,
2 DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade
3 from emp e,salgrade s
4 where e.sal between s.losal and s.hisal;
EMPNO ENAME HIREDATE SAL GRADE
---------- ---------- ------------ ---------- --------------------
7369 SMITH 17-DEC-80 800 E等工资
7900 JAMES 03-DEC-81 950 E等工资
7876 ADAMS 23-MAY-87 1100 E等工资
7521 WARD 22-FEB-81 1250 D等工资
7654 MARTIN 28-SEP-81 1250 D等工资
7934 MILLER 23-JAN-82 1300 D等工资
7844 TURNER 08-SEP-81 1500 C等工资
7499 ALLEN 20-FEB-81 1600 C等工资
7782 CLARK 09-JUN-81 2450 B等工资
7698 BLAKE 01-MAY-81 2850 B等工资
7566 JONES 02-APR-81 2975 B等工资
7788 SCOTT 19-APR-87 3000 B等工资
7902 FORD 03-DEC-81 3000 B等工资
7839 KING 17-NOV-81 5000 A等工资
14 rows selected.PPAS
scott=# select e.empno,e.ename,e.hiredate,e.sal,
scott-# DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade
scott-# from emp e,salgrade s
scott-# where e.sal between s.losal and s.hisal;
empno | ename | hiredate | sal | grade
-------+--------+--------------------+---------+---------
7369 | SMITH | 17-DEC-80 00:00:00 | 800.00 | E等工资
7876 | ADAMS | 23-MAY-87 00:00:00 | 1100.00 | E等工资
7900 | JAMES | 03-DEC-81 00:00:00 | 950.00 | E等工资
7521 | WARD | 22-FEB-81 00:00:00 | 1250.00 | D等工资
7654 | MARTIN | 28-SEP-81 00:00:00 | 1250.00 | D等工资
7934 | MILLER | 23-JAN-82 00:00:00 | 1300.00 | D等工资
7499 | ALLEN | 20-FEB-81 00:00:00 | 1600.00 | C等工资
7844 | TURNER | 08-SEP-81 00:00:00 | 1500.00 | C等工资
7566 | JONES | 02-APR-81 00:00:00 | 2975.00 | B等工资
7698 | BLAKE | 01-MAY-81 00:00:00 | 2850.00 | B等工资
7782 | CLARK | 09-JUN-81 00:00:00 | 2450.00 | B等工资
7788 | SCOTT | 19-APR-87 00:00:00 | 3000.00 | B等工资
7902 | FORD | 03-DEC-81 00:00:00 | 3000.00 | B等工资
7839 | KING | 17-NOV-81 00:00:00 | 5000.00 | A等工资
(14 rows)9查询每个雇员的姓名、职位、基本工资、部门名称、工资所在公司的工资等级
Oracle
SQL> select e.ename,e.job,e.sal,d.dname,s.grade,
2 DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade
3 from emp e,dept d,salgrade s
4 where e.deptno=d.deptno and e.sal between s.losal and s.hisal;
ENAME JOB SAL DNAME GRADE GRADE
---------- ---------- ---------- --------------- ---------- --------------------
KING PRESIDENT 5000 ACCOUNTING 5 A等工资
FORD ANALYST 3000 RESEARCH 4 B等工资
SCOTT ANALYST 3000 RESEARCH 4 B等工资
JONES MANAGER 2975 RESEARCH 4 B等工资
BLAKE MANAGER 2850 SALES 4 B等工资
CLARK MANAGER 2450 ACCOUNTING 4 B等工资
ALLEN SALESMAN 1600 SALES 3 C等工资
TURNER SALESMAN 1500 SALES 3 C等工资
MILLER CLERK 1300 ACCOUNTING 2 D等工资
WARD SALESMAN 1250 SALES 2 D等工资
MARTIN SALESMAN 1250 SALES 2 D等工资
ADAMS CLERK 1100 RESEARCH 1 E等工资
JAMES CLERK 950 SALES 1 E等工资
SMITH CLERK 800 RESEARCH 1 E等工资
14 rows selected.PPAS
scott=# select e.ename,e.job,e.sal,d.dname,s.grade,
scott-# DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade
scott-# from emp e,dept d,salgrade s
scott-# where e.deptno=d.deptno and e.sal between s.losal and s.hisal;
ename | job | sal | dname | grade | grade
--------+-----------+---------+------------+-------+---------
SMITH | CLERK | 800.00 | RESEARCH | 1 | E等工资
ADAMS | CLERK | 1100.00 | RESEARCH | 1 | E等工资
JAMES | CLERK | 950.00 | SALES | 1 | E等工资
WARD | SALESMAN | 1250.00 | SALES | 2 | D等工资
MARTIN | SALESMAN | 1250.00 | SALES | 2 | D等工资
MILLER | CLERK | 1300.00 | ACCOUNTING | 2 | D等工资
ALLEN | SALESMAN | 1600.00 | SALES | 3 | C等工资
TURNER | SALESMAN | 1500.00 | SALES | 3 | C等工资
JONES | MANAGER | 2975.00 | RESEARCH | 4 | B等工资
BLAKE | MANAGER | 2850.00 | SALES | 4 | B等工资
CLARK | MANAGER | 2450.00 | ACCOUNTING | 4 | B等工资
SCOTT | ANALYST | 3000.00 | RESEARCH | 4 | B等工资
FORD | ANALYST | 3000.00 | RESEARCH | 4 | B等工资
KING | PRESIDENT | 5000.00 | ACCOUNTING | 5 | A等工资
(14 rows)表的连接操作
10向emp表中 添加一条新的记录
Oracle
SQL> insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
2 values (8888,'SongYuejie','CLERK','7369',SYSDATE,800,100,null);
1 row created.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
8888 SongYuejie CLERK 7369 22-JUN-16 800 100
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
15 rows selected.PPAS
scott=# insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
scott-# values (8888,'SongYuejie','CLERK','7369',SYSDATE,800,100,null);
INSERT 0 1
scott=# select * from emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+------------+-----------+------+--------------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10
8888 | SongYuejie | CLERK | 7369 | 22-JUN-16 11:09:17 | 800.00 | 100.00 |
(15 rows)11将emp和dept表联合查询，使用内连接（等值连接）
Oracle
SQL> select *
2 from emp e,dept d
3 where e.deptno=d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
14 rows selected.PPAS
scott=# select *
scott-# from emp e,dept d
scott-# where e.deptno=d.deptno;
empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc
-------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 20 | RESEARCH | DALLAS
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 | 20 | RESEARCH | DALLAS
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 | 30 | SALES | CHICAGO
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 | 10 | ACCOUNTING | NEW YORK
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 | 10 | ACCOUNTING | NEW YORK
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 | 20 | RESEARCH | DALLAS
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 | 30 | SALES | CHICAGO
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 | 10 | ACCOUNTING | NEW YORK
(14 rows)12使用左外连接，显示雇员8888的信息
Oracle
SQL> select *
2 from emp e,dept d
3 where e.deptno=d.deptno(+);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
8888 SongYuejie CLERK 7369 22-JUN-16 800 100
15 rows selected.PPAS
scott=# select *
scott-# from emp e,dept d
scott-# where e.deptno=d.deptno(+);
empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc
-------+------------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 20 | RESEARCH | DALLAS
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 | 20 | RESEARCH | DALLAS
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 | 30 | SALES | CHICAGO
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 | 10 | ACCOUNTING | NEW YORK
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 | 10 | ACCOUNTING | NEW YORK
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 | 20 | RESEARCH | DALLAS
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 | 30 | SALES | CHICAGO
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 | 10 | ACCOUNTING | NEW YORK
8888 | SongYuejie | CLERK | 7369 | 22-JUN-16 11:09:17 | 800.00 | 100.00 | | | |
(15 rows)本连载博客主要探讨Oracle与PPAS(PostgreSQL)数据库的差异，以帮助更多读者了解如何实现数据库迁移！”