Oracle 11g SQL Fundamentals Training Introduction-01

Objectives

After comleting this lesson,you should be able to do the following:

• Understant the goals of course.
• Discuss the theoretical and physical aspect of arelational database.
• Identify the development environments that can be used fo this coure
• Describe the database and schema used in this course.

Course Objectives(上篇)

• Retrieve row and column data from tables with the SELECT statement
• Create reports of sorted and resticted data.
• Employ SQL functions to generate and retrieve customized data.
• Run complex queries to retrieve data from multiple tables.
• Run data manipulation language(DML) statements to update data in Oracle database 11g
• Run data definition language(DDL) statments to create and manage schema objects.

Course Objectives(下篇)

• Control database access to specific objects
• Add new users with different levels of access privileges
• Manage schema objects
• Manage objects with data dictionary views
• Maniplulate large data sets in the Oracle database by using subqueries
• Manage data in different time zones
• Write multiple-column subqueries
• Use scalar and corelated subqueries
• Use the regular expression support in SQL

Course Agenda

• Introduction
• Retrieving Data Using the SQL SELECT statement
• Restricting and Sorting Data
• Using Single-Row Functions to Customer Output
• Using Conversion Functions and Conditional Expressions
• Reporting Aggregated Data Using the Group Functions
• Displaying Data from Multiple Tables
• Using Subqueries to Solve Queries
• Using the Set Operators
• Manipulating Data
• Using DDL Statements to Create and Manage Tables
• Creating Other Schema Objects
• Controlling User Access
• Managing Schema Objects
• Managing Schema with Data Dictionary Views
• Manipulating Large Data Sets
• Managing Data in Different Time Zones
• Retrieving Data Using Subqueries
• Regular Expression Support

《Oracle Database 11g SQL》Master SQL and PL/SQL in the Oracle Database

《Oracle Essential》

Relational Database Concept

• Dr.E.F Codd proposed the relational model for database systems in 1970
  • -A Relational Model of Data for large Shared Data Bank
• It is the basis for the relational database management system(RDBMS)
• The relational model consists of the following:
  • -Collection of objects or relations
  • -Set of operators to act on the relations
  • -Data integrity for accuracy and consistency

Definition of a Relational Database

A relational database is a collection of relations or two-dimensional tables.

Entity Relationship Model

• Create an entity relationship diagram from business specification or narratives:

• Scenario:
  • -"... Assign one or more employees to a department..."
  • -"... Some departments do not yet have assigned employees..."

Entity Relationship Modeling Conventions

Relating Multiple Tables

• Each row of data in a table is unqiuely identified by a primary key.
• You can logically  relate data from multiple tables using foreign keys.(foreign key 就是外包中的主键，在本表中引用.)