Oracle PL/SQL/Select Query/Column Names — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 10:00, 26 мая 2010

col columnName new_value BLAH

   
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;
Table created.
SQL> insert into emp values(1,"Tom","N",   "TRAINER", 13,date "1965-12-17",  800 , NULL,  20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20",  1600, 300,   30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" ,  "Tester",6,date "1962-02-22",  1250, 500,   30);
1 row created.
SQL> insert into emp values(4,"Jane","JM",  "Designer", 9,date "1967-04-02",  2975, NULL,  20);
1 row created.
SQL> insert into emp values(5,"Mary","P",  "Tester",6,date "1956-09-28",  1250, 1400,  30);
1 row created.
SQL> insert into emp values(6,"Black","R",   "Designer", 9,date "1963-11-01",  2850, NULL,  30);
1 row created.
SQL> insert into emp values(7,"Chris","AB",  "Designer", 9,date "1965-06-09",  2450, NULL,  10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(9,"Peter","CC",   "Designer",NULL,date "1952-11-17",  5000, NULL,  10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28",  1500, 0,     30);
1 row created.
SQL> insert into emp values(11,"Ana","AA",  "TRAINER", 8,date "1966-12-30",  1100, NULL,  20);
1 row created.
SQL> insert into emp values(12,"Jane","R",   "Manager",   6,date "1969-12-03",  800 , NULL,  30);
1 row created.
SQL> insert into emp values(13,"Fake","MG",   "TRAINER", 4,date "1959-02-13",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager",   7,date "1962-01-23",  1300, NULL,  10);
1 row created.
SQL>
SQL>
SQL> col empno new_value BLAH
SQL>
SQL> select empno, ename, bdate
  2  ,      sal         as salary
  3  ,      comm         as commission
  4  from   emp;
Enter...
                                           month
 EMPNO        lname         BDATE         salary COMMISSION
------ -------------------- ---------- --------- ----------
     1 Tom                  17-12-1965   $800.00  [N/A]
     2 Jack                 20-02-1961  $1600.00    $300.00
     3 Wil                  22-02-1962  $1250.00    $500.00
     4 Jane                 02-04-1967  $2975.00  [N/A]
     5 Mary                 28-09-1956  $1250.00   $1400.00
     6 Black                01-11-1963  $2850.00  [N/A]
     7 Chris                09-06-1965  $2450.00  [N/A]
     8 Smart                26-11-1959  $3000.00  [N/A]
     9 Peter                17-11-1952  $5000.00  [N/A]
    10 Take                 28-09-1968  $1500.00       $.00
    11 Ana                  30-12-1966  $1100.00  [N/A]
    12 Jane                 03-12-1969   $800.00  [N/A]
    13 Fake                 13-02-1959  $3000.00  [N/A]
    14 Mike                 23-01-1962  $1300.00  [N/A]
14 rows selected.
SQL> def BLAH
DEFINE BLAH            =     14 (NUMBER)
SQL>
SQL> select empno, ename, bdate
  2  ,      sal         as salary
  3  ,      comm         as commission
  4  from   emp
  5  where  deptno = 30;
Enter...
                                           month
 EMPNO        lname         BDATE         salary COMMISSION
------ -------------------- ---------- --------- ----------
     2 Jack                 20-02-1961  $1600.00    $300.00
     3 Wil                  22-02-1962  $1250.00    $500.00
     5 Mary                 28-09-1956  $1250.00   $1400.00
     6 Black                01-11-1963  $2850.00  [N/A]
    10 Take                 28-09-1968  $1500.00       $.00
    12 Jane                 03-12-1969   $800.00  [N/A]
6 rows selected.
SQL> def    BLAH
DEFINE BLAH            =     12 (NUMBER)
SQL> undef  BLAH
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>



Column Renaming

  

SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.
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>
SQL> select empno "emp Number", ename "Name", sal "Salary" from emp;
emp Number Name           Salary
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100
emp Number Name           Salary
---------- ---------- ----------
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL>



Column renaming for function

  
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.
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>
SQL> select empno "Empl#", initcap(ename) "EmplName" from emp;
     Empl# EmplName
---------- ----------
      7369 Smith
      7499 Allen
      7521 Ward
      7566 Jones
      7654 Martin
      7698 Blake
      7782 Clark
      7788 Scott
      7839 King
      7844 Turner
      7876 Adams
     Empl# EmplName
---------- ----------
      7900 James
      7902 Ford
      7934 Miller
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.



Combined columns and renaming

  
SQL> create table emp(
  2           emp_id                integer         primary key
  3          ,lastname               varchar2(20)    not null
  4          ,firstname              varchar2(15)    not null
  5          ,midinit                varchar2(1)
  6          ,street                 varchar2(30)
  7          ,city                   varchar2(20)
  8          ,state                  varchar2(2)
  9          ,zip                    varchar2(5)
 10          ,shortZipCode                   varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,company_name           varchar2(50));
Table created.
SQL>
SQL>
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (1,"Jones","Joe","J","1 Ave","New York","NY","11202","1111","212", "221-4333","Big Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (2,"Smith","Sue","J","1 Street","New York","NY","11444","1111","212", "436-6773","Little Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (3,"X","Peggy","J","1 Drive","New York","NY","45502","2222","212", "234-4444","Medium Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (4,"Murdy","Jill", null,"930 Eady St","New York","NY","45452","6458","212", "634-7733","Wilton Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (5,"Oper","Carl","L","19 Studio Drive","New York","NY","67672","3234","212", "243-4243","Wesson and Smith Company");
1 row created.
SQL>
SQL>
SQL> select emp_id,
  2   firstname || " " || lastname as "Name",
  3   city, state
  4    from emp
  5   where state = "RI";
no rows selected
SQL>
SQL> drop table emp;
Table dropped.



Insert string text between column names

  

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2334.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 2334.78,"New York",  "Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 2334.78,"New York",  "Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 2334.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    2334.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    2334.78 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2334.78 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    2334.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    2334.78 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02    2334.78 Vancouver  Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- Insert string text between column names:
SQL> SELECT First_Name || "" || Last_Name || " is working." FROM Employee;
FIRST_NAME||""||LAST_NAME||"ISWO
--------------------------------
JasonMartin is working.
AlisonMathews is working.
JamesSmith is working.
CeliaRice is working.
RobertBlack is working.
LindaGreen is working.
DavidLarry is working.
JamesCat is working.
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>



Reference column name from renamed table name

   
SQL>
SQL>
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;
Table created.
SQL> insert into emp values(1,"Tom","N",   "Coder", 13,date "1965-12-17",  800 , NULL,  20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20",  1600, 300,   30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" ,  "Tester",6,date "1962-02-22",  1250, 500,   30);
1 row created.
SQL> insert into emp values(4,"Jane","JM",  "Designer", 9,date "1967-04-02",  2975, NULL,  20);
1 row created.
SQL> insert into emp values(5,"Mary","P",  "Tester",6,date "1956-09-28",  1250, 1400,  30);
1 row created.
SQL> insert into emp values(6,"Black","R",   "Designer", 9,date "1963-11-01",  2850, NULL,  30);
1 row created.
SQL> insert into emp values(7,"Chris","AB",  "Designer", 9,date "1965-06-09",  2450, NULL,  10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(9,"Peter","CC",   "Designer",NULL,date "1952-11-17",  5000, NULL,  10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28",  1500, 0,     30);
1 row created.
SQL> insert into emp values(11,"Ana","AA",  "Coder", 8,date "1966-12-30",  1100, NULL,  20);
1 row created.
SQL> insert into emp values(12,"Jane","R",   "Manager",   6,date "1969-12-03",  800 , NULL,  30);
1 row created.
SQL> insert into emp values(13,"Fake","MG",   "Coder", 4,date "1959-02-13",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager",   7,date "1962-01-23",  1300, NULL,  10);
1 row created.
SQL>
SQL> create table departments
  2  ( deptno NUMBER(2)     constraint D_PK primary key
  3  , dname  VARCHAR2(10)
  4  , location VARCHAR2(8)
  5  , mgr    NUMBER(4)
  6  ) ;
Table created.
SQL>
SQL> insert into departments values (10,"ACCOUNTING","NEW YORK",7);
1 row created.
SQL> insert into departments values (20,"TRAINING",  "DALLAS",  4);
1 row created.
SQL> insert into departments values (30,"SALES",     "CHICAGO", 6);
1 row created.
SQL> insert into departments values (40,"HR",        "BOSTON",  9);
1 row created.
SQL>
SQL>
SQL> select d.deptno, d.location, e.ename, e.init
  2  from   emp e, departments d;
    DEPTNO LOCATION ENAME    INIT
---------- -------- -------- -----
        10 NEW YORK Tom      N
        10 NEW YORK Jack     JAM
        10 NEW YORK Wil      TF
        10 NEW YORK Jane     JM
        10 NEW YORK Mary     P
        10 NEW YORK Black    R
        10 NEW YORK Chris    AB
        10 NEW YORK Smart    SCJ
        10 NEW YORK Peter    CC
        10 NEW YORK Take     JJ
        10 NEW YORK Ana      AA
        10 NEW YORK Jane     R
        10 NEW YORK Fake     MG
        10 NEW YORK Mike     TJA
        20 DALLAS   Tom      N
        20 DALLAS   Jack     JAM
        20 DALLAS   Wil      TF
        20 DALLAS   Jane     JM
        20 DALLAS   Mary     P
    DEPTNO LOCATION ENAME    INIT
---------- -------- -------- -----
        20 DALLAS   Black    R
        20 DALLAS   Chris    AB
        20 DALLAS   Smart    SCJ
        20 DALLAS   Peter    CC
        20 DALLAS   Take     JJ
        20 DALLAS   Ana      AA
        20 DALLAS   Jane     R
        20 DALLAS   Fake     MG
        20 DALLAS   Mike     TJA
        30 CHICAGO  Tom      N
        30 CHICAGO  Jack     JAM
        30 CHICAGO  Wil      TF
        30 CHICAGO  Jane     JM
        30 CHICAGO  Mary     P
        30 CHICAGO  Black    R
        30 CHICAGO  Chris    AB
        30 CHICAGO  Smart    SCJ
        30 CHICAGO  Peter    CC
        30 CHICAGO  Take     JJ
    DEPTNO LOCATION ENAME    INIT
---------- -------- -------- -----
        30 CHICAGO  Ana      AA
        30 CHICAGO  Jane     R
        30 CHICAGO  Fake     MG
        30 CHICAGO  Mike     TJA
        40 BOSTON   Tom      N
        40 BOSTON   Jack     JAM
        40 BOSTON   Wil      TF
        40 BOSTON   Jane     JM
        40 BOSTON   Mary     P
        40 BOSTON   Black    R
        40 BOSTON   Chris    AB
        40 BOSTON   Smart    SCJ
        40 BOSTON   Peter    CC
        40 BOSTON   Take     JJ
        40 BOSTON   Ana      AA
        40 BOSTON   Jane     R
        40 BOSTON   Fake     MG
        40 BOSTON   Mike     TJA
56 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table departments;
Table dropped.



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>



Use table name to reference column name

  
SQL>
SQL> CREATE TABLE emp (
  2    emp_id               NUMBER,
  3    ename             VARCHAR2(40),
  4    hire_date        DATE DEFAULT sysdate,
  5    end_date DATE,
  6    rate     NUMBER(5,2),
  7    CONSTRAINT emp_pk    PRIMARY KEY (emp_id)
  8  );
Table created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL> SET ECHO ON
SQL> SELECT emp.emp_id, emp.ename
  2  FROM emp;
       101 Mary
       102 Tom
       104 Peter
       105 Mike
       107 Less
       108 Park
       110 Ink
       111 Tike
       112 Inn
       113 Kate
10 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.



Use table name to reference the ambiguity column names

  
SQL> CREATE TABLE emp
  2  (
  3     cid                NUMBER,
  4     address        VARCHAR2(40),
  5     customer_state             VARCHAR2(40),
  6     phone     VARCHAR2(25),
  7     income_level          VARCHAR2(30),
  8     customer_credit            NUMBER,
  9     city              VARCHAR2(30)
 10  );
Table created.
SQL>
SQL> CREATE TABLE sales(
  2    product_id               NUMBER(6),
  3    cid               NUMBER,
  4    channel_id            CHAR(1),
  5    sold         NUMBER(3),
  6    cost                  NUMBER(10,2)
  7  );
Table created.
SQL>
SQL> select product_id, sold, city, customer_state
  2  from   sales s, emp c
  3  where  s.cid = c.cid
  4  and    product_id = 117;
no rows selected
SQL>
SQL> select s.product_id, s.sold, c.cid,
  2  c.city, c.customer_state
  3  from   sales s, emp c
  4  where  s.cid = c.cid
  5  and    s.product_id = 117;
no rows selected
SQL>
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table sales;
Table dropped.