Oracle PL/SQL/Select Query/Where
Содержание
- 1 A list of all Employees whose salary is more than $5000
- 2 Combining WHERE Conditions
- 3 List all employees who are younger than Jane
- 4 List all employees with salary between 1300 and 1600
- 5 Query a varchar2 type column in where statement
- 6 The TO_DATE function in where clause
- 7 Use an analytical function in a WHERE clause
- 8 Use ROWNUM in where clause
- 9 Use Trunc in where clause
- 10 Using the WHERE, GROUP BY, and HAVING Clauses Together
- 11 Where clause converts text with date value format to date value
- 12 WHERE clause with a GROUP BY clause
A list of all Employees whose salary is more than $5000
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 4334.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"), 6334.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"), 8334.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"), 1334.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"), 9334.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"), 8765.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 4334.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 6334.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 8334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 1334.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 9334.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 8765.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> --A list of all Employees whose salary is more than $5000 SQL> SQL> SELECT ID, First_Name, Last_Name FROM Employee WHERE Salary > 5000; ID FIRST_NAME LAST_NAME
---------- ----------
04 Celia Rice 05 Robert Black 07 David Larry 08 James Cat SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL>
</source>
Combining WHERE Conditions
<source lang="sql">
SQL> CREATE TABLE SAT (
2 StudentID INT NOT NULL, 3 ExamID INT NOT NULL, 4 Mark INT, 5 IfPassed SMALLINT, 6 Comments VARCHAR(255), 7 CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID) 8 );
Table created. SQL> SQL> SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,"Satisfactory"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,"Good result"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,"Hard"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1); 1 row created. SQL> SQL> SQL> SQL> SELECT StudentID, Mark, Comments FROM SAT
2 WHERE ExamID = 1 AND IfPassed = 1; STUDENTID MARK
----------
COMMENTS
1 55
Satisfactory
1 row selected. SQL> SQL> SQL> drop table SAT; Table dropped.
</source>
List all employees who are younger than Jane
<source lang="sql">
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N","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> SQL> select ename, init, bdate
2 from emp 3 where bdate > (select bdate 4 from emp 5 where ename = "Jane");
no rows selected SQL> SQL> SQL> SQL> drop table emp; Table dropped.
</source>
List all employees with salary between 1300 and 1600
<source lang="sql">
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "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> select ename, init, sal
2 from emp 3 where sal between 1300 and 1600;
ENAME INIT SAL
----- ----------
Jack JAM 1600 Take JJ 1500 Mike TJA 1300 SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Query a varchar2 type column in where statement
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE employees
2 ( employee_id number(10) not null, 3 last_name varchar2(50) not null, 4 email varchar2(30), 5 hire_date date, 6 job_id varchar2(30), 7 department_id number(10), 8 salary number(6), 9 manager_id number(6) 10 );
Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "YourName@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Dillon", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "wvelasq@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created. SQL> SQL> select * from employees; EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID
-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
1001 Lawson lawson@g.ru 01-JAN-02 MGR 1 30000 1004
1002 Wells wells@g.ru 01-JAN-02 DBA 2 20000 1005
1003 Bliss bliss@g.ru 01-JAN-02 PROG 3 24000 1004
1004 Kyte YourName@a.ru 13-JUN-98 MGR 4 25000 1005
1005 Dillon sdillon@a .ru 10-JUN-08 PROG 1 20000 1006
1006 Beck clbeck@g.ru 10-JUN-08 PROG 2 20000
1007 Java java01@g.ru 10-JUN-08 PROG 3 20000 1006
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID
-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
1008 Oracle wvelasq@g.ru 10-JUN-08 DBA 4 20000 1006
8 rows selected. SQL> SQL> SQL> SQL> select salary
2 from employees 3 where job_id = "DBA" 4 / SALARY
20000
20000
SQL> SQL> SQL> drop table employees; Table dropped. SQL>
</source>
The TO_DATE function in where clause
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 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> --The TO_DATE function SQL> SQL> SELECT ID FROM Employee WHERE Start_Date = TO_DATE("September 17, 1996","Month DD, YYYY"); ID
08 SQL> SQL> -- clean the table SQL> drop table Employee
2 /
</source>
Use an analytical function in a WHERE clause
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID 3 ename VARCHAR2(10 BYTE), -- Employee Name 4 hireDate DATE, -- Date Employee Hired 5 orig_salary Number(8,2), -- Orignal Salary 6 curr_salary Number(8,2), -- Current Salary 7 region VARCHAR2(1 BYTE) -- Region where employeed 8 ) 9 /
Table created. SQL> SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID 3 jobtitle VARCHAR2(10 BYTE) -- Employee job title 4 ) 5 /
Table created. SQL> -- prepare data for employee table SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, 48000, "E")
3 /
1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000, 58000, "E")
3 /
1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, 36000, "W")
3 /
1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000, 53000, "E")
3 /
1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, 85000, "W")
3 /
1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E")
3 /
1 row created. SQL> SQL> -- prepare data for job table SQL> SQL> insert into job(empno, jobtitle)
2 values(101, "Painter");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(122, "Tester");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(123, "Dediator");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(104, "Chemist");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(105, "Accountant");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(116, "Manager");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(117, "Programmer");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(108, "Developer");
1 row created. SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
Hit a key to continue
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 53000 58000 E
105 Robert 15-JAN-84 31000 36000 W
116 Linda 30-JUL-87 43000 53000 E
117 David 31-DEC-90 78000 85000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected. SQL> select * from job; Hit a key to continue
EMPNO JOBTITLE
----------
101 Painter
122 Tester
123 Dediator
104 Chemist
105 Accountant
116 Manager
117 Programmer
108 Developer
8 rows selected. SQL> SQL> SQL> SQL> -- Use an analytical function in a WHERE clause SQL> SQL> SQL> SELECT *
2 FROM 3 (SELECT empno, ename, orig_salary, 4 DENSE_RANK() OVER(ORDER BY orig_salary) d_rank 5 FROM employee) x 6 WHERE x.d_rank = 3;
Hit a key to continue
EMPNO ENAME ORIG_SALARY D_RANK
---------- ----------- ----------
105 Robert 31000 3
SQL> SQL> SQL> SQL> drop table job; Table dropped. SQL> SQL> -- clean the table SQL> drop table Employee; Table dropped. SQL> SQL>
</source>
Use ROWNUM in where clause
<source lang="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 ROWNUM, ename
2 FROM emp 3 WHERE ROWNUM < 6; ROWNUM ENAME
----------
1 SMITH
2 ALLEN
3 WARD
4 JONES
5 MARTIN
Page 1
SQL> SQL> drop table emp; Table dropped.
</source>
Use Trunc in where clause
<source lang="sql">
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> SQL> SELECT * FROM product WHERE TRUNC(last_stock_date) = "09-JUL-00"; no rows selected SQL> SQL> SQL> SQL> DROP TABLE product; Table dropped. SQL> SQL>
</source>
Using the WHERE, GROUP BY, and HAVING Clauses Together
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 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> -- Using the WHERE, GROUP BY, and HAVING Clauses Together SQL> SQL> --the WHERE clause first filters the rows, SQL> --then the remaining rows are grouped into blocks by the GROUP BY clause, SQL> --and finally the row groups are filtered by the HAVING clause. SQL> SQL> SELECT city, AVG(salary)
2 FROM employee 3 WHERE salary < 7000 4 GROUP BY city 5 HAVING AVG(salary) > 1500;
CITY AVG(SALARY)
-----------
New York 4322.78 Vancouver 3823.78 SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL>
</source>
Where clause converts text with date value format to date value
<source lang="sql">
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 ("S", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("M", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("C", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("R", 25, 10000, null); 1 row created. SQL> SQL> SELECT * FROM product; PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------- ---------------- ---------
S 99 1 15-JAN-03 M 75 1000 15-JAN-02 C 50 100 15-JAN-03 R 25 10000 SQL> SQL> SELECT * FROM product WHERE last_stock_date = "09-JUL-00"; no rows selected SQL> SQL> DROP TABLE product; Table dropped. SQL> SQL>
</source>
WHERE clause with a GROUP BY clause
<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> --WHERE clause with a GROUP BY clause: SQL> SQL> SELECT City, AVG(Salary) AS "Average Salary"
2 FROM Employee 3 WHERE Salary > 2000 4 GROUP BY City;
CITY Average Salary
--------------
New York 6110.28 Vancouver 4471.53 SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL>
</source>
