Oracle PL/SQL/Select Query/Select — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 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
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>
Employees from new york who have gifts
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>
Find all employees who are younger than employee whose id is 9999
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.
Math calculation in select
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>
Minus in select
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>
Plus in select statement
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>
Returning All Columns
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.
Returning Multiple Columns
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.
Returning Rows Call
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.
Selecting Categories That Contain Product
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.
Selecting Products That Belong to Category
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.
Selecting Products That Belong to Department with Join
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>
SELECT statement uses the not equal (< >) operator in the WHERE clause
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>
The following code provides a breakdown of the basic SELECT statement on the Oracle platform:
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> ]
--
UPPER(SUBSTR(first_name, 2, 8)): Combining Functions
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>
Use Arithmetic operators with literal values to derive values: add 5 to salary
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>
Using select statement and char function to create insert statement
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.
Using the > operator
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>