Oracle PL/SQL/Select Query/Select

Материал из SQL эксперт
Перейти к: навигация, поиск

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>