Oracle PL/SQL/Select Query/Select
Содержание
- 1 CONCATENATING TEXT
- 2 Employees from new york who have gifts
- 3 Find all employees who are younger than employee whose id is 9999
- 4 Math calculation in select
- 5 Minus in select
- 6 Plus in select statement
- 7 Returning All Columns
- 8 Returning Multiple Columns
- 9 Returning Rows Call
- 10 Selecting Categories That Contain Product
- 11 Selecting Products That Belong to Category
- 12 Selecting Products That Belong to Department with Join
- 13 SELECT statement uses the not equal (< >) operator in the WHERE clause
- 14 The following code provides a breakdown of the basic SELECT statement on the Oracle platform:
- 15 UPPER(SUBSTR(first_name, 2, 8)): Combining Functions
- 16 Use Arithmetic operators with literal values to derive values: add 5 to salary
- 17 Using select statement and char function to create insert statement
- 18 Using the > operator
CONCATENATING TEXT
<source lang="sql">
SQL> SQL> CREATE TABLE purchase (
2 product_name VARCHAR2(25), 3 product_price NUMBER(4,2), 4 sales_tax NUMBER(4,2), 5 purchase_date DATE, 6 salesperson VARCHAR2(3));
Table created. SQL> SQL> INSERT INTO purchase VALUES ("Product Name 1", 1, .08, "5-NOV-00", "AB"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 2", 2.5, .21, "29-JUN-01", "CD"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 3", 50.75, 4.19, "10-DEC-02", "EF"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 4", 99.99, 8.25, "31-AUG-03", "GH"); 1 row created. SQL> SQL> SQL> SELECT product_name || " was sold by " || salesperson FROM purchase; PRODUCT_NAME||"WASSOLDBY"||SALESPERSON
Product Name 1 was sold by AB Product Name 2 was sold by CD Product Name 3 was sold by EF Product Name 4 was sold by GH 4 rows selected. SQL> SQL> SQL> SQL> DROP TABLE purchase; Table dropped. SQL> SQL> SQL>
</source>
Employees from new york who have gifts
<source lang="sql">
SQL> SQL> create table emp(
2 emp_id integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,shortZipCode varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,company_name varchar2(50));
Table created. SQL> SQL> SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (1,"Jones","Joe","J","10 Park Ave","New York","NY","11202","3898","212", "221-4333","Big Company");
1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (2,"Smith","Sue","J","20 Rise Ave","New York","NY","11444","3898","212", "436-6773","Little Company");
1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (3,"X","Peggy","J","500 June St","New York","NY","45502","3668","212", "234-4444","Medium Company");
1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (4,"Murdy","Jill", null,"930 Eady St","New York","NY","45452","6458","212", "634-7733","Wilton Company");
1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (5,"Oper","Carl","L","19 Studio Drive","New York","NY","67672","3234","212", "243-4243","Wesson and Smith Company");
1 row created. SQL> SQL> create table gift(
2 gift_id integer primary key 3 ,emp_id integer 4 ,register_date date not null 5 ,total_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,message varchar2(100) 12 );
Table created. SQL> SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
2 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
2 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");
1 row created. SQL> SQL> SQL> select o.gift_id, c.emp_id, c.state
2 from gift o, emp c 3 where o.emp_id = c.emp_id 4 and c.state = "NY"; GIFT_ID EMP_ID ST
---------- --
1 1 NY 2 1 NY 3 2 NY 4 2 NY
SQL> SQL> SQL> drop table gift; Table dropped. SQL> drop table emp; Table dropped. SQL>
</source>
Find all employees who are younger than employee whose id is 9999
<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 empno = 99999);
no rows selected SQL> SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Math calculation in select
<source lang="sql">
SQL> SQL> CREATE TABLE purchase (
2 product_name VARCHAR2(25), 3 product_price NUMBER(4,2), 4 sales_tax NUMBER(4,2), 5 purchase_date DATE, 6 salesperson VARCHAR2(3));
Table created. SQL> SQL> INSERT INTO purchase VALUES ("Product Name 1", 1, .08, "5-NOV-00", "AB"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 2", 2.5, .21, "29-JUN-01", "CD"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 3", 50.75, 4.19, "10-DEC-02", "EF"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 4", 99.99, 8.25, "31-AUG-03", "GH"); 1 row created. SQL> SQL> SQL> SQL> SELECT product_name, sales_tax / product_price FROM purchase; PRODUCT_NAME SALES_TAX/PRODUCT_PRICE
-----------------------
Product Name 1 .08 Product Name 2 .084 Product Name 3 .082561576 Product Name 4 .082508251 4 rows selected. SQL> SQL> SQL> DROP TABLE purchase; Table dropped. SQL> SQL>
</source>
Minus in select
<source lang="sql">
SQL> SQL> CREATE TABLE purchase (
2 product_name VARCHAR2(25), 3 product_price NUMBER(4,2), 4 sales_tax NUMBER(4,2), 5 purchase_date DATE, 6 salesperson VARCHAR2(3));
Table created. SQL> SQL> INSERT INTO purchase VALUES ("Product Name 1", 1, .08, "5-NOV-00", "AB"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 2", 2.5, .21, "29-JUN-01", "CD"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 3", 50.75, 4.19, "10-DEC-02", "EF"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 4", 99.99, 8.25, "31-AUG-03", "GH"); 1 row created. SQL> SQL> SQL> SELECT product_name, 100 - product_price FROM purchase; PRODUCT_NAME 100-PRODUCT_PRICE
-----------------
Product Name 1 99 Product Name 2 97.5 Product Name 3 49.25 Product Name 4 .01 4 rows selected. SQL> SQL> SQL> DROP TABLE purchase; Table dropped. SQL> SQL> SQL>
</source>
Plus in select statement
<source lang="sql">
SQL> SQL> CREATE TABLE purchase (
2 product_name VARCHAR2(25), 3 product_price NUMBER(4,2), 4 sales_tax NUMBER(4,2), 5 purchase_date DATE, 6 salesperson VARCHAR2(3));
Table created. SQL> SQL> INSERT INTO purchase VALUES ("Product Name 1", 1, .08, "5-NOV-00", "AB"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 2", 2.5, .21, "29-JUN-01", "CD"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 3", 50.75, 4.19, "10-DEC-02", "EF"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 4", 99.99, 8.25, "31-AUG-03", "GH"); 1 row created. SQL> SQL> SQL> SQL> SELECT product_name, product_price + sales_tax FROM purchase; PRODUCT_NAME PRODUCT_PRICE+SALES_TAX
-----------------------
Product Name 1 1.08 Product Name 2 2.71 Product Name 3 54.94 Product Name 4 108.24 4 rows selected. SQL> SQL> SQL> DROP TABLE purchase; Table dropped. SQL> SQL>
</source>
Returning All Columns
<source lang="sql">
SQL> SQL> CREATE TABLE emp (
2 empID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL);
Table created. SQL> INSERT INTO emp (empID,Name) VALUES (1,"Tom"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (2,"Jack"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (3,"Mary"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (4,"Bill"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (5,"Cat"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (6,"Victor"); 1 row created. SQL> SQL> SQL> SELECT * FROM emp;
EMPID NAME
--------------------------------------------------
1 Tom 2 Jack 3 Mary 4 Bill 5 Cat 6 Victor
6 rows selected. SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Returning Multiple Columns
<source lang="sql">
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> 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,Comments) VALUES (2,5,39,0,"Simple"); 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, ExamID, Comments FROM SAT;
STUDENTID EXAMID
----------
COMMENTS
1 1
Satisfactory
1 2
Good result
2 3
Hard
2 5
Simple
2 6
5 rows selected. SQL> SQL> SQL> drop table SAT; Table dropped.
</source>
Returning Rows Call
<source lang="sql">
SQL> SQL> CREATE TABLE emp (
2 empID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL);
Table created. SQL> INSERT INTO emp (empID,Name) VALUES (1,"Tom"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (2,"Jack"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (3,"Mary"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (4,"Bill"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (5,"Cat"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (6,"Victor"); 1 row created. SQL> SQL> CREATE OR REPLACE PACKAGE emp_pkg
2 AS 3 TYPE studCur IS REF CURSOR; 4 PROCEDURE Getemp(o_StudCur OUT studCur); 5 END emp_pkg; 6 /
Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY emp_pkg
2 AS 3 PROCEDURE Getemp(o_StudCur OUT studCur) 4 IS 5 BEGIN 6 OPEN o_StudCur FOR 7 SELECT empID, Name FROM emp; 8 END Getemp; 9 END emp_pkg; 10 /
Package body created. SQL> SET SERVEROUT ON SQL> DECLARE
2 TYPE studCurType IS REF CURSOR; 3 mycur studCurType; 4 studrow emp%ROWTYPE; 5 BEGIN 6 emp_pkg.Getemp(mycur); 7 FETCH mycur INTO studrow; 8 WHILE mycur%FOUND 9 LOOP 10 dbms_output.put_line(studrow.empID || " " || 11 studrow.Name); 12 FETCH mycur INTO studrow; 13 END LOOP; 14 END; 15 /
1 Tom 2 Jack 3 Mary 4 Bill 5 Cat 6 Victor PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped.
</source>
Selecting Categories That Contain Product
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE ProductCategory (
2 ProductID INT NOT NULL, 3 CategoryID INT NOT NULL, 4 PRIMARY KEY (ProductID, CategoryID) 5 );
Table created. SQL> SQL> SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5); 1 row created. SQL> SQL> SQL> CREATE TABLE Category (
2 CategoryID INT NOT NULL PRIMARY KEY, 3 DepartmentID INT NOT NULL, 4 Name VARCHAR(50) NOT NULL, 5 Description VARCHAR (200) NULL);
Table created. SQL> SQL> CREATE SEQUENCE CategoryIDSeq; Sequence created. SQL> SQL> CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
2 BEFORE INSERT ON Category 3 FOR EACH ROW 4 BEGIN 5 SELECT CategoryIDSeq.NEXTVAL 6 INTO :NEW.CategoryID FROM DUAL; 7 END; 8 /
Trigger created. SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, "Local", "In town"); 1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, "Remote", "Telecommute"); 1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (2, "Masks", "By bits"); 1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, "Wireless", "Not connected"); 1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, "Wired", "Connected"); 1 row created. SQL> SQL> SQL> SELECT Category.CategoryID, Name
2 FROM Category INNER JOIN ProductCategory 3 ON Category.CategoryID = ProductCategory.CategoryID 4 AND ProductCategory.ProductID = 6;
CATEGORYID NAME
--------------------------------------------------
3 Masks 4 Wireless
2 rows selected. SQL> SQL> SQL> SELECT CategoryID, Name
2 FROM Category 3 WHERE CategoryID IN 4 (SELECT CategoryID 5 FROM ProductCategory 6 WHERE ProductCategory.ProductID = 6);
CATEGORYID NAME
--------------------------------------------------
3 Masks 4 Wireless
2 rows selected. SQL> SQL> SQL> drop table Category; Table dropped. SQL> drop table ProductCategory; Table dropped. SQL> drop sequence CategoryIDSeq; Sequence dropped.
</source>
Selecting Products That Belong to Category
<source lang="sql">
SQL> SQL> SQL> SQL> CREATE TABLE Product (
2 ProductID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL, 4 Description VARCHAR(1000) NOT NULL, 5 Price NUMBER NULL, 6 ImagePath VARCHAR(50) NULL, 7 soldout NUMBER(1,0) NULL, 8 Promotion NUMBER(1,0) NULL);
Table created. SQL> SQL> CREATE SEQUENCE ProductIDSeq; Sequence created. SQL> SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
2 BEFORE INSERT ON Product 3 FOR EACH ROW 4 BEGIN 5 SELECT ProductIDSeq.NEXTVAL 6 INTO :NEW.ProductID FROM DUAL; 7 END; 8 /
Trigger created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Ruler", "Long",14.99, "ruler.jpg", 0, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Mouse", "Wireless",9.99, "mouse.jpg", 1, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Keyboard", "keyboard",3.75, "keyboard.jpg", 0, 0);
1 row created. SQL> SQL> CREATE TABLE ProductCategory (
2 ProductID INT NOT NULL, 3 CategoryID INT NOT NULL, 4 PRIMARY KEY (ProductID, CategoryID) 5 );
Table created. SQL> SQL> SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5); 1 row created. SQL> SQL> SELECT Name
2 FROM Product, ProductCategory 3 WHERE Product.ProductID = ProductCategory.ProductID 4 AND ProductCategory.CategoryID = 3;
NAME
Pen Ruler Desk Keyboard 4 rows selected. SQL> SQL> SQL> SELECT Name
2 FROM Product INNER JOIN ProductCategory 3 ON Product.ProductID = ProductCategory.ProductID 4 WHERE ProductCategory.CategoryID = 3;
NAME
Pen Ruler Desk Keyboard 4 rows selected. SQL> SQL> SQL> SQL> drop table product; Table dropped. SQL> drop sequence ProductIDSeq; Sequence dropped. SQL> drop table ProductCategory; Table dropped.
</source>
Selecting Products That Belong to Department with Join
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE Product (
2 ProductID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL, 4 Description VARCHAR(1000) NOT NULL, 5 Price NUMBER NULL, 6 ImagePath VARCHAR(50) NULL, 7 soldout NUMBER(1,0) NULL, 8 Promotion NUMBER(1,0) NULL);
Table created. SQL> SQL> CREATE SEQUENCE ProductIDSeq; Sequence created. SQL> SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
2 BEFORE INSERT ON Product 3 FOR EACH ROW 4 BEGIN 5 SELECT ProductIDSeq.NEXTVAL 6 INTO :NEW.ProductID FROM DUAL; 7 END; 8 /
Trigger created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Ruler","Long",14.99, "ruler.jpg", 0, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Mouse", "Wireless",9.99, "mouse.jpg", 1, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Keyboard","keyboard",3.75, "keyboard.jpg", 0, 0);
1 row created. SQL> SQL> SQL> SQL> SQL> SQL> CREATE TABLE ProductCategory (
2 ProductID INT NOT NULL, 3 CategoryID INT NOT NULL, 4 PRIMARY KEY (ProductID, CategoryID) 5 );
Table created. SQL> SQL> SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5); 1 row created. SQL> SQL> SQL> CREATE TABLE Category (
2 CategoryID INT NOT NULL PRIMARY KEY, 3 DepartmentID INT NOT NULL, 4 Name VARCHAR(50) NOT NULL, 5 Description VARCHAR (200) NULL);
Table created. SQL> SQL> CREATE SEQUENCE CategoryIDSeq; Sequence created. SQL> SQL> CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
2 BEFORE INSERT ON Category 3 FOR EACH ROW 4 BEGIN 5 SELECT CategoryIDSeq.NEXTVAL 6 INTO :NEW.CategoryID FROM DUAL; 7 END; 8 /
Trigger created. SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (1, "Local", "In town");
1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (1, "Remote", "Telecommute");
1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (2, "Masks", "By bits");
1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (3, "Wireless", "Not connected");
1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (3, "Wired", "Connected");
1 row created. SQL> SQL> SQL> SELECT Product.ProductID, Product.Name
2 FROM Product, ProductCategory, Category 3 WHERE Product.ProductID = ProductCategory.ProductID 4 AND ProductCategory.CategoryID = Category.CategoryID 5 AND Category.DepartmentID = 1 6 ORDER BY Product.Name; PRODUCTID NAME
--------------------------------------------------
5 Mouse 4 PC 2 Ruler
3 rows selected. SQL> SQL> SQL> SQL> SQL> drop table Product; Table dropped. SQL> drop table ProductCategory; Table dropped. SQL> drop table Category; Table dropped. SQL> drop sequence CategoryIDSeq; Sequence dropped. SQL> drop sequence ProductIDSeq; Sequence dropped. SQL>
</source>
SELECT statement uses the not equal (< >) operator in the WHERE 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> SQL> SQL> -- SELECT statement uses the not equal (<>) operator in the WHERE clause SQL> SQL> SELECT * FROM employee WHERE id <> 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 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 7 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL>
</source>
The following code provides a breakdown of the basic SELECT statement on the Oracle platform:
<source lang="sql">
SELECT [hint] [ DISTINCT | ALL ] <select_list> FROM <table_reference> [WHERE <condition>] [START WITH <condition> CONNECT BY <condition>] [GROUP BY <elements> ] [HAVING <condition> ] [ORDER BY <elements> ] --
</source>
UPPER(SUBSTR(first_name, 2, 8)): Combining Functions
<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> -- UPPER(SUBSTR(first_name, 2, 8)): Combining Functions SQL> SQL> SELECT first_name, UPPER(SUBSTR(first_name, 2, 8)) FROM employee; FIRST_NAME UPPER(SU
--------
Jason ASON Alison LISON James AMES Celia ELIA Robert OBERT Linda INDA David AVID James AMES 8 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL>
</source>
Use Arithmetic operators with literal values to derive values: add 5 to salary
<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"), 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("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2334.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"), 2334.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"), 2334.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"), 2334.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 2334.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2334.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 2334.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 2334.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 2334.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> --Use Arithmetic operators with literal values to derive values: + SQL> SELECT ID, First_Name, Salary + 5 FROM Employee; ID FIRST_NAME SALARY+5
---------- ----------
01 Jason 1239.56 02 Alison 2339.78 03 James 2339.78 04 Celia 2339.78 05 Robert 2339.78 06 Linda 2339.78 07 David 2339.78 08 James 2339.78 8 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
Using select statement and char function to create insert statement
<source lang="sql">
SQL> CREATE TABLE emp (
2 emp_id NUMBER, 3 ename VARCHAR2(40), 4 hire_date DATE DEFAULT sysdate, 5 end_date DATE, 6 rate NUMBER(5,2), 7 CONSTRAINT emp_pk PRIMARY KEY (emp_id) 8 );
Table created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300); 1 row created. SQL> SQL> SQL> SQL> SELECT "INSERT INTO emp" || chr(10)
2 || " (emp_id, rate," || chr(10) 3 || " hire_date, ename)" || chr(10) 4 || "VALUES (" || TO_CHAR(emp_id) || "," || chr(10) 5 || " " || NVL(TO_CHAR(rate),"NULL") 6 || "," || chr(10) 7 || CASE WHEN hire_date IS NOT NULL then 8 " TO_DATE(""" || TO_CHAR(hire_date,"MM/DD/YYYY") 9 || ", ""MM/DD/YYYY"")" || chr(10) 10 ELSE 11 " NULL" || chr(10) 12 END 13 || " """ || ename || """);" 14 FROM emp 15 WHERE end_date IS NULL;
INSERT INTO emp
(emp_id, rate, hire_date, ename)
VALUES (101,
169, TO_DATE("11/15/1961, "MM/DD/YYY
Y")
"Mary");
INSERT INTO emp
(emp_id, rate, hire_date, ename)
VALUES (105,
121, TO_DATE("06/15/2004, "MM/DD/YYY
Y")
"Mike");
INSERT INTO emp
(emp_id, rate, hire_date, ename)
VALUES (107,
45, TO_DATE("01/02/2004, "MM/DD/YYY
Y")
"Less");
INSERT INTO emp
(emp_id, rate, hire_date, ename)
VALUES (111,
100, TO_DATE("08/23/1976, "MM/DD/YYY
Y")
"Tike");
4 rows selected. SQL> SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Using the > operator
<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> -- Using the > operator: SQL> SQL> SELECT id, first_name FROM employee WHERE salary > 2000; ID FIRST_NAME
----------
02 Alison 03 James 04 Celia 05 Robert 06 Linda 07 David 6 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>