Oracle SQL Fundamentals/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

← Introduction

Oracle SQL Fundamentals

Restricting Data →