Oracle PL/SQL Tutorial/Query Select/Select clause — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 Compare with number
- 2 Concatenate string
- 3 Don"t Perform the Same Calculation Over and Over
- 4 List single column from a table
- 5 Math calculation in select statement
- 6 Negate a column value
- 7 NO_INDEX function in select statement
- 8 Performing Single Table SELECT Statements
- 9 Search for String Across Columns
- 10 Select constant as a column
- 11 Select employee first and last and sort by last name
- 12 Select from a Subquery
- 13 Use as to specify the alias name
- 14 Use function in select clause
- 15 Use more than one aggregate functions in a select statement
- 16 where clause
Compare with number
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> select empno, ename, comm
2 from employees
3 where comm > 400;
EMPNO ENAME COMM
---------- -------- ----------
3 Jord 500
5 Joe 1400
SQL>
SQL> select empno, ename, comm
2 from employees
3 where comm <= 400;
EMPNO ENAME COMM
---------- -------- ----------
2 Jerry 300
10 Pink 0
SQL>
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL>
Concatenate string
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> select ename ||", "||init
2 as full_name
3 from employees;
FULL_NAME
---------------
Jason, N
Jerry, J
Jord, T
Mary, J
Joe, P
Black, R
Red, A
White, S
Yellow, C
Pink, J
10 rows selected.
SQL>
SQL> drop table employees;
Table dropped.
Don"t Perform the Same Calculation Over and Over
SQL>
SQL> CREATE TABLE contract
2 (income INTEGER
3 ,overhead INTEGER);
Table created.
SQL>
SQL>
SQL> INSERT INTO contract VALUES (1000,20);
1 row created.
SQL> INSERT INTO contract VALUES (2000,10);
1 row created.
SQL> INSERT INTO contract VALUES (1000,50);
1 row created.
SQL>
SQL> SELECT income,
2 overhead,
3 (income-income*overhead/100) AS residual,
4 0.20*(income-income*overhead/100) AS Est,
5 0.10*(income-income*overhead/100) AS Admin,
6 0.05*(income-income*overhead/100) AS Rsrv
7 FROM contract;
INCOME OVERHEAD RESIDUAL EST ADMIN RSRV
---------- ---------- ---------- ---------- ---------- ----------
1000 20 800 160 80 40
2000 10 1800 360 180 90
1000 50 500 100 50 25
SQL> SELECT income,
2 overhead,
3 residual,
4 0.20*residual AS Est,
5 0.10*residual AS Admin,
6 0.05*residual AS Rsrv
7 FROM
8 (SELECT income, overhead, (income-income*overhead/100) AS residual
9 FROM contract) subquery;
INCOME OVERHEAD RESIDUAL EST ADMIN RSRV
---------- ---------- ---------- ---------- ---------- ----------
1000 20 800 160 80 40
2000 10 1800 360 180 90
1000 50 500 100 50 25
SQL> CREATE VIEW residual1 AS
2 SELECT income, overhead, (income-income*overhead/100) AS residual
3 FROM contract;
View created.
SQL>
SQL> SELECT income,
2 overhead,
3 residual,
4 0.20*residual AS Est,
5 0.10*residual AS Admin,
6 0.05*residual AS Rsrv
7 FROM residual1;
INCOME OVERHEAD RESIDUAL EST ADMIN RSRV
---------- ---------- ---------- ---------- ---------- ----------
1000 20 800 160 80 40
2000 10 1800 360 180 90
1000 50 500 100 50 25
SQL> SELECT subquery.*,
2 0.20*residual AS Est,
3 0.10*residual AS Admin,
4 0.05*residual AS Rsrv
5 FROM
6 (SELECT contract.*, (income-income*overhead/100) AS residual
7 FROM contract) subquery;
INCOME OVERHEAD RESIDUAL EST ADMIN RSRV
---------- ---------- ---------- ---------- ---------- ----------
1000 20 800 160 80 40
2000 10 1800 360 180 90
1000 50 500 100 50 25
SQL>
SQL> DROP VIEW residual1;
View dropped.
SQL> DROP TABLE contract;
Table dropped.
SQL>
SQL>
List single column from a table
SQL>
SQL>
SQL>
SQL> CREATE TABLE employees (
2 au_id CHAR(3) NOT NULL,
3 au_fname VARCHAR(15) NOT NULL,
4 au_lname VARCHAR(15) NOT NULL,
5 phone VARCHAR(12) NULL ,
6 address VARCHAR(20) NULL ,
7 city VARCHAR(15) NULL ,
8 state CHAR(2) NULL ,
9 zip CHAR(5) NULL
10 );
Table created.
SQL>
SQL> INSERT INTO employees VALUES("A01","S","B","111-111-1111","75 St","Boston","NY","11111");
1 row created.
SQL> INSERT INTO employees VALUES("A02","W","H","222-222-2222","2922 Rd","Boston","CO","22222");
1 row created.
SQL> INSERT INTO employees VALUES("A03","H","H","333-333-3333","3800 Ave, #14F","San Francisco","CA","33333");
1 row created.
SQL> INSERT INTO employees VALUES("A04","K","H","444-444-4444","3800 Ave, #14F","San Francisco","CA","44444");
1 row created.
SQL> INSERT INTO employees VALUES("A05","C","K","555-555-5555","114 St","New York","NY","55555");
1 row created.
SQL> INSERT INTO employees VALUES("A06"," ","K","666-666-666","390 Mall","Palo Alto","CA","66666");
1 row created.
SQL> INSERT INTO employees VALUES("A07","P","O","777-777-7777","1442 St","Sarasota","FL","77777");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> SELECT city
2 FROM employees;
CITY
---------------
Boston
Boston
San Francisco
San Francisco
New York
Palo Alto
Sarasota
7 rows selected.
SQL>
SQL> drop table employees;
Table dropped.
Math calculation in select statement
SQL>
SQL>
SQL>
SQL> SELECT 2 + 3 * 4 AS "2+3*4",
2 (2 + 3) * 4 AS "(2+3)*4",
3 6 / 2 * 3 AS "6/2*3",
4 6 / (2 * 3) AS "6/(2*3)" from dual;
2+3*4 (2+3)*4 6/2*3 6/(2*3)
---------- ---------- ---------- ----------
14 20 9 1
Negate a column value
SQL>
SQL>
SQL> CREATE TABLE promotion(
2 title_id CHAR(3) NOT NULL,
3 advance DECIMAL(9,2) NULL ,
4 royalty_rate DECIMAL(5,2) NULL);
Table created.
SQL>
SQL>
SQL> INSERT INTO promotion VALUES("T01",10000,0.05);
1 row created.
SQL> INSERT INTO promotion VALUES("T02",1000,0.06);
1 row created.
SQL> INSERT INTO promotion VALUES("T03",15000,0.07);
1 row created.
SQL> INSERT INTO promotion VALUES("T04",20000,0.08);
1 row created.
SQL> INSERT INTO promotion VALUES("T05",100000,0.09);
1 row created.
SQL> INSERT INTO promotion VALUES("T06",20000,0.08);
1 row created.
SQL> INSERT INTO promotion VALUES("T07",1000000,0.11);
1 row created.
SQL> INSERT INTO promotion VALUES("T08",0,0.04);
1 row created.
SQL> INSERT INTO promotion VALUES("T09",0,0.05);
1 row created.
SQL> INSERT INTO promotion VALUES("T10",NULL,NULL);
1 row created.
SQL> INSERT INTO promotion VALUES("T11",100000,0.07);
1 row created.
SQL> INSERT INTO promotion VALUES("T12",50000,0.09);
1 row created.
SQL> INSERT INTO promotion VALUES("T13",20000,0.06);
1 row created.
SQL>
SQL>
SQL> SELECT title_id,
2 -advance AS "Advance"
3 FROM promotion;
TIT Advance
--- ----------
T01 -10000
T02 -1000
T03 -15000
T04 -20000
T05 -100000
T06 -20000
T07 -1000000
T08 0
T09 0
T10
T11 -100000
TIT Advance
--- ----------
T12 -50000
T13 -20000
13 rows selected.
SQL>
SQL>
SQL> drop table promotion;
Table dropped.
SQL>
SQL>
NO_INDEX function in select statement
SQL>
SQL>
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (10, "H", "Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL);
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT /*+ NO_INDEX (employee employee_id_pk) */
2 first_name, last_name
3 FROM employee
4 WHERE employee_id = 25;
no rows selected
SQL>
SQL>
SQL> drop table employee;
Table dropped.
Performing Single Table SELECT Statements
- The SELECT statement retrieves information from tables.
- The SELECT statement specifies the table and a list of column names.
- After the SELECT keyword, you supply the column names.
- After the FROM keyword, you supply the table name.
- The SQL statement is ended using a semicolon (;).
- The items that immediately follow the SELECT statement can be any valid expressions.
- The rows returned by the database are known as a result set.
- Oracle database converts the column names into their uppercase equivalents.
- Character and date columns are left-justified.
- Number columns are right-justified.
By default, the Oracle database displays dates in the format DD-MON-YY, where
- DD is the day number,
- MON is the first three characters of the month (in uppercase), and
- YY is the last two digits of the year.
The clauses of a SELECT statement are:
WITH
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Search for String Across Columns
SQL>
SQL> CREATE TABLE myRoom
2 (name VARCHAR(10)
3 ,floorcolor VARCHAR(10)
4 ,ceilingcolor VARCHAR(10)
5 ,wallcolor VARCHAR(10)
6 );
Table created.
SQL> INSERT INTO myRoom VALUES ("Jim","RED","GREEN","YELLOW");
1 row created.
SQL> INSERT INTO myRoom VALUES ("Bob","YELLOW","BLUE","BLACK");
1 row created.
SQL> INSERT INTO myRoom VALUES ("Allan","BLUE","PINK","BLACK");
1 row created.
SQL> INSERT INTO myRoom VALUES ("George","BLUE","GREEN","OAK");
1 row created.
SQL>
SQL> SELECT name FROM myRoom
2 WHERE floorcolor = "YELLOW"
3 OR ceilingcolor = "YELLOW"
4 OR wallcolor = "YELLOW";
NAME
----------
Jim
Bob
SQL>
SQL> DROP TABLE myRoom;
Table dropped.
SQL>
Select constant as a column
SQL>
SQL>
SQL>
SQL> CREATE TABLE book(
2 title_id CHAR(3) NOT NULL,
3 title_name VARCHAR(40) NOT NULL,
4 type VARCHAR(10) NULL ,
5 pub_id CHAR(3) NOT NULL,
6 pages INTEGER NULL ,
7 price DECIMAL(5,2) NULL ,
8 sales INTEGER NULL ,
9 pubdate DATE NULL ,
10 contract SMALLINT NOT NULL
11 );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> INSERT INTO book VALUES("T01","Java","history","P01",111,21.99,566,DATE "2000-08-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T02","Oracle","history","P03", 114,19.95,9566,DATE "1998-04-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T03","SQL","computer","P02", 122,39.95,25667,DATE "2000-09-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T04","C++","psychology","P04", 511,12.99,13001,DATE "1999-05-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T05","Python","psychology","P04", 101,6.95,201440,DATE "2001-01-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T06","JavaScript","biography","P01", 173,19.95,11320,DATE "2000-07-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T07","LINQ","biography","P03", 331,23.95,1500200,DATE "1999-10-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T08","C#","children","P04", 861,10.00,4095,DATE "2001-06-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T09","SQL Server","children","P04", 212,13.95,5000,DATE "2002-05-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T10","AJAX","biography","P01", NULL,NULL,NULL,NULL,0);
1 row created.
SQL> INSERT INTO book VALUES("T11","VB","psychology","P04", 821,7.99,94123,DATE "2000-11-30",1);
1 row created.
SQL> INSERT INTO book VALUES("T12","Office","biography","P01", 507,12.99,100001,DATE "2000-08-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T13","VBA","history","P03", 812,29.99,10467,DATE "1999-05-31",1);
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT title_id,
2 price,
3 0.10 AS "Discount",
4 price * (1 - 0.10) AS "New price"
5 FROM book;
TIT PRICE Discount New price
--- ---------- ---------- ----------
T01 21.99 .1 19.791
T02 19.95 .1 17.955
T03 39.95 .1 35.955
T04 12.99 .1 11.691
T05 6.95 .1 6.255
T06 19.95 .1 17.955
T07 23.95 .1 21.555
T08 10 .1 9
T09 13.95 .1 12.555
T10 .1
T11 7.99 .1 7.191
TIT PRICE Discount New price
--- ---------- ---------- ----------
T12 12.99 .1 11.691
T13 29.99 .1 26.991
13 rows selected.
SQL>
SQL> drop table book;
Table dropped.
SQL>
Select employee first and last and sort by last name
SQL>
SQL> CREATE TABLE employees (
2 au_id CHAR(3) NOT NULL,
3 au_fname VARCHAR(15) NOT NULL,
4 au_lname VARCHAR(15) NOT NULL,
5 phone VARCHAR(12) NULL ,
6 address VARCHAR(20) NULL ,
7 city VARCHAR(15) NULL ,
8 state CHAR(2) NULL ,
9 zip CHAR(5) NULL
10 );
Table created.
SQL>
SQL> INSERT INTO employees VALUES("A01","S","B","111-111-1111","75 St","Boston","NY","11111");
1 row created.
SQL> INSERT INTO employees VALUES("A02","W","H","222-222-2222","2922 Rd","Boston","CO","22222");
1 row created.
SQL> INSERT INTO employees VALUES("A03","H","H","333-333-3333","3800 Ave, #14F","San Francisco","CA","33333");
1 row created.
SQL> INSERT INTO employees VALUES("A04","K","H","444-444-4444","3800 Ave, #14F","San Francisco","CA","44444");
1 row created.
SQL> INSERT INTO employees VALUES("A05","C","K","555-555-5555","114 St","New York","NY","55555");
1 row created.
SQL> INSERT INTO employees VALUES("A06"," ","K","666-666-666","390 Mall","Palo Alto","CA","66666");
1 row created.
SQL> INSERT INTO employees VALUES("A07","P","O","777-777-7777","1442 St","Sarasota","FL","77777");
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT au_fname, au_lname
2 FROM employees
3 ORDER BY au_lname;
AU_FNAME AU_LNAME
--------------- ---------------
S B
H H
W H
K H
C K
K
P O
7 rows selected.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
Select from a Subquery
SQL>
SQL>
SQL>
SQL> create table history
2 ( empno NUMBER(4)
3 , beginyear NUMBER(4)
4 , begindate DATE
5 , enddate DATE
6 , deptno NUMBER(2)
7 , msal NUMBER(6,2)
8 , comments VARCHAR2(60)
9 ) ;
Table created.
SQL>
SQL>
SQL>
SQL> insert into history values (9,2000,date "2000-01-01",date "2002-01-02",40, 950,"history for 9");
1 row created.
SQL> insert into history values (8,2000,date "2000-01-02", NULL ,20, 800,"");
1 row created.
SQL> insert into history values (7,1988,date "2000-01-06",date "2002-01-07",30,1000,"");
1 row created.
SQL> insert into history values (6,1989,date "2000-01-07",date "2002-01-12",30,1300,"");
1 row created.
SQL> insert into history values (5,1993,date "2000-01-12",date "2002-01-10",30,1500,"history for 5");
1 row created.
SQL> insert into history values (4,1995,date "2000-01-10",date "2002-01-11",30,1700,"");
1 row created.
SQL> insert into history values (3,1999,date "2000-01-11", NULL ,30,1600,"");
1 row created.
SQL> insert into history values (2,1986,date "2000-01-10",date "2002-01-08",20,1000,"history for 2");
1 row created.
SQL> insert into history values (1,1987,date "2000-01-08",date "2002-01-01",30,1000,"history for 1");
1 row created.
SQL> insert into history values (7,1989,date "2000-01-01",date "2002-05-12",30,1150,"history for 7");
1 row created.
SQL>
SQL>
SQL>
SQL> select *
2 from (select empno, msal
3 from history
4 order by msal desc)
5 where rownum <= 3;
EMPNO MSAL
------ ------
4 1700
3 1600
5 1500
3 rows selected.
SQL>
SQL> drop table history;
Table dropped.
SQL>
SQL>
Use as to specify the alias name
SQL>
SQL> CREATE TABLE employees (
2 au_id CHAR(3) NOT NULL,
3 au_fname VARCHAR(15) NOT NULL,
4 au_lname VARCHAR(15) NOT NULL,
5 phone VARCHAR(12) NULL ,
6 address VARCHAR(20) NULL ,
7 city VARCHAR(15) NULL ,
8 state CHAR(2) NULL ,
9 zip CHAR(5) NULL
10 );
Table created.
SQL>
SQL> INSERT INTO employees VALUES("A01","S","B","111-111-1111","75 St","Boston","NY","11111");
1 row created.
SQL> INSERT INTO employees VALUES("A02","W","H","222-222-2222","2922 Rd","Boston","CO","22222");
1 row created.
SQL> INSERT INTO employees VALUES("A03","H","H","333-333-3333","3800 Ave, #14F","San Francisco","CA","33333");
1 row created.
SQL> INSERT INTO employees VALUES("A04","K","H","444-444-4444","3800 Ave, #14F","San Francisco","CA","44444");
1 row created.
SQL> INSERT INTO employees VALUES("A05","C","K","555-555-5555","114 St","New York","NY","55555");
1 row created.
SQL> INSERT INTO employees VALUES("A06"," ","K","666-666-666","390 Mall","Palo Alto","CA","66666");
1 row created.
SQL> INSERT INTO employees VALUES("A07","P","O","777-777-7777","1442 St","Sarasota","FL","77777");
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT au_fname AS "First name",
2 au_lname AS "Last name",
3 city AS "City",
4 state,
5 zip AS "Postal code"
6 FROM employees;
First name Last name City ST Posta
--------------- --------------- --------------- -- -----
S B Boston NY 11111
W H Boston CO 22222
H H San Francisco CA 33333
K H San Francisco CA 44444
C K New York NY 55555
K Palo Alto CA 66666
P O Sarasota FL 77777
7 rows selected.
SQL>
SQL>
SQL> drop table employees;
Table dropped.
Use function in select clause
The following example displays the absolute value of subtracting 8000 from the salary column.
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"), 6661.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"), 6544.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"), 2344.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"), 4322.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"), 7897.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"), 1232.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 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.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 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> SELECT id, salary, salary - 80000, ABS(salary - 80000) FROM employee;
ID SALARY SALARY-80000 ABS(SALARY-80000)
---- ---------- ------------ -----------------
01 1234.56 -78765.44 78765.44
02 6661.78 -73338.22 73338.22
03 6544.78 -73455.22 73455.22
04 2344.78 -77655.22 77655.22
05 2334.78 -77665.22 77665.22
06 4322.78 -75677.22 75677.22
07 7897.78 -72102.22 72102.22
08 1232.78 -78767.22 78767.22
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Use more than one aggregate functions in a select statement
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
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"), 6661.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"), 6544.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"), 2344.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"), 4322.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"), 7897.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"), 1232.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 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.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 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> select city, avg(salary), sum(salary), max(salary), count(*)
2 from employee
3 group by city;
CITY AVG(SALARY) SUM(SALARY) MAX(SALARY) COUNT(*)
---------- ----------- ----------- ----------- ----------
New York 6110.28 12220.56 7897.78 2
Toronto 1234.56 1234.56 1234.56 1
Vancouver 3823.78 19118.9 6661.78 5
3 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
where clause
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
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"), 6661.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"), 6544.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"), 2344.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"), 4322.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"), 7897.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"), 1232.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 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.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 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> select * from employee
2 where first_name like "J%";
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
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
3 rows selected.
SQL> select * from employee where id = 5 and salary > 0
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
SQL> /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.