Oracle PL/SQL/Select Query/Column Names
Содержание
- 1 col columnName new_value BLAH
- 2 Column Renaming
- 3 Column renaming for function
- 4 Combined columns and renaming
- 5 Insert string text between column names
- 6 Reference column name from renamed table name
- 7 Reference column without table name during table join
- 8 Use table name to reference column name
- 9 Use table name to reference the ambiguity column names
col columnName new_value BLAH
<source lang="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", "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>
</source>
Column Renaming
<source lang="sql">
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>
</source>
Column renaming for function
<source lang="sql">
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.
</source>
Combined columns and renaming
<source lang="sql">
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.
</source>
Insert string text between column names
<source lang="sql">
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>
</source>
Reference column name from renamed table name
<source lang="sql">
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.
</source>
Reference column without table name during table join
<source lang="sql">
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>
</source>
Use table name to reference column name
<source lang="sql">
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.
</source>
Use table name to reference the ambiguity column names
<source lang="sql">
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.
</source>