Virtual training week 8
Download
1 / 65

Virtual training week 8 - PowerPoint PPT Presentation


  • 71 Views
  • Uploaded on

Virtual training week 8. Sections from 7 to 9. Create table copy_departments As (select * from departments) This syntax used to create table with same structure and same data type and all rows of the originL Table with other name. _____________________________________________

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Virtual training week 8' - yvonne-buckner


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Virtual training week 8

Virtual training week 8

Sections from 7 to 9

Oracle Academy Egypt


Create table copy_departments

As (select * from departments)

This syntax used to create table with same structure and same data type and all rows of the originL Table with other name.

_____________________________________________

Create table sales_reps

As (select * from employees

Where 1 = 2)

This syntax used to create table with same structure and same data type but without any row.

Oracle Academy Egypt









  • Multi-table inserts can be unconditional or conditional.

  • In an unconditional multitable insert Oracle will insert all rows returned by the subquery into all table insert clauses found in the statement.

  • In a conditional multi-table insert you can specify either ALL or FIRST.

ALL

If you specify ALL, the default value, then the database evaluates each WHEN

clause regardless of the results of the evaluation of any other WHEN clause.

For each WHEN clause whose condition evaluates to true, the database executes

the corresponding INTO clause list.

FIRST

If you specify FIRST, then the database evaluates each WHEN clause in the order

in which it appears in the statement. For the first WHEN clause that evaluates to

true, the database executes the corresponding INTO clause and skips subsequent

WHEN clauses for the given row.

ELSE clause For a given row, if no WHEN clause evaluates to true, then:

If you have specified an ELSE clause, then the database executes the INTO clause

list associated with the ELSE clause.

If you did not specify an else clause, then the database takes no action for that row.

Oracle Academy Egypt


Multitable insert Unconditional

INSERT ALL

INTO all_calls VALUES (caller_id, call_timestamp, call_duration, call_format)

INTO police_record_calls VALUES (caller_id, call_timestamp, recipient_caller)

SELECT caller_id, call_timestamp, call_duration, call_format , recipient_caller)

FROM calls

WHERE TRUNC(call_timestamp ) = TRUNC(SYSDATE )

Oracle Academy Egypt


Multi-Table Inserts Conditional

INSERT ALL

WHEN call_ format IN (‘tlk’,’txt’,’pic’) THEN

INTO all_calls VALUES (caller_id, call_timestamp, call_duration, call_format)

WHEN call_ format IN (‘tlk’,’txt’) THEN

INTO police_record_calls VALUES (caller_id, call_timestamp, recipient_caller)

WHEN call_duration < 50 AND call_type = ‘tlk’THEN

INTO short_calls VALUES (caller_id, call_timestamp, call_duration)

WHEN call_duration > = 50 AND call_type = ‘tlk’THEN

INTO long_calls VALUES (caller_id, call_timestamp, call_duration)

SELECT caller_id, call_timestamp, call_duration, call_format , recipient_caller)

FROM calls

WHERE TRUNC(call_timestamp ) = TRUNC(SYSDATE )

Oracle Academy Egypt






Note/ If no where condition is used all rows in the table will be deleted

Oracle Academy Egypt


Oracle Academy Egypt will be deleted


Oracle Academy Egypt will be deleted


Data copied from items table to copy_items table will be deleted

Oracle Academy Egypt


Oracle Academy Egypt will be deleted


Oracle Academy Egypt will be deleted


Oracle Academy Egypt will be deleted


Oracle Academy Egypt will be deleted


If you don’t enter any value a will be deleted0 will be entered, same time any other number (value) can be entered

The length of the character is up to 10 characters

Oracle Academy Egypt


In this case 0 will be added will be deleted

Oracle Academy Egypt


