Oracle SQL Fundamentals/Collection

Oracle SQL Fundamentals

Learning Guide

This learning guide supports the Wikiversity course Oracle SQL Fundamentals, available at http://en.wikiversity.org/wiki/Oracle_SQL_Fundamentals.

Overview

Oracle SQL Fundamentals introduces Oracle SQL language and database concepts, including data selection and manipulation, data definition, and other schema objects. This course is Oracle-specific. See Database Fundamentals for a more generic introduction to database concepts.

This course comprises 15 lessons covering Oracle SQL fundamentals. Each lesson includes a combination of Wikipedia and Oracle readings, YouTube videos, and hands-on learning activities. The course also assists learners in preparing for Oracle SQL Fundamentals I certification.

Preparation

This is a second-semester, college-level course. Learners should already be familiar with Information Systems concepts. Familiarity with Database Software is also helpful.

Lessons

1. Introduction
2. Selecting Data
3. Restricting Data
4. Sorting Data
5. Single-Row Functions
6. Conversion Functions
7. Conditional Expressions
8. Aggregating Data
9. Joining Data
10. Subqueries
11. Set Operators
12. Manipulating Data
13. Transactions
14. Data Definition Language
15. Other Schema Objects

See Also

• Database Fundamentals
• Database Software
• Information Systems
• Wikibooks: Structured Query Language
• Oracle Database Administration
• Codecademy: Learn SQL
• TutorialsPoint: SQL Fundamentals Certification

References

• Oracle: Database 11g: SQL Fundamentals I Exam Topics
• Ramklass and Watson. OCA Oracle Database 11g SQL Fundamentals I Exam Guide: Exam 1Z0-051. Oracle Press. ISBN 9780071597869

Lesson 1 - Introduction

This lesson introduces Oracle SQL database concepts, the SQL language, client tools, and sample schemas.

Objectives and Skills

Objectives and skills for Oracle SQL Fundamentals I certification are covered in detail in other lessons. This lesson helps you:

• Understand Oracle server technologies
• Understand relational database concepts
• Summarize the SQL language
• Use Oracle client tools
• Create example schemas for use in the course

Readings

1. Read Wikipedia: Oracle Database.
2. Read Wikipedia: Relational database.
3. Read Wikipedia: Database normalization.
4. Read Wikipedia: SQL.
5. Read Wikipedia: SQL*Plus.
6. Read Wikipedia: Oracle SQL Developer.

Multimedia

1. YouTube: Oracle 11g SQL Tutorial & Exam 1Z0-051 : Introduction
2. YouTube: How To Prepare For Your Oracle Certification Exam

Activities

1. Install Oracle Express.
   1. Review Oracle: Oracle Database Express Edition Documentation and the the installation guide for your platform (Linux or Windows).
   2. On the system you plan to use as your database server, download and install Oracle Database Express.
2. Use SQL*PLUS to verify the installation.
   1. Review Oracle: Identifying Your Oracle Database Software Release.
   2. At a command prompt on the database server, enter sqlplus system.
   3. At the prompt, enter the password specified during installation.
   4. To display current version information, run the query SELECT * FROM PRODUCT_COMPONENT_VERSION;
   5. Enter exit to exit SQL*PLUS.
3. Use SQL*PLUS to create the HR and OE sample schemas.
   1. Review Oracle: Installing the Sample Schemas.
   2. Download and extract the sample schemas from Oracle:sample-schem-scripts.zip.
   3. At a command prompt, navigate to the schema/hr folder you just extracted.
   4. In the schema/hr folder, enter sqlplus / as sysdba to start SQL*PLUS.
   5. Run the command @hr_main.sql.
   6. Enter the system password. When the script finishes, if necessary enter exit to exit SQL*PLUS and return to the command prompt.
   7. Navigate to the schema/oe folder you extracted.
   8. In the schema/oe folder, enter sqlplus / as sysdba to start SQL*PLUS.
   9. Run the command @oe_main.sql.
   10. Enter the system password. When the script finishes, if necessary enter exit to exit SQL*PLUS and return to the command prompt.
4. Use SQL*PLUS to verify sample schema installation.
   1. At a command prompt, enter sqlplus hr/hr.
   2. Run the query show user; to show that you are currently connected as hr.
   3. Run the query SELECT table_name FROM user_tables; to see the tables that were created.
   4. Run the command connect OE/OE to connect as OE.
   5. Run the query show user; to show that you are currently connected as OE.
   6. Run the query SELECT table_name FROM user_tables; to see the tables that were created.
   7. Enter exit to exit SQL*PLUS.
5. Enable network connections to the server.
   1. Review Oracle: Database Postinstallation Tasks (Linux) or Oracle: Postinstallation Configuration Tasks on Windows.
   2. Configure the server firewall to allow access to the Oracle Listener on TCP port 1521.
6. Install and test SQL*PLUS on a client computer.
   1. Review Oracle: SQL*Plus Instant Client.
   2. Download and install the Instant Client package for your system.
   3. Download and install the SQL*PLUS package for your system.
   4. At a command prompt, navigate to the folder where you installed the Instant Client and SQL*PLUS.
   5. Enter either sqlplus system@hostname or sqlplus system@ip address, replacing hostname or ip address with the hostname or address of your server. If you are unable to connect, check the firewall settings on the server.
   6. At the prompt, enter the password specified during installation.
   7. To display current version information, run the query SELECT * FROM PRODUCT_COMPONENT_VERSION;
   8. Enter exit to exit SQL*PLUS.
7. Install and test Oracle SQL Developer on a client computer.
   1. Review Oracle: Installing Oracle SQL Developer.
   2. Download and install Java SE Development Kit.
   3. Download and install Oracle SQL Developer.
   4. Run SQL Developer.
   5. Add a connection to your server. Include username, password, hostname or ip address, and port number. Test and save the connection.
   6. Double-click on the connection to connect.
   7. In the connection worksheet, enter SELECT * FROM PRODUCT_COMPONENT_VERSION;
   8. Run the query.
   9. Close SQL Developer.
8. Review HR and OE schema diagrams.
   1. Review the Oracle: HR and OE schema diagrams.

Lesson Summary

• Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is an object-relational database management system produced and marketed by Oracle Corporation.
• A relational database is a digital database whose organization is based on the relational model of data.
• The various software systems used to maintain relational databases are known as a relational database management system (RDBMS).
• The relational model organizes data into one or more tables (or "relations") of columns and rows, with a unique key identifying each row. Rows are also called records or tuples.
• Each table/relation represents one "entity type". The rows represent instances of that type of entity and the columns representing values attributed to that instance.
• Database normalization, or simply normalization, is the process of organizing the columns (attributes) and tables (relations) of a relational database to minimize data redundancy.
• Normalization involves decomposing a table into less redundant (and smaller) tables without losing information, and then linking the data back together by defining foreign keys in the old table referencing the primary keys of the new ones.
• The objective of normalization is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database using the defined foreign keys.
• Virtually all relational database systems use SQL (Structured Query Language) as the language for querying and maintaining the database.
• SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS).
• SQL was originally based upon relational algebra and tuple relational calculus and consists of a data definition language, data manipulation language, and data control language.
• Data Definition Language (DDL) commands include CREATE, ALTER, DROP, and RENAME.
• Data Manipulation Language (DML) commands include SELECT, INSERT, UPDATE, and DELETE.
• Data Control Language (DCL) commands include GRANT and REVOKE.
• Transaction Control Language (TCL) commands include COMMIT, ROLLBACK, and SAVEPOINT.
• Tools available for managing Oracle databases include SQL*Plus, Oracle SQL Developer, and Oracle Enterprise Manager.

Key Terms

entity relationship diagram

A diagram that demonstrates the relationships between entities in a relational database.

foreign key

A field in a relational table that matches the primary key column of another table.

grid computing

The collection of computer resources from multiple locations to reach a common goal.

instance

Refers to any running process.

Oracle Enterprise Manager (OEM or EM)

A set of web-based tools aimed at managing software and hardware produced by Oracle Corporation as well as by some non-Oracle entities.

Oracle SQL Developer (SQLDeveloper)

An Integrated development environment (IDE) for working with SQL in Oracle databases using the Java Development Kit.

PGA (Program Global Area)

Memory-area of an Oracle instance that contains data and control-information for Oracle's server-processes.

PL/SQL (Procedural Language/Structured Query Language)

Oracle Corporation's procedural extension for SQL and the Oracle relational database.

primary key

A field or fields that uniquely specifies a record or tuple within a table.

schema

The structure of a database described in a formal language supported by the database management system (DBMS). An Oracle database associates a separate schema with each database user.

SGA (System Global Area)

Forms the part of the system memory (RAM) shared by all the processes belonging to a single Oracle database instance.

SQL*Plus

An Oracle Database utility, with a basic command-line interface, commonly used by users, administrators, and programmers.

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - General

See Also

• Oracle: Database 2 Day Developers Guide - Introduction

References

Oracle SQL Fundamentals

Lesson 2 - Selecting Data

This lesson introduces selecting data using the SELECT statement.

Objectives and Skills

Objectives and skills for the selecting data portion of Oracle SQL Fundamentals I certification include:

• Retrieving Data Using the SQL SELECT Statement
  • List the capabilities of SQL SELECT statements
  • Execute a basic SELECT statement

Readings

1. Read Wikipedia: Select (SQL).
2. Read Wikipedia: DUAL table.
3. Read Oracle: DESCRIBE.
4. Read Oracle: SELECT.

Multimedia

1. YouTube: Oracle SQL Tutorial - Querying a table - Part 1
2. YouTube: Oracle SQL Tutorial - Querying data - Part 2
3. YouTube: Oracle SQL Tutorial - Querying data - Part 3 - Column Aliases
4. YouTube: Oracle SQL Tutorial - Querying data - Part 5 - SQL DISTINCT clause
5. YouTube: Oracle 11g SQL Tutorial & Exam 1Z0-051 : Lesson 1 SELECT Statement

Activities

