Oracle12c Release 2 SQL (Five Days)Course: OR12R2S5
Duration: 5 Days
Level: I
Course Summary
This course provides a complete, hands-on introduction to 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
CHAPTER 1 -- BASIC RDBMS PRINCIPLESC***************************************************RELATIONAL DESIGN PRINCIPLESACCESSING DATA THROUGH A STRUCTURED QUERY LANGUAGEENTITY RELATIONSHIP DIAGRAMSDATA DOMAINSNULL VALUESINDEXESVIEWSDENORMALIZATIONDATA MODEL REVIEWLAB 1: BASIC RDBMS PRINCIPLESLAB 1 SOLUTIONS: BASIC RDBMS PRINCIPLESC*********************************************************CHAPTER 2 -- THE SQL LANGUAGE AND TOOLSC*********************************************************USING SQL*PLUS
The COLUMN Command
PREDEFINED DEFINE VARIABLESLOGIN.SQLWE HAVE HISTORY!PERFORMANCE SETTINGSCSV OPTION FOR MARKUPFEEDBACK ONLYSUPPORT FOR LONG IDENTIFIERSCOPY 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 TOOLSC***********************************************CHAPTER 3 -- USING SQL DEVELOPERC***********************************************CHOOSING A SQL DEVELOPER VERSIONCONFIGURING CONNECTIONS
CONFIGURING PREFERENCESUSING SQL DEVELOPER
DIFFERENCES BETWEEN SQL DEVELOPER AND SQL*PLUS
SPOOLING IN SQL DEVELOPERDATA DICTIONARY REPORTSUSER DEFINED REPORTSUSING SCRIPTS IN SQL DEVELOPERINTRODUCING SQLCL: THE DEATH OF SQL*PLUS?
LAB 3: USING SQL DEVELOPERLAB 3 SOLUTIONS: USING SQL DEVELOPERC*****************************************CHAPTER 4 -- SQL QUERY BASICSC*****************************************UNDERSTANDING THE DATA DICTIONARY
THE DICTIONARY VIEWCOMPONENTS OF A SELECT STATEMENT
COLUMN ALIASESFULLY QUALIFYING TABLES AND COLUMNSTABLE ALIASESA NEAT TRICKUSING DISTINCT AND ALL IN SELECT STATEMENTSLAB 4: SQL QUERY BASICSLAB 4 SOLUTIONS: SQL QUERY BASICSC**********************************************CHAPTER 5 -- WHERE AND ORDER BYC**********************************************WHERE CLAUSE BASICSCOMPARISON OPERATORSLITERALS AND CONSTANTS IN SQLSIMPLE PATTERN MATCHINGLOGICAL OPERATORSTHE DUAL TABLEARITHMETIC OPERATORSEXPRESSIONS IN SQLCHARACTER OPERATORSPSEUDO COLUMNSORDER BY CLAUSE BASICSORDERING NULLSACCENT AND CASE INSENSITIVE SORTSDATA BOUND COLLATIONCASE-INSENSITIVE DATABASESAMPLING DATAWHERE AND ORDER BY IN SQL DEVELOPERALL, ANY, SOMELAB 5: WHERE AND ORDER BYLAB 5 SOLUTIONS: WHERE AND ORDER BYC********************************CHAPTER 6 -- FUNCTIONSC********************************THE BASICS OF ORACLE FUNCTIONSNUMBER FUNCTIONSCHARACTER FUNCTIONSDATE FUNCTIONSCONVERSION FUNCTIONSOTHER FUNCTIONSLARGE OBJECT FUNCTIONSERROR FUNCTIONSTHE RR FORMAT MODELLEVERAGING YOUR KNOWLEDGELAB 6: FUNCTIONSLAB 6 SOLUTIONS: FUNCTIONSC**********************************CHAPTER 7 -- ANSI 92 JOINSC**********************************BASICS OF ANSI 92 JOINSUSING QUERY BUILDER WITH MULTIPLE TABLESTABLE ALIASESOUTER JOINS
SET OPERATORSSELF-REFERENTIAL JOINSNON-EQUIJOINSLAB 7: ANSI 92 JOINSLAB 7: SOLUTIONS: ANSI 92 JOINSC**********************************CHAPTER 8 -- ANSI 99 JOINSC**********************************CHANGES WITH ANSI99CROSS JOINNATURAL JOINJOIN USINGJOIN ONLEFT / RIGHT OUTER JOINFULL OUTER JOINLAB 8: ANSI 99 JOINSLAB 8 SOLUTIONS: ANSI 99 JOINSC**********************************************CHAPTER 9 -- GROUP BY AND HAVINGC**********************************************INTRODUCTION TO GROUP FUNCTIONS
GROUP FUNCTION REQUIREMENTSTHE HAVING CLAUSEOTHER GROUP FUNCTION RULESUSING QUERY BUILDER WITH GROUP CLAUSESROLLUP AND CUBETHE GROUPING FUNCTIONGROUPING SETSLAB 9: GROUP BY AND HAVINGLAB 9 SOLUTIONS: GROUP BY AND HAVINGC*******************************************************CHAPTER 10 -- SUBQUERIESC*******************************************************WHY USE SUBQUERIES?WHERE CLAUSE SUBQUERIESFROM CLAUSE SUBQUERIESHAVING CLAUSE SUBQUERIESCORRELATED SUBQUERIESSCALAR SUBQUERIESDML AND SUBQUERIESEXISTS SUBQUERIESHIERARCHICAL QUERIESTOP N AND BOTTOM N QUERIESCREATING SUBQUERIES USING QUERY BUILDERLAB 10: SUBQUERIESLAB 10 SOLUTIONS: SUBQUERIESC************************************************CHAPTER 11-- REGULAR EXPRESSIONSC*************************************************AVAILABLE REGULAR EXPRESSION FUNCTIONSREGULAR EXPRESSION OPERATORSCHARACTER CLASSESPATTERN MATCHING OPTIONSREGEX_LIKEREGEXP_SUBSTRREGEXP_INSTRREGEXP_REPLACEREGEXP_COUNTLAB 11: REGULAR EXPRESSIONSLAB 11 SOLUTIONS: REGULAR EXPRESSIONSC********************************CHAPTER 12 -- ANALYTICSC********************************THE WITH CLAUSEREPORTING AGGREGATE FUNCTIONSANALYTICAL FUNCTIONSUSER-DEFINED BUCKET HISTOGRAMSTHE MODEL CLAUSEPIVOT AND UNPIVOTTEMPORAL VALIDITYLAB 12: ANALYTICSLAB 12 SOLUTIONS: ANALYTICSC**********************************CHAPTER 13 -- ANALYTICS IIC**********************************RANKING FUNCTIONSRANKDENSE_RANKCUME_DISTPERCENT_RANKROW_NUMBERWINDOWING AGGREGATE FUNCTIONSRATIO_TO_REPORTLAG / LEADLINEAR REGRESSION FUNCTIONSINVERSE PERCENTILE FUNCTIONSHYPOTHETICAL RANKING FUNCTIONSPATTERN MATCHINGC******************************************CHAPTER 14 -- BASIC REPORTINGC******************************************BASIC REPORTING
PRINT | NOPRINTTTITLE | BTITLEREPHEADER / REPFOOTERNEW_VALUE / OLD_VALUETHE COMPUTE COMMANDCOMMENTS IN SCRIPT FILESSUBSTITUTION VARIABLES
RUNNING SCRIPTS UNATTENDEDLAB 14: BASIC REPORTINGLAB 14 SOLUTIONS: BASIC REPORTINGC********************************CHAPTER 15 -- SECURITYC********************************BASIC SECURITY
THE DATA DICTIONARY AND SECURITYUSING ROLES FOR PRIVILEGE MANAGEMENTUSING PROFILES
LAB 15: SECURITYLAB 15 SOLUTIONS: SECURITYC****************************************************CHAPTER 16 -- DATA IMPORT AND EXPORTC****************************************************USING SQL*LOADER WITH FIELD DELIMITED DATAUSING SQL*LOADER WITH COMMA DELIMITED DATADATA LOADING USING SQL DEVELOPEREXPORTING ORACLE DATA INTO EXCELDOING AN ODBC QUERYEXPORTING DATA SETSDATA VALIDATIONA WORD ABOUT DATA PUMPLAB 16: DATA IMPORT AND EXPORTLAB 16 SOLUTIONS: DATA IMPORT AND EXPORTC*********************************************CHAPTER 17 -- DATA MANIPULATIONC********************************************THE DATA MANIPULATION LANGUAGE
USING SQL DEVELOPER FOR DMLTHE TRANSACTION CONTROL LANGUAGE (TCL)IMPLICIT TCLLAB 17: DATA MANIPULATIONLAB 17 SOLUTIONS: DATA MANIPULATIONC**********************************************************CHAPTER 18 -- ADVANCED DATA MANIPULATIONC**********************************************************THE MERGE COMMANDMULTIPLE COLUMN SUBQUERY UPDATES AND DELETESDML AGAINST VIEWSTRANSACTIONS AND READ CONSISTENCYDML LOCKSFLASHBACK TECHNOLOGIESINSERTING LARGE OBJECTSCHANGED DATA TRACKING
LAB 18: ADVANCED DATA MANIPULATIONLAB 18 SOLUTIONS: ADVANCED DATA MANIPULATIONC***************************************************************CHAPTER 19 -- INTRODUCTION TO DATA DEFINITIONC***************************************************************INTRODUCTION TO DDL COMMANDSKEY OBJECTSOBJECT NAMING RULESTHE DATA DICTIONARYAVAILABLE DATATYPES
THE CREATE TABLE STATEMENTNAMING CONSTRAINTSINTEGRITY CONSTRAINTS
IDENTITY ColumnsCONSTRAINTS AND CREATE TABLE? AS SELECTCONSTRAINT LIMITATIONSJSON SUPPORTCREATING TABLES IN SQL DEVELOPEROTHER DDL ACTIONS IN SQL DEVELOPERTHE ALTER TABLE COMMANDDROPPING OBJECTSRENAMING OBJECTSTHE TRUNCATE COMMANDTHE COMMENT COMMANDCREATING SIMPLE VIEWSLAB 19: INTRODUCTION TO DATA DEFINITIONLAB 19 SOLUTIONS: INTRODUCTION TO DATA DEFINITIONC*******************************************************CHAPTER 20 -- ADVANCED DATA DEFINITIONC*******************************************************DDL AND THE DATA DICTIONARYDISABLING CONSTRAINTSENABLING CONSTRAINTSHANDLING CONSTRAINT EXCEPTIONSUSING DEFERRABLE CONSTRAINTSSEQUENCESEXTERNAL TABLES FOR DATA STORAGE
EXTERNAL TABLES AND THE ORACLE_DATAPUMP DRIVERINDEXES
COMMENTSSYNONYMS
COMPLEX VIEWS
VIRTUAL COLUMNSCOMPRESSED TABLESINVISIBLE INDEXESONLINE DDL ENHANCEMENTSINVISIBLE COLUMNSCREATING MULTIPLE INDEXES ON COLUMNSLAB 20: ADVANCED DATA DEFINITIONLAB 20 SOLUTIONS: ADVANCED DATA DEFINITIONWho 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
Lecture and Hands On Labs 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] |
||||||||||||||||||||||||||||||||