| Oracle Database 12c: SQL Workshop 1 |
| Introduction | Course Objectives |
| Training Syllabus |
| Relational and Object Relational Database Management Systems |
| Relational Database Concept |
| Definition of a Relational Database |
| Relating Multiple Tables |
| Relational Database Terminology |
| Introduction to SQL and its development environments |
| Using SQL to Query Your Database |
| SQL Statements Used in the Course |
| Development Environments for SQL |
| Installing Oracle Database | Installing Oracle Database 12c |
| Configuring and Creating CDB and PDBs |
| Connect to a PDB using a service name |
| Configuring SQL Developer and Creating a Database connection |
| Retrieving 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 |
| Write readable code with appropriate indentation |
| Writing SQL Statements |
| Use Column Heading Defaults |
| Arithmetic Expressions |
| Use Arithmetic Operators |
| Understand Operator Precedence |
| Defining a Null Value |
| Null Values in Arithmetic Expressions |
| Defining a Column Alias |
| Using Column Aliases |
| Concatenation Operator |
| Literal Character Strings |
| Using Literal Character Strings |
| Using Literal Character Strings |
| Duplicate Rows |
| Learn the DESCRIBE command to display the table structure |
| Create reports of sorted and restricted data | Write queries that contain a WHERE clause to limit the output retrieved |
| Use of Character Strings and Dates |
| List the comparison operators and logical operators that are used in a WHERE clause |
| Range Conditions Using the BETWEEN Operator |
| Membership Condition Using the IN Operator |
| Pattern Matching Using the LIKE Operator |
| Combining Wildcard Characters |
| Using the NULL Conditions |
| Defining Conditions Using the Logical Operators |
| Describe the rules of precedence for comparison and logical operators |
| Write queries that contain an ORDER BY clause to sort the output of a SELECT statement |
| Sort output in descending and ascending order |
| SQL Row Limiting Clause |
| Employ SQL functions to generate and retrieve customized data | SQL Functions |
| Describe the differences between single row and multiple row functions |
| Single-Row Functions |
| Manipulate strings with character function in the SELECT and WHERE clauses |
| Nesting Functions |
| Case-Conversion Functions |
| Character-Manipulation Functions |
| Working with Dates |
| Manipulate numbers with the ROUND, TRUNC, and MOD functions |
| Perform arithmetic with date data |
| Manipulate dates with the DATE functions |
| Invoke Conversion Functions and Conditional Expressions | Describe implicit and explicit data type conversion |
| Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions |
| with the RR Date Format |
| General functions |
| Apply the NVL, NULLIF, and COALESCE functions to data |
| Use conditional IF THEN ELSE logic in a SELECT statement |
| Reporting Aggregated Data Using the Group Functions | Use the aggregation functions in SELECT statements to produce meaningful reports |
| Types of Group Functions |
| Group Functions and Null Values |
| Creating Groups of Data |
| Grouping by More Than One Column |
| Divide the data in groups by using the GROUP BY clause |
| Illegal Queries Using Group Functions |
| Include or Exclude groups of date by using the HAVING clause |
| Nesting Group Functions |
| Display data from multiple tables using Joins | Write SELECT statements to access data from more than one table |
| Types of Joins |
| Creating Natural Joins |
| Creating Joins with the USING Clause |
| Joining Column Names |
| Retrieving Records with the USING Clause |
| Using Table Aliases with the USING Clause |
| Creating Joins with the ON Clause |
| Creating Three-Way Joins with the ON Clause |
| Applying Additional Conditions to a Join |
| Joining a Table to Itself |
| Retrieving Records with Nonequijoins |
| View data that generally does not meet a join condition by using outer joins |
| INNER Versus OUTER Joins |
| LEFT OUTER JOIN |
| RIGHT OUTER JOIN |
| FULL OUTER JOIN |
| Generating a Cartesian Product |
| Creating Cross Joins |
| Display data from multiple tables using sub-queries | Describe the types of problem that sub-queries can solve |
| Define sub-queries |
| Rules for Using Subqueries |
| List the types of sub-queries |
| Write single-row and multiple-row sub-queries |
| Null values in a subquery |
| Using the Set Operators | Use a SET operator to combine multiple queries into a single query |
| Describe the SET operators |
| Set operators rules |
| Oracle Server and Set Operators |
| UNION and UNION ALL operator |
| INTERSECT operator |
| MINUS operator |
| Matching the SELECT statements |
| Using the ORDER BY clause in set operations |
| Managing Tables Using DML Statements | Data manipulation language |
| Adding a new row to a Table |
| Inserting new row |
| Inserting new row with Null values |
| Inserting special values |
| Inserting specific date and time values |
| Creating a script |
| Copying rows from another table |
| Changing data in a table |
| Updating rows in a table |
| Updating two columns with a subquery |
| Updating rows based on another table |
| Removing a row from a table |
| Deleting rows from a table with the DELETE statement |
| Deleting rows based on another table |
| Removing rows from a table with the TRUNCATE statement |
| Database transactions control using COMMIT, ROLLBACK and SAVEPOINT |
| Database Transactions |
| Database Transactions: Start and End |
| Explicit transaction control statements |
| Rolling back changes to a marker |
| Implicit transaction processing |
| State of the data before COMMIT or ROLLBACK |
| State of the data after COMMIT |
| Committing data |
| State of the data after ROLLBACK |
| Explain read consistency |
| Implementing read consistency |
| FOR UPDATE clause in a SELECT statement |
| Introduction to Data Definition Language | Categorize the main database objects |
| Naming rules |
| List the data types available for columns |
| Date time data types |
| CREATE TABLE statement |
| Review the table structure |
| Including constraints |
| Constraints guidelines |
| Defining constraints |
| NOT NULL constraint |
| UNIQUE constraint |
| PRIMARY KEY constraint |
| FOREIGN KEY constraint |
| FOREIGN KEY constraint: keywords |
| CHECK constraint |
| Violating constraints |
| Creating a table using a subquery |
| ALTER TABLE statement |
| Adding column |
| Modifying column |
| Dropping a column |
| SET UNUSED option |
| Read-only tables |
| Dropping a table |
| Oracle Database 12c: SQL Workshop 2 |
| Creating Sequences, Synonyms, and Indexes | Overview of sequences |
| Creating, using, and modifying a sequence |
| Cache sequence values |
| NEXTVAL and CURRVAL pseudocolumns |
| SQL column defaulting using a sequence |
| Overview of synonyms |
| Creating, dropping synonyms |
| Overview of indexes |
| Creating indexes |
| Creating Views | Overview of views |
| What Is a View? |
| Advantages of Views |
| Simple Views and Complex Views |
| Creating, modifying, and retrieving data from a view |
| Creating a Complex View |
| View Information |
| Data Manipulation Language (DML) operations on a view |
| Using the WITH CHECK OPTION Clause |
| Dropping a view |
| Retrieving Data by Using Subqueries | Retrieving data by using a subquery as a source |
| Writing a multiple-column subquery? |
| Using scalar subqueries in SQL |
| Solving problems with correlated subqueries |
| Using the EXISTS and NOT EXISTS operators |
| Using the WITH clause |
| Manipulating Data by Using Subqueries | Using subqueries to manipulate data |
| Inserting values by using a subquery as a target |
| Using the WITH CHECK OPTION keyword on DML statements |
| Using correlated subqueries to update and delete rows |
| Controlling User Access | Privileges |
| System privileges |
| Creating a role |
| Object privileges |
| Revoking object privileges |