In this course students learn the concepts of relational databases. This course provides the essential SQL skills that allow developers to write queries against single and multiple tables, manipulate data in tables, and create database objects. Students learn to control privileges at the object and system level.
This course covers creating indexes and constraints, and altering existing schema objects. Students also learn how to create and query external tables. Students learn to use the advanced features of SQL in order to query and manipulate data within the database, use the dictionary views to retrieve metadata and create reports about their schema objects. Students also learn some of the date-time functions available in the Oracle Database. This course discusses how to use the regular expression support in SQL.
In this course, students use Oracle SQL Developer as the main development tool. SQL*Plus is introduced as an optional development tool.
Introducing Oracle Database 11gList the features of Oracle Database 11gDiscuss the basic design, theoretical and physical aspects of a relational databaseCategorize the different types of SQL statementsDescribe the data set used by the courseLog onto the database using the SQL Developer environmentSave queries to files and use script files in SQL DeveloperRetrieving 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 structureRestricting and Sorting 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 sort the output of a SELECT statementSort output in descending and ascending orderUsing 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 functionsUsing 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 statementReporting Aggregated Data Using the Group FunctionsUse the aggregation functions in SELECT statements to produce meaningful reportsCreate queries that divide the data in groups by using the GROUP BY clauseCreate queries that exclude groups of date by using the HAVING clauseDisplaying Data From Multiple TablesWrite 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 by using a self joinUsing 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-queriesUsing the SET OperatorsDescribe the SET operatorsUse a SET operator to combine multiple queries into a single queryControl the order of rows returned when using the SET operatorsManipulating DataDescribe each DML statementInsert rows into a table with the INSERT statementUse the UPDATE statement to change rows in a tableDelete rows from a table with the DELETE statementSave and discard changes with the COMMIT and ROLLBACK statementsExplain read consistencyUsing 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 workCreating Other Schema ObjectsCreate a simple and complex viewRetrieve data from viewsCreate, maintain, and use sequencesCreate and maintain indexesCreate private and public synonymsControlling User AccessDifferentiate system privileges from object privilegesGrant privileges on tablesView privileges in the data dictionaryGrant rolesDistinguish between privileges and rolesManaging Schema ObjectsAdd constraintsCreate indexesCreate indexes using the CREATE TABLE statementCreate function-based indexesDrop columns and set column UNUSEDPerform FLASHBACK operationsCreate and use external tablesManaging Objects with Data Dictionary ViewsExplain the data dictionaryFind table informationReport on column informationView constraint informationFind view informationVerify sequence informationUnderstand synonymsAdd commentsManipulating Large Data SetsManipulate data using sub-queriesDescribe the features of multi-table insertsUse the different types of multi-table insertsMerge rows in a tableTrack the changes to data over a period of timeManaging Data in Different Time ZonesUse data types similar to DATE that store fractional seconds and track time zonesUse data types that store the difference between two date-time valuesPractice using the multiple data-time functions for globalize applicationsRetrieving Data Using Sub-queriesWrite a multiple-column sub-queryUse scalar sub-queries in SQLSolve problems with correlated sub-queriesUpdate and delete rows using correlated sub-queriesUse the EXISTS and NOT EXISTS operatorsUse the WITH clauseRegular Expression SupportList the benefits of using regular expressionsUse regular expressions to search for, match, and replace strings
Retrieve row and column data from tables with the SELECT statementCreate reports of sorted and restricted dataEmploy SQL functions to generate and retrieve customized dataDisplay data from multiple tables using the ANSI SQL 99 JOIN syntaxIdentify the major structural components of the Oracle Database 11gCreate reports of aggregated dataWrite SELECT statements that include queriesRun data manipulation statements (DML) to update data in the Oracle Database 11gCreate tables to store dataUtilize views to display and retrieve dataControl database access to specific objectsManage schema objectsManage objects with data dictionary viewsWrite multiple-column sub-queriesUse scalar and correlated sub-queriesUse the regular expression support in SQL