SQL Fundamentals I

 Click here

Course Overview

This course introduces students to the fundamentals of the SQL programming language in the Oracle database environment. It begins with an introduction to basic relational database concepts and the SQL programming language. Then the focus turns to developing the skills needed to retrieve data, manipulate data stored in tables and create database objects.

Students will learn how to construct powerful SQL statements to aggregate values, perform data conversions and generate customize output. The step-by-step method of instruction employed makes it possible for students without prior knowledge of SQL and/or relational databases to quickly grasp the key topics.

The course consists of a series of lessons, each one consisting of demonstrations and hands-on practices that reinforce the fundamental concepts and encourage students to interact with the Oracle RDBMS.

Course Goals

• To provide students with a solid understanding of Oracle RDBMS and SQL language.
• To assist students with developing the coding skills necessary for retrieving and/or manipulating data stored in an Oracle database.
• To complete hands-on exercises in an interactive, instructor-led environment.

Prerequisites

• Familiarity with data processing concepts and techniques.

Who Should Attend?

• Application Developers
• PL/SQL Developers
• Forms Developers
• Reports Developers
• Technical Consultants
• Portal Developers
• Functional Implementers
• End Users

Course Materials

• Student Handbook
• Reference Book

Duration

• Three (3) days

Course Outline

• Introduction
  • Listing the relevent features of Oracle Database
  • Discussing the basic design, theoretical and physical aspects of a relational database
  • Describing the development environments for SQL
  • Describing Oracle SQL Developer
  • Describing the data set used by the course
• The SELECT Statement
  • Listing the capabilities of SQL SELECT statements.
  • Generating a report of data from the output of a basic SELECT statement
  • Using arithmetic expressions and NULL values in the SELECT statement
  • Using Column aliases
  • Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
  • Displaying the table structure using the DESCRIBE command
• Restricting and Sorting Data
  • Writing queries with a WHERE clause to limit the output retrieved
  • Using the comparison operators and logical operators
  • Describing the rules of precedence for comparison and logical operators
  • Using character string literals in the WHERE clause
  • Writing queries with an ORDER BY clause to sort the output
  • Sorting output in descending and ascending order
• Single-Row Functions
  • Differentiating between single row and multiple row functions
  • Manipulating strings using character functions
  • Manipulating numbers with the ROUND, TRUNC and MOD functions
  • Performing arithmetic with date data
  • Manipulating dates with the date functions
• Conversion Functions and Condition Expressions
  • Describing implicit and explicit data type conversion
  • Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Nesting multiple functions
  • Applying the NVL, NULLIF, and COALESCE functions to data
  • Using conditional IF THEN ELSE logic in a SELECT statement
• Group Functions
  • Using the aggregation functions in SELECT statements to produce meaningful reports
  • Using AVG, SUM, MIN, and MAX function
  • Handling Null Values in a group function
  • Creating queries that divide the data in groups by using the GROUP BY clause
  • Creating queries that exclude groups of date by using the HAVING clause
• Table Joins
  • Writing SELECT statements to access data from more than one table
  • Joining Tables Using SQL:1999 Syntax
  • Viewing data that does not meet a join condition by using outer joins
  • Joining a table by using a self join
  • Creating Cross Joins
• Subqueries
  • Using a Subquery to Solve a Problem
  • Executing Single-Row Sub-queries
  • Using Group Functions in a Sub-query
  • Using Multiple-Row Subqueries
  • Using the ANY and ALL Operator in Multiple-Row Sub-queries
• Set Operators
  • Describing the SET operators
  • Using a SET operator to combine multiple queries into a single query
  • Using UNION, UNION ALL, INTERSECT, and MINUS Operator
  • Using the ORDER BY Clause in Set Operations
• Data Manipulation Language
  • Adding New Rows to a Table Using the INSERT statement
  • Changing Data in a Table Using the UPDATE Statement
  • Using DELETE and TRUNCATE Statements
  • Saving and discarding changes with the COMMIT and ROLLBACK statements
  • Implementing Read Consistency
  • Using the FOR UPDATE Clause
• Database Tables
  • Categorizing Database Objects
  • Creating Tables using the CREATE TABLE Statement
  • Describing the data types
  • Describing Constraints
  • Creating a table using a subquery
  • Altering and Dropping a table
• Other Schema Objects
  • Creating, modifying, and retrieving data from a view
  • Performing Data manipulation language (DML) operations on a view
  • Dropping a view
  • Creating, using, and modifying a sequence
  • Creating and dropping indexes
  • Creating and dropping synonyms