Database Design and Programming with SQL – Course Objectives

Overview
This course engages students to implement database models by creating physical databases using SQL. Students will learn basic SQL
syntax and the rules for constructing valid SQL statements to generate report-like output. Demonstrations and hands-on practice
reinforce the fundamental concepts.

Available Curriculum Languages:

• English, Simplified Chinese, Brazilian Portuguese, Spanish, Indonesian

Duration
• Recommended total course time: 90 hours*
• Professional education credit hours for educators who complete Oracle Academy training: 60
* Course time includes instruction, self-study/homework, practices, projects, and assessment

Target Audiences
Educators
• College/university faculty who teach computer programming, information communications technology (ICT), or a related
subject
• Secondary school teachers who teach computer programming, ICT, or a related subject
Students
• Students who wish to learn the techniques and tools to design, guild and extract information from a database
• Students who possess basic mathematical, logical, and analytical problem-solving skills
• Novice programmers, as well as those at advanced levels, to learning the SQL Programming language to an advanced level

Prerequisites
Required
• Ease with using a computer
• General knowledge of databases and query activity
Suggested
• None

Suggested Next Courses

• Database Programming with PL/SQL

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Lesson-by-Lesson Topics and Objectives

Section 1 – Introduction
• 1-1 Oracle Application Express
o Distinguish between application software and system software and give an example of each
o Log-in to the Oracle Application Express practice environment
o Execute a simple query to retrieve information from the Database
o Apply the rules of SQL to display all columns and a subset of columns specified by criteria

• 1-2 Relational Database Technology

o Define and give an example of a relational database
o Identify table-key terms, including row, column, field, primary key, and foreign key
o Relate the importance of databases to everyday life

• 1-3 Anatomy of a SQL Statement

o Match projection and selection with their correct capabilities
o Create a basic SELECT statement
o Use the correct syntax to display all rows in a table
o Use the correct syntax to select specific columns in a table, modify the way data is displayed, and perform calculations
using arithmetic expressions and operators

Section 2 – SELECT and WHERE

• 2-1 Columns, Characters, and Rows
o Apply the concatenation operator to link columns to other columns, arithmetic expressions, or constant values to create a
character expression
o Use column aliases to rename columns in the query result
o Enter literal values of type character, number, or date into a SELECT statement
o Define and use DISTINCT to eliminate duplicate rows
o Edit, execute, and save SQL statements in Oracle Application Express

• 2-2 Limit Rows Selected

o Apply SQL syntax to restrict the rows returned from a query
o Demonstrate application of the WHERE clause syntax
o Explain why it is important, from a business perspective, to be able to easily limit data retrieved from a table
o Construct and produce output using a SQL query containing character strings and date values

• 2-3 Comparison Operators

o Apply the proper comparison operator to return a desired result
o Demonstrate proper use of BETWEEN, IN, and LIKE conditions to return a desired result
o Distinguish between zero and NULL, the latter of which is unavailable, unassigned, unknown, or inapplicable
o Explain the use of comparison conditions and NULL

Section 3 – WHERE, ORDER BY, and Intro to Functions

• 3-1 Logical Comparisons and Precedence Rules
o Evaluate logical comparisons to restrict the rows returned based on two or more conditions
o Apply the rules of precedence to determine the order in which expressions are evaluated and calculated

• 3-2 Sorting Rows

o Construct a query to sort a result set in ascending or descending order
o State the order in which expressions are evaluated and calculated based on the rules of precedence
o Construct a query to order a result set using a column alias
o Construct a query to order a result set for single or multiple columns

• 3-3 Introduction to Functions

o Identify appropriate applications of single-row functions in query statements
o Classify a function as a single-row or multi-row function
o Differentiate between single-row functions and multi-row functions and the results returned by each

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

2
Section 4 – Single Row Functions Part I
• 4-1 Case and Character Manipulation
o Select and apply single-row functions that perform case conversion and/or character manipulation
o Select and apply character case-manipulation functions LOWER, UPPER, and INITCAP in a SQL query
o Select and apply character-manipulation functions CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM, and
REPLACE in a SQL query
o Write flexible queries using substitution variables

• 4-2 Number Functions

o Select and apply the single-row number functions ROUND, TRUNC, and MOD in a SQL query
o Distinguish between the results obtained when TRUNC is applied to a numeric value and ROUND is applied to a
numeric value
o State the implications for business when applying TRUNC and ROUND to numeric values

