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
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.