Student Guide SQL Fundamentals
User Manual:
Open the PDF directly: View PDF
Page Count: 838 [warning: Documents this large are best viewed by clicking the View PDF Link!]
- SQL Fundamentals
- Table of Contents
- Lesson 1: Introduction
- Lesson 2: Relational Database Overview
- Course Roadmap
- Objectives
- Lesson Agenda
- Database: Definition
- Data Storage on Different Media
- Database Management System (DBMS)
- Why Do I Need a Database Solution?
- Examples of Databases
- Lesson Agenda
- Oracle Database 12c: Focus Areas
- Oracle Database 12c
- Lesson Agenda
- Relational and Object Relational Database Management Systems
- Relational Database Concept
- Definition of a Relational Database
- Data Models
- Entity Relationship Model
- Entity Relationship Modeling Conventions
- Relating Multiple Tables
- Relational Database Terminology
- Advantages of a Relational Database
- Lesson Agenda
- OLTP Versus OLAP
- SQL Database Versus NoSQL Database
- Multitenant Architecture
- Introduction to Oracle Cloud
- Oracle Cloud Services
- Database on Oracle Cloud
- Quiz
- Summary
- Practice 2: Overview
- Lesson 3: Database Storage Structures
- Course Roadmap
- Objectives
- Lesson Agenda
- Database Data Storage
- Lesson Agenda
- Introduction to Logical Structures
- Data Blocks
- Extents
- Segments
- Tablespaces
- Lesson Agenda
- Introduction to Physical Storage Structures
- Data Files
- Control Files
- Online Redo Log Files
- Lesson Agenda
- Relational Tables
- Quiz
- Summary
- Practice 3: Overview
- Lesson 4: Introduction to SQL
- Course Roadmap
- Objectives
- Lesson Agenda
- Using SQL to Query Your Database
- SQL Statements Used in the Course
- Lesson Agenda
- Introduction to PL/SQL
- Lesson Agenda
- Human Resources (HR) Schema for This Course
- Tables Used in the Course
- Academic (AD) Schema
- Academic (AD) Schema (Notes Only)
- Class Account Information
- Course Environment
- Lesson Agenda
- SQL Development Environments
- What Is Oracle SQL Developer?
- Specifications of SQL Developer
- SQL Developer 4.1.3 Interface
- SQL Developer 4.1.3 Interface (Notes Only)
- Creating a Database Connection
- Coding SQL in SQL*Plus
- Creating a Connection to Database on Oracle Cloud
- Quiz
- Summary
- Practice 4: Overview
- Lesson 5: Retrieving Data Using the SQL SELECT Statement
- Course Roadmap
- Objectives
- Lesson Agenda
- Basic SELECT Statement
- Selecting All Columns
- Selecting Specific Columns
- Writing SQL Statements
- Column Heading Defaults for Output
- Lesson Agenda
- Arithmetic Expressions
- Using Arithmetic Operators
- Operator Precedence
- Defining a Null Value
- Lesson Agenda
- Defining a Column Alias
- Using Column Aliases
- Lesson Agenda
- Concatenation Operator
- Literal Character Strings
- Using Literal Character Strings
- Alternative Quote (q) Operator
- Using the DISTINCT keyword
- Using DISTINCT with Multiple Columns
- Lesson Agenda
- Displaying Table Structure
- Using the DESCRIBE Command
- Quiz
- Summary
- Practice 5: Overview
- Lesson 6: Restricting and Sorting Data
- Course Roadmap
- Objectives
- Lesson Agenda
- Limiting Rows by Using a Selection
- Limiting Rows That Are Selected
- Using the WHERE Clause
- Character Strings and Dates
- Comparison Operators
- Using Comparison Operators
- Range Conditions Using the BETWEEN Operator
- Using the IN Operator
- Pattern Matching Using the LIKE Operator
- Combining Wildcard Characters
- Using NULL Conditions
- Defining Conditions Using Logical Operators
- Using the AND Operator
- Using the OR Operator
- Using the NOT Operator
- Lesson Agenda
- Rules of Precedence
- Lesson Agenda
- Using the ORDER BY Clause
- Sorting
- Lesson Agenda
- Using SQL Row Limiting Clause in a Query
- SQL Row Limiting Clause: Example
- Lesson Agenda
- Substitution Variables
- Using the Single-Ampersand Substitution Variable
- Character and Date Values with Substitution Variables
- Specifying Column Names, Expressions, and Text
- Using the Double-Ampersand Substitution Variable
- Lesson Agenda
- Using the DEFINE Command
- Using the VERIFY Command
- Quiz
- Summary
- Practice 6: Overview
- Lesson 7: Using Single-Row Functions to Customize Output
- Course Roadmap
- Objectives
- Lesson Agenda
- SQL Functions
- Two Types of SQL Functions
- Single-Row Functions
- Lesson Agenda
- Character Functions
- Case-Conversion Functions
- Using Case-Conversion Functions
- Character-Manipulation Functions
- Using Character-Manipulation Functions
- Lesson Agenda
- Nesting Functions
- Nesting Functions: Example
- Lesson Agenda
- Numeric Functions
- Using the ROUND Function
- Using the TRUNC Function
- Using the MOD Function
- Lesson Agenda
- Working with Dates
- RR Date Format
- Using the SYSDATE Function
- Using the CURRENT_DATE and CURRENT_TIMESTAMP Functions
- Arithmetic with Dates
- Using Arithmetic Operators with Dates
- Lesson Agenda
- Date-Manipulation Functions
- Using Date Functions
- Using ROUND and TRUNC Functions with Dates
- Quiz
- Summary
- Practice 7: Overview
- Lesson 8: Using Conversion Functions
- Course Roadmap
- Objectives
- Lesson Agenda
- Conversion Functions
- Implicit Data Type Conversion
- Explicit Data Type Conversion
- Lesson Agenda
- Using the TO_CHAR Function with Dates
- Elements of the Date Format Model
- Using the TO_CHAR Function with Dates
- Using the TO_CHAR Function with Numbers
- Using the TO_NUMBER and TO_DATE Functions
- Using the TO_CHAR and TO_DATE Functions with the RR Date Format
- Lesson Agenda
- General Functions
- NVL Function
- Using the NVL Function
- Using the NVL2 Function
- Using the NULLIF Function
- Using the COALESCE Function
- Quiz
- Summary
- Practice 8: Overview
- Lesson 9: Using Conditional Expressions
- Lesson 10: Reporting Aggregated Data Using the Group Functions
- Course Roadmap
- Objectives
- Lesson Agenda
- What Is Data Aggregation?
- Lesson Agenda
- Types of Group Functions
- Group Functions
- Group Functions: Syntax
- Common Group Functions
- Using the AVG and SUM Functions
- Using the MIN and MAX Functions
- Using the COUNT Function
- Using DISTINCT in COUNT function
- Group Functions and Null Values
- Lesson Agenda
- Creating Groups of Data
- Creating Groups of Data: GROUP BY Clause Syntax
- Using the GROUP BY Clause
- Grouping by More Than One Column
- Using the GROUP BY Clause on Multiple Columns
- Common Errors: Using Group Functions
- Restricting Group Results: Using the HAVING Clause
- Restricting Group Results with the HAVING Clause
- Using the HAVING Clause
- Lesson Agenda
- Nesting Group Functions
- Quiz
- Summary
- Practice 10: Overview
- Lesson 11: Retrieving Data from Multiple Tables Using Joins
- Course Roadmap
- Objectives
- Lesson Agenda
- Why Join?
- Obtaining Data from Multiple Tables
- Types of Joins
- Joining Tables Using the SQL:1999 Syntax
- Inner Joins
- Creating Natural Joins
- Retrieving Records with Natural Joins
- Creating Joins with the USING Clause
- Joining Column Names
- Retrieving Records with the USING Clause
- Qualifying Ambiguous Column Names
- Using Table Aliases with the USING Clause
- Creating Joins with the ON Clause
- Retrieving Records with the ON Clause
- Creating Three-Way Joins
- Applying Additional Conditions to a Join
- Lesson Agenda
- Joining a Table to Itself
- Self-Joins Using the ON Clause
- Lesson Agenda
- Nonequijoins
- Retrieving Records with Nonequijoins
- Lesson Agenda
- Returning Records with No Direct Match Using OUTER Joins
- INNER Versus OUTER Joins
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- Lesson Agenda
- Cartesian Products
- Generating a Cartesian Product
- Creating Cross Joins
- Quiz
- Summary
- Practice 11: Overview
- Lesson 12: Using the Set Operators
- Course Roadmap
- Objectives
- Lesson Agenda
- Set Operators
- Set Operator Rules
- Oracle Server and Set Operators
- Lesson Agenda
- Tables Used in This Lesson
- Notes Page
- Lesson Agenda
- UNION Operator
- Using the UNION Operator
- UNION ALL Operator
- Using the UNION ALL Operator
- Lesson Agenda
- INTERSECT Operator
- Using the INTERSECT Operator
- Lesson Agenda
- MINUS Operator
- Using the MINUS Operator
- Lesson Agenda
- Matching the SELECT Statements
- Matching the SELECT Statement: Example
- Lesson Agenda
- Using the ORDER BY Clause in Set Operations
- Quiz
- Summary
- Practice 12: Overview
- Lesson 13: Using Subqueries to Solve Queries
- Course Roadmap
- Objectives
- Lesson Agenda
- Using a Subquery to Solve a Problem
- Subquery Syntax
- Using a Subquery
- Rules and Guidelines for Using Subqueries
- Types of Subqueries
- Lesson Agenda
- Single-Row Subqueries
- Single-Row Subqueries: Example
- Executing Single-Row Subqueries
- Using Group Functions in a Subquery
- HAVING Clause with Subqueries
- What Is Wrong with This Statement?
- No Rows Returned by the Inner Query
- Lesson Agenda
- Multiple-Row Subqueries
- Using the IN Operator in Multiple-Row Subqueries
- Using the ANY Operator in Multiple-Row Subqueries
- Using the ALL Operator in Multiple-Row Subqueries
- Lesson Agenda
- Multiple-Column Subqueries
- Multiple-Column Subquery: Example
- Lesson Agenda
- Null Values in a Subquery
- Quiz
- Summary
- Practice 13: Overview
- Lesson 14: Introduction to Data Manipulation Language
- Course Roadmap
- Objectives
- Lesson Agenda
- DML
- 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 Transaction: Example
- 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
- Setting AutoCommit in SQL Developer
- Commit/Rollback on Exiting SQL Developer
- State of Data Before COMMIT or ROLLBACK
- State of Data After COMMIT
- COMMIT: Example
- State of Data After ROLLBACK
- ROLLBACK: Example
- Statement-Level Rollback
- Lesson Agenda
- Read Consistency
- Notes Page
- Quiz
- Summary
- Practice 14: Overview
- Lesson 15: Introduction to Data Definition Language
- Course Roadmap
- Objectives
- Lesson Agenda
- Database Objects
- Naming Rules
- Lesson Agenda
- CREATE TABLE Statement
- Creating Tables
- Lesson Agenda
- Data Types
- DEFAULT Option
- 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
- Quiz
- Summary
- Practice 15: Overview
- Lesson 16: Managing Tables Using DML Statements
- Lesson 17: Introduction to Data Dictionary Views
- Course Roadmap
- Objectives
- Lesson Agenda
- Data Dictionary
- Data Dictionary Structure
- How to Use the Dictionary Views
- USER_OBJECTS and ALL_OBJECTS Views
- USER_OBJECTS View
- Lesson Agenda
- Table Information
- Column Information
- Constraint Information
- USER_CONSTRAINTS: Example
- Querying USER_CONS_COLUMNS
- Lesson Agenda
- Adding Comments to a Table
- Quiz
- Summary
- Practice 17: Overview
- Lesson 18: Creating Views
- Course Roadmap
- Objectives
- Lesson Agenda
- Database Objects
- Views
- Advantages of Views
- Simple Views and Complex Views
- Lesson Agenda
- Creating a View
- Retrieving Data from a View
- Modifying a View
- Creating a Complex View
- View Information
- Lesson Agenda
- Rules for Performing DML Operations on a View
- Using the WITH CHECK OPTION Clause
- Denying DML Operations
- Lesson Agenda
- Removing a View
- Quiz
- Summary
- Practice 18: Overview
- Lesson 19: Creating Sequences, Synonyms, and Indexes
- Course Roadmap
- Objectives
- Lesson Agenda
- Database Objects
- Referencing Another User’s Tables
- Sequence
- CREATE SEQUENCE Statement: Syntax
- Notes Page
- Creating a Sequence: Example
- NEXTVAL and CURRVAL Pseudocolumns
- Using a Sequence
- SQL Column Defaulting Using a Sequence
- Caching Sequence Values
- Modifying a Sequence
- Guidelines for Modifying a Sequence
- Sequence Information
- Lesson Agenda
- Synonyms
- Creating a Synonym for an Object
- Creating and Removing Synonyms
- Synonym Information
- Lesson Agenda
- Indexes
- How Are Indexes Created?
- Creating an Index
- CREATE INDEX with the CREATE TABLE Statement
- Notes Page
- Function-Based Indexes
- Creating Multiple Indexes on the Same Set of Columns
- Creating Multiple Indexes on the Same Set of Columns: Example
- Index Information
- Removing an Index
- Quiz
- Summary
- Practice 19: Overview
- Lesson 20: Managing Constraints, Temporary Tables, and External Tables
- Course Roadmap
- Objectives
- Lesson Agenda
- Adding a Constraint Syntax
- Adding a Constraint
- Dropping a Constraint
- Dropping a CONSTRAINT ONLINE
- ON DELETE Clause
- Cascading Constraints
- Renaming Table Columns and Constraints
- Disabling Constraints
- Enabling Constraints
- Constraint States
- Deferring Constraints
- Difference Between INITIALLY DEFERRED and INITIALLY IMMEDIATE
- DROP TABLE … PURGE
- Lesson Agenda
- Temporary Tables
- Creating a Temporary Table
- Lesson Agenda
- External Tables
- Creating a Directory for the External Table
- Creating an External Table
- Creating an External Table by Using ORACLE_LOADER
- Quiz
- Summary
- Practice 20: Overview
- Lesson 21: Using Advanced Subqueries
- Course Roadmap
- Objectives
- Lesson Agenda
- Retrieving Data by Using a Subquery as a Source
- Lesson Agenda
- Multiple-Column Subqueries
- Column Comparisons
- Pairwise Comparison Subquery
- Nonpairwise Comparison Subquery
- Lesson Agenda
- Scalar Subquery Expressions
- Scalar Subqueries: Examples
- Lesson Agenda
- Correlated Subqueries
- Using Correlated Subqueries: Example 1
- Using Correlated Subqueries: Example 2
- Lesson Agenda
- Using the EXISTS Operator
- Finding All Departments That Do Not Have Any Employees
- Lesson Agenda
- WITH Clause
- WITH Clause: Example
- Recursive WITH Clause
- Recursive WITH Clause: Example
- Quiz
- Summary
- Practice 21: Overview
- Lesson 22: Manipulating Data by Using Advanced Subqueries
- Course Roadmap
- Objectives
- Lesson Agenda
- Using Subqueries to Manipulate Data
- Lesson Agenda
- Inserting by Using a Subquery as a Target
- Lesson Agenda
- Using the WITH CHECK OPTION Keyword on DML Statements
- Lesson Agenda
- Correlated UPDATE
- Using Correlated UPDATE
- Correlated DELETE
- Using Correlated DELETE
- Summary
- Practice 22: Overview
- Lesson 23: Controlling User Access
- Course Roadmap
- Objectives
- Lesson Agenda
- Database Security
- Controlling User Access
- Privileges
- System Privileges
- Typical DBA Privileges
- Creating Users
- User System Privileges
- Granting System Privileges
- Lesson Agenda
- What Is a Role?
- Role: Syntax
- Creating and Granting Privileges to a Role
- Changing Your Password
- Lesson Agenda
- Object Privileges
- Granting Object Privileges
- Passing On Your Privileges
- Confirming Granted Privileges
- Lesson Agenda
- Revoking Object Privileges
- Lesson Agenda
- Oracle Cloud Service Administration Roles
- Quiz
- Summary
- Practice 23: Overview
- Lesson 24: Advanced Data Manipulation
- Course Roadmap
- Objectives
- Lesson Agenda
- Explicit Default Feature: Overview
- Using Explicit Default Values
- Lesson Agenda
- Multitable INSERT Statements: Overview
- Types of Multitable INSERT Statements
- Multitable INSERT Statements
- Unconditional INSERT ALL
- Conditional INSERT ALL: Example
- Conditional INSERT ALL
- Conditional INSERT FIRST: Example
- Conditional INSERT FIRST
- Pivoting INSERT
- Lesson Agenda
- MERGE Statement
- MERGE Statement Syntax
- Merging Rows: Example
- Lesson Agenda
- FLASHBACK TABLE Statement
- Using the FLASHBACK TABLE Statement
- Lesson Agenda
- Tracking Changes in Data
- Flashback Query: Example
- Flashback Version Query: Example
- VERSIONS BETWEEN Clause
- Quiz
- Summary
- Lesson 25: Managing Multiple Time Zones
- Course Roadmap
- Objectives
- Lesson Agenda
- Time Zones
- TIME_ZONE Session Parameter
- CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
- Comparing Date and Time in a Session’s Time Zone
- DBTIMEZONE and SESSIONTIMEZONE
- TIMESTAMP Data Types
- TIMESTAMP Fields
- Difference Between DATE and TIMESTAMP
- Comparing TIMESTAMP Data Types
- Lesson Agenda
- INTERVAL Data Types
- INTERVAL Fields
- INTERVAL YEAR TO MONTH: Example
- INTERVAL DAY TO SECOND Data Type: Example
- Lesson Agenda
- EXTRACT
- TZ_OFFSET
- FROM_TZ
- TO_TIMESTAMP
- TO_YMINTERVAL
- TO_DSINTERVAL
- Daylight Saving Time (DST)
- Quiz
- Summary