CREATE OR REPLACE VIEW test_v AS
SELECT owner, table_name
FROM all_tab_columns;
 
-- any valid sql statement can follow the create or
-- replace statement

SELECT sal
FROM emp
WHERE deptno = 30;

SELECT p.last_name, t.title_name
FROM person p, title t
WHERE p.title_1 = t.title_abbrev;
 
SELECT p.last_name, r.role_name
FROM person p, person_role_ie i, person_role r
WHERE p.person_id = i.person_id
AND i.role_id = r.role_id;
 
Left Outer Join
 
SELECT p.last_name, t.title_name
FROM person p, role r
WHERE p.title_1 = r.title(+);
 
Returns ALL records FROM role TABLE AND ALL matching records FROM person TABLE
 
 
 
Right Outer Join
 
SELECT p.last_name, t.title_name
FROM person p, role r
WHERE p.title_1 (+)= r.title;
 
Returns ALL records FROM person TABLE AND ALL matching records FROM role TABLE

DECLARE
    salary          emp.sal%TYPE;
    mgr_num         emp.mgr%TYPE;
    last_name       emp.ename%TYPE;
    starting_empno  CONSTANT NUMBER(4) := 7902;
BEGIN
    SELECT sal, mgr INTO salary, mgr_num FROM emp
        WHERE empno = starting_empno;
    WHILE salary < 4000 LOOP
        SELECT sal, mgr, ename INTO salary, mgr_num, last_name
            FROM emp
            WHERE empno = mgr_num;
    END LOOP;
 
    INSERT INTO temp VALUES (NULL, salary, last_name);
    COMMIT;
END;

WITH <alias_one> AS
     (subquery_sql_statement)
          <alias_two> AS
     (sql_statement_from_alias_one)
SELECT <column_name_list>
FROM <alias_one>, <alias_two>
WHERE <join_condition>;
 
WITH RT1 AS
     (SELECT inst_id FROM gv$session),
          RT2 AS
     (SELECT UNIQUE inst_id FROM RT1
     UNION ALL
     SELECT UNIQUE inst_id FROM RT1)
SELECT /*+ MATERIALIZE */ *
FROM RT1, RT2
WHERE RT1.inst_id = RT2.inst_id;

WITH <alias_name> AS (subquery_sql_statement)
     SELECT <column_name_list> FROM <alias>;
     WITH xyz AS (SELECT dummy FROM dual)
SELECT dummy FROM xyz;

SELECT LOWER 'Harold Stassen' FROM dual;
 
Returns - harold stassen

SELECT REVERSE ('fundamentalist') FROM dual;
 
Returns - tsilatnemadnuf

SELECT UPPER('Harold Stassen') FROM dual;
 
Returns HAROLD STASSEN

-- String parsing using both SUBSTR And INSTR
 
 
- get ALL characters up TO the FIRST comma
SELECT SUBSTR('abc,def,ghi', 1 ,INSTR('abc,def,ghi', ',', 1, 1)-1)
FROM dual;
 
 
-- get the character(s) in between the commas
SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 1)+1,
INSTR('abc,def,ghi',',',1,2)-INSTR('abc,def,ghi',',',1,1)-1)
FROM dual;
 
 
-- get the character(s) after the last comma
SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 2)+1)
FROM dual;
 
 
-- find the first blank from the right
-- the -1 parameter of INSTR indicates that we're searching
-- for the first occurrence going backwards from the end of
-- the column.
 
     SELECT SUBSTR(name
                 ,INSTR(name,' ',-1)+1
                 ) AS surname
     FROM test_table
 
 
 
-- a PL/SQL function that takes two strings representing a
-- list of numbers separated by commas and returns a string
-- representing the list of each nth element added together.
 
