Oracle PL/SQL/Select Query/alias column — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 Column Headings for calculated column
- 2 To_char function with alias column name
- 3 TO_CHAR(last_stock_date, "MM-DD-YYYY HH24:MI") with column alias
- 4 Use column command to define column name before select statement
- 5 Use table alias to reference column names
- 6 Using Aliases
- 7 Using Aliases with Simple Subqueries
- 8 Using Aliasing in Equi-Joins
Column Headings for calculated column
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));
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>
SQL> select ename, sal, sal * 1.1 from emp;
ENAME SAL SAL*1.1
---------- ---------- ----------
SMITH 800 880
ALLEN 1600 1760
WARD 1250 1375
JONES 2975 3272.5
MARTIN 1250 1375
BLAKE 2850 3135
CLARK 2450 2695
SCOTT 3000 3300
KING 5000 5500
TURNER 1500 1650
ADAMS 1100 1210
ENAME SAL SAL*1.1
---------- ---------- ----------
JAMES 950 1045
FORD 3000 3300
MILLER 1300 1430
14 rows selected.
SQL>
SQL> select ename, sal, sal * 1.1 raise from emp;
ENAME SAL RAISE
---------- ---------- ----------
SMITH 800 880
ALLEN 1600 1760
WARD 1250 1375
JONES 2975 3272.5
MARTIN 1250 1375
BLAKE 2850 3135
CLARK 2450 2695
SCOTT 3000 3300
KING 5000 5500
TURNER 1500 1650
ADAMS 1100 1210
ENAME SAL RAISE
---------- ---------- ----------
JAMES 950 1045
FORD 3000 3300
MILLER 1300 1430
14 rows selected.
SQL>
SQL>
SQL> column ename heading "Employee"
SQL> column sal heading "Salary"
SQL> column raise heading "Raise"
SQL>
SQL> select ename, sal, sal * 1.1 raise from emp;
Employee Salary Raise
---------- ---------- ----------
SMITH 800 880
ALLEN 1600 1760
WARD 1250 1375
JONES 2975 3272.5
MARTIN 1250 1375
BLAKE 2850 3135
CLARK 2450 2695
SCOTT 3000 3300
KING 5000 5500
TURNER 1500 1650
ADAMS 1100 1210
Employee Salary Raise
---------- ---------- ----------
JAMES 950 1045
FORD 3000 3300
MILLER 1300 1430
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
To_char function with alias column name
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2),
4 quantity_on_hand NUMBER(5,0),
5 last_stock_date DATE);
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null);
1 row created.
SQL>
SQL>
SQL> select PRODUCT_NAME,
2 to_char(PRODUCT_PRICE, "$9,999.00") "Price",
3 to_char(QUANTITY_ON_HAND, "999,999") "On Hand",
4 " "|| to_char(LAST_STOCK_DATE, "MON DD, YYYY") "Last Stocked"
5 from PRODUCT;
PRODUCT_NAME Price On Hand Last Stocked
------------------------- ---------- -------- --------------
Small Widget $99.00 1 JAN 15, 2003
Medium Widget $75.00 1,000 JAN 15, 2002
Product Number $50.00 100 JAN 15, 2003
Round Church Station $25.00 10,000
SQL>
SQL> DROP TABLE product;
Table dropped.
SQL>
TO_CHAR(last_stock_date, "MM-DD-YYYY HH24:MI") with column alias
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2),
4 quantity_on_hand NUMBER(5,0),
5 last_stock_date DATE);
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null);
1 row created.
SQL>
SQL> SELECT product_name,
2 product_price,
3 quantity_on_hand,
4 TO_CHAR(last_stock_date, "MM-DD-YYYY HH24:MI") "Last Stocked"
5 FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND Last Stocked
------------------------- ------------- ---------------- ----------------
Small Widget 99 1 01-15-2003 00:00
Medium Widget 75 1000 01-15-2002 00:00
Product Number 50 100 01-15-2003 00:00
Round Church Station 25 10000
SQL>
SQL>
SQL>
SQL> DROP TABLE product;
Table dropped.
SQL>
SQL>
SQL>
Use column command to define column name before select statement
SQL>
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));
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> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
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 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
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
14 rows selected.
SQL>
SQL> column ename heading "Employee"
SQL> column sal heading "Salary"
SQL> column raise heading "Raise"
SQL>
SQL> select ename, sal, sal * 1.1 raise
2 from emp;
Employee Salary Raise
---------- ---------- ----------
SMITH 800 880
ALLEN 1600 1760
WARD 1250 1375
JONES 2975 3272.5
MARTIN 1250 1375
BLAKE 2850 3135
CLARK 2450 2695
SCOTT 3000 3300
KING 5000 5500
TURNER 1500 1650
ADAMS 1100 1210
Employee Salary Raise
---------- ---------- ----------
JAMES 950 1045
FORD 3000 3300
MILLER 1300 1430
14 rows selected.
SQL>
SQL> column ename heading "Employee Name"
SQL> /
Employee N Salary Raise
---------- ---------- ----------
SMITH 800 880
ALLEN 1600 1760
WARD 1250 1375
JONES 2975 3272.5
MARTIN 1250 1375
BLAKE 2850 3135
CLARK 2450 2695
SCOTT 3000 3300
KING 5000 5500
TURNER 1500 1650
ADAMS 1100 1210
Employee N Salary Raise
---------- ---------- ----------
JAMES 950 1045
FORD 3000 3300
MILLER 1300 1430
14 rows selected.
SQL> column ename format a13 heading "Employee Name"
SQL> /
Employee Name Salary Raise
------------- ---------- ----------
SMITH 800 880
ALLEN 1600 1760
WARD 1250 1375
JONES 2975 3272.5
MARTIN 1250 1375
BLAKE 2850 3135
CLARK 2450 2695
SCOTT 3000 3300
KING 5000 5500
TURNER 1500 1650
ADAMS 1100 1210
Employee Name Salary Raise
------------- ---------- ----------
JAMES 950 1045
FORD 3000 3300
MILLER 1300 1430
14 rows selected.
SQL>
SQL> select ename, sal, sal * 1.1 raise
2 from emp;
Employee Name Salary Raise
------------- ---------- ----------
SMITH 800 880
ALLEN 1600 1760
WARD 1250 1375
JONES 2975 3272.5
MARTIN 1250 1375
BLAKE 2850 3135
CLARK 2450 2695
SCOTT 3000 3300
KING 5000 5500
TURNER 1500 1650
ADAMS 1100 1210
Employee Name Salary Raise
------------- ---------- ----------
JAMES 950 1045
FORD 3000 3300
MILLER 1300 1430
14 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
Use table alias to reference column names
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 e.emp_id, e.ename
2 FROM emp e;
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.
SQL>
Using Aliases
SQL>
SQL> CREATE TABLE emp (
2 empID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL);
Table created.
SQL>
SQL> INSERT INTO emp (empID,Name) VALUES (1,"Tom");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (2,"Jack");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (3,"Mary");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (4,"Bill");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (5,"Cat");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (6,"Victor");
1 row created.
SQL>
SQL> SELECT Name AS empName, empID AS ID FROM emp;
EMPNAME ID
-------------------------------------------------- ----------
Tom 1
Jack 2
Mary 3
Bill 4
Cat 5
Victor 6
6 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
Using Aliases with Simple Subqueries
SQL>
SQL>
SQL> CREATE TABLE dept (
2 deptID INT NOT NULL PRIMARY KEY,
3 empID INT NOT NULL,
4 ClassID INT NOT NULL,
5 EnrolledOn DATE,
6 Grade INT);
Table created.
SQL>
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (1,1,1,DATE "2002-09-23",62);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (2,1,2,DATE "2002-09-30",70);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (3,2,3,DATE "2003-09-23",51);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (4,2,5,DATE "2003-09-23",41);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (5,2,6,DATE "2003-09-23",68);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (6,3,4,DATE "2002-09-30",78);
1 row created.
SQL>
SQL>
SQL> SELECT e1.empID, e1.ClassID, (
2 SELECT COUNT(*) FROM dept e2
3 WHERE e1.ClassID = e2.ClassID)-1
4 AS OtherempsInClass
5 FROM dept e1
6 WHERE empID = 6;
no rows selected
SQL>
SQL> drop table dept;
Table dropped.
Using Aliasing in Equi-Joins
SQL> CREATE TABLE emp (
2 empID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL);
Table created.
SQL> INSERT INTO emp (empID,Name) VALUES (1,"Tom");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (2,"Jack");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (3,"Mary");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (4,"Bill");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (5,"Cat");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (6,"Victor");
1 row created.
SQL>
SQL> CREATE TABLE empExam (
2 empID INT NOT NULL,
3 ExamID INT NOT NULL,
4 Mark INT,
5 Taken SMALLINT,
6 Comments VARCHAR(255),
7 CONSTRAINT PK_empExam PRIMARY KEY (empID, ExamID));
Table created.
SQL>
SQL>
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,1,55,1,"Satisfactory");
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,2,73,1,"Good result");
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,3,44,1,"Hard");
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,5,39,0,"Simple");
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken) VALUES (2,6,63,1);
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT SE.ExamID,
2 SE.Mark,
3 S.Name AS empName
4 FROM empExam AS SE
5 JOIN emp AS S
6 ON SE.empID = S.empID
7 ORDER BY ExamID
8
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table empexam;
Table dropped.