Oracle12c Release 1 SQL And PL/SQL (Five Days)Course: OR12E
Duration: 5 Days
Level: I
Course Summary
This course provides a complete, hands-on introduction to both SQL and PL/SQL including the use of both SQL Developer and SQL*Plus. This coverage is appropriate for both users of Oracle12c and Oracle11g. A full presentation of the basics of relational databases and their use are also covered. Topics Covered In This Course
Course Content SQLCHAPTER 1 BASIC RDBMS PRINCIPLESCHAPTER OVERVIEWRELATIONAL DESIGN PRINCIPLESACCESSING DATA THROUGH A STRUCTURED QUERY LANGUAGEENTITY RELATIONSHIP DIAGRAMSDATA DOMAINSNULL VALUESINDEXESVIEWSDENORMALIZATIONDATA MODEL REVIEWLAB 1: BASIC RDBMS PRINCIPLESLAB 1 SOLUTIONS: BASIC RDBMS PRINCIPLESCHAPTER SUMMARYCHAPTER 2 THE SQL LANGUAGE AND TOOLSCHAPTER OVERVIEWUSING SQL*PLUS
The COLUMN Command
PREDEFINED DEFINE VARIABLESLOGIN.SQLCOMMAND HISTORYCOPY AND PASTE IN SQL*PLUSENTERING SQL COMMANDSENTERING PL/SQL COMMANDSENTERING SQL*PLUS COMMANDSDEFAULT OUTPUT FROM SQL*PLUSENTERING QUERIESWHAT ABOUT PL/SQL?LAB 2: SQL LANGUAGE AND TOOLSLAB 2 SOLUTIONS: SQL LANGUAGE AND TOOLSCHAPTER SUMMARYCHAPTER 3 USING SQL DEVELOPERCHAPTER OVERVIEWCHOOSING A SQL DEVELOPER VERSIONCONFIGURING CONNECTIONS
CONFIGURING PREFERENCESUSING SQL DEVELOPER
DIFFERENCES BETWEEN SQL DEVELOPER AND SQL*PLUS 79
DATA DICTIONARY REPORTSUSER DEFINED REPORTSUSING SCRIPTS IN SQL DEVELOPERLAB 3: USING SQL DEVELOPERLAB 3 SOLUTIONS: USING SQL DEVELOPERCHAPTER SUMMARYCHAPTER 4 SQL QUERY BASICSCHAPTER OVERVIEWUNDERSTANDING THE DATA DICTIONARY
THE DICTIONARY VIEWCOMPONENTS OF A SELECT STATEMENT
COLUMN ALIASESFULLY QUALIFYING TABLES AND COLUMNSTABLE ALIASESUSING DISTINCT AND ALL IN SELECT STATEMENTSLAB 4: SQL QUERY BASICSLAB 4 SOLUTIONS: SQL QUERY BASICSCHAPTER SUMMARYCHAPTER 5 DATA MANIPULATIONCHAPTER OVERVIEWTHE DATA MANIPULATION LANGUAGE
USING SQL DEVELOPER FOR DMLTHE TRANSACTION CONTROL LANGUAGE (TCL)IMPLICIT TCLLAB 5: DATA MANIPULATIONLAB 5 SOLUTIONS: DATA MANIPULATIONCHAPTER SUMMARYCHAPTER 6 WHERE AND ORDER BYCHAPTER OVERVIEWWHERE CLAUSE BASICSCOMPARISON OPERATORSLITERALS AND CONSTANTS IN SQLSIMPLE PATTERN MATCHINGLOGICAL OPERATORSTHE DUAL TABLEARITHMETIC OPERATORSEXPRESSIONS IN SQLCHARACTER OPERATORSPSEUDO COLUMNSORDER BY CLAUSE BASICSORDERING NULLSACCENT AND CASE INSENSITIVE SORTSSAMPLING DATAWHERE AND ORDER BY IN SQL DEVELOPERALL, ANY, SOMELAB 6: WHERE AND ORDER BYLAB 6 SOLUTIONS: WHERE AND ORDER BYCHAPTER SUMMARYCHAPTER 7 FUNCTIONSCHAPTER OVERVIEWTHE BASICS OF ORACLE FUNCTIONSNUMBER FUNCTIONSCHARACTER FUNCTIONSDATE FUNCTIONSCONVERSION FUNCTIONSOTHER FUNCTIONSLARGE OBJECT FUNCTIONSERROR FUNCTIONSTHE RR FORMAT MODELLEVERAGING YOUR KNOWLEDGELAB 7: FUNCTIONSLAB 7 SOLUTIONS: FUNCTIONSCHAPTER SUMMARYCHAPTER 8 ANSI 92 JOINSCHAPTER OVERVIEWBASICS OF ANSI 92 JOINSUSING QUERY BUILDER WITH MULTIPLE TABLESTABLE ALIASESOUTER JOINS
SET OPERATORSSELF-REFERENTIAL JOINSNON-EQUIJOINSLAB 8: ANSI 92 JOINSLAB 8 SOLUTIONS: ANSI 92 JOINSCHAPTER SUMMARYCHAPTER 9 ANSI 99 JOINSCHAPTER OVERVIEWCHANGES WITH ANSI99CROSS JOINNATURAL JOINJOIN USINGJOIN ONLEFT / RIGHT OUTER JOINFULL OUTER JOINLAB 9: ANSI 99 JOINSLAB 9 SOLUTIONS: ANSI 99 JOINSCHAPTER SUMMARYCHAPTER 10 GROUP BY AND HAVINGCHAPTER OVERVIEWINTRODUCTION TO GROUP FUNCTIONS
GROUP FUNCTION REQUIREMENTSTHE HAVING CLAUSEOTHER GROUP FUNCTION RULESUSING QUERY BUILDER WITH GROUP CLAUSESROLLUP AND CUBETHE GROUPING FUNCTIONGROUPING SETSLAB 10: GROUP BY AND HAVINGLAB 10 SOLUTIONS: GROUP BY AND HAVINGCHAPTER SUMMARYCHAPTER 11 SUBQUERIESCHAPTER OVERVIEWWHY USE SUBQUERIES?WHERE CLAUSE SUBQUERIESFROM CLAUSE SUBQUERIESHAVING CLAUSE SUBQUERIESCORRELATED SUBQUERIESSCALAR SUBQUERIESDML AND SUBQUERIESEXISTS SUBQUERIESHIERARCHICAL QUERIESTOP N AND BOTTOM N QUERIESCREATING SUBQUERIES USING QUERY BUILDERLAB 11: SUBQUERIESLAB 11 SOLUTIONS: SUBQUERIESCHAPTER SUMMARYCourse Content PL/SQLCHAPTER 1 PL/SQL PROGRAM STRUCTUREOVERVIEW OF THIS CHAPTERPL/SQL VS. SQLPL/SQL ENGINES AVAILABLEANONYMOUS PL/SQL BLOCK STRUCTUREOBJECT NAMING RULESVARIABLE DECLARATIONSAVAILABLE DATATYPES
EXECUTABLE STATEMENTSEXPRESSIONSBLOCK LABELINGVARIABLE SCOPING RULESCOMMENTS IN PROGRAMS AND SCRIPTSBASIC CODING STANDARDSLAB 1: PL/SQL PROGRAM STRUCTURELAB 1 SOLUTIONS: PL/SQL PROGRAM STRUCTURESUMMARY OF THIS CHAPTERCHAPTER 2 PL/SQL FLOW CONTROLOVERVIEW OF THIS CHAPTERCONDITIONAL CONTROLCOMPARISON OPERATORSLOGICAL OPERATORS
REPETITION CONTROL
THE GOTO STATEMENTCASE EXPRESSIONS / STATEMENTSBIND VARIABLESSUBSTITUTION VARIABLESLAB 2: PL/SQL FLOW CONTROLLAB 2 SOLUTIONS: PL/SQL FLOW CONTROLSUMMARY OF THIS CHAPTERCHAPTER 3 SQL DEVELOPER AND PL/SQLOVERVIEW OF THIS CHAPTERSQL DEVELOPER AND PL/SQLCREATING AND EXECUTING SCRIPTSLAB 3: SQL DEVELOPER AND PL/SQLLAB 3 SOLUTIONS: SQL DEVELOPER AND PL/SQLSUMMARY OF THIS CHAPTERCHAPTER 4 SELECT INTOOVERVIEW OF THIS CHAPTERSELECTING SINGLE ROWS OF DATAANCHORING VARIABLES TO DATATYPESDML IN PL/SQLRETURNING INTOSEQUENCES IN PL/SQLTRANSACTION CONTROL IN PL/SQLAUTONOMOUS TRANSACTIONSLAB 4: SELECT INTOLAB 4 SOLUTIONS: SELECT INTOSUMMARY OF THIS CHAPTERCHAPTER 5 THE PL/SQL CURSOROVERVIEW OF THIS CHAPTERDECLARING EXPLICIT CURSORSOPENING AND CLOSING EXPLICIT CURSORSUSING EXPLICIT CURSORS TO RETRIEVE VALUESEXPLICIT CURSOR ATTRIBUTESUSING A LOOP WITH AN EXPLICIT CURSORUSING %ROWTYPE WITH CURSORSTHE CURSOR FOR LOOPDBMS_OUTPUTLAB 5: THE PL/SQL CURSORLAB 5 SOLUTIONS: THE PL/SQL CURSORSUMMARY OF THIS CHAPTERCHAPTER 6 OPTIMIZATIONOVERVIEW OF THIS CHAPTERTIMING PL/SQLFOR UPDATE / WHERE CURRENT OFLAB 6: OPTIMIZATIONLAB 6 SOLUTIONS: OPTIMIZATIONSUMMARY OF THIS CHAPTERCHAPTER 7 PL/SQL EXCEPTION HANDLINGOVERVIEW OF THIS CHAPTERTHE EXCEPTION SECTIONORACLE NAMED EXCEPTIONSPRAGMA EXCEPTION_INITUSER DEFINED EXCEPTIONS
RAISING NAMED EXCEPTIONSEXCEPTION PROPAGATIONRAISING AN EXCEPTION AGAINLIFE AFTER AN EXCEPTIONWHEN OTHERSTAKING YOUR BALL AND GOING HOMEDBMS_ERRLOGLAB 7: PL/SQL EXCEPTION HANDLINGLAB 7 SOLUTIONS: PL/SQL EXCEPTION HANDLINGSUMMARY OF THIS CHAPTERCHAPTER 8 STORED PROCEDURESOVERVIEW OF THIS CHAPTERPROCEDURESBENEFITS OF STORED PROCEDURES
PARAMETERS AND STORED PROCEDURES
STORED OBJECT CREATION
COMPILATION ERRORSVIEWING COMPILED CODEDROPPING A PROCEDURETHE ALTER COMMAND AND STORED PROCEDURESLAB 8: STORED PROCEDURESLAB 8 SOLUTIONS: STORED PROCEDURESSUMMARY OF THIS CHAPTERCHAPTER 9 CREATING FUNCTIONS IN PL/SQLOVERVIEW OF THIS CHAPTERFUNCTIONSPURITY LEVELSUSING WHITE LISTSOPTIMIZATIONSPARALLEL_ENABLEDETERMINISTIC FUNCTIONSPL/SQL RESULT CACHENOCOPYDBMS_OUTPUT IN FUNCTIONSUSING THE WITH CLAUSE FOR FUNCTIONSPRAGMA UDFPRAGMA INLINEUSING SQL DEVELOPER WITH STORED PROCEDURESDEBUGGINGLAB 9: FUNCTIONSLAB 9 SOLUTIONS: FUNCTIONSSUMMARY OF THIS CHAPTERCHAPTER 10 PACKAGESOVERVIEW OF THIS CHAPTERCREATING PACKAGESPACKAGE BENEFITS
A SIMPLE PACKAGEOVERLOADINGBODILESS PACKAGESSOURCE CODE ENCRYPTIONCREATING PACKAGES FROM PROCEDURES AND FUNCTIONSLAB 10: PACKAGESLAB 10 SOLUTIONS: PACKAGESSUMMARY OF THIS CHAPTERCHAPTER 11 -- CREATING DML TRIGGERSOVERVIEW OF THIS CHAPTERDML TRIGGERSDML TRIGGER STRUCTURECONDITIONAL TRIGGERING PREDICATESTRIGGERS FOR BUSINESS RULES ENFORCEMENTMUTATING AND CONSTRAINING TABLESCOMPOUND TRIGGERSCONTROLLING FIRING ORDERDDL FOR TRIGGERSVIEWING TRIGGER SOURCEINSTEAD OF TRIGGERSLAB 11: DML TRIGGERSLAB 11 SOLUTIONS: DML TRIGGERSSUMMARY OF THIS CHAPTERWho Should Take This Course
This course is appropriate for anyone needing to interface with an Oracle database or those needing a general understanding of Oracle database functionality. That would include end users, business analysts, application developers and database administrators. Recommended Prerequisites
Basic computer skills are needed. A basic knowledge of databases is desired but not required Training Style
Hands on Labs and Lecture Related Courses
Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year. You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details. |
|
||||||||||||||||
Can't find the course you want? Call us at 800.533.3893, or email us at [email protected] |
|||||||||||||||||