1. Describe tables in the HR schema.
   1. Run SQL Developer.
   2. Add a new connection to the HR schema. Include username hr, password hr, hostname or ip address, and port number. Test and save the connection.
   3. Double-click on the HR connection to connect.
   4. In the connection worksheet, enter DESCRIBE COUNTRIES; and run the command. Observe the script output.
   5. Under the HR connection, expand Tables.
   6. Select COUNTRIES. Observe the columns and properties.
   7. Use the DESCRIBE statement or SQL Developer to view other tables in the HR schema.
2. Select data from the HR schema.
   1. Run the query SELECT * FROM COUNTRIES; to see all data in the COUNTRIES table.
   2. Run the query SELECT COUNTRY_ID FROM COUNTRIES; to see all country abbreviations in the COUNTRIES table.
   3. Run the query SELECT COUNTRY_NAME FROM COUNTRIES; to see all countries in the COUNTRIES table.
   4. Run the query SELECT COUNTRY_ID, COUNTRY_NAME FROM COUNTRIES; to see all country abbreviations and countries in the COUNTRIES table.
   5. Run the query SELECT REGION_ID FROM COUNTRIES; to see all regions in the COUNTRIES table.
   6. Run the query SELECT DISTINCT REGION_ID FROM COUNTRIES; to see unique regions in the COUNTRIES table.
