1 / 65

Virtual training week 8

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. _____________________________________________

Download Presentation

Virtual training week 8

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Virtual training week 8 Sections from 7 to 9 Oracle Academy Egypt

  2. 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

  3. Oracle Academy Egypt

  4. Oracle Academy Egypt

  5. Oracle Academy Egypt

  6. Oracle Academy Egypt

  7. Oracle Academy Egypt

  8. Oracle Academy Egypt

  9. Oracle Academy Egypt

  10. 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

  11. 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

  12. 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

  13. Oracle Academy Egypt

  14. Oracle Academy Egypt

  15. We can use single row subquery only to update rows Oracle Academy Egypt

  16. Department table didn't have department_id 55 Oracle Academy Egypt

  17. Note/ If no where condition is used all rows in the table will be deleted Oracle Academy Egypt

  18. Oracle Academy Egypt

  19. Oracle Academy Egypt

  20. Data copied from items table to copy_items table Oracle Academy Egypt

  21. Oracle Academy Egypt

  22. Oracle Academy Egypt

  23. Oracle Academy Egypt

  24. Oracle Academy Egypt

  25. If you don’t enter any value a 0 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

  26. In this case 0 will be added Oracle Academy Egypt

  27. External Tables • 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

  28. CREATE TABLE emp_load (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

  29. Data dictionary contains what is called metadata (data of data) Oracle Academy Egypt

  30. Show all available views in your schema 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

  31. Oracle Academy Egypt

  32. Oracle Academy Egypt

  33. Oracle Academy Egypt

  34. Oracle Academy Egypt

  35. Oracle Academy Egypt

  36. Oracle Academy Egypt

  37. Sometimes tables don’t be dropped if there’re dependences (PK and FK between two tables) Oracle Academy Egypt

  38. Oracle Academy Egypt

  39. Oracle Academy Egypt

  40. Oracle Academy Egypt

  41. Flashback • 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

  42. FLASHBACK TABLE 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

  43. FLASHBACK QUERY 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

  44. Oracle Academy Egypt

  45. FLASHBACK QUERY 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

  46. NOTE 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

  47. Oracle Academy Egypt

  48. Oracle Academy Egypt

  49. 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

  50. Oracle Academy Egypt

More Related