Microsoft Csg1 [Read Only] Oracle Database 11g SQL Fundamentals I Volume II Student Guide
D59981-Oracle-SQL-Fundamentals-Student-Guide-Volume-1-II
User Manual: Pdf
Open the PDF directly: View PDF
Page Count: 298 [warning: Documents this large are best viewed by clicking the View PDF Link!]
- Cover Page
- Table of Contents
- Lesson 9: Manipulating Data
- Objectives
- Lesson Agenda
- Data Manipulation Language
- Adding a New Row to a Table
- INSERT Statement Syntax
- Inserting New Rows
- Inserting Rows with Null Values
- Inserting Special Values
- Inserting Specific Date and Time Values
- Creating a Script
- Copying Rows from Another Table
- Lesson Agenda
- Changing Data in a Table
- UPDATE Statement Syntax
- Updating Rows in a Table•
- Updating Two Columns with a Subquery
- Updating Rows Based on Another Table
- Lesson Agenda
- Removing a Row from a Table
- DELETE Statement
- Deleting Rows from a Table
- Deleting Rows Based on Another Table
- TRUNCATE Statement
- Lesson Agenda
- Database Transactions
- Database Transactions: Start and End
- Advantages of COMMIT and ROLLBACK Statements
- Explicit Transaction Control Statements
- Rolling Back Changes to a Marker
- Implicit Transaction Processing
- State of the Data Before COMMIT or ROLLBACK
- State of the Data After COMMIT
- Committing Data
- State of the Data After ROLLBACK
- State of the Data After ROLLBACK: Example
- Statement-Level Rollback
- Lesson Agenda
- Read Consistency
- Implementing Read Consistency
- Lesson Agenda
- FOR UPDATE Clause in a SELECT Statement
- FOR UPDATE Clause: Examples
- Quiz
- Summary
- Practice 9: Overview
- Lesson 10: Using DDL Statements to Create and Manage Tables
- Objectives
- Lesson Agenda
- Database Objects
- Naming Rules
- Lesson Agenda
- CREATE TABLE Statement
- Referencing Another User’s Tables
- DEFAULT Option
- Creating Tables
- Lesson Agenda
- Data Types
- Datetime Data Types
- Lesson Agenda
- Including Constraints
- Constraint Guidelines
- Defining Constraints
- NOT NULL Constraint
- UNIQUE Constraint
- PRIMARY KEY Constraint
- FOREIGN KEY Constraint
- FOREIGN KEY Constraint: Keywords
- CHECK Constraint
- CREATE TABLE: Example
- Violating Constraints
- Lesson Agenda
- Creating a Table Using a Subquery
- Lesson Agenda
- ALTER TABLE Statement
- Read-Only Tables
- Lesson Agenda
- Dropping a Table
- Quiz
- Summary
- Practice 10: Overview
- Lesson 11: Creating Other Schema Objects
- Objectives
- Lesson Agenda
- Database Objects
- What Is a View?
- Advantages of Views
- Simple Views and Complex Views
- Creating a View
- Retrieving Data from a View
- Modifying a View
- Creating a Complex View
- Rules for Performing DML Operations on a View
- Using the WITH CHECK OPTION Clause
- Denying DML Operations
- Removing a View
- Practice 11: Overview of Part 1
- Lesson Agenda
- Sequences
- CREATE SEQUENCE Statement: Syntax
- Creating a Sequence
- NEXTVAL and CURRVAL Pseudocolumns
- Using a Sequence
- Caching Sequence Values
- Modifying a Sequence
- Guidelines for Modifying a Sequence
- Lesson Agenda
- Indexes
- How Are Indexes Created?
- Creating an Index
- Index Creation Guidelines
- Removing an Index
- Lesson Agenda
- Synonyms
- Creating a Synonym for an Object
- Creating and Removing Synonyms
- Quiz
- Summary
- Practice 11: Overview of Part 2
- Appendix A: Practice Solutions
- Solutions for Practice I: Introduction
- Solutions for Practice 1: Retrieving Data Using the SQL SELECT Statement
- Solutions for Practice 2: Restricting and Sorting Data
- Solutions for Practice 3: Using Single-Row Functions to Customize Output
- Solutions for Practice 4: Using Conversion Functions and Conditional Expressions
- Solutions for Practice 5: Reporting Aggregated Data Using the Group Functions
- Solutions for Practice 6: Displaying Data from Multiple Tables
- Solutions for Practice 7: Using Subqueries to Solve Queries
- Solutions for Practice 8: Using the Set Operators
- Solutions for Practice 9: Manipulating Data
- Solutions for Practice 10: Using DDL Statements to Create and Manage Tables
- Solutions for Practice 11: Creating Other Schema Objects
- Solutions for Practice C: Oracle Join Syntax
- Appendix B: Table Descriptions
- Appendix C: Oracle Join Syntax
- Objectives
- Obtaining Data from Multiple Tables
- Cartesian Products
- Generating a Cartesian Product
- Types of Oracle-Proprietary Joins
- Joining Tables Using Oracle Syntax
- Qualifying Ambiguous Column Names
- Equijoins
- Retrieving Records with Equijoins
- Retrieving Records with Equijoins: Example
- Additional Search Conditions Using the AND Operator
- Joining More than Two Tables
- Nonequijoins
- Retrieving Records with Nonequijoins
- Returning Records with No Direct Match with Outer Joins
- Outer Joins: Syntax
- Using Outer Joins
- Outer Join: Another Example
- Joining a Table to Itself
- Self-Join: Example
- Summary
- Practice C: Overview
- Appendix D: Using SQL*Plus
- Objectives
- SQL and SQL*Plus Interaction
- SQL Statements Versus SQL*Plus Commands
- Overview of SQL*Plus
- Logging In to SQL*Plus
- Changing the Settings of SQL*Plus Environment
- Displaying Table Structure
- SQL*Plus Editing Commands
- Using LIST, n, and APPEND
- Using the CHANGE Command
- SQL*Plus File Commands
- Using the SAVE, START, and EDIT Commands
- SERVEROUTPUT Command
- Using the SQL*Plus SPOOL Command
- Using the AUTOTRACE Command
- Summary
- Appendix E: Using SQL Developer
- Objectives
- What Is Oracle SQL Developer?
- Specifications of SQL Developer
- Installing SQL Developer
- SQL Developer 1.2 Interface
- Creating a Database Connection
- Browsing Database Objects
- Creating a Schema Object
- Creating a New Table: Example
- Using the SQL Worksheet
- Executing SQL Statements
- Saving SQL Scripts
- Executing Saved Script Files: Method 1
- Executing Saved Script Files: Method 2
- Executing SQL Statements
- Formatting the SQL Code
- Using Snippets
- Using Snippets: Example
- Using SQL*Plus
- Debugging Procedures and Functions
- Database Reporting
- Creating a User-Defined Report
- Search Engines and External Tools
- Setting Preferences
- Specifications of SQL Developer 1.5.3
- Installing SQL Developer 1.5.3
- SQL Developer 1.5.3 Interface
- Summary
- index