# 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

* Read Wikipedia: Select (SQL).
* Read Wikipedia: DUAL table.
* Read Oracle: DESCRIBE.
* Read Oracle: SELECT.

## Multimedia

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

## Activities

* Describe tables in the HR schema.
** Run SQL Developer.
** 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.
** Double-click on the HR connection to connect.
** In the connection worksheet, enter DESCRIBE COUNTRIES; and run the command. Observe the script output.
** Under the HR connection, expand Tables.
** Select COUNTRIES. Observe the columns and properties.
** Use the DESCRIBE statement or SQL Developer to view other tables in the HR schema.
* Select data from the HR schema.
** Run the query SELECT * FROM COUNTRIES; to see all data in the COUNTRIES table.
** Run the query SELECT COUNTRY_ID FROM COUNTRIES; to see all country abbreviations in the COUNTRIES table.
** Run the query SELECT COUNTRY_NAME FROM COUNTRIES; to see all countries in the COUNTRIES table.
** Run the query SELECT COUNTRY_ID, COUNTRY_NAME FROM COUNTRIES; to see all country abbreviations and countries in the COUNTRIES table.
** Run the query SELECT REGION_ID FROM COUNTRIES; to see all regions in the COUNTRIES table.
** Run the query SELECT DISTINCT REGION_ID FROM COUNTRIES; to see unique regions in the COUNTRIES table.
* Calculate results from the HR schema.
** Run the query SELECT * FROM JOBS; to see all data in the JOBS table.
** Run the query SELECT JOB_TITLE, MAX_SALARY - MIN_SALARY FROM JOBS; to see jobs and salary ranges.
** 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.
** 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.
** Run the query SELECT 60 * 60 * 24 AS SECONDS_IN_A_DAY FROM DUAL; to calculate the number of seconds in a day.
* Describe tables in the OE schema.
** Run SQL Developer.
** 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.
** Double-click on the OE connection to connect.
** Use the DESCRIBE statement or SQL Developer to view table structure for different tables in the OE schema.
* Select data from the OE schema.
** Select all data from the CATEGORIES table.
** Select only category names from the CATEGORIES table.
** Select category descriptions from the CATEGORIES table.
** Select category names and descriptions from the CATEGORIES table.
** Select all countries from the CUSTOMERS table.
** Select a unique list of countries from the CUSTOMERS table.
* Calculate results from the OE schema.
** Select all data from the ORDER_ITEMS table.
** Select the product ID and discount (unit price - discount price) for each item.
** Select the product ID and discount for each item with appropriate column aliases.
** Select the product ID and discount for each item as a sentence, such as "The discount for product 3150 is $2."
** 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