Oracle Database: Introduction to SQL



Oracle Database: Introduction to SQLIntroduction to Oracle Database List the features of Oracle Database 12cDiscuss the basic design, theoretical, and physical aspects of a relational databaseCategorize the different types of SQL statementsDescribe the data set used by the courseLog on to the database using SQL Developer environmentSave queries to files and use script files in SQL DeveloperRetrieve Data using the SQL SELECT StatementList the capabilities of SQL SELECT statementsGenerate a report of data from the output of a basic SELECT statementSelect All ColumnsSelect Specific ColumnsUse Column Heading DefaultsUse Arithmetic OperatorsUnderstand Operator PrecedenceLearn the DESCRIBE command to display the table structureLearn to Restrict and Sort DataWrite queries that contain a WHERE clause to limit the output retrievedList the comparison operators and logical operators that are used in a WHERE clauseDescribe the rules of precedence for comparison and logical operatorsUse character string literals in the WHERE clauseWrite queries that contain an ORDER BY clause to sort the output of a SELECT statementSort output in descending and ascending orderUsage of Single-Row Functions to Customize OutputDescribe the differences between single row and multiple row functionsManipulate strings with character function in the SELECT and WHERE clausesManipulate numbers with the ROUND, TRUNC, and MOD functionsPerform arithmetic with date dataManipulate dates with the DATE functionsInvoke Conversion Functions and Conditional ExpressionsDescribe implicit and explicit data type conversionUse the TO_CHAR, TO_NUMBER, and TO_DATE conversion functionsNest multiple functionsApply the NVL, NULLIF, and COALESCE functions to dataUse conditional IF THEN ELSE logic in a SELECT statementAggregate Data Using the Group FunctionsUse the aggregation functions to produce meaningful reportsDivide the retrieved data in groups by using the GROUP BY clauseExclude groups of data by using the HAVING clauseDisplay Data From Multiple Tables Using JoinsWrite SELECT statements to access data from more than one tableView data that generally does not meet a join condition by using outer joinsJoin a table to itself by using a self-joinUse Sub-queries to Solve QueriesDescribe the types of problem that sub-queries can solveDefine sub-queriesList the types of sub-queriesWrite single-row and multiple-row sub-queriesThe SET OperatorsDescribe the SET operatorsUse a SET operator to combine multiple queries into a single queryControl the order of rows returnedData Manipulation StatementsDescribe each DML statementInsert rows into a tableChange rows in a table by the UPDATE statementDelete rows from a table with the DELETE statementSave and discard changes with the COMMIT and ROLLBACK statementsExplain read consistencyUse of DDL Statements to Create and Manage TablesCategorize the main database objectsReview the table structureList the data types available for columnsCreate a simple tableDecipher how constraints can be created at table creationDescribe how schema objects workOther Schema ObjectsCreate a simple and complex viewRetrieve data from viewsCreate, maintain, and use sequencesCreate and maintain indexesCreate private and public synonymsControl User AccessDifferentiate system privileges from object privilegesCreate Users Grant System PrivilegesCreate and Grant Privileges to a RoleChange Your PasswordGrant Object PrivilegesHow to pass on privileges? Revoke Object PrivilegesManagement of Schema ObjectsAdd, Modify, and Drop a ColumnAdd, Drop, and Defer a ConstraintHow to enable and Disable a Constraint?Create and Remove IndexesCreate a Function-Based IndexPerform Flashback OperationsCreate an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMPQuery External TablesManage Objects with Data Dictionary ViewsExplain the data dictionaryUse the Dictionary ViewsUSER_OBJECTS and ALL_OBJECTS ViewsTable and Column InformationQuery the dictionary views for constraint informationQuery the dictionary views for view, sequence, index and synonym informationAdd a comment to a table Query the dictionary views for comment informationManipulate Large Data SetsUse Subqueries to Manipulate DataRetrieve Data Using a Subquery as SourceInsert Using a Subquery as a TargetUsage of the WITH CHECK OPTION Keyword on DML StatementsList the types of Multitable INSERT StatementsUse Multitable INSERT StatementsMerge rows in a tableTrack Changes in Data over a period of timeData Management in different Time ZonesTime ZonesCURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMPCompare Date and Time in a Session’s Time ZoneDBTIMEZONE and SESSIONTIMEZONEDifference between DATE and TIMESTAMPINTERVAL Data TypesUse EXTRACT, TZ_OFFSET and FROM_TZInvoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVALRetrieve Data Using Sub-queriesMultiple-Column SubqueriesPairwise and Non-pairwise ComparisonScalar Subquery ExpressionsSolve problems with Correlated SubqueriesUpdate and Delete Rows Using Correlated SubqueriesThe EXISTS and NOT EXISTS operatorsInvoke the WITH clauseThe Recursive WITH clauseRegular Expression SupportUse the Regular Expressions Functions and Conditions in SQLUse Meta Characters with Regular ExpressionsPerform a Basic Search using the REGEXP_LIKE functionFind patterns using the REGEXP_INSTR functionExtract Substrings using the REGEXP_SUBSTR functionReplace Patterns Using the REGEXP_REPLACE functionUsage of Sub-Expressions with Regular Expression SupportImplement the REGEXP_COUNT functionOracle Database: Program with PL/SQLIntroduction to PL/SQLOverview of PL/SQL Identify the benefits of PL/SQL SubprogramsOverview of the types of PL/SQL blocksCreate a Simple Anonymous Block How to generate output from a PL/SQL Block?Declare PL/SQL IdentifiersList the different Types of Identifiers in a PL/SQL subprogramUsage of the Declarative Section to Define IdentifiersUse variables to store dataIdentify Scalar Data TypesThe %TYPE Attribute What are Bind Variables?Sequences in PL/SQL ExpressionsWrite Executable StatementsDescribe Basic PL/SQL Block Syntax GuidelinesLearn to Comment the CodeDeployment of SQL Functions in PL/SQLHow to convert Data Types?Describe Nested Blocks Identify the Operators in PL/SQLInteraction with the Oracle Server Invoke SELECT Statements in PL/SQL Retrieve Data in PL/SQLSQL Cursor conceptAvoid Errors by using Naming Conventions when using Retrieval and DML StatementsData Manipulation in the Server using PL/SQL Understand the SQL Cursor conceptUse SQL Cursor Attributes to Obtain Feedback on DMLSave and Discard TransactionsControl StructuresConditional processing using IF StatementsConditional processing using CASE StatementsDescribe simple Loop StatementDescribe While Loop StatementDescribe For Loop StatementUse the Continue StatementComposite Data Types Use PL/SQL Records The %ROWTYPE Attribute Insert and Update with PL/SQL RecordsINDEX BY Tables Examine INDEX BY Table Methods Use INDEX BY Table of Records Explicit CursorsWhat are Explicit Cursors?Declare the CursorOpen the CursorFetch data from the CursorClose the CursorCursor FOR loopThe %NOTFOUND and %ROWCOUNT Attributes Describe the FOR UPDATE Clause and WHERE CURRENT Clause Exception Handling Understand ExceptionsHandle Exceptions with PL/SQLTrap Predefined Oracle Server ErrorsTrap Non-Predefined Oracle Server ErrorsTrap User-Defined Exceptions Propagate ExceptionsRAISE_APPLICATION_ERROR ProcedureStored ProceduresCreate a Modularized and Layered Subprogram DesignModularize Development With PL/SQL BlocksUnderstand the PL/SQL Execution EnvironmentList the benefits of using PL/SQL SubprogramsList the differences between Anonymous Blocks and SubprogramsCreate, Call, and Remove Stored Procedures Implement Procedures Parameters and Parameters ModesView Procedure Information Stored Functions and Debugging SubprogramsCreate, Call, and Remove a Stored FunctionIdentify the advantages of using Stored FunctionsIdentify the steps to create a stored functionInvoke User-Defined Functions in SQL StatementsRestrictions when calling FunctionsControl side effects when calling FunctionsView Functions InformationHow to debug Functions and Procedures?PackagesListing the advantages of PackagesDescribe PackagesWhat are the components of a Package?Develop a PackageHow to enable visibility of a Package’s Components?Create the Package Specification and Body using the SQL CREATE Statement and SQL DeveloperInvoke the Package ConstructsView the PL/SQL Source Code using the Data DictionaryDeploying PackagesOverloading Subprograms in PL/SQLUse the STANDARD PackageUse Forward Declarations to solve Illegal Procedure ReferenceImplement Package Functions in SQL and RestrictionsPersistent State of PackagesPersistent State of a Package CursorControl side effects of PL/SQL SubprogramsInvoke PL/SQL Tables of Records in PackagesImplement Oracle-Supplied Packages in Application DevelopmentWhat are Oracle-Supplied Packages?Examples of some of the Oracle-Supplied PackagesHow does the DBMS_OUTPUT Package work?Use the UTL_FILE Package to Interact with Operating System FilesInvoke the UTL_MAIL PackageWrite UTL_MAIL SubprogramsDynamic SQLThe Execution Flow of SQLWhat is Dynamic SQL? Declare Cursor VariablesDynamically Executing a PL/SQL BlockConfigure Native Dynamic SQL to Compile PL/SQL CodeHow to invoke DBMS_SQL Package?Implement DBMS_SQL with a Parameterized DML StatementDynamic SQL Functional CompletenessDesign Considerations for PL/SQL CodeStandardize Constants and ExceptionsUnderstand Local SubprogramsWrite Autonomous TransactionsImplement the NOCOPY Compiler HintInvoke the PARALLEL_ENABLE HintThe Cross-Session PL/SQL Function Result CacheThe DETERMINISTIC Clause with FunctionsUsage of Bulk Binding to Improve PerformanceTriggersDescribe TriggersIdentify the Trigger Event Types and BodyBusiness Application Scenarios for Implementing TriggersCreate DML Triggers using the CREATE TRIGGER Statement and SQL DeveloperIdentify the Trigger Event Types, Body, and Firing (Timing)Differences between Statement Level Triggers and Row Level TriggersCreate Instead of and Disabled TriggersHow to Manage, Test and Remove Triggers?Creating Compound, DDL, and Event Database TriggersWhat are Compound Triggers?Identify the Timing-Point Sections of a Table Compound TriggerUnderstand the Compound Trigger Structure for Tables and ViewsImplement a Compound Trigger to Resolve the Mutating Table ErrorComparison of Database Triggers to Stored ProceduresCreate Triggers on DDL StatementsCreate Database-Event and System-Events TriggersSystem Privileges Required to Manage TriggersPL/SQL Compiler What is the PL/SQL Compiler?Describe the Initialization Parameters for PL/SQL CompilationList the new PL/SQL Compile Time WarningsOverview of PL/SQL Compile Time Warnings for SubprogramsList the benefits of Compiler WarningsList the PL/SQL Compile Time Warning Messages CategoriesSetting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBMS_WARNING Package SubprogramsView Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary ViewsManage DependenciesOverview of Schema Object DependenciesQuery Direct Object Dependencies using the USER_DEPENDENCIES ViewQuery an Object’s StatusInvalidation of Dependent ObjectsDisplay the Direct and Indirect DependenciesFine-Grained Dependency Management in Oracle Database 12cUnderstand Remote DependenciesRecompile a PL/SQL Program UnitOracle Fusion Middleware 11g: Build Applications with Oracle FormsRunning a Forms ApplicationRunning a FormIdentifying the Data ElementsNavigating a Forms ApplicationUsing the Modes of OperationQuerying DataInserting, Updating, and Deleting RecordsSaving ChangesDisplaying ErrorsWorking in the Forms Builder EnvironmentForms Builder Key FeaturesForms Builder Components Navigating the Forms Builder InterfaceForms Builder Module Object Hierarchy Customizing Your Forms Builder Session Forms Executables and Module TypesDefining Environment Variables Testing a Form with the Run Form ButtonCreating a Basic Form ModuleCreating a New Form ModuleCreating a New Data Bock Using Template FormsSaving and Compiling a Form Module Module Types and Storage FormatsDeploying a Form ModuleProducing DocumentationCreating a Master-Detail FormCreating Data Blocks with Relationships Running a Master-Detail Form ModuleModifying the Structure of a Data BlockModifying the Layout of a Data Block Working Data Blocks and FramesManaging Object PropertiesCreating Visual AttributesControlling the Behavior and Appearance of Data BlocksControlling Frame PropertiesDisplaying Multiple Property PalettesSetting Properties on Multiple ObjectsCopying Properties Creating Control Blocks Working with Text ItemsCreating a Text Item Modifying the Appearance of a Text Item Controlling the Data of a Text ItemAltering the Navigational Behavior of a Text ItemEnhancing the Relationship between Text Item and Database Adding Functionality to a Text Item Displaying Helpful MessagesCreating LOVs and EditorsLOVs and Record Groups Creating an LOV Manually Using the LOV Wizard to Create an LOVSetting LOV PropertiesLOV Column Mapping Defining an Editor Setting Editor Properties Associating an Editor with a Text Item Creating Additional Input ItemsInput Items Overview Creating a Check Box Creating a List Item Creating a Radio Group Creating Noninput ItemsNoninput Items OverviewCreating a Display ItemCreating an Image Item Creating a Push Button Creating a Calculated Item Creating a Hierarchical Tree Item Creating a Bean Area Item Creating Windows and Content CanvasesDisplaying a Form Module in Multiple WindowsCreating a New Window Displaying a Form Module on Multiple Layouts Creating a New Content Canvas Working with Other Canvas TypesOverview of Canvas Types Creating a Stacked Canvas Creating a Toolbar Creating a Tab Canvas Producing and Debugging TriggersTrigger Overview Creating Triggers in Forms BuilderSpecifying Execution Hierarchy PL/SQL Editor Features Using the Database Trigger Editor Using Variables in Triggers Adding Functionality with Built-in Subprograms Using the Forms DebuggerAdding Functionality to ItemsCoding Item Interaction TriggersInteracting with Noninput Items Displaying Run-Time Messages and AlertsBuilt-Ins and Handling Errors Controlling System MessagesThe FORM_TRIGGER_FAILURE Exception Triggers for Intercepting System MessagesCreating and Controlling Alerts Handling Server Errors Using Query TriggersQuery Processing Overview SELECT Statements Issued During Query Processing Setting WHERE and ORDER BY clauses and ONETIME_WHERE property Writing Query Triggers Query Array Processing Coding Triggers for Enter-Query Mode Overriding Default Query Processing Obtaining Query Information at Run Time Validating User InputValidation ProcessControlling Validation by Using PropertiesControlling Validation by Using Triggers Performing Client-Side Validation with PJCs Tracking Validation StatusUsing Built-ins to Control When Validation Occurs Controlling NavigationUsing Object Properties to Control NavigationWriting Navigation Triggers Avoiding the Navigation Trap Using Navigation Built-Ins in TriggersOverriding or Supplementing Transaction ProcessingTransaction Processing OverviewUsing Commit TriggersTesting the Results of Trigger DMLDML Statements Issued during Commit ProcessingOverriding Default Transaction ProcessingGetting and Setting the Commit Status Implementing Array DMLWriting Flexible CodeWhat is Flexible Code? Using System Variables for Flexible CodingUsing Built-in Subprograms for Flexible CodingCopying and Subclassing Objects and CodeReferencing Objects by Internal IDReferencing Items IndirectlySharing Objects and CodeBenefits of Reusable Objects and Code Working with Property ClassesWorking with Object GroupsCopying and Subclassing Objects and CodeWorking with Object LibrariesWorking with SmartClassesReusing PL/SQLWorking with PL/SQL LibrariesUsing WebUtil to Interact with the ClientBenefits of WebUtilIntegrating WebUtil into a FormInteracting with the ClientIntroducing Multiple Form ApplicationsMultiple Form Applications Overview Starting Another Form ModuleDefining Multiple Form FunctionalitySharing Data among ModulesOracle Reports Developer 10g: Build ReportsIntroduction to Oracle Reports DeveloperBusiness IntelligenceEnterprise ReportingOracle Reports DeveloperOracle Database 10gOracle Developer Suite 10gOracle Application Server 10gOracleAS Reports ServicesOracleAS Reports Services Architecture for the WebDesigning and Running ReportsUnderstanding User RequirementsDesigning ReportsTabularMaster-DetailMaster with Two DetailsMatrixRetrieving and Sharing DataRunning a ReportExploring Oracle Reports DeveloperReports Developer ExecutablesInvoking Reports BuilderReports Builder ModulesReport Data and LayoutReports Builder ComponentsObject NavigatorReport-Level ObjectsData Model ObjectsCreating a Paper ReportReport Module ComponentsBuilding a Paper ReportViewing the Paper Report OutputSaving the Report DefinitionReentering the WizardCreating Break ReportsBreak Report LabelsCreating Mailing Labels and LettersEnhancing a Basic Paper ReportWhat Is the Paper Design?The Paper Design WindowModifying a ReportAligning ColumnsSetting a Format MaskManipulating ObjectsModifying Visual AttributesApplying Conditional FormattingManaging Report TemplatesUsing Report TemplatesModifying a TemplateCustomizing the Template MarginCustomizing the Template BodyAdding Web Links to a Template for Report HTML OutputPredefining Your Own TemplatesAdding a Template Preview ImageCreating a Web ReportWhat Is JSP Technology?JSP AdvantagesSimple JSP ExampleBuilding a Web ReportUsing the Report WizardReport Editor: Web Source ViewJSP TagsWeb Source ExampleEnhancing Reports Using the Data Model: Queries and GroupsThe Data Model ObjectsModifying Properties of a QueryApplying ChangesChanging the Group StructureGroup HierarchyOrdering Data in a GroupQuery ModificationsFiltering Data in a GroupEnhancing Reports Using the Data Model: Data SourcesData Source TypesPluggable Data SourcesUsing XML as a Data SourceDocument Type Definition FileOLAP Data SourceUsing Text as a Data SourceUsing JDBC as a Data SourceUsing REF Cursor QueriesEnhancing Reports Using the Data Model: Creating ColumnsData Model ColumnsMaintaining Data Source ColumnsProducing File Content OutputCreating a ColumnCreating Summary ColumnsDisplaying SubtotalsDisplaying PercentagesCreating a Formula ColumnEnhancing Reports Using the Paper LayoutViewing the Paper LayoutDesigning Multipanel ReportsPrinting Multipanel ReportsDifferent Objects in the Paper LayoutThe Paper Layout LayersReport ProcessingPaper Layout ToolsReport BurstingControlling the Paper Layout: Common PropertiesModifying Paper Layout Object PropertiesCommon Layout PropertiesSizing ObjectsAnchorsLayout Object RelationshipsPagination Icons in the Paper LayoutControlling Print FrequencyUsing Format TriggersControlling the Paper Layout: Specific PropertiesProperties of a Repeating FrameSpecifying Print DirectionControlling the Number of Records per PageControlling Spacing Between RecordsMinimum Widow RecordsSystem VariablesValid Source ColumnsDisplaying File ContentsWeb ReportingComparing Static and Dynamic ReportingAdding Dynamic ContentCreating a Report BlockInvoking the Report Block WizardExamining the Web Source Coderw:foreach Tagrw:field TagCustomizing Reports JSPsExtending Functionality Using XMLWhy Use XML Report Definitions?Creating XML Report DefinitionsPartial Report Definitions: Format Modification ExamplePartial Report Definitions: Format Exception ExampleFull Report Definition: Data Model Modification ExampleRunning XML Report DefinitionsDebugging XML Report DefinitionsCreating and Using Report ParametersCreating User ParametersReferencing Parameters in a Report QueryUsing Bind ReferencesUsing Lexical ReferencesHints and Tips When Referencing ParametersCreating a List of ValuesReferencing System ParametersBuilding a Paper Parameter FormEmbedding a Graph in a ReportAdding a Graph to a Paper ReportAdding a Graph to a Web ReportSelecting the Graph TypeSelecting the Graph DataAdding Options to the GraphCustomizing Web GraphsThe rw:graph TagCustomizing Graphs Using the Graph.XML FileEnhancing Matrix ReportsThe Matrix Data ModelThe Matrix Paper LayoutCreating Matrix SummariesCreating the Matrix ManuallyThe Matrix with Group Data ModelThe Matrix with Group LayoutBuilding a Nested MatrixNested Matrix Paper LayoutCoding PL/SQL TriggersTypes of Triggers in ReportsTrigger CodeUsing Report TriggersUsing Data Model Triggers: PL/SQL Group FilterUsing Data Model Triggers: Parameter ValidationUsing Layout TriggersUsing Format TriggersEvent-Based ReportingExtending Functionality Using the SRW PackageContents of the SRW PackageOutputting MessagesExecuting a Nested ReportRestricting DataInitializing FieldsCreating a Table of ContentsPerforming DDL StatementsSetting Format AttributesMaximizing Performance Using OracleAS Reports ServicesRunning Reports Using OracleAS Reports ServicesReport Request MethodsOracle Application Server ComponentsEnabling Single Sign-On AccessRunning the Web Layout: JSP Run-time ArchitectureRunning the Paper Layout: Servlet Run-time ArchitectureRunning a Paper Report on the WebQueue ManagerBuilding Reports: Efficiency GuidelinesTuning ReportsPerformance MeasurementNon SQL Data SourcesInvestigating the Data ModelInvestigating the Paper LayoutRunning the ReportDifferent Development and Run-Time EnvironmentsDeveloping Reports to Run in Different GUIsFor further Information/Free Demo please SMS(Course)/Call at the under-given numbers :- Kalkaji : 9311002620 South Ex : 9311149666 Noida : 9310254311 Gurgaon : 9310806006 Tagore Garden : 9311306006 Pitampura : 9311962325 Mumbai : 9022110049 Jalandhar : 9988115054 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download