Oracle18c SQLCourse: OR18SQL
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 users of Oracle11g and higher. A full presentation of the basics of relational databases and their use are also covered. Topics Covered In This Course
CHAPTER 1 – BASIC RDBMS PRINCIPLESRELATIONAL DESIGN PRINCIPLESACCESSING DATA THROUGH A STRUCTURED QUERY LANGUAGEENTITY RELATIONSHIP DIAGRAMSDATA DOMAINSNULL VALUESINDEXESVIEWSDENORMALIZATIONDATA MODEL REVIEWLAB 1: BASIC RDBMS PRINCIPLESLAB 1 SOLUTIONS: BASIC RDBMS PRINCIPLESCHAPTER 2 – THE SQL LANGUAGE AND TOOLSUSING 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?INTRODUCING SQLCL: THE DEATH OF SQL*PLUS?
LAB 2: SQL LANGUAGE AND TOOLSLAB 2 SOLUTIONS: SQL LANGUAGE AND TOOLSCHAPTER 3 – USING SQL DEVELOPERCHOOSING 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 DEVELOPERLAB 3: USING SQL DEVELOPERLAB 3 SOLUTIONS: USING SQL DEVELOPERCHAPTER 4 – SQL QUERY BASICSUNDERSTANDING 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 BASICSCHAPTER 5 – WHERE AND ORDER BYWHERE 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 BYCHAPTER 6 – FUNCTIONSTHE BASICS OF ORACLE FUNCTIONSNUMBER FUNCTIONSCHARACTER FUNCTIONSDATE FUNCTIONSCONVERSION FUNCTIONSOTHER FUNCTIONSLARGE OBJECT FUNCTIONSERROR FUNCTIONSTHE RR FORMAT MODELLEVERAGING YOUR KNOWLEDGELAB 6: FUNCTIONSLAB 6 SOLUTIONS: FUNCTIONSCHAPTER 7 – ANSI 92 JOINSBASICS OF ANSI 92 JOINSUSING QUERY BUILDER WITH MULTIPLE TABLESTABLE ALIASESOUTER JOINS
SET OPERATORSSELF-REFERENTIAL JOINSNON-EQUIJOINSLAB 7: ANSI 92 JOINSLAB 7: SOLUTIONS: ANSI 92 JOINSCHAPTER 8 – ANSI 99 JOINSCHANGES WITH ANSI99CROSS JOINNATURAL JOINJOIN USINGJOIN ONLEFT / RIGHT OUTER JOINFULL OUTER JOINLAB 8: ANSI 99 JOINSLAB 8 SOLUTIONS: ANSI 99 JOINSCHAPTER 9 – GROUP BY AND HAVINGINTRODUCTION 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 HAVINGCHAPTER 10 – SUBQUERIESWHY 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: SUBQUERIESCHAPTER 11– REGULAR EXPRESSIONSAVAILABLE REGULAR EXPRESSION FUNCTIONSREGULAR EXPRESSION OPERATORSCHARACTER CLASSESPATTERN MATCHING OPTIONSREGEX_LIKEREGEXP_SUBSTRREGEXP_INSTRREGEXP_REPLACEREGEXP_COUNTLAB 11: REGULAR EXPRESSIONSLAB 11 SOLUTIONS: REGULAR EXPRESSIONSCHAPTER 12 – ANALYTICSTHE WITH CLAUSEREPORTING AGGREGATE FUNCTIONSANALYTICAL FUNCTIONSUSER-DEFINED BUCKET HISTOGRAMSTHE MODEL CLAUSEPIVOT AND UNPIVOTTEMPORAL VALIDITYLAB 12: ANALYTICSLAB 12 SOLUTIONS: ANALYTICSCHAPTER 13 – ANALYTICS IIRANKING FUNCTIONSRANKDENSE_RANKCUME_DISTPERCENT_RANKROW_NUMBERWINDOWING AGGREGATE FUNCTIONSRATIO_TO_REPORTLAG / LEADLINEAR REGRESSION FUNCTIONSINVERSE PERCENTILE FUNCTIONSHYPOTHETICAL RANKING FUNCTIONSPATTERN MATCHINGCHAPTER 14 – BASIC REPORTINGBASIC REPORTING
PRINT | NOPRINTTTITLE | BTITLEREPHEADER / REPFOOTERNEW_VALUE / OLD_VALUETHE COMPUTE COMMANDCOMMENTS IN SCRIPT FILESSUBSTITUTION VARIABLES
RUNNING SCRIPTS UNATTENDEDLAB 14: BASIC REPORTINGLAB 14 SOLUTIONS: BASIC REPORTINGCHAPTER 15 – SECURITYBASIC SECURITY
SCHEMA ONLY ACCOUNTSTHE DATA DICTIONARY AND SECURITYUSING ROLES FOR PRIVILEGE MANAGEMENTUSING PROFILES
LAB 15: SECURITYLAB 15 SOLUTIONS: SECURITYCHAPTER 16– DATA IMPORT AND EXPORTUSING 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 EXPORTCHAPTER 17 – DATA MANIPULATIONTHE DATA MANIPULATION LANGUAGE
USING SQL DEVELOPER FOR DMLTHE TRANSACTION CONTROL LANGUAGE (TCL)IMPLICIT TCLLAB 17: DATA MANIPULATIONLAB 17 SOLUTIONS: DATA MANIPULATIONCHAPTER 18– ADVANCED DATA MANIPULATIONTHE 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 MANIPULATIONCHAPTER 19– INTRODUCTION TO DATA DEFINITIONINTRODUCTION 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 DEFINITIONCHAPTER 20 – ADVANCED DATA DEFINITIONDDL AND THE DATA DICTIONARYDISABLING CONSTRAINTSENABLING CONSTRAINTSHANDLING CONSTRAINT EXCEPTIONSUSING DEFERRABLE CONSTRAINTSSEQUENCES
EXTERNAL 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 DEFINITIONWhat You Can Expect
To cover a complete, hands-on introduction to SQL including the use of both SQL Developer and SQL*Plus. presentation of the basics of relational databases and their use. This includes anyone needing to interface with an Oracle database or those needing a general understanding of Oracle database functionality. Who 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. |
Notify me the next time this course is confirmed! | |||||||||||||||||||||||||||||||||||||||||||||||
Can't find the course you want? Call us at 800.533.3893, or email us at info@verhoef.com |
||||||||||||||||||||||||||||||||||||||||||||||||