External tables
External Tables will be deleted

  • an external table would be used to store data migrated from older versions of the databases used by a company.

  • The syntax to create an external table

    Same syntax for creating table in addition to the following …..

    ORGANIZATION EXTERNAL -- tells Oracle to create an external table

    (TYPE ORACLE_LOADER -- of type Oracle Loader (an Oracle Product)

    DEFAULT DIRECTORY def_dir1 -- what is the name of the directory where the file exists

    ACCESS PARAMETERS -- how to read the file

    (RECORDS DELIMITED BY NEWLINE -- how to identify a new row starts

    FIELDS – the start of the external file field name and datatype specification

    ...

    LOCATION – name of the actual file containing the data

Oracle Academy Egypt


CREATE TABLE emp_load will be deleted

(employee_number CHAR(5),

employee_dob CHAR(20),

employee_last_name CHAR(20),

employee_first_name CHAR(15),

employee_middle_name CHAR(15),

employee_hire_date DATE)

ORGANIZATION EXTERNAL

(TYPE ORACLE_LOADER

DEFAULT DIRECTORY def_dir1

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

FIELDS (employee_number CHAR(2),

employee_dob CHAR(20),

employee_last_name CHAR(18),

employee_first_name CHAR(11),

employee_middle_name CHAR(11),

employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy“))

LOCATION ('info.dat'));

Oracle Academy Egypt


Data dictionary contains what is called will be deletedmetadata (data of data)

Oracle Academy Egypt


Show all available views in your schema will be deleted

Show the names of all tables in your schema

Show all objects available in your schema

Show the names of the objects and their titles in your schema

Oracle Academy Egypt


Oracle Academy Egypt will be deleted


Oracle Academy Egypt will be deleted


Oracle Academy Egypt will be deleted


Oracle Academy Egypt will be deleted


Oracle Academy Egypt will be deleted


Oracle Academy Egypt will be deleted


Sometimes tables don’t be dropped if there’re dependences (PK and FK between two tables)

Oracle Academy Egypt


Oracle Academy Egypt dependences (PK and FK between two tables)


Oracle Academy Egypt dependences (PK and FK between two tables)


Oracle Academy Egypt dependences (PK and FK between two tables)


Flashback
Flashback dependences (PK and FK between two tables)

  • This command can be used to restore a table, a view or an index that was dropped in error.

  • Also used to view row data at specific points in time, so we can compare different versions of a row over time.

    (We can use this facility to look at what the rows looked like BEFORE those changes were applied)

Oracle Academy Egypt


Flashback table
FLASHBACK TABLE dependences (PK and FK between two tables)

FLASHBACK TABLE tablename TO BEFORE DROP;

Note/

As the owner of a table you can issue the flashback command, and if the table that you are restoring had any indexes, then these are also restored.

Oracle Academy Egypt


Flashback query
FLASHBACK QUERY dependences (PK and FK between two tables)

A typical flashback query might look like this:

SELECT versions_starttime AS "START_DATE",

versions_endtime AS "END_DATE",

salary

FROM employees

VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

WHERE department_id = 90;

Oracle Academy Egypt


Oracle Academy Egypt dependences (PK and FK between two tables)


FLASHBACK QUERY dependences (PK and FK between two tables)

SELECT versions_starttime "START_DATE",

versions_endtime "END_DATE",

salary

FROM employees

VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

WHERE department_id=90

Rows from the ‘old’updated version are in red in the output table below:

Oracle Academy Egypt


NOTE dependences (PK and FK between two tables)

Oracle is getting the FLASHBACK QUERY data from the “before images” held in the database in the UNDO tablespace. Typically, this UNDO data is only available for up to 15 minutes after the transaction was committed.

Once the UNDO data has been overwritten by Oracle, Flashback queries are no longer possible, so if you want to use this functionality, you will have to do it quickly after the changes were committed.

Oracle Academy Egypt


Oracle Academy Egypt dependences (PK and FK between two tables)


Oracle Academy Egypt dependences (PK and FK between two tables)


When the constraint is created, it can be given a name, such as clients_client_num_pk, or given no name, in which case the system gives the constraint a name, such as SYS_C00585417.

Note: If the word CONSTRAINT is used in the CREATE TABLE definition, you must give the constraint a name. It is best to name constraints yourself. Why?

The system-generated names are not intuitive. You can do better than the database when it comes to naming!

Oracle Academy Egypt












clients_email_nn_not null as

Oracle Academy Egypt






Good luck
GOOD LUCK as

SEE YOU NEXT MEETING

Raafat Rashad

[email protected]

[email protected]

Oracle Academy Egypt


ad