Oracle PL/SQL Tutorial/Query Select/Select clause
Содержание
- 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
<source lang="sql">
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></source>
Concatenate string
<source lang="sql">
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.</source>
Don"t Perform the Same Calculation Over and Over
<source lang="sql">
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></source>
List single column from a table
<source lang="sql">
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.</source>
Math calculation in select statement
<source lang="sql">
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</source>
Negate a column value
<source lang="sql">
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></source>
NO_INDEX function in select statement
<source lang="sql">
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.</source>
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:
<source lang="sql">
WITH SELECT FROM WHERE GROUP BY HAVING ORDER BY</source>
Search for String Across Columns
<source lang="sql">
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></source>
Select constant as a column
<source lang="sql">
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></source>
Select employee first and last and sort by last name
<source lang="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> 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></source>
Select from a Subquery
<source lang="sql">
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></source>
Use as to specify the alias name
<source lang="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> 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.</source>
Use function in select clause
The following example displays the absolute value of subtracting 8000 from the salary column.
<source lang="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"), 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.</source>
Use more than one aggregate functions in a select statement
<source lang="sql">
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.</source>
where clause
<source lang="sql">
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.</source>