• 4-3 Date Functions

o Demonstrate the use of SYSDATE and date functions
o State the implications for world businesses to be able to easily manipulate data stored in date format

Section 5 – Single Row Functions Part II

• 5-1 Conversion Functions
o Provide an example of an explicit data-type conversion and an implicit data-type conversion
o Explain why it is important, from a business perspective, for a language to have built-in data-conversion capabilities
o Construct a SQL query that correctly applies TO_CHAR, TO_NUMBER, and TO_DATE single-row functions to produce
a desired result
o Apply the appropriate date and/or character format model to produce a desired output
o Explain and apply the use of YY and RR to return the correct year as stored in the database

• 5-2 NULL Functions

o Demonstrate and explain the evaluation of a nested function
o List at least four general functions that work with any data type and relate to handling null values
o Explain the use of the COALESCE and the NVL functions
o Explain the use of general functions to deal with null values in data
o Construct and execute a SQL query that correctly applies NVL, NVL2, NULLIF, and COALESCE single-row functions

• 5-3 Conditional Expressions

o Compare and contrast the DECODE and CASE functions
o Construct and execute a SQL query that correctly uses the DECODE and CASE functions
o Construct and execute two methods for implementing IF-THEN-ELSE conditional logic

Section 6 – JOINs Part I

• 6-1 Cross Joins and Natural Joins
o Construct and execute a natural join using ANSI-99 SQL join syntax
o Create a cross join using ANSI-99 SQL join syntax
o Explain the importance of having a standard for SQL as defined by ANSI
o Describe a business need for combining information from multiple data sources

• 6-2 Join Clauses

o Construct and execute a join with the ANSI-99 USING Clause
o Construct and execute a join with the ANSI-99 ON Clause
o Construct and execute an ANSI-99 query that joins three tables

• 6-3 Inner versus Outer Joins

o Compare and contrast an inner and an outer join
o Construct and execute a query to use a left outer join
o Construct and execute a query to use a right outer join
o Construct and execute a query to use a full outer join

• 6-4 Self-Joins and Hierarchical Queries

o Construct and execute a SELECT statement to join a table to itself using a self-join
o Interpret the concept of a hierarchical query
o Create a tree-structured report
o Format hierarchical data
o Exclude branches from the tree structure

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

3
Section 7 – JOINs Part II
• 7-1 Oracle Equijoin and Cartesian Product
o Name the Oracle proprietary joins and their ANSI/ISO SQL: 99 counterparts
o Construct and execute a SELECT statement that results in a Cartesian product
o Construct and execute SELECT statements to access data from more than one table using an equijoin
o Construct and execute SELECT statements that add search conditions using the AND operator
o Apply the rule for using table aliases in a join statement

• 7-2 Oracle Nonequijoins and Outer Joins

o Construct and execute a SELECT statement to access data from more than one table using a nonequijoin
o Create and execute a SELECT statement to access data from more than one table using an Oracle outer join

Section 8 – Group Functions Part I

• 8-1 Group Functions
o Define and give an example of the seven group functions: SUM, AVG, COUNT, MIN, MAX, STDDEV, VARIANCE
o Construct and execute a SQL query using group functions
o Construct and execute group functions that operate only with numeric data types

• 8-2 COUNT, DISTINCT, NVL

o Construct and execute a SQL query using the COUNT group function
o Use DISTINCT and the NVL function with group functions

Section 9 – Group Functions Part II

• 9-1 Using Group By and Having Clauses
o Construct and execute a SQL query using GROUP BY
o Construct and execute a SQL query using GROUP BY … HAVING
o Construct and execute a GROUP BY on more than one column
o Nest group functions

• 9-2 Using Rollup and Cube Operations, and Grouping Sets

o Use ROLLUP to produce subtotal values
o Use CUBE to produce cross-tabulation values
o Use GROUPING SETS to produce a single result set
o Use the GROUPING function to identify the extra row values created by either a ROLLUP or CUBE operation

• 9-3 Using Set Operators

o Define and explain the purpose of Set Operators
o Use a set operator to combine multiple queries into a single query
o Control the order of rows returned using set operators

Section 10 – Subqueries
• 10-1 Fundamentals of Subqueries
o Define and explain the purpose of subqueries for retrieving data
o Construct and execute a single-row subquery in the WHERE clause
o Distinguish between single-row and multiple-row subqueries

