Oracle PL/SQL Tutorial: Introduction

1. dual

1.1. The dual Table

dual is a table that contains a single row.

The dual table has one VARCHAR2 column named dummy.

dual contains a single row with the value X.

describe dual;

Name Null Type ----- ---- ----------- DUMMY VARCHAR2(1)

select * from dual;

1.2. Do simple calculation by using dual

select 123 * 456 from dual; select sysdate from dual;

2. Arithmetic operator

2.1. The normal rules of arithmetic operator precedence apply in SQL

The normal rules of arithmetic operator precedence apply in SQL:

multiplication and division are performed first.
followed by addition and subtraction.
If operators of the same precedence are used, they are performed from left to right.

select 10 * 12 / 3 - 1 from dual;

2.2. Use parentheses () to specify the order of execution for the operators

select 10 * (12 / 3 - 1) from dual;

3. Comparison Operators

3.1. Using Comparison Operators

There are many comparison operators that you can use in a WHERE clause:

The following table lists the comparison operators.

Operator	Description
=	Equal
<> or !=	Not equal
<	Less than
>	Greater than
<=	Less than or equal
>=	Greater than or equal
ANY	Compares one value with any value in a list
ALL	Compares one value with all values in a list

3.2. Uses the not equal (<>) operator in the WHERE clause

