Oracle Database 11g: Introduction to SQL

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.

Learn To:

	Create reports of sorted and restricted data
	Run data manipulation statements (DML) to update data
	Control database access to specific objects
	Manage schema objects
	Manage objects with data dictionary views
	Retrieve row and column data from tables

course content details

	Introducing Oracle Database 11g

	List the features of Oracle Database 11g
	Discuss the basic design, theoretical and physical aspects of a relational database
	Categorize the different types of SQL statements
	Describe the data set used by the course
	Log onto the database using the SQL Developer environment
	Save queries to files and use script files in SQL Developer

	Retrieving Data Using the SQL SELECT Statement

	List the capabilities of SQL SELECT statements
	Generate a report of data from the output of a basic SELECT statement
	Select All Columns
	Select Specific Columns
	Use Column Heading Defaults
	Use Arithmetic Operators
	Understand Operator Precedence
	Learn the DESCRIBE command to display the table structure

	Restricting and Sorting Data

	Write queries that contain a WHERE clause to limit the output retrieved
	List the comparison operators and logical operators that are used in a WHERE clause
	Describe the rules of precedence for comparison and logical operators
	Use character string literals in the WHERE clause
	Write queries that contain an ORDER BY clause sort the output of a SELECT statement
	Sort output in descending and ascending order

	Using Single-Row Functions to Customize Output

	Describe the differences between single row and multiple row functions
	Manipulate strings with character function in the SELECT and WHERE clauses
	Manipulate numbers with the ROUND, TRUNC and MOD functions
	Perform arithmetic with date data
	Manipulate dates with the date functions

	Using Conversion Functions and Conditional Expressions

	Describe implicit and explicit data type conversion
	Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
	Nest multiple functions
	Apply the NVL, NULLIF, and COALESCE functions to data
	Use conditional IF THEN ELSE logic in a SELECT statement

	Reporting Aggregated Data Using the Group Functions

	Use the aggregation functions in SELECT statements to produce meaningful reports
	Create queries that divide the data in groups by using the GROUP BY clause
	Create queries that exclude groups of date by using the HAVING clause

	Displaying Data From Multiple Tables

	Write SELECT statements to access data from more than one table
	View data that generally does not meet a join condition by using outer joins
	Join a table by using a self join

	Using Sub-queries to Solve Queries

	Describe the types of problem that sub-queries can solve
	Define sub-queries
	List the types of sub-queries
	Write single-row and multiple-row sub-queries

	Using the SET Operators

	Describe the SET operators
	Use a SET operator to combine multiple queries into a single query
	Control the order of rows returned when using the SET operators

	Manipulating Data

	Describe each DML statement
	Insert rows into a table with the INSERT statement
	Use the UPDATE statement to change rows in a table
	Delete rows from a table with the DELETE statement
	Save and discard changes with the COMMIT and ROLLBACK statements
	Explain read consistency

	Using DDL Statements to Create and Manage Tables

	Categorize the main database objects
	Review the table structure
	List the data types available for columns
	Create a simple table
	Decipher how constraints can be created at table creation
	Describe how schema objects work

	Creating Other Schema Objects

	Create a simple and complex view
	Retrieve data from views
	Create, maintain, and use sequences
	Create and maintain indexes
	Create private and public synonyms

	Controlling User Access

	Differentiate system privileges from object privileges
	Grant privileges on tables
	View privileges in the data dictionary
	Grant roles
	Distinguish between privileges and roles

	Managing Schema Objects

	Add constraints
	Create indexes
	Create indexes using the CREATE TABLE statement
	Create function-based indexes
	Drop columns and set column UNUSED
	Perform FLASHBACK operations
	Create and use external tables

	Managing Objects with Data Dictionary Views

	Explain the data dictionary
	Find table information
	Report on column information
	View constraint information
	Find view information
	Verify sequence information
	Understand synonyms
	Add comments

	Manipulating Large Data Sets

	Manipulate data using sub-queries
	Describe the features of multi-table inserts
	Use the different types of multi-table inserts
	Merge rows in a table
	Track the changes to data over a period of time

	Managing Data in Different Time Zones

	Use data types similar to DATE that store fractional seconds and track time zones
	Use data types that store the difference between two date-time values
	Practice using the multiple data-time functions for globalize applications

	Retrieving Data Using Sub-queries

	Write a multiple-column sub-query
	Use scalar sub-queries in SQL
	Solve problems with correlated sub-queries
	Update and delete rows using correlated sub-queries
	Use the EXISTS and NOT EXISTS operators
	Use the WITH clause

	Regular Expression Support

	List the benefits of using regular expressions
	Use regular expressions to search for, match, and replace strings