3. Calculate results from the HR schema.
   1. Run the query SELECT * FROM JOBS; to see all data in the JOBS table.
   2. Run the query SELECT JOB_TITLE, MAX_SALARY - MIN_SALARY FROM JOBS; to see jobs and salary ranges.
   3. Run the query SELECT JOB_TITLE AS JOB, MAX_SALARY - MIN_SALARY AS SALARY_RANGE FROM JOBS; to see jobs and salary ranges with column aliases.
   4. Run the query SELECT 'The ' || JOB_TITLE || '''s salary range is ' || (MAX_SALARY - MIN_SALARY) || '.' AS SALARY_RANGE FROM JOBS; to see jobs and salary ranges concatenated as sentences.
   5. Run the query SELECT 60 * 60 * 24 AS SECONDS_IN_A_DAY FROM DUAL; to calculate the number of seconds in a day.
4. Describe tables in the OE schema.
   1. Run SQL Developer.
   2. Add a new connection to the OE schema. Include username OE, password OE, hostname or ip address, and port number. Test and save the connection.
   3. Double-click on the OE connection to connect.
   4. Use the DESCRIBE statement or SQL Developer to view table structure for different tables in the OE schema.
5. Select data from the OE schema.
   1. Select all data from the CATEGORIES table.
   2. Select only category names from the CATEGORIES table.
   3. Select category descriptions from the CATEGORIES table.
   4. Select category names and descriptions from the CATEGORIES table.
   5. Select all countries from the CUSTOMERS table.
   6. Select a unique list of countries from the CUSTOMERS table.
6. Calculate results from the OE schema.
   1. Select all data from the ORDER_ITEMS table.
   2. Select the product ID and discount (unit price - discount price) for each item.
   3. Select the product ID and discount for each item with appropriate column aliases.
   4. Select the product ID and discount for each item as a sentence, such as "The discount for product 3150 is $2."
   5. Calculate the number of seconds in the current year (365 days for a typical year or 366 days for a leap year).

Lesson Summary

• The SQL SELECT statement returns a result set of records from one or more tables.
• The basic SELECT statement syntax is SELECT [DISTINCT] <column> [AS <alias>, ...] FROM <table> [AS <alias>, ...].
• An asterisk ("*") can be used to specify that the query should return all columns of the queried tables.
• SQL statements are terminated with a semicolon (";").
• The optional DISTINCT keyword removes duplicate rows from the result set.
• The DESCRIBE command lists column information for a given table or view.
• SQL data types include character strings, bit strings, numbers, and temporal (date/time) types.
• String and date/time literals are enclosed in single quotes (').
• String concatenation is accomplished using the concatenation operator ||.
• Aliases may be enclosed in double quotes (") for case sensitivity or to include multiple words.
• Oracle SQL statements are not case sensitive, but string literals are case sensitive.
• The DUAL table is a special one-row, one-column table present by default in Oracle and other database installations.

Key Terms

concatenation

The operation of joining character strings end-to-end.

join

A relational algebra term which refers to a combination of result sets based on equal values for common (shared) attribute names.

null

A special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database.

projection

A relational algebra term which refers to restricting a result set to a subset of the available attributes or columns.

selection

A relational algebra term which refers to restricting a result set to a subset of the available records or rows.

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Select Statement

See Also

• Oracle: Database 2 Day Developers Guide - Connecting to Oracle Database

References

Oracle SQL Fundamentals

Lesson 3 - Restricting Data

This lesson introduces restricting data using the WHERE clause.

Objectives and Skills

Objectives and skills for the restricting data portion of Oracle SQL Fundamentals I certification include:

• Restricting and Sorting Data
  • Limit the rows that are retrieved by a query

Readings

1. Read Wikipedia: Where (SQL).

Multimedia

1. Oracle SQL Tutorial - Querying data - Part 6 - WHERE clause
2. Oracle SQL Tutorial - Querying data - Part 7 - WHERE clause continued
3. Oracle SQL Tutorial - Querying data - Part 8 - WHERE clause continued

Activities

1. Select HR data using the WHERE clause.
   1. Run the query SELECT * FROM EMPLOYEES; to see all data in the EMPLOYEES table.
   2. Run the query SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 20; to see all employees in department 30.
   3. Run the query SELECT * FROM EMPLOYEES WHERE JOB_ID = 'SA_REP'; to see all employees with a job id of SA_REP.
   4. Run the query SELECT * FROM EMPLOYEES WHERE JOB_ID = 'sa_rep'; to see all employees with a job id of sa_rep. Note that Oracle WHERE comparisons are case-sensitive.
   5. Run the query SELECT * FROM EMPLOYEES WHERE HIRE_DATE >= '1 JAN 2000'; to see all employees hired on or after January 1, 2000.
   6. Run the query SELECT * FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '1 JAN 1999' AND '31 DEC 1999'; to see all employees hired in 1999.
   7. Run the query SELECT * FROM EMPLOYEES WHERE MANAGER_ID IN (100, 145, 205); to see all employees who have manager 100, 145, or 205.
   8. Run the query SELECT * FROM EMPLOYEES WHERE PHONE_NUMBER LIKE '0%'; to see all employees with a phone number beginning with 0.
   9. Run the query SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE 'J___'; to see all employees with a four-character first name beginning with J.
   10. Run the query SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IS NULL; to see all employees not assigned to a department.
2. Select HR data using logical operators.
   1. Run the query SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 80 AND COMMISSION_PCT < 0.2; to see all employees in department 80 with a commission percentage less than 0.2.
   2. Run the query SELECT * FROM EMPLOYEES WHERE SALARY > 20000 OR COMMISSION_PCT > 0.3; to see all employees with a salary greater than 20,000 or with a commission percentage greater than 0.3.
   3. Run the query SELECT * FROM EMPLOYEES WHERE PHONE_NUMBER NOT LIKE '515%'; to see all employees with phone numbers that do not begin with 515.
3. Select OE data using the WHERE clause.
   1. Select all data in the CUSTOMERS table.
   2. Select all customers with a credit limit greater than 100000.
   3. Select all customers with Platinum status.
   4. Select all customers with Gold status.
   5. Select all customers with a credit limit between 50000 and 100000.
   6. Select all customers in Germany (DE), Italy (IT), and Switzerland (CH).
   7. Select all customers with phone numbers beginning with '+1 '.
   8. Select all customers with a six-character postal code ending with 0.
   9. Select all customers not assigned to an account manager.
4. Select OE data using logical operators.
   1. Select all US customers with Silver status.
   2. Select all customers from India (IN) or China (CN).
   3. Select all customers who do not have account manager 145.

Lesson Summary

• A WHERE clause in SQL specifies that a SQL Data Manipulation Language (DML) statement should only affect rows that meet specified criteria.
• The basic WHERE clause syntax is <SQL-DML-Statement> FROM <table> WHERE <predicate>.
• Simple predicates use one of the operators =, <>, >, >=, <, <=, IN, BETWEEN, LIKE, IS NULL or IS NOT NULL.
• IN will find any values existing in a set of candidates.
• BETWEEN will find any values within a range.
• LIKE will find a string fitting a certain description.
• The underscore character (_) is used as a wildcard character to match any single character for LIKE comparisons.
• The percent character (%) is used as a wildcard character for to match any character string for LIKE comparisons.
• The keywords AND and OR may be used to combine two predicates into a new one.
• The keyword NOT may be used to negate a condition.
• Predicates may be enclosed in parentheses if desired.
• Operator precedence is evaluated in the order (), */, +-, ||, = <> <= >=, IN LIKE IS NULL, BETWEEN, !=, NOT, AND, OR.

Key Terms

operator precedence

A collection of rules that define which procedures to perform first in order to evaluate a given mathematical expression.

wildcard

a single character used to represent a number of characters or an empty string.

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Select Statement

See Also

• Oracle: Database 2 Day Developers Guide - Selecting Table Data

References

Oracle SQL Fundamentals

Lesson 4 - Sorting Data

This lesson introduces sorting data using the ORDER BY clause and runtime ampersand statement substitution.

Objectives and Skills

Objectives and skills for the sorting data portion of Oracle SQL Fundamentals I certification include:

• Restricting and Sorting Data
  • Sort the rows that are retrieved by a query
  • Use ampersand substitution to restrict and sort output at runtime

Readings

1. Read Wikipedia: Order by (SQL).
2. Read Oracle: Using Substitution Variables.

Multimedia

1. YouTube: Oracle SQL Tutorial - Querying data - Part 4 - (ORDER BY clause)

Activities

1. Select HR data using the ORDER BY clause.
   1. Run the query SELECT * FROM EMPLOYEES; to see all data in the EMPLOYEES table.
   2. Run the query SELECT * FROM EMPLOYEES ORDER BY LAST_NAME; to see all employees in order by last name.
   3. Run the query SELECT * FROM EMPLOYEES ORDER BY LAST_NAME, FIRST_NAME; to see all employees in order by last name and first name.
   4. Run the query SELECT * FROM EMPLOYEES ORDER BY SALARY DESC, LAST_NAME, FIRST_NAME; to see all employees in order by descending salary and then last name and first name.
   5. Run the query SELECT * FROM EMPLOYEES ORDER BY DEPARTMENT_ID NULLS FIRST; to see all employees in order by department ID, with no department ID listed first.
   6. Run the query SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 80 ORDER BY SALARY DESC, COMMISSION_PCT DESC; to see all employees in department 80 in order by descending salary and descending commission percentage.
2. Select HR data using ampersand substitution.
   1. Run the query SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = &EMPLOYEE_ID; to select a given employee at run time.
   2. Run the query SELECT * FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '&STARTING_DATE' AND '&ENDING_DATE'; to select employees based on a hire date given at run time.
   3. Run the query SELECT * FROM EMPLOYEES WHERE &&COLUMN < &VALUE ORDER BY &COLUMN; to select employees based on a column and value given at run time.
3. Select OE data using the ORDER BY clause.
   1. Select all data in the CUSTOMERS table.
   2. Select all customers in order by country, postal code, last name, and first name.
   3. Select all customers in order by account manager and descending credit limit, with customers with no account manager listed last.
   4. Select all customers in order by account manager and descending credit limit, but do not include customers with no account manager.
4. Select OE data using ampersand substitution.
   1. Create a query to select a given customer at run time.
   2. Create a query to select customers within a given credit range entered at run time.
   3. Create a query to select and sort customers based on a column and value entered at run time.

Lesson Summary

• By default, relational database systems may return data rows in any order, or more specifically, without any order.
• An ORDER BY clause in SQL specifies that a SQL SELECT statement returns a result set with the rows being sorted by the values of one or more columns.
• The sort criteria do not have to be included in the result set.
• The sort criteria can be expressions, including column names, user-defined functions, arithmetic operations, or CASE expressions.
• The basic ORDER BY clause syntax is SELECT <column(s)> FROM <table(s)> ORDER BY <column> [DESC] [NULLS FIRST], ....
• The DESC keyword will sort a given column in descending order.
• The NULLS FIRST will sort null values first. The SQL standard does not define define a default sort order for nulls, but Oracle defaults to NULLS LAST.
• When combined with a WHERE clause, the syntax is SELECT <column(s)> FROM <table(s)> WHERE predicate ORDER BY <columns>
• A substitution variable is preceded by one or two ampersands (&, &&).
• Oracle prompts for values for undefined variables preceded by an ampersand.
• Oracle prompts for values for undefined variables preceded by two ampersands and defines those values.
• Substitution variables may be undefined using the UNDEFINE keyword.
• SQL*Plus will echo before and after SQL statements to verify proper substitution. This echo may be disabled using the command SET VERIFY OFF.

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Select Statement

See Also

• Oracle: Database 2 Day Developers Guide - Selecting Table Data

References

Oracle SQL Fundamentals

Lesson 5 - Single-Row Functions

This lesson introduces single-row functions.

Objectives and Skills

Objectives and skills for the single-row functions portion of Oracle SQL Fundamentals I certification include:

• Using Single-Row Functions to Customize Output
  • Describe various types of functions available in SQL
  • Use character, number, and date functions in SELECT statements

Readings

1. Read Oracle: Single-Row Functions

Multimedia

1. YouTube: Oracle SQL Tutorial - Querying Data - Part 9 - Intro to Single-Row Functions
2. YouTube: Oracle SQL Tutorial - Querying Data - Part 10(a) - Single-Row Character Functions
3. YouTube: Oracle SQL Tutorial - Querying data - Part 10(b) - Single-Row Character Functions
4. YouTube: Oracle SQL Tutorial - Querying a table - Part 10(c) Single-Row Numeric Functions
5. YouTube: Oracle SQL Tutorial - Querying a table - Part 10(d) - Single-Row Date Functions

Activities

1. Test case conversion functions using HR data.
   1. Run the following query:
      SELECT COUNTRY_NAME AS ORIGINAL, UPPER(COUNTRY_NAME) AS UPPER, LOWER(COUNTRY_NAME) AS LOWER, INITCAP(COUNTRY_NAME) AS MIXED FROM COUNTRIES;
2. Test character manipulation functions using HR data.
   1. Run the following query to test LENGTH():
      SELECT '"' || COUNTRY_NAME || '" is ' || LENGTH(COUNTRY_NAME) || ' characters.' AS COUNTRY FROM COUNTRIES;
   2. Run the following query to test CONCAT() and LPAD()):
      SELECT CONCAT(LPAD(SALARY, 8, '*'), '.00') FROM EMPLOYEES;
   3. Run the following query to test SUBSTR() and TRIM():
      SELECT FIRST_NAME, LAST_NAME, CONCAT(LOWER(SUBSTR(TRIM(FIRST_NAME), 1, 1)), LOWER(SUBSTR(TRIM(LAST_NAME), 1, 7))) AS EMAIL FROM EMPLOYEES;
   4. Run the following query to test INSTR():
      SELECT TRIM(SUBSTR('Wales, Jimbo', INSTR('Wales, Jimbo', ',') + 1)) AS FIRST_NAME FROM DUAL;
   5. Run the following query to test REPLACE():
      SELECT REPLACE(PHONE_NUMBER, '.', '-') FROM EMPLOYEES;
3. Test numeric functions using HR data.
   1. Run the following query to test ROUND() and TRUNC():
      SELECT LAST_NAME, SALARY * 12 / 365 AS DAILY, ROUND(SALARY * 12 / 365, 2) AS ROUND, TRUNC(SALARY * 12 / 365, 2) AS TRUNC FROM EMPLOYEES;
   2. Run the following query to test MOD():
      SELECT MOD(365, 7) FROM DUAL;
4. Test date functions using HR data.
   1. Run the following query to test MONTHS_BETWEEN():
      SELECT LAST_NAME, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) AS MONTHS_SINCE_HIRE FROM EMPLOYEES;
   2. Run the following query to test MONTHS_ADD():
      SELECT ADD_MONTHS(SYSDATE, 60) AS "60 MONTHS" FROM DUAL;
   3. Run the following query to test NEXT_DAY():
      SELECT NEXT_DAY(SYSDATE, 'MONDAY') AS NEXT_MONDAY, LAST_DAY(SYSDATE) AS LAST_DAY_OF_MONTH FROM DUAL;
5. Test case conversion functions using OE data.
   1. Select customer email address as lower case.
   2. Select customer NLS territory as mixed case.
   3. Select customer status as upper case.
6. Test character manipulation functions using OE data.
   1. Select the total length of customer's first and last names.
   2. Select the customer's credit limit, displayed in a column 10 characters wide with leading asterisks.
   3. Trim any leading or trailing spaces from the customer's address.
   4. Select the domain name / company name from the customer's email address.
   5. Separate the country code and local phone number from the customer's full phone number.
   6. Select the customer's phone number, replacing spaces with hyphens.
7. Test numeric functions using OE data.
   1. Select the customer's credit limit, rounded to 24 payments and truncated to 24 payments.
   2. Select the number of cards left over in a 52-card deck if there are 5 players and they each receive an equal number of cards.
8. Test date functions using OE data.
   1. Select the number of months between each order's order date and the current date.
   2. Select 24 months after the order date to determine when payment is due.
   3. Select the calendar date of next Saturday based on SYSDATE.
   4. Select the last day of the month for each order date.

Lesson Summary

• Single-row functions return a single result row for every row of a queried table or view.

Case Conversion Functions

• UPPER(char) returns char, with all letters uppercase.
• LOWER(char) returns char, with all letters lowercase.
• INITCAP(char) returns char, with the first letter of each word in uppercase, all other letters in lowercase.

Character Functions

• LENGTH(char) returns the length of the given character string.
• CONCAT(char1, char2) returns char1 concatenated with char2.
• SUBSTR(char, start, [length]) returns a portion of char, beginning at character start, either length characters long or to the end of the string if length is omitted.
• INSTR(string, substring, [start,] [occurrence]) searches string for substring, optionally beginning at start and seeking the given occurrence.
• LPAD(expr1, n, [expr2]) returns expr1, left-padded to length n characters with either the sequence of characters in expr2 or spaces.
• RPAD(expr1, n, [expr2]) returns expr1, right-padded to length n characters with either the sequence of characters in expr2 or spaces.
• TRIM([options,] string) trims leading and trailing spaces or other specified characters from a character string
• REPLACE(char, search, [replacement]) returns char with every occurrence of search replaced with replacement. If replacement is omitted or null, then all occurrences of search are removed.

Number Functions

• ROUND(number, [integer]) ROUND returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to zero places. If integer is negative, then n is rounded off to the left of the decimal point.
• TRUNC(n1, n2) returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
• MOD(n2, n1) returns the remainder of n2 divided by n1.

Date Functions

• ADD_MONTHS(date, integer) returns the given date plus integer months.
• LAST_DAY(date) returns the date of the last day of the month that contains date.
• MONTHS_BETWEEN(date1, date2) returns the number of months between dates date1 and date2.
• NEXT_DAY(date, char) returns the date of the first weekday named by char that is later than the given date.
• SYSDATE returns the current date and time set for the operating system on which the database server resides.

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Date Functions

See Also

• Oracle: Database 2 Day Developers Guide - Selecting Table Data

References

Oracle SQL Fundamentals

Lesson 6 - Conversion Functions

This lesson introduces conversion functions.

Objectives and Skills

Objectives and skills for the conversion functions portion of Oracle SQL Fundamentals I certification include:

• Using Conversion Functions and Conditional Expressions
  • Describe various types of conversion functions that are available in SQL
  • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

Readings

1. Read Wikipedia: Primitive data type.
2. Read Oracle: Conversion Functions.
3. Read Oracle: TO_CHAR (character).
4. Read Oracle: TO_CHAR (number).
5. Read Oracle: TO_CHAR (datetime).
6. Read Oracle: TO_NUMBER.
7. Read Oracle: TO_DATE.
8. Read Oracle: Format Models.

Multimedia

1. YouTube: Oracle SQL Tutorial - Querying a table - Part 10(e) - Single-Row Conversion Functions

Activities

1. Test character conversion functions using HR data.
   1. Run the following query:
      SELECT LAST_NAME, TO_CHAR(SALARY, '$99,999.00') AS SALARY, TO_CHAR(COMMISSION_PCT, 'V99') AS COMMISSION FROM EMPLOYEES;
   2. Run the following query:
      SELECT LAST_NAME, TO_CHAR(HIRE_DATE, 'DY MON DD YYYY') FROM EMPLOYEES;
   3. Run the following query:
      SELECT LAST_NAME, TO_CHAR(HIRE_DATE, 'fmDay, Month D, YYYY') FROM EMPLOYEES;
2. Test date conversion functions using DUAL.
   1. Run the following query:
      SELECT TO_DATE('012345', 'MMDDYY') AS "DATE" FROM DUAL;
   2. Run the following query:
      SELECT TO_DATE('01/23/45', 'MM/DD/YY') AS "DATE" FROM DUAL;
3. Test number conversion functions using DUAL.
   1. Run the following query:
      SELECT TO_NUMBER('$12,345.67', '$99,999.00') AS "NUMBER" FROM DUAL;
4. Test character conversion functions using OE data.
   1. Select customer credit limit using different numeric formats.
   2. Select order date using different date formats.
5. Test date conversion functions using DUAL.
   1. Select various character-based date formats and convert the values to dates.
6. Test number conversion functions using DUAL.
   1. Select various character-based numeric formats and convert the values to numbers.

Lesson Summary

• TO_CHAR (character) converts NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set. The value returned is always VARCHAR2.
• TO_CHAR (n, [format]) converts n to a value of VARCHAR2 data type, using the optional number format.
• TO_CHAR (datetime, [format]) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, or INTERVAL YEAR TO MONTH data type to a value of VARCHAR2 data type in the format specified by the date format.
• TO_NUMBER(expression, [format]) converts expression to a value of NUMBER data type. The expression can be a number value of CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY_FLOAT, or BINARY_DOUBLE data type.
• TO_DATE(char, [format]) converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of DATE data type.

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Functions

See Also

• Oracle: Database 2 Day Developers Guide - Selecting Table Data

References

Oracle SQL Fundamentals

Lesson 7 - Conditional Expressions

This lesson introduces conditional expressions.

Objectives and Skills

Objectives and skills for the conditional expressions portion of Oracle SQL Fundamentals I certification include:

• Using Conversion Functions and Conditional Expressions
  • Apply conditional expressions in a SELECT statement

Readings

1. Read Wikipedia: Null (SQL).
2. Read Oracle: NVL.
3. Read Oracle: NVL2.
4. Read Oracle: NULLIF.
5. Read Oracle: COALESCE.
6. Read Oracle: DECODE.
7. Read Oracle: CASE Statement.

Multimedia

1. YouTube: Oracle SQL Tutorial - Querying a table - Part 10(f) - Single Row Null Functions

Activities

1. Test null conditions using HR data.
   1. Run the following query:
      SELECT LAST_NAME, NVL(COMMISSION_PCT, 0) AS COMMISSION FROM EMPLOYEES;
   2. Run the following query:
      SELECT LAST_NAME, NVL2(TO_CHAR(COMMISSION_PCT), TO_CHAR(COMMISSION_PCT, '0.00'), ' None') AS COMMISSION FROM EMPLOYEES;
   3. Run the following query:
      SELECT NULLIF(HIRE_DATE, SYSDATE) AS NULLS_CURRENT_DATE FROM EMPLOYEES;
   4. Run the following query:
      SELECT COALESCE(NULL, NULL, 'Not Null', NULL) AS "COALESCE" FROM DUAL;
2. Test conditional expressions using HR data.
   1. Run the following query:
      SELECT LAST_NAME,
DECODE(SUBSTR(PHONE_NUMBER, 1, 3),
    '515', 'Iowa',
    '590', 'Unassigned',
    '603', 'New Hampshire',
    '650', 'California',
    '011', 'International') AS AREA
FROM EMPLOYEES;
   2. Run the following query:
      SELECT LAST_NAME,
    CASE SUBSTR(PHONE_NUMBER, 1, 3)
    WHEN '515' THEN 'Iowa'
    WHEN '590' THEN 'Unassigned'
    WHEN '603' THEN 'New Hampshire'
    WHEN '650' THEN 'California'
    WHEN '011' THEN 'International' END AS AREA
FROM EMPLOYEES;
   3. Run the following query:
      SELECT LAST_NAME,
CASE WHEN MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12 < 1 THEN 'Rookie'
    WHEN MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12 < 5 THEN 'Junior'
    WHEN MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12 < 10 THEN 'Senior'
    ELSE 'Master' END AS STATUS
FROM EMPLOYEES;
3. Test null conditions using OE data.
   1. Use NLV to display when a customer has no account manager.
   2. Use NLV2 to display when an order has no sales rep.
   3. Use NULLIF to clear order mode for a direct order.
   4. Use COALESCE to select from a series of null and non-null values using DUAL.
4. Test conditional expressions using OE data.
   1. Use DECODE to assign values to order status.
   2. Use CASE to display different messages based on customer status.
   3. Use CASE WHEN for order totals >= 10000 to display a message with a special premium support phone number they may call if they need any assistance.

Lesson Summary

• NVL(expression, replacement) replaces a NA value or an empty string with a string.
• NVL2(expr1, expr2, expr3) returns one value when the value of a specified expression is not NA or an empty string, or another value when the value of the specified expression is an empty string or NA.
• NULLIF(expr1, expr2) compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1.
• COALESCE(expr, expr, [...]) returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.
• DECODE(expr , search, result [, search , result]... [, default]) compares one expression to one or more other expressions and, when the base expression is equal to a search expression, returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified, or NA when it is not.
• Simple CASE: CASE variable WHEN value THEN ... WHEN value THEN ... [...] [ELSE ...] END; chooses from a sequence of conditions and runs a corresponding statement.
• Searched CASE: CASE WHEN ... THEN ... WHEN ... THEN .... [...] [ELSE ...] END; chooses from a sequence of conditions and runs a corresponding statement.

See Also

• Oracle: Database 2 Day Developers Guide - Selecting Table Data

References

Oracle SQL Fundamentals

Lesson 8 - Aggregating Data

This lesson introduces aggregating data.

Objectives and Skills

Objectives and skills for the aggregating data portion of Oracle SQL Fundamentals I certification include:

• Reporting Aggregated Data Using the Group Functions
  • Identify the available group functions
  • Describe the use of group functions
  • Group data by using the GROUP BY clause
  • Include or exclude grouped rows by using the HAVING clause

Readings

1. Read Wikipedia: Aggregate function.
2. Read Wikipedia: Group by (SQL).
3. Read Wikipedia: Having (SQL).
4. Read Oracle: Aggregate Functions.

Multimedia

1. YouTube: Oracle SQL Tutorial - Querying a table - Part 11a) - Group Functions (Theory)
2. YouTube: Oracle SQL Tutorial - Querying a Table - Part 11(b) - Group Functions (Hands-on)

Activities

1. Test aggregate functions using HR data.
   1. Count the total number of employees using the following query:
      SELECT COUNT(*) AS EMPLOYEE_COUNT FROM EMPLOYEES;
   2. Count the total number of employees assigned to any department using the following query:
      SELECT COUNT(DEPARTMENT_ID) AS EMPLOYEES_IN_DEPARTMENTS FROM EMPLOYEES;
   3. Determine the average commission of all sales reps using the following query:
      SELECT TO_CHAR(AVG(COMMISSION_PCT), '0.00') || '%' AS AVERAGE_SALES_REP_COMMISSION
FROM EMPLOYEES
WHERE JOB_ID = 'SA_REP';
   4. Determine the total salary of all managers using the following query:
      SELECT TO_CHAR(SUM(SALARY), '$999,990') AS TOTAL_MANAGER_SALARIES
FROM EMPLOYEES
WHERE JOB_ID LIKE '%MGR%' OR JOB_ID LIKE '%MAN%';
2. Test data grouping using HR data.
   1. Count the total number of employees assigned to each department using the following query:
      SELECT DEPARTMENT_ID, COUNT(*) AS EMPLOYEE_COUNT
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY COUNT(*) DESC;
   2. Determine the average salary for each job using the following query:
      SELECT JOB_ID, TO_CHAR(AVG(SALARY), '$99,990.00') AS AVERAGE_SALARY
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;
   3. Determine the total number of employees and total salary for each department, sorted by total salary in descending order using the following query:
      SELECT DEPARTMENT_ID, COUNT(*) AS EMPLOYEES, TO_CHAR(SUM(SALARY), '$999,990.00') AS TOTAL_SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID
ORDER BY SUM(SALARY) DESC, COUNT(*);
3. Test restricted grouping using HR data.
   1. Count the total number of employees assigned to each department with a count greater than 10 using the following query:
      SELECT DEPARTMENT_ID, COUNT(*) AS EMPLOYEE_COUNT
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) > 10
ORDER BY COUNT(*) DESC;
   2. Determine the average salary for each rep position with an average less than 10,000 using the following query:
      SELECT JOB_ID, TO_CHAR(AVG(SALARY), '$99,990.00') AS AVERAGE_SALARY
FROM EMPLOYEES
WHERE JOB_ID LIKE '%REP%'
GROUP BY JOB_ID
HAVING AVG(SALARY) < 10000
ORDER BY JOB_ID;
   3. Determine the total salary and average commission for each employee reporting to the same manager, sorted by total salary in descending order. List only those managers with employees having an average commission greater than or equal to 0.20 using the following query:
      SELECT MANAGER_ID, TO_CHAR(SUM(SALARY), '$999,990.00') AS TOTAL_SALARY, TO_CHAR(AVG(COMMISSION_PCT), '0.00') || '%' AS AVERAGE_COMMMISSION
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
GROUP BY MANAGER_ID
HAVING AVG(COMMISSION_PCT) >= 0.2
ORDER BY SUM(SALARY) DESC;
4. Test aggregate functions using OE data.
   1. Determine the total number of orders.
   2. Determine the total number of orders assigned to a sales rep.
   3. Determine the average order total.
   4. Determine the total order amount for all online orders.
5. Test data grouping using OE data.
   1. Determine the total number of orders for each year based on order date.
   2. Determine the average order amount for each order mode.
   3. Determine the total number of orders and total order amount for each sales rep, sorted by total order amount in descending order.
6. Test restricted grouping using OE data.
   1. Determine the total number of orders for each year based on order date for dates in 2000 or later.
   2. Determine the average order amount for each order mode where the order status is 0.
   3. Determine the total number of orders and total order amount for each sales rep, sorted by total order amount in descending order. List only those orders sold by sales reps having more than 10 sales.

Lesson Summary

• An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement.
• Common aggregate functions include sum (SUM), count, (COUNT), average (AVG), minimum (MIN), and maximum (MAX).
• SUM(expr) returns the sum of values of expr.
• COUNT(expr) returns the number of rows returned by the query.
• AVG(expr) returns the average value of expr.
• MIN(expr) returns the minimum value of expr.
• MAX(expr) returns the maximum value of expr.
• The GROUP BY clause selects rows having common values into a smaller set of rows. GROUP BY is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The WHERE clause is applied before the GROUP BY clause.
• The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause. Because it acts on the results of the GROUP BY clause, aggregation functions can be used in the HAVING clause predicate.
• The full syntax of the SELECT statement is SELECT column(s) FROM table(s) WHERE condition(s) GROUP BY column(s) HAVING condition(s) ORDER BY column(s).

See Also

• Oracle: Database 2 Day Developers Guide - Selecting Table Data

References

Oracle SQL Fundamentals

Lesson 9 - Joining Data

This lesson introduces joining data.

Objectives and Skills

Objectives and skills for the joining data portion of Oracle SQL Fundamentals I certification include:

• Displaying Data from Multiple Tables
  • Write SELECT statements to access data from more than one table using equijoins and nonequijoins
  • Join a table to itself by using a self-join
  • View data that generally does not meet a join condition by using outer joins
  • Generate a Cartesian product of all rows from two or more tables

Readings

1. Read Wikipedia: Join (SQL).
2. Read Oracle: Joins.
3. Read Guide to SQL Joins.

Multimedia

1. YouTube: Oracle SQL Tutorials - Joining Tables Theory - Part 1
2. YouTube: Oracle SQL Tutorial - Joining Tables Theory - Part 2
3. YouTube: Oracle SQL Tutorial - Joining Tables (Hands-on) - Part 1
4. YouTube: Oracle SQL Tutorial - Joining Tables (Hands-on) - Part 2
5. YouTube: SQL Class - Demo - Explaining Outer Joins and Self Join

Activities

Schema Diagrams

1. Review HR and OE schema diagrams.
   1. Review the Oracle: HR and OE schema diagrams.

HR Joins

Natural Joins

1. Test natural joins of two tables using HR data.
   1. Join the regions and countries tables with a WHERE clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME
FROM REGIONS R, COUNTRIES C
WHERE R.REGION_ID = C.REGION_ID
ORDER BY R.REGION_NAME, C.COUNTRY_NAME;
   2. Join the regions and countries tables with a NATURAL JOIN clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME
FROM REGIONS R NATURAL JOIN COUNTRIES C
ORDER BY R.REGION_NAME, C.COUNTRY_NAME;
   3. Join the regions and countries tables with a JOIN USING clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME
FROM REGIONS R JOIN COUNTRIES C USING (REGION_ID)
ORDER BY R.REGION_NAME, C.COUNTRY_NAME;
   4. Join the regions and countries tables with a JOIN ON clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME
FROM REGIONS R JOIN COUNTRIES C ON (R.REGION_ID = C.REGION_ID)
ORDER BY R.REGION_NAME, C.COUNTRY_NAME;
2. Test natural joins of three tables using HR data.
   1. Join the regions, countries, and locations tables with a WHERE clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME, L.CITY
FROM REGIONS R, COUNTRIES C, LOCATIONS L
WHERE R.REGION_ID = C.REGION_ID
AND C.COUNTRY_ID = L.COUNTRY_ID
ORDER BY R.REGION_NAME, C.COUNTRY_NAME, L.CITY;
   2. Join the regions, countries, and locations tables with a NATURAL JOIN clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME, L.CITY
FROM REGIONS R NATURAL JOIN COUNTRIES C NATURAL JOIN LOCATIONS L
ORDER BY R.REGION_NAME, C.COUNTRY_NAME, L.CITY;
   3. Join the regions, countries, and locations tables with a JOIN USING clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME, L.CITY
FROM REGIONS R JOIN COUNTRIES C USING (REGION_ID) JOIN LOCATIONS L USING (COUNTRY_ID)
ORDER BY R.REGION_NAME, C.COUNTRY_NAME, L.CITY;
   4. Join the regions, countries, and locations tables with a JOIN ON clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME, L.CITY
FROM REGIONS R JOIN COUNTRIES C ON (R.REGION_ID = C.REGION_ID)
JOIN LOCATIONS L ON (C.COUNTRY_ID = L.COUNTRY_ID)
ORDER BY R.REGION_NAME, C.COUNTRY_NAME, L.CITY;

Nonequijoins

1. Test nonequijoins using HR data.
   1. Select employees whose salary is less than the minimum salary of other positions and list the position using the following query:
      SELECT E.LAST_NAME, E.SALARY, J.MIN_SALARY, J.JOB_TITLE
FROM EMPLOYEES E JOIN JOBS J ON (E.SALARY < J.MIN_SALARY)
ORDER BY J.JOB_TITLE, E.SALARY, E.LAST_NAME;

Self Joins

1. Test self joins using HR data.
   1. Select manager names and employee names from the employees table using the following query:
      SELECT M.LAST_NAME AS MANAGER, E.LAST_NAME AS EMPLOYEE
FROM EMPLOYEES E JOIN EMPLOYEES M ON (E.MANAGER_ID = M.EMPLOYEE_ID)
ORDER BY M.LAST_NAME, E.LAST_NAME;

Outer Joins

1. Test outer joins using HR data.
   1. Select all departments and the last names of employees in those departments. Include departments with no employees using ANSI syntax in the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME
FROM DEPARTMENTS D
LEFT OUTER JOIN EMPLOYEES E ON (D.DEPARTMENT_ID = E.DEPARTMENT_ID)
ORDER BY D.DEPARTMENT_NAME, E.LAST_NAME;
   2. Select all departments and the last names of employees in those departments. Include departments with no employees using Oracle syntax in the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID (+)
ORDER BY D.DEPARTMENT_NAME, E.LAST_NAME;
   3. Select the last names of all employees and their departments. Include employees with no department using ANSI syntax in the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME
FROM DEPARTMENTS D
RIGHT OUTER JOIN EMPLOYEES E ON (D.DEPARTMENT_ID = E.DEPARTMENT_ID)
ORDER BY D.DEPARTMENT_NAME NULLS FIRST, E.LAST_NAME;
   4. Select the last names of all employees and their departments. Include employees with no department using Oracle syntax in the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID (+) = E.DEPARTMENT_ID
ORDER BY D.DEPARTMENT_NAME NULLS FIRST, E.LAST_NAME;
   5. Select all departments and the last names of all employees. Include departments with no employees and employees with no departments using the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME
FROM DEPARTMENTS D
FULL OUTER JOIN EMPLOYEES E ON (D.DEPARTMENT_ID = E.DEPARTMENT_ID)
ORDER BY D.DEPARTMENT_NAME NULLS FIRST, E.LAST_NAME;
   6. Select the last name of employees with no department using the following query:
      SELECT E.LAST_NAME
FROM EMPLOYEES E
LEFT OUTER JOIN DEPARTMENTS D ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
WHERE D.DEPARTMENT_ID IS NULL;

Cross Joins

1. Test cross joins using HR data.
   1. Select the number of employees using the following query:
      SELECT COUNT(*) AS COUNT FROM EMPLOYEES;
   2. Select the number of employees in departments using the following query:
      SELECT COUNT(*) AS COUNT
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);
   3. Select the number of employees times the number of departments using the following query:
      SELECT COUNT(*) AS COUNT
FROM EMPLOYEES CROSS JOIN DEPARTMENTS;
   4. Explain why the following query shows a different number of employees in departments:
      SELECT COUNT(*) AS COUNT
FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;

OE Joins

1. Test natural joins using OE data.
   1. List customers, orders, order items, and product information. Test a variety of join types until you are comfortable with the syntax.
   2. List warehouse, product, and inventory availability. Test a variety of join types until you are comfortable with the syntax.
2. Test nonequijoins using OE data.
3. Test self joins using OE data.
4. Test outer joins using OE data.
   1. List customers, orders, order items, and product information. Test a variety of ANSI and Oracle outer join types until you are comfortable with the syntax.
   2. List warehouse, product, and inventory availability. Test a variety of ANSI and Oracle outer join types until you are comfortable with the syntax.
5. Test cross joins using OE data.

Lesson Summary

• A SQL join clause combines columns from one or more tables in a relational database.
• ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join.
• An inner join contains each row in the two joined tables that have matching rows.
• A left outer join contains all rows of the "left" table, even if the join-condition does not find any matching row in the "right" table.
• A right outer join contains all rows of the "right" table, even if the join-condition does not find any matching row in the "left" table.
• A full outer join combines the effect of applying both left and right outer joins, containing all rows of both tables, and joined where there are matching rows.
• A cross join combines each row from the first table with every row from the second table.
• A self-join is joining a table to itself.
• An equi-join is a specific type of comparator-based join that uses equality comparisons in the join-predicate.
• A non-equi-join uses non-equality comparison operators (such as < or >) in the join-predicate.
• WHERE syntax: SELECT <field(s)> FROM <table1>, <table2> WHERE <table1.field> = <table2.field>;
• NATURAL JOIN syntax: SELECT <field(s)> FROM <table1> NATURAL JOIN <table2>;
• JOIN USING syntax: SELECT <field(s)> FROM <table1> JOIN <table2> USING (<column>);
• JOIN ON syntax: SELECT <field(s)> FROM <table1> JOIN <table2> ON (<table1.field> = <table2.field>);
• LEFT OUTER JOIN syntax: SELECT <field(s)> FROM <table1> LEFT OUTER JOIN <table2> ON (<table1.field> = <table2.field>);
• RIGHT OUTER JOIN syntax: SELECT <field(s)> FROM <table1> RIGHT OUTER JOIN <table2> ON (<table1.field> = <table2.field>);
• FULL OUTER JOIN syntax: SELECT <field(s)> FROM <table1> FULL OUTER JOIN <table2> ON (<table1.field> = <table2.field>);
• Explicit CROSS JOIN syntax: SELECT <field(s)> FROM <table1> CROSS JOIN <table2>;
• Implicit CROSS JOIN syntax: SELECT <field(s)> FROM <table1>, <table2>;
• Self-join syntax: SELECT <field(s> FROM <table1 AS alias1> INNER JOIN <table1 AS alias2> ON (<alias1.field> = <alias2.field>);
• Oracle deprecated left-outer-join syntax: SELECT <field(s)> FROM <table1>, <table2> WHERE <table1.field> = <table2.field> (+)
• Oracle deprecated right-outer-join syntax: SELECT <field(s)> FROM <table1>, <table2> WHERE <table1.field> (+) = <table2.field>
• Non-equi-join example: SELECT <field(s)> FROM <table1> JOIN <table2> ON (<table1.field> < <table2.field>);

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Joins

See Also

• Oracle: Database 2 Day Developers Guide - Selecting Table Data

References

Oracle SQL Fundamentals

Lesson 10 - Subqueries

This lesson introduces subqueries.

Objectives and Skills

Objectives and skills for the subqueries portion of Oracle SQL Fundamentals I certification include:

• Using Subqueries to Solve Queries
  • Define subqueries
  • Describe the types of problems that the subqueries can solve
  • List the types of subqueries
  • Write single-row and multiple-row subqueries

Readings

1. Read Wikipedia: Subquery.
2. Read Wikipedia: Correlated subquery.
3. Read Oracle: Using Subqueries.

Multimedia

1. YouTube: Oracle SQL Tutorials Subqueries in Oracle (Theory)
2. YouTube: Oracle SQL Tutorial Subqueries in Oracle [Hands On)

Activities

Schema Diagrams

1. Review HR and OE schema diagrams.
   1. Review the Oracle: HR and OE schema diagrams.

HR Subqueries

Single-Row Subqueries

1. Test single-row subqueries using HR data.
   1. List department name, employee last name, salary, and the average employee salary using the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME, E.SALARY,
    ROUND((SELECT AVG(SALARY) AS AVERAGE
    FROM EMPLOYEES)) AS AVERAGE_SALARY
FROM DEPARTMENTS D
JOIN EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
ORDER BY DEPARTMENT_NAME;
   2. List department name, employee last name, and salary difference from the average salary using the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME,
TO_CHAR(E.SALARY -
    (SELECT AVG(SALARY) AS AVERAGE
    FROM EMPLOYEES), '99,990') AS SALARY_DIFFERENCE
FROM DEPARTMENTS D
JOIN EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
ORDER BY DEPARTMENT_NAME;

Multiple-Row Subqueries

1. Test multiple-row subqueries using HR data.
   1. List department name, employee last name, salary, and the average employee salary. Include only employees and departments that include a commission using the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME, E.SALARY,
    ROUND((SELECT AVG(SALARY) AS AVERAGE
    FROM EMPLOYEES
    WHERE COMMISSION_PCT IS NOT NULL)) AS AVERAGE_SALARY
FROM DEPARTMENTS D
JOIN EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE D.DEPARTMENT_ID IN
    (SELECT DEPARTMENT_ID FROM EMPLOYEES
    WHERE COMMISSION_PCT IS NOT NULL)
ORDER BY DEPARTMENT_NAME;

Correlated Subqueries

1. Test correlated subqueries using HR data.
   1. List department name, employee last name, salary, and the average employee salary within each department using the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME, E.SALARY, S.AVERAGE AS AVERAGE
FROM DEPARTMENTS D
JOIN EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
JOIN (SELECT DEPARTMENT_ID, ROUND(AVG(SALARY)) AS AVERAGE
    FROM EMPLOYEES
    GROUP BY DEPARTMENT_ID) S ON E.DEPARTMENT_ID = S.DEPARTMENT_ID
ORDER BY DEPARTMENT_NAME;

OE Subqueries

1. Test single-row subqueries using OE data. Calculations may be made based on inventory quantity on hand, product pricing, and order detail information.
2. Test multiple-row subqueries using OE data. Calculations may be made based on inventory quantity on hand, product pricing, and order detail information.
3. Test correlated subqueries using OE data. Calculations may be made based on inventory quantity on hand, product pricing, and order detail information.

Lesson Summary

• Queries can be nested so that the results of one query can be used in another query via a relational operator or aggregation function.
• A subquery in the FROM clause of a SELECT statement is also called an inline view.
• A subquery in the WHERE clause of a SELECT statement is also called a nested subquery.
• A subquery that uses values from the outer query is known as a correlated subquery.
• A correlated subquery is evaluated once for each row processed by the parent query.
• Because a correlated subquery is evaluated once for each row processed by the outer query, it can be inefficient.
• WHERE subquery example: SELECT <field(s)> FROM <table1> WHERE <field> < (SELECT AVG(<field>) FROM <table2>);
• FROM subquery example: SELECT <field(s)> FROM <table1> AS <alias1> JOIN (SELECT field1, SUM(field2) FROM table2) AS <alias2> ON <alias1.field> = <alias2.field>;
• Correlated subquery example: SELECT <field(s)> FROM <table1> AS <alias1> WHERE <field> <= (SELECT AVG(<field>) FROM <table2> WHERE field = <alias1.field>);

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Subquery

References

Oracle SQL Fundamentals

Lesson 11 - Set Operators

This lesson introduces set operators.

Objectives and Skills

Objectives and skills for the set operators portion of Oracle SQL Fundamentals I certification include:

• Using the Set Operators
  • Describe set operators
  • Use a set operator to combine multiple queries into a single query
  • Control the order of rows returned

Readings

1. Read Wikipedia: Set operations (SQL).
2. Read Oracle: The UNION (ALL), INTERSECT, MINUS Operators.

Multimedia

1. YouTube: SQL Basics with Oracle - Traditional set operators: UNION, INTERSECT & MINUS

Activities

Schema Diagrams

1. Review HR and OE schema diagrams.
   1. Review the Oracle: HR and OE schema diagrams.

HR Set Operators

1. Test set operators using HR data.
   1. Combine two queries using UNION with the following:
      SELECT REGION_NAME FROM REGIONS
UNION
SELECT REGION_NAME FROM REGIONS;
   2. Combine two queries using UNION ALL with the following:
      SELECT REGION_NAME FROM REGIONS
UNION ALL
SELECT REGION_NAME FROM REGIONS;
   3. Combine two queries using INTERSECT with the following:
      SELECT REGION_NAME FROM REGIONS
INTERSECT
SELECT REGION_NAME FROM REGIONS;
   4. Combine two queries using MINUS with the following:
      SELECT REGION_NAME FROM REGIONS
MINUS
SELECT REGION_NAME FROM REGIONS;
   5. Combine multiple queries to create a report using the following:
      SELECT 'Regions' AS "Report Item", COUNT(*) AS "Value" FROM REGIONS
UNION ALL
SELECT 'Countries', COUNT(*) FROM COUNTRIES
UNION ALL
SELECT 'Locations', COUNT(*) FROM LOCATIONS
UNION ALL
SELECT 'Departments', COUNT(*) FROM DEPARTMENTS
UNION ALL
SELECT 'Employees', COUNT(*) FROM EMPLOYEES;
   6. Combine and sort the results from multiple queries using the following:
      SELECT 'Regions' AS "Report Item", COUNT(*) AS "Value" FROM REGIONS
UNION ALL
SELECT 'Countries', COUNT(*) FROM COUNTRIES
UNION ALL
SELECT 'Locations', COUNT(*) FROM LOCATIONS
UNION ALL
SELECT 'Departments', COUNT(*) FROM DEPARTMENTS
UNION ALL
SELECT 'Employees', COUNT(*) FROM EMPLOYEES
ORDER BY 2 DESC;

OE Set Operators

1. Test set operators using OE data.
   1. Combine queries using UNION.
   2. Combine queries using UNION ALL.
   3. Combine queries using INTERSECT.
   4. Combine queries using MINUS.
   5. Sort the results of combined queries.

Lesson Summary

• The UNION clause combines the results of two SQL queries into a single table of all matching rows. The two queries must result in the same number of columns and compatible data types in order to unite. Any duplicate records are automatically removed unless UNION ALL is used.
• The INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets.
• The MINUS operator takes the distinct rows of one query and returns the rows that do not appear in a second result set.
• Set operators do not guarantee the order of rows. In situations where a specific order is desired, ORDER BY must be used.
• UNION syntax: SELECT <field(s)> FROM <table1> UNION SELECT <field(s)> FROM <table2>;
• INTERSECT syntax: SELECT <field(s)> FROM <table1> INTERSECT SELECT <field(s)> FROM <table2>;
• MINUS syntax: SELECT <field(s)> FROM <table1> MINUS SELECT <field(s)> FROM <table2>;

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Set Operators

References

Oracle SQL Fundamentals

Lesson 12 - Manipulating Data

This lesson introduces manipulating data.

Objectives and Skills

Objectives and skills for the manipulatin data portion of Oracle SQL Fundamentals I certification include:

• Manipulating Data
  • Describe each data manipulation language (DML) statement
  • Insert rows into a table
  • Update rows in a table
  • Delete rows from a table

Readings

1. Read Wikipedia: Data manipulation language.
2. Read Wikipedia: Insert (SQL).
3. Read Wikipedia: Update (SQL).
4. Read Wikipedia: Delete (SQL).
5. Read Wikipedia: Truncate (SQL).
6. Read Wikipedia: Merge (SQL).

Multimedia

1. YouTube: PL SQL Tutorial DML and TCL statements (Theory)
2. YouTube: PL SQL Tutorial DML and TCL statements (Hands On)

Activities

Schema Diagrams

1. Review HR and OE schema diagrams.
   1. Review the Oracle: HR and OE schema diagrams.

HR DML Statements

1. Insert records using INSERT.
   1. Insert the region Antarctica using the following query:
      INSERT INTO REGIONS (REGION_ID, REGION_NAME) VALUES (5, 'ANTARCTICA');
   2. Select all records from regions to verify the results.
   3. Insert all countries as a new region using the following query:
      INSERT INTO REGIONS (REGION_ID, REGION_NAME)
(SELECT ROWNUM + 10, COUNTRY_NAME FROM COUNTRIES);
   4. Select all records from regions to verify the results.
2. Update records using UPDATE.
   1. Update the region Antarctica using the following query:
      UPDATE REGIONS SET REGION_NAME = 'Antarctica'
WHERE REGION_ID = 5;
   2. Select all records from regions to verify the results.
   3. Update all country regions to upper case using the following query:
      UPDATE REGIONS SET REGION_NAME = UPPER(REGION_NAME)
WHERE REGION_ID > 10;
   4. Select all records from regions to verify the results.
   5. Update employees to give each employee a 5% salary increase using the following query:
      UPDATE EMPLOYEES SET SALARY = SALARY * 1.05;
   6. Select all records from employees to verify the results.
3. Delete records using DELETE.
   1. Delete the region Antarctica using the following query:
      DELETE FROM REGIONS
WHERE REGION_ID = 5;
   2. Select all records from regions to verify the results.
   3. Delete the country regions using the following query:
      DELETE FROM REGIONS
WHERE REGION_ID > 10;
   4. Select all records from regions to verify the results.
   5. Delete all records from job history using the following query:
      DELETE FROM JOB_HISTORY
   6. Select all records from job history to verify the results.
   7. To restore job history, open the sample schema scripts folder you created in the Introduction. Open the schema/oe/hr/hr_popul.sql file and copy all statements that insert records into the JOB_HISTORY table. Run those statements to restore the data.
   8. Select all records from job history to verify the results.
4. Truncate records using TRUNCATE.
   1. Truncate all job history using the following query:
      TRUNCATE TABLE JOB_HISTORY;
   2. Select all records from job history to verify the results.
   3. To restore job history, open the sample schema scripts folder you created in the Introduction. Open the schema/oe/hr/hr_popul.sql file and copy all statements that insert records into the JOB_HISTORY table. Run those statements to restore the data.
   4. Select all records from job history to verify the results.

OE DML Statements

1. Insert records using INSERT.
2. Update records using UPDATE.
3. Delete records using DELETE.
4. Truncate records using TRUNCATE.

Lesson Summary

• A data manipulation language (DML) is a family of syntax elements used for selecting (SELECT), inserting (INSERT), updating (UPDATE) and deleting (DELETE) data in a database.
• An SQL INSERT statement adds one or more records to any single table in a relational database.
• An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.
• An SQL DELETE statement removes one or more records from a table. Either all records are removed, or a subset may be chosen using a condition.
• An SQL TRUNCATE TABLE statement is a Data Definition Language (DDL) operation that marks the extents of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms.
• An SQL MERGE statement inserts records or updates existing records based on the given condition.
• INSERT syntax: INSERT INTO <table1>(<column(s)>) VALUES(<value(s)>);
• UPDATE syntax: UPDATE <table1> SET <column> = <value>, ... [WHERE <condition>];
• DELETE syntax: DELETE FROM <table1> [WHERE <condition>];
• TRUNCATE TABLE syntax: TRUNCATE TABLE <table1>;
• MERGE syntax: MERGE INTO <table1> USING <table2> ON <condition> WHEN MATCHED THEN UPDATE SET <column> = <value> [, ...] WHEN NOT MATCHED THEN INSERT (<column(s)>) VALUES (<value(s)>);

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Insert/Update/Delete Statements

See Also

• Oracle: Database 2 Day Developers Guide - DML Statements and Transactions

References

Oracle SQL Fundamentals

Lesson 13 - Transactions

This lesson introduces transactions.

Objectives and Skills

Objectives and skills for the transactions portion of Oracle SQL Fundamentals I certification include:

• Manipulating Data
  • Control transactions

Readings

1. Read Wikipedia: Database transaction.
2. Read Wikipedia: Commit (data management).
3. Read Wikipedia: Rollback (data management).
4. Read Wikipedia: Savepoint.
5. Read Oracle: Transaction Management.

Multimedia

1. YouTube: PL SQL Tutorial DML and TCL statements (Theory)
2. YouTube: PL SQL Tutorial DML and TCL statements (Hands On)

Activities

Schema Diagrams

1. Review HR and OE schema diagrams.
   1. Review the Oracle: HR and OE schema diagrams.

HR TCL Statements

1. Control transactions using ROLLBACK.
   1. Insert the region Antarctica using the following query:
      INSERT INTO REGIONS (REGION_ID, REGION_NAME) VALUES (5, 'ANTARCTICA');
   2. Select all records from regions to verify the results.
   3. Roll back the current transaction using the following query:
      ROLLBACK;
   4. Select all records from regions to verify the results.
2. Control transactions using COMMIT.
   1. Insert the region Antarctica using the following query:
      INSERT INTO REGIONS (REGION_ID, REGION_NAME) VALUES (5, 'ANTARCTICA');
   2. Select all records from regions to verify the results.
   3. Commit the current transaction using the following query:
      COMMIT;
   4. Select all records from regions to verify the results.
3. Control transactions using SAVEPOINT.
   1. Update regions using the following queries:
      SAVEPOINT STEP_1;
UPDATE REGIONS SET REGION_NAME = 'Antarctica'
WHERE REGION_ID = 5;

SAVEPOINT STEP_2;
DELETE FROM REGIONS WHERE REGION_ID = 5;

ROLLBACK TO SAVEPOINT STEP_2;
COMMIT;
   2. Select all records from regions to verify the results.
4. Control transactions using FOR UPDATE.
   1. Select the region Antarctica for update using the following query:
      SELECT * FROM REGIONS WHERE REGION_ID = 5 FOR UPDATE;
   2. Start a second connection to the HR database using SQL Developer or SQL*PLUS.
   3. In the second session, attempt to update the region Antarctica using the following query:
      UPDATE REGIONS SET REGION_NAME = 'Antarctica' WHERE REGION_ID = 5;
   4. In the first session, delete the region Antarctica using the following query:
      DELETE FROM REGIONS WHERE REGION_ID = 5;
   5. In the first session, commit the transaction using the following query:
      COMMIT;
   6. Observe the results in the second session.

OE TCL Statements

1. Control transactions using ROLLBACK.
2. Control transactions using COMMIT.
3. Control transactions using SAVEPOINT.
4. Control transactions using FOR UPDATE.

Lesson Summary

• A transaction symbolizes a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions.
• Transactions in a database environment have two main purposes:
  • To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure.
  • To provide isolation between programs accessing a database concurrently.
• A database transaction, by definition, must be atomic, consistent, isolated and durable (ACID).
• In Oracle databases, a transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued.
• A COMMIT statement ends a transaction within a relational database management system (RDBMS) and makes all changes visible to other users.
• A ROLLBACK statement undoes all work performed since the transaction (or optional savepoint) began.
• A savepoint is a way of implementing subtransactions (also known as nested transactions) within a relational database management system by indicating a point within a transaction that can be "rolled back to" without affecting any work done in the transaction before the savepoint was created. Multiple savepoints can exist within a single transaction.
• The SELECT statement FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction.
• COMMIT syntax: COMMIT;
• ROLLBACK syntax: ROLLBACK [TO SAVEPOINT <name>];
• SAVEPOINT syntax: SAVEPOINT <name>;
• FOR UPDATE syntax: SELECT <column(s) FROM <table(s) [WHERE condition] FOR UPDATE;

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Transactions

See Also

• Oracle: Database 2 Day Developers Guide - DML Statements and Transactions

References

Oracle SQL Fundamentals

Lesson 14 - Data Definition Language

This lesson introduces data definition language.

Objectives and Skills

Objectives and skills for the data definition language portion of Oracle SQL Fundamentals I certification include:

• Using DDL Statements to Create and Manage Tables
  • Categorize the main database objects
  • Review the table structure
  • List the data types that are available for columns
  • Create a simple table
  • Explain how constraints are created at the time of table creation
  • Describe how schema objects work

Readings

1. Read Wikipedia: Data definition language.
2. Read Wikipedia: Check constraint.
3. Read Wikipedia: Unique key.
4. Read Oracle: Data Types.

Multimedia

1. YouTube: Oracle SQL Tutorial Creating a table (Theory)
2. YouTube: Oracle SQL Tutorial Creating a Table (Hands On CREATE TABLE command)
3. YouTube: Oracle SQL Tutorial Creating a Table (SQL Developer)
4. YouTube: Oracle SQL Tutorial Creating a table and its constraints
5. YouTube: Oracle SQL Tutorial Creating constraints after Table Creation
6. YouTube: Oracle SQL Tutorial Adding a column to a table
7. YouTube: Oracle SQL Tutorial Altering a Table using SQL commands (Theory)
8. YouTube: Oracle SQL Tutorial Altering a table using SQL commands (Hands On)

Activities

Schema Diagrams

1. Review HR and OE schema diagrams.
   1. Review the Oracle: HR and OE schema diagrams.

HR DDL Statements

1. Display database objects.
   1. Display all user object types in the schema using the following query:
      SELECT OBJECT_TYPE, COUNT(OBJECT_TYPE) AS "COUNT"
FROM USER_OBJECTS
GROUP BY OBJECT_TYPE;
   2. Display all object types in the schema using the following query:
      SELECT OBJECT_TYPE, COUNT(OBJECT_TYPE) AS "COUNT"
FROM ALL_OBJECTS
GROUP BY OBJECT_TYPE;
   3. Display user tables in the schema using the following query:
      SELECT TABLE_NAME FROM USER_TABLES;
   4. Display information for all user table columns using the the following query:
      SELECT * FROM USER_TAB_COLUMNS;
2. Display table structure and column data types.
   1. Display table structure and column data types using the following queries:
      DESCRIBE REGIONS;
DESCRIBE COUNTRIES;
DESCRIBE LOCATIONS;
DESCRIBE DEPARTMENTS;
DESCRIBE EMPLOYEES;
DESCRIBE JOBS;
DESCRIBE JOB_HISTORY;
   2. Display column types for user tables in the HR schema using the following query:
      SELECT DISTINCT DATA_TYPE FROM USER_TAB_COLUMNS;
   3. Display table names, column names, data types, length, precision, scale, and nullable for user tables in the HR schema using the following query:
      SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,
DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE
FROM USER_TAB_COLUMNS;
3. Create a simple table and add constraints with table creation.
   1. Create a table to track employee equipment. Include an ID, description, and employee ID using the following query:
      CREATE TABLE EQUIPMENT(
EQUIPMENT_ID NUMBER PRIMARY KEY,
DESCRIPTION VARCHAR2(50) NOT NULL,
EMPLOYEE_ID NUMBER(6) NOT NULL,
CONSTRAINT FK_EMPLOYEE_ID
FOREIGN KEY (EMPLOYEE_ID)
REFERENCES EMPLOYEES (EMPLOYEE_ID)
);
   2. Add fields to the equipment table to track purchase date and purchase price using the following query:
      ALTER TABLE EQUIPMENT
ADD(
PURCHASE_DATE DATE,
PURCHASE_PRICE NUMBER,
WEIGHT NUMBER
);
   3. Modify the purchase date field using the following queries:
      ALTER TABLE EQUIPMENT
MODIFY PURCHASE_PRICE NUMBER(7,2);
ALTER TABLE EQUIPMENT
ADD CONSTRAINT PRICE_CK CHECK (PURCHASE_PRICE > 0);
   4. Remove the weight column from the equipment table using the following query:
      ALTER TABLE EQUIPMENT
DROP COLUMN WEIGHT;
   5. Insert equipment data using the following query:
      INSERT INTO EQUIPMENT (EQUIPMENT_ID, DESCRIPTION,
EMPLOYEE_ID, PURCHASE_DATE, PURCHASE_PRICE)
VALUES(1, 'Laptop', 100, '1-JUL-87', 2413.89);
   6. Remove the Equipment table using the following query:
      DROP TABLE EQUIPMENT;

OE DDL Statements

1. Display database objects
2. Display table structure and column data types.
3. Create a simple table and add constraints with table creation

Lesson Summary

• A data definition language (DDL) is a syntax for defining data structures, especially database schemas.
• Oracle data types include CHAR, NCHAR, VARCHAR2, NVARCHAR2, NUMBER, FLOAT, DATE, TIMESTAMP, RAW, and block data types.
• DDL statements include CREATE, ALTER, DROP, and RENAME.
• A check constraint is a type of integrity constraint in SQL which specifies a requirement that must be met by each row in a database table.
• CREATE TABLE syntax: CREATE TABLE <table1> (<column> <type> [PRIMARY KEY] [not null] [, ...]);
• CREATE TABLE syntax: CREATE TABLE <table1> AS SELECT <column(s)> FROM <table(s) [WHERE <condition>];
• ALTER TABLE syntax: ALTER TABLE <table1> ADD | MODIFY | DROP <column> <type>;
• DROP TABLE syntax: DROP TABLE <table1>;
• RENAME TABLE syntax: RENAME TABLE <table1> TO <table2>;
• CONSTRAINT syntax: CREATE TABLE ... CONSTRAINT <name> CHECK (<predicate>) ...;
• CONSTRAINT syntax: CREATE TABLE ... CONSTRAINT <name> PRIMARY KEY ...;
• CONSTRAINT syntax: CREATE TABLE ... CONSTRAINT <name> FOREIGN KEY (<column>) REFERENCES <table(column)> ...;
• CONSTRAINT syntax: ALTER TABLE <table1> ADD CONSTRAINT <name> CHECK (<predicate>) ...;
• CONSTRAINT syntax: ALTER TABLE <table1> ADD CONSTRAINT <name> PRIMARY KEY (<column(s)>);
• CONSTRAINT syntax: ALTER TABLE <table1> ADD CONSTRAINT <name> FOREIGN KEY (<column>) REFERENCES <table(column)> ...;

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Table Objects
• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Privileges
• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Cluster Objects

See Also

• Oracle: Database 2 Day Developers Guide - Creating and Managing Schema Objects

References

Oracle SQL Fundamentals

Lesson 15 - Other Schema Objects

This lesson introduces other schema objects.

Objectives and Skills

Objectives and skills for the other schema objects portion of Oracle SQL Fundamentals I certification include:

• Creating Other Schema Objects
  • Create simple and complex views
  • Retrieve data from views
  • Create, maintain, and use sequences
  • Create and maintain indexes
  • Create private and public synonyms

Readings

1. Read Wikipedia: View (SQL).
2. Read Wikipedia: Database index.

Multimedia

1. YouTube: Oracle SQL Tutorial - Creating a view
2. YouTube: Oracle SQL Tutorial Creating a sequence
3. YouTube: Oracle SQL Tutorial - Creating an Index
4. YouTube: Oracle SQL Tutorial Creating a synonym

Activities

Schema Diagrams

1. Review HR and OE schema diagrams.
   1. Review the Oracle: HR and OE schema diagrams.

HR Schema Objects

Views

1. Create simple and complex views and retrieve data from views.
   1. Create a simple view of sales employees by job description using the following query:
      CREATE VIEW SALES_EMPLOYEES AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER
FROM EMPLOYEES
WHERE JOB_ID LIKE 'SA_%';
   2. Select all records from sales employees to test the view.
   3. Create a simple view of sales employees by department using the following query:
      CREATE VIEW SALES_DEPARTMENTS AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE LOWER(DEPARTMENT_NAME) LIKE '%sales%';
   4. Select all records from sales departments to test the view.
   5. Use the following query to identify employees with a sales job description who aren't in a sales department:
      SELECT E.EMPLOYEE_ID AS SALES_EMPLOYEES_ID, D.EMPLOYEE_ID AS
SALES_DEPARTMENTS_ID
FROM SALES_EMPLOYEES E
FULL OUTER JOIN SALES_DEPARTMENTS D
ON E.EMPLOYEE_ID = D.EMPLOYEE_ID
WHERE E.EMPLOYEE_ID IS NULL OR D.EMPLOYEE_ID IS NULL;

Sequences

1. Create, maintain, and use sequences.
   1. Create a sequence for region IDs using the following query:
      CREATE SEQUENCE REGION_ID START WITH 11;
   2. Test the region sequence using the following query:
      INSERT INTO REGIONS (REGION_ID, REGION_NAME)
(SELECT REGION_ID.NEXTVAL, COUNTRY_NAME
FROM COUNTRIES);
   3. Select all records from regions to verify the results.
   4. Delete all regions with an ID greater than 10 using the following query:
      DELETE FROM REGIONS WHERE REGION_ID > 10;
   5. Run the insert query again and test the results.
   6. Delete all regions with an ID greater than 10.
   7. Remove the sequence using the following query:
      DROP SEQUENCE REGION_ID;

Indexes

1. Create and maintain indexes.
   1. Create an equipment table using the following query:
      CREATE TABLE EQUIPMENT(
EQUIPMENT_ID NUMBER NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL,
EMPLOYEE_ID NUMBER(6),
PURCHASE_DATE DATE,
PURCHASE_PRICE NUMBER(7,2)
);
   2. Add unique indexes to the equipment table using the following queries:
      CREATE UNIQUE INDEX EQUIPMENT_PK ON EQUIPMENT(EQUIPMENT_ID);
CREATE UNIQUE INDEX EQUIPMENT_IX ON EQUIPMENT(DESCRIPTION);
   3. Add constraints to the equipment table using the following queries:
      ALTER TABLE EQUIPMENT ADD CONSTRAINT EQUIPMENT_PK PRIMARY KEY (EQUIPMENT_ID);
ALTER TABLE EQUIPMENT ADD CONSTRAINT EQUIPMENT_FK
FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID);

Synonyms

1. Create private and public synonyms.
   1. Create a private synonym for the equipment table using the following query:
      CREATE SYNONYM EQUIP FOR EQUIPMENT;
   2. Describe the EQUIP table using the following query:
      DESCRIBE EQUIP;
   3. Remove the private synonym using the following query:
      DROP SYNONYM EQUIP;

OE Schema Objects

1. Create simple and complex views and retrieve data from views.
2. Create, maintain, and use sequences.
3. Create and maintain indexes.
4. Create private and public synonyms.

Lesson Summary

• A view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object.
• Views can provide advantages over tables:
  • Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the underlying tables.
  • Views can join and simplify multiple tables into a single virtual table.
  • Views can act as aggregated tables, where the database engine aggregates data and presents the calculated results as part of the data.
  • Views can hide the complexity of data.
  • Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.
• A sequence is a database object from which multiple users may generate unique integers.
• A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.
• A synonym is an alternative name for a database object.
• CREATE VIEW syntax: CREATE VIEW <name> AS <SELECT ...>;
• CREATE SEQUENCE syntax: CREATE SEQUENCE <name> START WITH <value>;
• CREATE INDEX syntax: CREATE UNIQUE INDEX <name> ON <table(column(s)>);
• CREATE SYNONYM syntax: CREATE SYNONYM <name1> FOR <name2>;

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - View Objects
• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Synonym Objects
• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Sequence Objects
• Flashcards: Quizlet: Oracle 1Z0-051 Exam - PL/SQL

See Also

• Oracle: Database 2 Day Developers Guide - Creating and Managing Schema Objects

References

Oracle SQL Fundamentals