• 10-2 Single-Row Subqueries

o Construct and execute a single-row subquery in the WHERE clause or HAVING clause
o Construct and execute a SELECT statement using more than one subquery
o Construct and execute a SELECT statement using a group function in the subquery

• 10-3 Multiple-Row Subqueries

o Correctly use the comparison operators IN, ANY, and ALL in multiple-row subqueries
o Construct and execute a multiple-row subquery in the WHERE clause or HAVING clause
o Describe what happens if a multiple-row subquery returns a null value
o Understand when multiple-row subqueries should be used, and when it is safe to use a single-row subquery
o Distinguish between pair-wise and non-pair-wise subqueries

• 10-4 Correlated Subqueries

o Identify when correlated subqueries are needed.
o Construct and execute correlated subqueries.
o Create a query using the EXISTS and NOT EXISTS operators to test for returned rows from the subquery
o Construct and execute named subqueries using the WITH clause.

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

4
Section 11 – Ensuring Quality Queries Part I
• 11-1 Ensuring Quality Query Results
o Create a query to produce specified data
o Modify a query to produce specified data

Section 12 – DML
• 12-1 INSERT Statements
o Explain the importance of being able to alter the data in a database
o Construct and execute INSERT statements which insert a single row using a VALUES clause
o Construct and execute INSERT statements that use special values, null values, and date values
o Construct and execute INSERT statements that copy rows from one table to another using a subquery

• 12-2 Updating Column Values and Deleting Rows

o Construct and execute an UPDATE statement
o Construct and execute a DELETE statement
o Construct and execute a query that uses a subquery to update and delete data from a table
o Construct and execute a query that uses a correlated subquery to update and delete from a table
o Explain how foreign-key and primary-key integrity constraints affect UPDATE and DELETE statements
o Explain the purpose of the FOR UPDATE Clause in a SELECT statement

• 12-3 DEFAULT Values, MERGE, and Multi-Table Inserts

o Understand when to specify a DEFAULT value
o Construct and execute a MERGE statement
o Construct and execute DML statements using subqueries
o Construct and execute multi-table inserts

Section 13 – DDL
• 13-1 Creating Tables
o List and categorize the main database objects
o Review a table structure
o Describe how schema objects are used by the Oracle database
o Create a table using the appropriate data type for each column
o Explain the use of external tables
o Query the Data Dictionary to obtain the names and other attributes of database objects

• 13-2 Using Data Types

o Create a table using TIMESTAMP and TIMESTAMP WITH TIME ZONE column data types
o Create a table using INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND column data types
o Give examples of organizations and personal situations where it is important to know to which time zone a date-time
value refers
o List and provide an example of each of the number, date, and character data types

• 13-3 Modifying a Table

o Explain why it is important to be able to modify a table
o Explain and provide an example for each of the DDL statements—ALTER, DROP, RENAME, and TRUNCATE—and the
effect each has on tables and columns
o Construct a query and execute the ALTER TABLE commands ADD, MODIFY, and DROP
o Explain and perform FLASHBACK QUERY on a table
o Explain and perform FLASHBACK table operations
o Track the changes to data over a period of time
o Explain the rationale for using TRUNCATE versus DELETE for tables
o Add a comment to a table using the COMMENT ON TABLE command
o Name the changes that can and cannot be made to modify a column
o Explain when and why the SET UNUSED statement is advantageous

Section 14 – Constraints
• 14-1 Intro to Constraints; NOT NULL and UNIQUE Constraints
o Define the term "constraint" as it relates to data integrity
o State when it is possible to define a constraint at the column level, and when it is possible at the table level
o State why it is important to give meaningful names to constraints
o State which data integrity rules are enforced by NOT NULL and UNIQUE constraints
o Write a CREATE TABLE statement which includes NOT NULL and UNIQUE constraints at the table and column levels
o Explain how constraints are created at the time of table creation

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

5
 • 14-2 PRIMARY KEY, FOREIGN KEY, and CHECK Constraints
o Define and give an example of a PRIMARY KEY, FOREIGN KEY, and CHECK constraint
o Explain the purpose of defining PRIMARY KEY, FOREIGN KEY, and CHECK constraints
o Demonstrate the creation of constraints at the column level and table level in a CREATE TABLE statement
o Evaluate a business problem requiring the addition of a PRIMARY KEY and FOREIGN KEY constraint and write the
code to execute the change

