Oracle PL/SQL Tutorial/Table Joins/Table Alias — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 10:03, 26 мая 2010
Содержание
- 1 Calculation between column from different tables
- 2 Join tables and order by columns from different tables
- 3 Join two tables and combine three tables
- 4 List table from two tables and specifying its column owner
- 5 List table from two tables without indicating column owner
- 6 Reference column without table name during table join
- 7 Supplying Table Aliases
- 8 Use * to reference all columns from a table
Calculation between column from different tables
SQL>
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL> create table salgrades
2 ( grade NUMBER(2)
3 , lowerlimit NUMBER(6,2)
4 , upperlimit NUMBER(6,2)
5 , bonus NUMBER(6,2)
6 ) ;
Table created.
SQL>
SQL>
SQL> insert into salgrades values (1, 700,1200, 0);
1 row created.
SQL> insert into salgrades values (2, 1201,1400, 50);
1 row created.
SQL> insert into salgrades values (3, 1401,2000, 100);
1 row created.
SQL> insert into salgrades values (4, 2001,3000, 200);
1 row created.
SQL> insert into salgrades values (5, 3001,9999, 500);
1 row created.
SQL>
SQL>
SQL>
SQL> select e.ename employee
2 , 12*e.msal+s.bonus total_salary
3 from employees e
4 , salgrades s
5 where e.msal between s.lowerlimit
6 and s.upperlimit;
EMPLOYEE TOTAL_SALARY
-------- ------------
Jason 9600
Jerry 19300
Jord 20500
Mary 21700
Joe 22900
Black 24100
Red 25400
White 26600
Yellow 27800
Pink 29000
10 rows selected.
SQL>
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL> drop table salgrades;
Table dropped.
SQL>
Join tables and order by columns from different tables
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL> create table departments
2 ( deptno NUMBER(2)
3 , dname VARCHAR2(10)
4 , location VARCHAR2(20)
5 , mgr NUMBER(4)
6 ) ;
Table created.
SQL>
SQL> insert into departments values (10,"ACCOUNTING","NEW YORK" , 2);
1 row created.
SQL> insert into departments values (20,"TRAINING", "VANCOUVER", 3);
1 row created.
SQL> insert into departments values (30,"SALES", "CHICAGO", 4);
1 row created.
SQL> insert into departments values (40,"HR", "BOSTON", 5);
1 row created.
SQL>
SQL>
SQL> select d.deptno, d.location
2 , e.ename, e.init
3 from employees e, departments d
4 where e.deptno = d.deptno
5 order by d.deptno, e.ename;
DEPTNO LOCATION ENAME INIT
---------- -------------------- -------- -----
10 NEW YORK Jason N
10 NEW YORK Jerry J
20 VANCOUVER Jord T
20 VANCOUVER Mary J
20 VANCOUVER Yellow C
30 CHICAGO Black R
30 CHICAGO Joe P
30 CHICAGO Pink J
40 BOSTON Red A
40 BOSTON White S
10 rows selected.
SQL>
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL> drop table departments;
Table dropped.
SQL>
Join two tables and combine three tables
SQL>
SQL>
SQL> create table salgrades
2 ( grade NUMBER(2)
3 , lowerlimit NUMBER(6,2)
4 , upperlimit NUMBER(6,2)
5 , bonus NUMBER(6,2)
6 ) ;
Table created.
SQL>
SQL>
SQL> insert into salgrades values (1, 700,1200, 0);
1 row created.
SQL> insert into salgrades values (2, 1201,1400, 50);
1 row created.
SQL> insert into salgrades values (3, 1401,2000, 100);
1 row created.
SQL> insert into salgrades values (4, 2001,3000, 200);
1 row created.
SQL> insert into salgrades values (5, 3001,9999, 500);
1 row created.
SQL>
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL> create table departments
2 ( deptno NUMBER(2)
3 , dname VARCHAR2(10)
4 , location VARCHAR2(20)
5 , mgr NUMBER(4)
6 ) ;
Table created.
SQL>
SQL> insert into departments values (10,"ACCOUNTING","NEW YORK" , 2);
1 row created.
SQL> insert into departments values (20,"TRAINING", "VANCOUVER", 3);
1 row created.
SQL> insert into departments values (30,"SALES", "CHICAGO", 4);
1 row created.
SQL> insert into departments values (40,"HR", "BOSTON", 5);
1 row created.
SQL>
SQL>
SQL>
SQL> select e.ename employee
2 , 12*e.msal+s.bonus total_salary
3 , d.dname department
4 from employees e
5 , salgrades s
6 , departments d
7 where e.msal between s.lowerlimit
8 and s.upperlimit
9 and e.deptno = d.deptno;
EMPLOYEE TOTAL_SALARY DEPARTMENT
-------- ------------ ----------
Jason 9600 ACCOUNTING
Jerry 19300 ACCOUNTING
Jord 20500 TRAINING
Mary 21700 TRAINING
Joe 22900 SALES
Black 24100 SALES
Red 25400 HR
White 26600 HR
Yellow 27800 TRAINING
Pink 29000 SALES
10 rows selected.
SQL>
SQL> drop table salgrades;
Table dropped.
SQL>
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL> drop table departments;
Table dropped.
SQL>
List table from two tables and specifying its column owner
SQL>
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL> create table departments
2 ( deptno NUMBER(2)
3 , dname VARCHAR2(10)
4 , location VARCHAR2(20)
5 , mgr NUMBER(4)
6 ) ;
Table created.
SQL>
SQL> insert into departments values (10,"ACCOUNTING","NEW YORK" , 2);
1 row created.
SQL> insert into departments values (20,"TRAINING", "VANCOUVER", 3);
1 row created.
SQL> insert into departments values (30,"SALES", "CHICAGO", 4);
1 row created.
SQL> insert into departments values (40,"HR", "BOSTON", 5);
1 row created.
SQL>
SQL>
SQL>
SQL> select d.deptno, d.location, e.ename, e.init
2 from employees e, departments d;
DEPTNO LOCATION ENAME INIT
---------- -------------------- -------- -----
10 NEW YORK Jason N
10 NEW YORK Jerry J
10 NEW YORK Jord T
10 NEW YORK Mary J
10 NEW YORK Joe P
10 NEW YORK Black R
10 NEW YORK Red A
10 NEW YORK White S
10 NEW YORK Yellow C
10 NEW YORK Pink J
20 VANCOUVER Jason N
20 VANCOUVER Jerry J
20 VANCOUVER Jord T
20 VANCOUVER Mary J
20 VANCOUVER Joe P
20 VANCOUVER Black R
20 VANCOUVER Red A
20 VANCOUVER White S
20 VANCOUVER Yellow C
20 VANCOUVER Pink J
30 CHICAGO Jason N
30 CHICAGO Jerry J
30 CHICAGO Jord T
30 CHICAGO Mary J
30 CHICAGO Joe P
30 CHICAGO Black R
30 CHICAGO Red A
30 CHICAGO White S
30 CHICAGO Yellow C
30 CHICAGO Pink J
40 BOSTON Jason N
40 BOSTON Jerry J
40 BOSTON Jord T
40 BOSTON Mary J
40 BOSTON Joe P
40 BOSTON Black R
40 BOSTON Red A
40 BOSTON White S
40 BOSTON Yellow C
40 BOSTON Pink J
40 rows selected.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL> drop table departments;
Table dropped.
List table from two tables without indicating column owner
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL> create table departments
2 ( deptno NUMBER(2)
3 , dname VARCHAR2(10)
4 , location VARCHAR2(20)
5 , mgr NUMBER(4)
6 ) ;
Table created.
SQL>
SQL> insert into departments values (10,"ACCOUNTING","NEW YORK" , 2);
1 row created.
SQL> insert into departments values (20,"TRAINING", "VANCOUVER", 3);
1 row created.
SQL> insert into departments values (30,"SALES", "CHICAGO", 4);
1 row created.
SQL> insert into departments values (40,"HR", "BOSTON", 5);
1 row created.
SQL>
SQL>
SQL>
SQL> select deptno, location, ename, init
2 from employees, departments;
select deptno, location, ename, init
*
ERROR at line 1:
ORA-00918: column ambiguously defined
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL> drop table departments;
Table dropped.
SQL>
Reference column without table name during table join
SQL> CREATE TABLE emp
2 (
3 cid NUMBER,
4 city VARCHAR2(30),
5 customer_state VARCHAR2(40),
6 country_id CHAR(2)
7 );
Table created.
SQL>
SQL> CREATE TABLE sales(
2 product_id NUMBER(6),
3 cid NUMBER,
4 sold NUMBER(3),
5 amount NUMBER(10,2),
6 cost NUMBER(10,2)
7 );
Table created.
SQL>
SQL> select product_id, sold, city, customer_state
2 from sales, emp
3 where sales.cid = emp.cid
4 and product_id = 117;
no rows selected
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table sales;
Table dropped.
SQL>
Supplying Table Aliases
The alias is specified in the FROM clause after each table name.
Table aliases make your queries more readable.
SQL> -- create demo table
SQL> create table Employee(
2 EMPNO NUMBER(3),
3 ENAME VARCHAR2(15 BYTE),
4 HIREDATE DATE,
5 ORIG_SALARY NUMBER(6),
6 CURR_SALARY NUMBER(6),
7 REGION VARCHAR2(1 BYTE)
8 )
9 /
Table created.
SQL>
SQL> create table job (
2 EMPNO NUMBER(3),
3 jobtitle VARCHAR2(20 BYTE)
4 )
5 /
Table created.
SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,"Tester");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (2,"Accountant");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (3,"Developer");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (4,"COder");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (5,"Director");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (6,"Mediator");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (7,"Proffessor");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (8,"Programmer");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (9,"Developer");
1 row created.
SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (1, "Jason", to_date("19960725","YYYYMMDD"), 1234, 8767, "E")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (2, "John", to_date("19970715","YYYYMMDD"), 2341, 3456, "W")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (3, "Joe", to_date("19860125","YYYYMMDD"), 4321, 5654, "E")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (4, "Tom", to_date("20060913","YYYYMMDD"), 2413, 6787, "W")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (5, "Jane", to_date("20050417","YYYYMMDD"), 7654, 4345, "E")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (6, "James", to_date("20040718","YYYYMMDD"), 5679, 6546, "W")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (7, "Jodd", to_date("20030720","YYYYMMDD"), 5438, 7658, "E")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (8, "Joke", to_date("20020101","YYYYMMDD"), 8765, 4543, "W")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (9, "Jack", to_date("20010829","YYYYMMDD"), 7896, 1232, "E")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- --------------- --------- ----------- ----------- -
1 Jason 25-JUL-96 1234 8767 E
2 John 15-JUL-97 2341 3456 W
3 Joe 25-JAN-86 4321 5654 E
4 Tom 13-SEP-06 2413 6787 W
5 Jane 17-APR-05 7654 4345 E
6 James 18-JUL-04 5679 6546 W
7 Jodd 20-JUL-03 5438 7658 E
8 Joke 01-JAN-02 8765 4543 W
9 Jack 29-AUG-01 7896 1232 E
9 rows selected.
SQL> select * from job
2 /
EMPNO JOBTITLE
---------- --------------------
1 Tester
2 Accountant
3 Developer
4 COder
5 Director
6 Mediator
7 Proffessor
8 Programmer
9 Developer
9 rows selected.
SQL>
SQL>
SQL> SELECT e.ename, j.jobtitle FROM employee e, job j
2 WHERE e.empno = j.empno;
ENAME JOBTITLE
--------------- --------------------
Jason Tester
John Accountant
Joe Developer
Tom COder
Jane Director
James Mediator
Jodd Proffessor
Joke Programmer
Jack Developer
9 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL> drop table job
2 /
Table dropped.
SQL>
Use * to reference all columns from a table
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL>
SQL> select dept.*, empno, ename, job, mgr, hiredate, sal, comm
2 from emp, dept
3 where emp.deptno = dept.deptno
4 and dept.deptno = 10
5 /
DEPTNO DNAME LOC EMPNO ENAME JOB
---------- -------------- ------------- ---------- ---------- ---------
MGR HIREDATE SAL COMM
---------- --------- ---------- ----------
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER
7839 09-JUN-81 2450
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT
17-NOV-81 5000
10 ACCOUNTING NEW YORK 7934 MILLER CLERK
7782 23-JAN-82 1300
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER
7839 09-JUN-81 2450
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT
17-NOV-81 5000
10 ACCOUNTING NEW YORK 7934 MILLER CLERK
7782 23-JAN-82 1300
6 rows selected.
SQL> drop table emp;
Table dropped.
SQL>
SQL> drop table dept;
Table dropped.
SQL>