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  Select (SQL).
 * 2) Read  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.
 * 2) Run SQL Developer.
 * 3) 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.
 * 4) Double-click on the HR connection to connect.
 * 5) In the connection worksheet, enter   and run the command. Observe the script output.
 * 6) Under the HR connection, expand Tables.
 * 7) Select COUNTRIES. Observe the columns and properties.
 * 8) Use the DESCRIBE statement or SQL Developer to view other tables in the HR schema.
 * 9) Select data from the HR schema.
 * 10) Run the query   to see all data in the COUNTRIES table.
 * 11) Run the query   to see all country abbreviations in the COUNTRIES table.
 * 12) Run the query   to see all countries in the COUNTRIES table.
 * 13) Run the query   to see all country abbreviations and countries in the COUNTRIES table.
 * 14) Run the query   to see all regions in the COUNTRIES table.
 * 15) Run the query   to see unique regions in the COUNTRIES table.
 * 16) Calculate results from the HR schema.
 * 17) Run the query   to see all data in the JOBS table.
 * 18) Run the query   to see jobs and salary ranges.
 * 19) Run the query   to see jobs and salary ranges with column aliases.
 * 20) Run the query   to see jobs and salary ranges concatenated as sentences.
 * 21) Run the query   to calculate the number of seconds in a day.
 * 22) Describe tables in the OE schema.
 * 23) Run SQL Developer.
 * 24) 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.
 * 25) Double-click on the OE connection to connect.
 * 26) Use the DESCRIBE statement or SQL Developer to view table structure for different tables in the OE schema.
 * 27) Select data from the OE schema.
 * 28) Select all data from the CATEGORIES table.
 * 29) Select only category names from the CATEGORIES table.
 * 30) Select category descriptions from the CATEGORIES table.
 * 31) Select category names and descriptions from the CATEGORIES table.
 * 32) Select all countries from the CUSTOMERS table.
 * 33) Select a unique list of countries from the CUSTOMERS table.
 * 34) Calculate results from the OE schema.
 * 35) Select all data from the ORDER_ITEMS table.
 * 36) Select the product ID and discount (unit price - discount price) for each item.
 * 37) Select the product ID and discount for each item with appropriate column aliases.
 * 38) Select the product ID and discount for each item as a sentence, such as "The discount for product 3150 is $2."
 * 39) 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] &lt;column&gt; [AS &lt;alias&gt;, ...] FROM &lt;table&gt; [AS &lt;alias&gt;, ...].
 * 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