• 14-3 Managing Constraints

o List four different functions that the ALTER statement can perform on constraints
o Write ALTER TABLE statements to add, drop, disable, and enable constraints
o Name a business function that would require a DBA to drop, enable, and/or disable a constraint or use the CASCADE
syntax
o Query the data dictionary for USER_CONSTRAINTS and interpret the information returned

Section 15 – Views
• 15-1 Creating Views
o List three uses for views from the standpoint of a database administrator
o Explain, from a business perspective, why it is important to be able to create and use logical subsets of data derived
from one or more tables
o Create a view with and without column aliases in the subquery using a single base table
o Create a complex view that contains group functions to display values from two tables
o Retrieve data from a view

• 15-2 DML Operations and Views

o Write and execute a query that performs DML operations on a simple view
o Name the conditions that restrict your ability to modify a view using DML operations
o Write and execute a query using the WITH CHECK OPTION clause
o Explain the use of WITH CHECK OPTION as it applies to integrity constraints and data validation
o Apply the WITH READ ONLY option to a view to restrict DML operations

• 15-3 Managing Views

o Create and execute a SQL statement that removes a view
o Create and execute a query using an inline view
o Create and execute a top-n-analysis query

Section 16 – Sequences and Synonyms

• 16-1 Working With Sequences
o List at least three useful characteristics of a sequence
o Write and execute a SQL statement that creates a sequence
o Query the data dictionary using USER_SEQUENCES to confirm a sequence definition
o Apply the rules for using NEXTVAL to generate sequential unique numbers in a table
o List the advantages and disadvantages of caching sequence values
o Name three reasons why gaps can occur in a sequence

• 16-2 Indexes and Synonyms

o Define an index and its use as a schema object
o Name the conditions that cause an index to be created automatically
o Create and execute a CREATE INDEX and DROP INDEX statement
o Create and execute a function-based index
o Create private and public synonyms

Section 17 – Privileges and Regular Expressions

• 17-1 Controlling User Access
o Compare the difference between object privileges and system privileges
o Construct the two commands required to enable a user to have access to a database
o Construct and execute a GRANT… ON …TO statement to assign privileges to objects in their schema to other users
and/or PUBLIC
o Query the data dictionary to confirm privileges granted

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

6
 • 17-2 Creating and Revoking Object Privileges
o Explain what a ROLE is and what its advantages are
o Construct a statement to create a ROLE and GRANT privileges to it
o Construct a GRANT .. ON .. TO.. WITH GRANT OPTION statement to assign privileges on objects in your schema to
other users and/or PUBLIC
o Construct and execute a statement to REVOKE object privileges from other users and/or from PUBLIC
o Distinguish between privileges and roles
o Explain the purpose of a database link

• 17-3 Regular Expressions

o Describe regular expressions
o Use regular expressions to search, match, and replace strings in SQL statements
o Construct and execute regular expressions and check constraints

Section 18 – TCL
• 18-1 Database Transactions
o Define the terms COMMIT, ROLLBACK, and SAVEPOINT as they relate to data transactions
o List three advantages of the COMMIT, ROLLBACK, and SAVEPOINT statements
o Explain why it is important, from a business perspective, to be able to control the flow of transaction processing

Section 19 – Final Project and Exam Review

• 19-1 Testing
o Develop and apply a strategy for testing that a database functions as designed

• 19-2 Final Project Database Creation

o Apply SQL concepts to create a functional database appropriate for a small business

• 19-3 Final Exam Review

o Review the key points about case and character manipulation
o Review number, date, conversion, and general functions
o Review conditional expressions
o Review Cartesian product and join operations
o Review non-equijoins, outer joins, self joins, cross joins, natural joins, and join clauses
o Review group functions, group by syntax, and having clauses
o Review single-row and multiple row subqueries
o Review pair-wise and non-pair-wise subqueries
o Review correlated subqueries
o Review DML statements insert, update, delete, merge, and multi-table inserts
o Review DDL statements CREATE, ALTER, RENAME, TRUNCATE, FLASHBACK TABLE, DROP, and FLASHBACK
QUERY
o Review DCL statements CREATE and REVOKE object privileges

Section 20 – Ensuring Quality Queries Part II

• 20-1 Ensuring Quality Query Results - Advanced Techniques
o Create an advanced query to produce specified data
o Modify an advanced query to produce specified data

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.