Oracle Database 11g SQL Fundamental I & II


Oracle Database 11g SQL Fundamental I & II

این دوره برای مدیران پایگاه داده اوراکل و تمامی برنامه نویسانی که با بانک اوراکل در ارتباط هستند مفید می باشد.در این دوره دانش آموزان با مفاهیم پایگاه داده رابطه ای آشنا می شوند . این دوره ضروریات مهارت SQL را بیان می کند و به برنامه نویسان و مدیران پایگاه داده اوراکل اجازه می دهد:

  • نوشتن پرس و جو های ساده و پیچده از روی یک جدول یا چند جدول

  • نوشتن پرس و جوهای تو در تو

  • دستکاری داده ها در جداول توسط دستورات DML

  • ایجاد اشیاء پایگاه داده با دستور DDL

  • کنترل دسترسی در سطح شی و سیستم.(Object privileges , System privileges)

  • ایجاد محدودیت ها (Constraints)

  • ایجاد شاخص (Index)

  • اصلاح اشیاء موجود در پایگاه داده

  • ایجاد پرس و جو روی جداول خارجی که به شکل یک فایل متنی تعریف شده اند.

  • استفاده از ویژگی های پیشرفته جستجو و دستکاری داده ها در پایگاه داده .

  • استفاده از فرهنگ لغت پایگاه داده (Data Dictionary) برای نمایش ساختار اشیاء و ایجاد گزارش در مورد اشیاء موجود در پایگاه داده.

  • چگونگی استفاده از توابع موجود در بانک اطلاعاتی اوراکل یاد بگیرند.

  • آشنایی و نحوه استفاده از ابزارهای مختلف اوراکل مثل

SQL Plus, SQL Developer, PLSQL Developer

پیشنیاز: ندارد.

سرفصل دروس:

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