-- create demo table CREATE TABLE Employee ( ID VARCHAR2(4 BYTE) NOT NULL, First_Name VARCHAR2(10 BYTE), Last_Name VARCHAR2(10 BYTE), Start_Date DATE, End_Date DATE, Salary NUMBER(8,2), City VARCHAR2(10 BYTE), Description VARCHAR2(15 BYTE) ); -- prepare data insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('01', 'Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('03', 'James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('04', 'Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('05', 'Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('06', 'Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78, 'New York', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('07', 'David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78, 'New York', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('08', 'James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78, 'Vancouver', 'Tester'); -- display data in the table select * from Employee; select * from employee where id <> 2; -- clean the table drop table employee;

3.3. Using the > operator

-- create demo table CREATE TABLE Employee ( ID VARCHAR2(4 BYTE) NOT NULL, First_Name VARCHAR2(10 BYTE), Last_Name VARCHAR2(10 BYTE), Start_Date DATE, End_Date DATE, Salary NUMBER(8,2), City VARCHAR2(10 BYTE), Description VARCHAR2(15 BYTE) ); -- prepare data insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('01', 'Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('03', 'James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('04', 'Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('05', 'Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('06', 'Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78, 'New York', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('07', 'David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78, 'New York', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('08', 'James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78, 'Vancouver', 'Tester'); -- display data in the table select * from Employee; select id, first_name from employee where id > 3; -- clean the table drop table employee;

3.4. Use >= operator (2)

-- create demo table CREATE TABLE Employee ( ID VARCHAR2(4 BYTE) NOT NULL, First_Name VARCHAR2(10 BYTE), Last_Name VARCHAR2(10 BYTE), Start_Date DATE, End_Date DATE, Salary NUMBER(8,2), City VARCHAR2(10 BYTE), Description VARCHAR2(15 BYTE) ); -- prepare data insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('01', 'Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('03', 'James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('04', 'Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('05', 'Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('06', 'Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78, 'New York', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('07', 'David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78, 'New York', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('08', 'James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78, 'Vancouver', 'Tester'); -- display data in the table select * from Employee; select id, first_name from employee where id >= 3; -- clean the table drop table employee;

3.5. Use <= operator

-- create demo table CREATE TABLE Employee ( ID VARCHAR2(4 BYTE) NOT NULL, First_Name VARCHAR2(10 BYTE), Last_Name VARCHAR2(10 BYTE), Start_Date DATE, End_Date DATE, Salary NUMBER(8,2), City VARCHAR2(10 BYTE), Description VARCHAR2(15 BYTE) ); -- prepare data insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('01', 'Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('03', 'James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('04', 'Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('05', 'Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('06', 'Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78, 'New York', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('07', 'David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78, 'New York', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('08', 'James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78, 'Vancouver', 'Tester'); -- display data in the table select * from Employee; select id, first_name from employee where id <= 3; -- clean the table drop table employee;

3.6. Larger and equals

-- create demo table create table emp ( empno NUMBER(4) constraint E_PK primary key , ename VARCHAR2(8) , init VARCHAR2(5) , job VARCHAR2(8) , mgr NUMBER(4) , bdate DATE , sal NUMBER(6,2) , comm NUMBER(6,2) , deptno NUMBER(2) default 10 ); -- prepare data insert into emp values(1, 'Tom', 'N', 'Coder', 13, date '1965-12-17', 800 , NULL, 20); insert into emp values(2, 'Jack', 'JAM', 'Tester', 6, date '1961-02-20', 1600, 300, 30); insert into emp values(3, 'Wil', 'TF' , 'Tester', 6, date '1962-02-22', 1250, 500, 30); insert into emp values(4, 'Jane', 'JM', 'Designer', 9, date '1967-04-02', 2975, NULL, 20); insert into emp values(5, 'Mary','P', 'Tester', 6, date '1956-09-28', 1250, 1400, 30); insert into emp values(6, 'Black', 'R', 'Designer', 9, date '1963-11-01', 2850, NULL, 30); insert into emp values(7, 'Chris', 'AB', 'Designer', 9, date '1965-06-09', 2450, NULL, 10); insert into emp values(8, 'Smart', 'SCJ', 'Coder', 4, date '1959-11-26', 3000, NULL, 20); insert into emp values(9, 'Peter', 'CC', 'Designer', NULL, date '1952-11-17', 5000, NULL, 10); insert into emp values(10, 'Take', 'JJ', 'Tester',6, date '1968-09-28', 1500, 0, 30); insert into emp values(11, 'Ana', 'AA', 'Coder', 8, date '1966-12-30', 1100, NULL, 20); insert into emp values(12, 'Jane', 'R', 'Manager', 6, date '1969-12-03', 800 , NULL, 30); insert into emp values(13, 'Fake', 'MG', 'Coder', 4, date '1959-02-13', 3000, NULL, 20); insert into emp values(14, 'Mike', 'TJA','Manager', 7, date '1962-01-23', 1300, NULL, 10); -- display data in the table select * from emp; select ename, init, sal from emp where sal >= 3000; -- clean the table drop table emp;

3.7. Less and equals

-- create demo table create table emp ( empno NUMBER(4) constraint E_PK primary key , ename VARCHAR2(8) , init VARCHAR2(5) , job VARCHAR2(8) , mgr NUMBER(4) , bdate DATE , sal NUMBER(6,2) , comm NUMBER(6,2) , deptno NUMBER(2) default 10 ); -- prepare data insert into emp values(1, 'Tom', 'N', 'Coder', 13, date '1965-12-17', 800 , NULL, 20); insert into emp values(2, 'Jack', 'JAM', 'Tester', 6, date '1961-02-20', 1600, 300, 30); insert into emp values(3, 'Wil', 'TF' , 'Tester', 6, date '1962-02-22', 1250, 500, 30); insert into emp values(4, 'Jane', 'JM', 'Designer', 9, date '1967-04-02', 2975, NULL, 20); insert into emp values(5, 'Mary','P', 'Tester', 6, date '1956-09-28', 1250, 1400, 30); insert into emp values(6, 'Black', 'R', 'Designer', 9, date '1963-11-01', 2850, NULL, 30); insert into emp values(7, 'Chris', 'AB', 'Designer', 9, date '1965-06-09', 2450, NULL, 10); insert into emp values(8, 'Smart', 'SCJ', 'Coder', 4, date '1959-11-26', 3000, NULL, 20); insert into emp values(9, 'Peter', 'CC', 'Designer', NULL, date '1952-11-17', 5000, NULL, 10); insert into emp values(10, 'Take', 'JJ', 'Tester',6, date '1968-09-28', 1500, 0, 30); insert into emp values(11, 'Ana', 'AA', 'Coder', 8, date '1966-12-30', 1100, NULL, 20); insert into emp values(12, 'Jane', 'R', 'Manager', 6, date '1969-12-03', 800 , NULL, 30); insert into emp values(13, 'Fake', 'MG', 'Coder', 4, date '1959-02-13', 3000, NULL, 20); insert into emp values(14, 'Mike', 'TJA','Manager', 7, date '1962-01-23', 1300, NULL, 10); -- display data in the table select * from emp; select ename, job, deptno from emp where deptno <= 10; -- clean the table drop table emp;

3.8. invalid relational operator

-- create demo table create table emp ( empno NUMBER(4) constraint E_PK primary key , ename VARCHAR2(8) , init VARCHAR2(5) , job VARCHAR2(8) , mgr NUMBER(4) , bdate DATE , sal NUMBER(6,2) , comm NUMBER(6,2) , deptno NUMBER(2) default 10 ); -- prepare data insert into emp values(1, 'Tom', 'N', 'Coder', 13, date '1965-12-17', 800 , NULL, 20); insert into emp values(2, 'Jack', 'JAM', 'Tester', 6, date '1961-02-20', 1600, 300, 30); insert into emp values(3, 'Wil', 'TF' , 'Tester', 6, date '1962-02-22', 1250, 500, 30); insert into emp values(4, 'Jane', 'JM', 'Designer', 9, date '1967-04-02', 2975, NULL, 20); insert into emp values(5, 'Mary','P', 'Tester', 6, date '1956-09-28', 1250, 1400, 30); insert into emp values(6, 'Black', 'R', 'Designer', 9, date '1963-11-01', 2850, NULL, 30); insert into emp values(7, 'Chris', 'AB', 'Designer', 9, date '1965-06-09', 2450, NULL, 10); insert into emp values(8, 'Smart', 'SCJ', 'Coder', 4, date '1959-11-26', 3000, NULL, 20); insert into emp values(9, 'Peter', 'CC', 'Designer', NULL, date '1952-11-17', 5000, NULL, 10); insert into emp values(10, 'Take', 'JJ', 'Tester',6, date '1968-09-28', 1500, 0, 30); insert into emp values(11, 'Ana', 'AA', 'Coder', 8, date '1966-12-30', 1100, NULL, 20); insert into emp values(12, 'Jane', 'R', 'Manager', 6, date '1969-12-03', 800 , NULL, 30); insert into emp values(13, 'Fake', 'MG', 'Coder', 4, date '1959-02-13', 3000, NULL, 20); insert into emp values(14, 'Mike', 'TJA','Manager', 7, date '1962-01-23', 1300, NULL, 10); -- display data in the table select * from emp; select ename, job, deptno from emp where deptno NOT > 10; -- clean the table drop table emp;

4. Logical Operators

4.1. Using the Logical Operators

There are three logical operators that may be used in a WHERE clause.

The logical operators allow you to limit rows based on logical conditions.

The logical operators are listed in the following table:

Operator	Description
x AND y	Returns true when both x and y are true
x OR y	Returns true when either x or y is true
NOT x	Returns true if x is false, and returns false if x is true

4.2. Use the AND operator to retrieve rows

-- create demo table CREATE TABLE Employee ( ID VARCHAR2(4 BYTE) NOT NULL, First_Name VARCHAR2(10 BYTE), Last_Name VARCHAR2(10 BYTE), Start_Date DATE, End_Date DATE, Salary NUMBER(8,2), City VARCHAR2(10 BYTE), Description VARCHAR2(15 BYTE) ); -- prepare data insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('01', 'Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('03', 'James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('04', 'Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('05', 'Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('06', 'Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78, 'New York', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('07', 'David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78, 'New York', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('08', 'James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78, 'Vancouver', 'Tester'); -- display data in the table select * from Employee; select * from employee where start_date > '01-JAN-1970' and id > 3; -- clean the table drop table employee;

4.3. Use the OR operator to retrieve rows

-- create demo table CREATE TABLE Employee ( ID VARCHAR2(4 BYTE) NOT NULL, First_Name VARCHAR2(10 BYTE), Last_Name VARCHAR2(10 BYTE), Start_Date DATE, End_Date DATE, Salary NUMBER(8,2), City VARCHAR2(10 BYTE), Description VARCHAR2(15 BYTE) ); -- prepare data insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('01', 'Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('03', 'James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('04', 'Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('05', 'Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('06', 'Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78, 'New York', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('07', 'David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78, 'New York', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('08', 'James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78, 'Vancouver', 'Tester'); -- display data in the table select * from Employee; select * from employee where start_date > '01-JAN-1970' or id > 3; -- clean the table drop table employee;

4.4. Use NOT in where clause

-- create demo table CREATE TABLE Employee ( ID VARCHAR2(4 BYTE) NOT NULL, First_Name VARCHAR2(10 BYTE), Last_Name VARCHAR2(10 BYTE), Start_Date DATE, End_Date DATE, Salary NUMBER(8,2), City VARCHAR2(10 BYTE), Description VARCHAR2(15 BYTE) ); -- prepare data insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('01', 'Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('03', 'James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('04', 'Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('05', 'Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('06', 'Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78, 'New York', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('07', 'David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78, 'New York', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('08', 'James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78, 'Vancouver', 'Tester'); -- display data in the table select * from Employee; select * from employee where not(start_date > '01-JAN-1970'); -- clean the table drop table employee;

4.5. Understanding Operator Precedence

If you combine AND and OR in the same expression, the AND operator takes precedence over the OR operator.

The comparison operators take precedence over AND.

You can override these using parentheses.

-- create demo table CREATE TABLE Employee ( ID VARCHAR2(4 BYTE) NOT NULL, First_Name VARCHAR2(10 BYTE), Last_Name VARCHAR2(10 BYTE), Start_Date DATE, End_Date DATE, Salary NUMBER(8,2), City VARCHAR2(10 BYTE), Description VARCHAR2(15 BYTE) ); -- prepare data insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('01', 'Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('03', 'James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('04', 'Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('05', 'Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('06', 'Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78, 'New York', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('07', 'David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78, 'New York', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('08', 'James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78, 'Vancouver', 'Tester'); -- display data in the table select * from Employee; select * from employee where start_date > '01-JAN-1970' or id < 2 and first_name like '%e'; -- clean the table drop table employee;

5. Null Values

5.1. Understanding Null Values

A database use null value to represent a unknown value.

A null value is not a blank string.

A null value means the value for the column is unknown.

When you select a column that contains a null value, you see nothing in that column.

-- create demo table CREATE TABLE Employee ( ID VARCHAR2(4 BYTE) NOT NULL, First_Name VARCHAR2(10 BYTE), Last_Name VARCHAR2(10 BYTE), Start_Date DATE, End_Date DATE, Salary NUMBER(8,2), City VARCHAR2(10 BYTE), Description VARCHAR2(15 BYTE) ); -- prepare data insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('01', 'Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('03', 'James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('04', 'Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('05', 'Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('06', 'Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78, 'New York', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('07', 'David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78, 'New York', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('08', null, 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78, 'Vancouver', 'Tester'); -- display data in the table select first_name from employee; -- clean the table drop table employee;

5.2. How do you tell the difference between a null value and a blank string if you retrieve all the rows?

The answer is to use one of Oracle's built-in functions: NVL().

NVL() allows you to convert a null value into another value.

NVL() accepts two parameters: a column, and the value that should be substituted.

In the following example, NVL() is used to convert a null value in the first_name column to the string Unknown First Name:

-- create demo table CREATE TABLE Employee ( ID VARCHAR2(4 BYTE) NOT NULL, First_Name VARCHAR2(10 BYTE), Last_Name VARCHAR2(10 BYTE), Start_Date DATE, End_Date DATE, Salary NUMBER(8,2), City VARCHAR2(10 BYTE), Description VARCHAR2(15 BYTE) ); -- prepare data insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('01', 'Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('03', 'James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('04', 'Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('05', 'Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('06', 'Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78, 'New York', 'Tester'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('07', 'David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78, 'New York', 'Manager'); insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('08', null, 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78, 'Vancouver', 'Tester'); -- display data in the table select * from employee; select id, first_name, last_name, NVL(first_name, 'Unknown First Name') from employee; -- clean the table drop table employee;

6. Print

6.1. Print: output the value of a predefined variable

SQL> variable average_salary number; SQL> update employee 2 set salary = salary * 0.75 3 returning avg(salary) into :average_salary; 8 rows updated. SQL> print average_salary; AVERAGE_SALARY -------------- 3053.81875 SQL>

7. Variable

7.1. Define variable and use it in SQL command

SQL> variable average_salary number; SQL> update employee 2 set salary = salary * 0.75 3 returning avg(salary) into :average_salary; 8 rows updated. SQL> print average_salary; AVERAGE_SALARY -------------- 3053.81875 SQL>

8. Comments

8.1. Use multi-line comment blocks

SQL> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> /* SQL> DECLARE SQL> v_price BOOKS.PRICE%TYPE; SQL> BEGIN SQL> SQL> SQL> EXCEPTION SQL> WHEN OTHERS SQL> THEN DBMS_OUTPUT.PUT_LINE (SQLERRM); SQL> END; SQL> / SQL> SQL> */ SQL>

8.2. multiple-line commenting

SQL> /* SQL> This script is designed to show how multiple-line commenting SQL> SQL> SQL> works. SQL> SQL> */

8.3. Use single line comments

SQL> -- create demo table

9. Terminology

9.1. Understanding basic database terminology

A database consists of tables and columns.

Database Design Terminology

Logical/Relational	Logical/Object-Oriented	Physical Implementation
Entity	Class	Table
Attribute	Attribute	Column
Instance	Object	Row

Entity: An entity maps something in the real world. For example, departments within an organization, employees, or sales.

Attribute: Represent information about an entity instance or an object. For example, the birth date or Social Security number of an employee.

Entities (classes) are implemented in the database as tables.

Attributes are implemented in the database as columns.

Instances (objects) are implemented in the database as rows.

A primary key uniquely identifies a specific instance of an entity.

No two instances of an entity can have the same primary key.

The values of all parts of the primary key must never be null.

The most common types of primary keys in relational databases are ID numbers.

Sometimes more than one attribute (or sets of attributes) can be used as a primary key.

10. Database Normalization

10.1. Introducing database normalization

Database normalization is useful for several reasons:

It helps to build a structure that is logical and easy to maintain.
Normalized databases are the industry standard.
Retrieving data will be easier.

First Normal Form means that the database doesn't contain any repeating attributes.

Violations of Second Normal Form occur when the table contains attributes that depend on a portion of the primary key.

Second Normal Form violations can exist only when you have a multi-column primary key.

Third Normal Form violations occur when a transitive dependency exists.

All attributes in entities (columns in tables) must be dependent upon the primary key or one of the candidate keys and not on other attributes.

通过 Wiz 发布

posted @ 2012-01-17 16:55 __BSD__ 阅读(224) 评论(0) 编辑收藏举报

刷新页面返回顶部