CREATE OR REPLACE FUNCTION test_func(p_arg1 VARCHAR2, p_arg2 VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
 IF ( INSTR(p_arg1,',') = 0 AND INSTR(p_arg2,',') = 0 ) THEN
  RETURN TO_NUMBER(p_arg1) + TO_NUMBER(p_arg2);
 ELSIF (INSTR(p_arg1,',') = 0 OR INSTR(p_arg2,',') = 0) THEN
  raise_application_error(-20001, 'Length of the strings are not equal');
 ELSE
  RETURN TO_CHAR(TO_NUMBER(SUBSTR(p_arg1, 1, INSTR(p_arg1,',') - 1)) + TO_NUMBER(SUBSTR(p_arg2, 1, INSTR(p_arg2,',') - 1)))
         ||','||
         test_func(SUBSTR(p_arg1, INSTR(p_arg1,',') + 1 ), SUBSTR(p_arg2, INSTR(p_arg2,',') + 1 ));
 END IF;
END;
/
 
 
 
-- the Linux command "basename" is most famous for taking a
-- full file path string and stripping away the leading path
-- component, returning just the name of the file. This can
-- be emulated in PL/SQL with calls to SUBSTR and INSTR,
-- like this:
 
SUBSTR(dirname,INSTR(dirname,'/',-1)+1)

TIMESTAMP_TO_SCN(<timestamp>)
 
 
-- The following w3mentor inserts a row into the oe.orders table
-- and then uses TIMESTAMP_TO_SCN to determine the system change
-- number of the insert operation. (The actual SCN returned will
-- differ on each system.)
 
INSERT INTO orders (order_id, order_date, customer_id, order_total)
   VALUES (5000, SYSTIMESTAMP, 188, 2345);
1 ROW created.
 
COMMIT;
COMMIT complete.
 
SELECT TIMESTAMP_TO_SCN(order_date) FROM orders
   WHERE order_id = 5000;
 
TIMESTAMP_TO_SCN(ORDER_DATE)
----------------------------
                      574100

TO_YMINTERVAL(<char>) RETURN YMINTERVAL
 
-- calculate for each employee in the a date that is one year
-- and four months after the hire date:
 
SELECT hire_date, hire_date + TO_YMINTERVAL('01-04') "16 months"
   FROM employees;
 
HIRE_DATE 16 months
--------- ---------
17-JUN-87 17-SEP-88
21-SEP-89 21-JAN-91
13-JAN-93 13-MAY-94
03-JAN-90 03-MAY-91
21-MAY-91 21-SEP-92

UPDATE <table_name> <alias>
SET (<column_name>,<column_name> ) = (
   SELECT (<column_name>, <column_name>)
   FROM <table_name>
   WHERE <alias.column_name> = <alias.column_name>)
WHERE <column_name> <condition> <value>;
 
/*-------------------------------------------------*/
 
UPDATE indirect_customerb b
   SET (b.class_of_trade) =
       (SELECT a.classoftrade
          FROM gpo_memberships a
              ,(SELECT name,importance
                  FROM COT_importance) c
         WHERE a.dea = b.industry_id
           AND a.classoftrade = c.name
           AND c.importance = (SELECT MIN(importance)
                                 FROM COT_importance
                                WHERE c.name = name)
       )
 WHERE EXISTS (SELECT 'foo' FROM gpo_memberships c
                WHERE c.dea = b.industry_id)
 
/*-------------------------------------------------
mysql> select * from Course;
+----------+----------------------+---------+
| CourseID | Name                 | Credits |
+----------+----------------------+---------+
|        1 | Mediaeval Romanian   |       5 |
|        2 | Philosophy           |       5 |
|        3 | History of Computing |       5 |
+----------+----------------------+---------+
3 rows in set (0.00 sec)
 
mysql> SELECT Name FROM Course
    -> WHERE CourseID IN
    -> (
    -> SELECT CourseID from EXAM
    -> WHERE SustainedOn='26-MAR-03'
    -> );
Empty set, 1 warning (0.00 sec)
 
 
-------------------------------------------------*/
 
DROP TABLE Course;
DROP TABLE Exam;
 
CREATE TABLE Course (
   CourseID INT NOT NULL PRIMARY KEY,
   Name     VARCHAR(50),
   Credits  INT)
TYPE = InnoDB;
 
CREATE TABLE Exam (
   ExamID      INT NOT NULL PRIMARY KEY,
   CourseID    INT NOT NULL,
   SustainedOn DATE,
   Comments    VARCHAR(255),
 
   INDEX       examcourse_index(CourseID)
 
 
)TYPE = InnoDB;
 
 
INSERT INTO Course (CourseID,Name,Credits) VALUES (1,'Mediaeval Romanian',5);
INSERT INTO Course (CourseID,Name,Credits) VALUES (2,'Philosophy',5);
INSERT INTO Course (CourseID,Name,Credits) VALUES (3,'History of Computing',5);
 
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES
      (1,1,'2003-03-12','JavaScript');
 
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES
      (2,1,'2003-03-13','Java');
 
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES
      (3,2,'2003-03-11','Python');
 
INSERT INTO Exam (ExamID,CourseID,SustainedOn) VALUES
      (4,3,'2003-03-18','Swing');
 
 
SELECT * FROM Course;
 
SELECT Name FROM Course
WHERE CourseID IN
(
SELECT CourseID FROM EXAM
WHERE SustainedOn='26-MAR-03'
);

CREATE Name Column WITH "ROW" TYPE AND "TABLE" TYPE
CREATE OR REPLACE TYPE "ROW_NAME"
  AS OBJECT
  (FIRST VARCHAR2(60),
   LAST VARCHAR2(60),
   MIDDLE VARCHAR2(30),
   PREFIX VARCHAR2(10),
   SUFFIX VARCHAR2(10),
   CREATE_DATE DATE,
   CHANGE_DATE DATE,
   CREATE_USER VARCHAR2(30),
   CHANGE_USER VARCHAR2(30))
/
CREATE OR REPLACE TYPE "TAB_NAME"
  AS TABLE OF ROW_NAME
/
--> Create Person Column with "ROW" Type and "TABLE" Type
CREATE OR REPLACE TYPE "ROW_PERSON"
  AS OBJECT
  (SSN VARCHAR2(9),
   GENDER VARCHAR2(1),
   BDATE DATE,
   ETHNICITY VARCHAR2(2),
   MARITAL_STATUS VARCHAR2(2),
   HAIR VARCHAR2(10),
   EYES VARCHAR2(10),
   HEIGHT VARCHAR2(10),
   CREATE_DATE DATE,
   CHANGE_DATE DATE,
   CREATE_USER VARCHAR2(30),
   CHANGE_USER VARCHAR2(30))
/
CREATE OR REPLACE TYPE "TAB_PERSON"
  AS TABLE OF ROW_PERSON
/
--> Create Employment Column with "ROW" Type and "TABLE" Type
CREATE OR REPLACE TYPE "ROW_EMPLOYMENT"
  AS OBJECT
  (JOB_TITLE VARCHAR2(60),
   START_DATE DATE,
   TERMINATED VARCHAR2(1),
   END_DATE DATE,
   SALARY NUMBER(8,4),
   CREATE_DATE DATE,
   CHANGE_DATE DATE,
   CREATE_USER VARCHAR2(30),
   CHANGE_USER VARCHAR2(30)
   )
/
CREATE OR REPLACE TYPE "TAB_EMPLOYMENT"
  AS TABLE OF ROW_EMPLOYMENT
/
--> Create table with UID and TAB_NAME & TAB_PERSON tables as columns
CREATE TABLE "ENTITY"
   (    "UIDN"          NUMBER(10) NOT NULL,
        "NAME"          "TAB_NAME" DEFAULT "TAB_NAME"(),
        "PERSON"        "TAB_PERSON" DEFAULT "TAB_PERSON"(),
        "EMPLOYMENT"    "TAB_EMPLOYMENT" DEFAULT "TAB_EMPLOYMENT"(),
        "DEAD"          VARCHAR2(1) DEFAULT 'N',
        "DEAD_DATE"     DATE,
        "CREATE_DATE"   DATE DEFAULT SYSDATE NOT NULL ENABLE,
        "CHANGE_DATE"   DATE,
        "CREATE_USER"   VARCHAR2(30) DEFAULT USER,
        "CHANGE_USER"   VARCHAR2(30),
         PRIMARY KEY ("UIDN"))
 NESTED TABLE "NAME" STORE AS "ENTITY_NAME"
 NESTED TABLE "PERSON" STORE AS "ENTITY_PERSON"
 NESTED TABLE "EMPLOYMENT" STORE AS "ENTITY_EMPLOYMENT"
/
COMMENT ON TABLE "ENTITY" IS 'Entity Base Table with repeating data as Nested Tables';
COMMENT ON COLUMN "ENTITY"."UIDN" IS 'Unique Identifaction Number/KEY';
COMMENT ON COLUMN "ENTITY"."NAME" IS 'Nested Table Storing Name Data for an entity';
COMMENT ON COLUMN "ENTITY"."PERSON" IS 'Nested Table Storing Person Data for an entity';
COMMENT ON COLUMN "ENTITY"."EMPLOYMENT" IS 'Nested Table Storing Employment related data for an entity';
COMMENT ON COLUMN "ENTITY"."DEAD" IS 'Y if entity is dead else N';
COMMENT ON COLUMN "ENTITY"."DEAD_DATE" IS 'Date of Death';
 
--> Now lets insert some records
INSERT INTO entity VALUES
(1,TAB_NAME(ROW_NAME('Orion','Pax',NULL,NULL,NULL,SYSDATE,NULL,USER,NULL),
            ROW_NAME('Optimus','Prime',NULL,NULL,NULL,SYSDATE,NULL,USER,NULL)),
 TAB_PERSON(ROW_PERSON('000000000','M','01-JAN-1985','AB',NULL,NULL,NULL,'10 meters',SYSDATE,NULL,USER,NULL)),
 TAB_EMPLOYMENT(ROW_EMPLOYMENT('Autobot Leader','01-JAN-1985',NULL,NULL,1000,SYSDATE,NULL,USER,NULL)),
 'N',NULL,SYSDATE,NULL,USER,NULL);
INSERT INTO entity VALUES
(2,TAB_NAME(ROW_NAME('Megatron',NULL,NULL,NULL,NULL,SYSDATE,NULL,USER,NULL)),
 TAB_PERSON(ROW_PERSON('000000001','M','01-JAN-1985','DC',NULL,NULL,NULL,'10 meters',SYSDATE,NULL,USER,NULL)),
 TAB_EMPLOYMENT(ROW_EMPLOYMENT('Decepticon Leader','01-JAN-1985',NULL,NULL,995,SYSDATE,NULL,USER,NULL)),
 'N',NULL,SYSDATE,NULL,USER,NULL);
COMMIT;
 
--> View what we have inserted by flattening out the column collections
SELECT src.uidn,
       n.*,
       p.*,
       e.*,
       dead,
       dead_date,
       src.create_date,
       src.change_date,
       src.create_user,
       src.change_user
FROM ENTITY src,
     TABLE(src.name) n,
     TABLE(src.person) p,
     TABLE(src.employment) e;
 
--> Insert specific records into column of collections
 
/* Megatron's name changed during Transformers the Movie so lets insert
   a new record into the collection for Names.
   The first thing that needs to be done is to get the base record from
   the entity and the column with the collection that we want to update
   table we do so with this statement
     INSERT INTO
     TABLE(select entity.name
           from entity
           where entity.uidn = 2)
   The next part of the insert involves adding a record to the collection
   column
     ('Galvatron',NULL,NULL,NULL,
       NULL,SYSDATE,NULL,USER,NULL)
*/
 
INSERT INTO
TABLE(SELECT entity.name
      FROM entity
      WHERE entity.uidn = 2)
VALUES
('Galvatron',NULL,NULL,NULL,
 NULL,SYSDATE,NULL,USER,NULL);
COMMIT;
 
--> Update specific records inside of column collections
/* We'll use a similiar method to update Megatrons Galvatron's
   NAME record to have a last name
*/
 
UPDATE TABLE(SELECT entity.name
             FROM entity
             WHERE entity.uidn = 2)
SET LAST = 'Decepticon',
    change_date = SYSDATE,
    change_user = USER
WHERE FIRST = 'Galvatron';
COMMIT;
 
--> Delete specific records inside of column collections
 
/* Eventually Galvatron's name returns to being Megatron,
   so lets delete out the Galvatron record from the NAME
   collection
*/
 
DELETE TABLE(SELECT entity.name
             FROM entity
             WHERE entity.uidn = 2)
WHERE FIRST = 'Galvatron';
COMMIT;

UPDATE STUDENTS
SET
       EMPLOYEE_ID_FK                = '3789'  ,
       GENDER_CODE                   = 'F'  ,
       MARITAL_STATUS_CODE           = 'M'
WHERE  STUDENT_ID = 11
/
 
COMMIT WORK ;
/

UPDATE <table_name> PARTITION (<partition_name>)
SET <column_name> = <value>
WHERE <column_name> <condition> <value>;
 
 
-- The following w3mentor updates values in a single
-- partition of the read_messages table:
 
UPDATE read_messages PARTITION (user_2153) s
   SET s.msg_id = 1
   WHERE read_date = '2009-04-01';

UPDATE <table_name>
SET <column_name> = <value>
WHERE <column_name> = <value>
 
SELECT DISTINCT object_name
FROM my_table;
 
UPDATE my_table
SET object_name = 'LOAD'
WHERE object_name = 'DUAL';
 
COMMIT;
 
SELECT DISTINCT object_name
FROM my_table

UPDATE <table_name>
SET <column_name> = (
  SELECT <column_name>
  FROM <table_name
  WHERE <column_name> <condition> <value>)
WHERE <column_name> <condition> <value>;