Syllabus

About - Syllabus

Syllabus

Syllabus:Oracle Database 12c: SQL Fundamentals

 

Chapter 1 - Introduction to Oracle Database

  •  List the features of Oracle Database 12c
  •  Discuss the basic design, theoretical, and physical aspects of a relational

Chapter 2 - database

  •  Categorize the different types of SQL statements
  •  Describe the 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

Chapter 3 - Retrieve Data using the SQL SELECT Statement

  •  List the 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
  • Understand Operator Precedence
  • Learn the DESCRIBE command to display the table structure

Chapter 4 - Restrict and Sort Data

  •  Write queries that contain a WHERE clause to limit the output retrieved
  •  List the comparison operators and logical operators that are used in a WHERE

clause

  •  Describe the 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

Chapter 5 - Usage of Single-Row Functions to Customize Output

  •  Describe the 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

Chapter 6 - Invoke Conversion Functions and Conditional Expressions

  •  Describe 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

Chapter 7 - 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

Chapter 8 - 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

Chapter 9 - Use Sub-queries to Solve Queries

  •  Describe the types of problem that sub-queries can solve
  •  Define sub-queries
  •  List the types of sub-queries
  •  Write single-row and multiple-row sub-queries

Chapter 10 - The SET Operators

  •  Describe the SET operators
  •  Use a SET operator to combine multiple queries into a single query
  •  Control the order of rows returned

Chapter 11 - Data Manipulation Statements

  •  Describe each DML statement
  •  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
  •  Explain read consistency

Chapter 12 - Use of DDL Statements to Create and Manage Tables

  •  Categorize the main database objects
  •  Review the table structure
  •  List the data types available for columns
  •  Create a simple table
  •  Decipher how constraints can be created at table creation
  •  Describe how schema objects work

Chapter 13 - 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

Chapter 14 - Control User Access

  •  Differentiate system privileges from 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

Chapter 15 - 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

Chapter 16 - Manage Objects with Data Dictionary Views

  •  Explain the 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

Chapter 17 - 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
  •  List the types of Multitable INSERT Statements
  •  Use Multitable INSERT Statements
  •  Merge rows in a table
  •  Track Changes in Data over a period of time

Chapter 18 - 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

Chapter 19 - Retrieve Data Using Sub-queries

  •  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

Chapter 20 - 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

© 2015 by Learncertification All Rights Reserved. The certification names are the trademarks of their respective owners. Terms & Privacy Policy