Course Overview

This course teaches students to understand how business requirements become database designs, and the basics of reading and writing to a relational database using SQL (Structured Query Language).

This course is not just for developers, but for anyone who wants to understand the impact of business requirements to complexity and cost.

Learning Outcomes

  • Gain a working knowledge of SQL when managing data held in a relational database management system

Course Outline

Data Retrieval

  • Describing tables
  • Capabilities of the SELECT statement: projection, selection, and joining
  • Executing a basic SELECT
  • SQL expressions and operators
  • NULL is nothing

Restricting and Sorting Data

  • Limit the rows retrieved by a query
  • The WHERE clause
  • Comparison operators (equality, inequality, BETWEEN, IN, LIKE, IS NULL)
  • Boolean operators (NOT, AND, OR)
  • Precedence rules
  • Sort the rows retrieved by a query
  • ORDER BY clause
  • Ascending, descending, NULLS FIRST, NULLS LAST
  • Positional sorting
  • Composite sorting

Single Row and Conversion Functions

  • Character, Number, and Date Functions in SELECT Statements
  • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Conditional expressions in a SELECT Statement
  • COALESCE (NVL() in Oracle)
  • CASE expression (DECODE() in Oracle)

Aggregating Data Using Group Functions

  • Describing and using the group functions: COUNT, SUM, AVG, MIN / MAX; Group Data by Using the GROUP BY Clause
  • Include or Exclude Grouped Rows by Using the HAVING Clause

Retrieving Data from Multiple Tables

  • Write SELECT Statements to Access Data from More Than One Table
  • Join a Table to Itself Using a Self-Join
  • View Data That Does Not Meet a Join Condition Using Outer Joins
  • Generate a Cartesian Product of All Rows from Two or More Tables

Using Subqueries and Set Operators

  • Define subqueries
  • Types of problems that the subqueries can solve
  • Types of subqueries: Single-row subqueries, Multiple-row subqueries, Correlated subqueries; Describe set operators: UNION, INTERSECTS, EXCEPT
  • Use a set operator to combine multiple queries into a single query

Manipulating Data and Transactions

  • Insert rows into a table
  • Update rows in a table
  • Delete rows from a table
  • Control transactions

Skill Level

Beginner

Suitable For

Anyone that wants a high-level appreciation of managing data stored in relational databases including developers, business analysts, QA, testers, and project managers.

Duration

2

 day

s

Related Topics

Enquire Now

Skill Level

Beginner

Suitable For

Anyone that wants a high-level appreciation of managing data stored in relational databases including developers, business analysts, QA, testers, and project managers.

Duration

2

 day

s

Related Topics

Course Overview

This course teaches students to understand how business requirements become database designs, and the basics of reading and writing to a relational database using SQL (Structured Query Language).

This course is not just for developers, but for anyone who wants to understand the impact of business requirements to complexity and cost.

Learning Outcomes

  • Gain a working knowledge of SQL when managing data held in a relational database management system

Course Outline

Data Retrieval

  • Describing tables
  • Capabilities of the SELECT statement: projection, selection, and joining
  • Executing a basic SELECT
  • SQL expressions and operators
  • NULL is nothing

Restricting and Sorting Data

  • Limit the rows retrieved by a query
  • The WHERE clause
  • Comparison operators (equality, inequality, BETWEEN, IN, LIKE, IS NULL)
  • Boolean operators (NOT, AND, OR)
  • Precedence rules
  • Sort the rows retrieved by a query
  • ORDER BY clause
  • Ascending, descending, NULLS FIRST, NULLS LAST
  • Positional sorting
  • Composite sorting

Single Row and Conversion Functions

  • Character, Number, and Date Functions in SELECT Statements
  • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Conditional expressions in a SELECT Statement
  • COALESCE (NVL() in Oracle)
  • CASE expression (DECODE() in Oracle)

Aggregating Data Using Group Functions

  • Describing and using the group functions: COUNT, SUM, AVG, MIN / MAX; Group Data by Using the GROUP BY Clause
  • Include or Exclude Grouped Rows by Using the HAVING Clause

Retrieving Data from Multiple Tables

  • Write SELECT Statements to Access Data from More Than One Table
  • Join a Table to Itself Using a Self-Join
  • View Data That Does Not Meet a Join Condition Using Outer Joins
  • Generate a Cartesian Product of All Rows from Two or More Tables

Using Subqueries and Set Operators

  • Define subqueries
  • Types of problems that the subqueries can solve
  • Types of subqueries: Single-row subqueries, Multiple-row subqueries, Correlated subqueries; Describe set operators: UNION, INTERSECTS, EXCEPT
  • Use a set operator to combine multiple queries into a single query

Manipulating Data and Transactions

  • Insert rows into a table
  • Update rows in a table
  • Delete rows from a table
  • Control transactions