• Oracle Database: Introduction to SQL (DB-INTRO-SQL
  Oracle Database: Introduction to SQL (DB-INTRO-SQL)培訓
  培訓大綱：
  1. Introduction to Oracle Database
  Features of Oracle Database 11g
  Basic design, theoretical, and physical aspects of a relational database
  Categorize the different types of SQL statements
  Data set used by the course
  Log on to the database using SQL Developer environment
  Save queries to files and use script files in SQL Developer
  2. Retrieve Data using the SQL SELECT Statement
  Capabilities of SQL SELECT statements
  Generate a report of data from the output of a basic SELECT statement
  Select all columns
  Select specific columns
  Use column heading defaults
  Use arithmetic operators
  Operator precedence
  DESCRIBE command to display the table structure
  3. Learn to Restrict and Sort Data
  Write queries that contain a WHERE clause to limit the output retrieved
  Comparison operators and logical operators that are used in a WHERE clause
  Rules of precedence for comparison and logical operators
  Use character string literals in the WHERE clause
  Write queries that contain an ORDER BY clause to sort the output of a SELECT statement
  Sort output in descending and ascending order
  4. Usage of Single-Row Functions to Customize Output
  Differences between single row and multiple row functions
  Manipulate strings with character function in the SELECT and WHERE clauses
  Manipulate numbers with the ROUND, TRUNC, and MOD functions
  Perform arithmetic with date data
  Manipulate dates with the DATE functions
  5. Invoke Conversion Functions and Conditional Expressions
  Implicit and explicit data type conversion
  Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  Nest multiple functions
  Apply the NVL, NULLIF, and COALESCE functions to data
  Use conditional IF THEN ELSE logic in a SELECT statement
  6. Aggregate Data Using the Group Functions
  Use the aggregation functions to produce meaningful reports
  Divide the retrieved data in groups by using the GROUP BY clause
  Exclude groups of data by using the HAVING clause
  7. Display Data from Multiple Tables Using Joins
  Write SELECT statements to access data from more than one table
  View data that generally does not meet a join condition by using outer joins
  Join a table to itself by using a self join
  8. Use Subqueries to Solve Queries
  Types of problems that subqueries can solve
  Subqueries
  Types of subqueries
  Write single-row and multiple-row subqueries
  9. The SET Operators
  SET operators
  Use a SET operator to combine multiple queries into a single query
  Control the order of rows returned
  10. Data Manipulation Statements
  DML statements
  Insert rows into a table
  Change rows in a table by the UPDATE statement
  Delete rows from a table with the DELETE statement
  Save and discard changes with the COMMIT and ROLLBACK statements
  Read consistency
  11. Use of DDL Statements to Create and Manage Tables
  Categorize the main database objects
  Table structure
  Data types available for columns
  Create a simple table
  How constraints can be created at table creation
  How schema objects work
  12. Other Schema Objects
  Create a simple and complex view
  Retrieve data from views
  Create, maintain, and use sequences
  Create and maintain indexes
  Create private and public synonyms
  13. Control User Access
  System privileges vs. object privileges
  Create users
  Grant system privileges
  Create and grant privileges to a role
  Change your password
  Grant object privileges
  How to pass on privileges?
  Revoke object privileges
  14. Management of Schema Objects
  Add, modify, and drop a column
  Add, drop, and defer a constraint
  How to enable and disable a constraint?
  Create and remove indexes
  Create a Function-Based index
  Perform Flashback operations
  Create an External Table by using ORACLE_LOADER and by using ORACLE_DATAPUMP
  Query external tables
  15. Manage Objects with Data Dictionary Views
  Data dictionary
  Use the dictionary views
  USER_OBJECTS and ALL_OBJECTS Views
  Table and Column Information
  Query the dictionary views for constraint information
  Query the dictionary views for view, sequence, index, and synonym information
  Add a comment to a table
  Query the dictionary views for comment information
  16. Manipulate Large Data Sets
  Use subqueries to manipulate data
  Retrieve data using a subquery as source
  Insert using a subquery as a target
  Usage of the WITH CHECK OPTION keyword on DML statements
  Types of multitable INSERT statements
  Use multitable INSERT statements
  Merge rows in a table
  Track changes in data over a period of time
  17. Data Management in Different Time Zones
  Time zones
  CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
  Compare date and time in a session''s time zone
  DBTIMEZONE and SESSIONTIMEZONE
  Difference between DATE and TIMESTAMP
  INTERVAL data types
  Use EXTRACT, TZ_OFFSET, and FROM_TZ
  Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL
  18. Retrieve Data Using Subqueries
  Multiple-column subqueries
  Pairwise and nonpairwise comparison
  Scalar subquery expressions
  Solve problems with Correlated subqueries
  Update and delete rows using correlated subqueries
  The EXISTS and NOT EXISTS operators
  Invoke the WITH clause
  The recursive WITH clause
  19. Regular Expression Support
  Use the regular expressions functions and conditions in SQL
  Use meta characters with regular expressions
  Perform a basic search using the REGEXP_LIKE function
  Find patterns using the REGEXP_INSTR function
  Extract Substrings using the REGEXP_SUBSTR function
  Replace patterns using the REGEXP_REPLACE function
  Usage of sub-expressions with regular expression support
  Implement the REGEXP_COUNT function
  ?
  ?
  ?
  ?
  ?
  ?
• 
  ?
• 
  ?
• 
  ?
  ?
  ?
  ?
• 
  ?
  ?

?
?

?

?

?

?

?
?

?
?

?

?

?

?
???

?

?
?

?

?