Oracle SQL Fundamentals/Selecting Data
This lesson introduces selecting data using the SELECT statement.
Objectives and Skills
editObjectives and skills for the selecting data portion of Oracle SQL Fundamentals I certification include:[1]
- Retrieving Data Using the SQL SELECT Statement
- List the capabilities of SQL SELECT statements
- Execute a basic SELECT statement
Readings
edit- Read Wikipedia: Select (SQL).
- Read Wikipedia: DUAL table.
- Read Oracle: DESCRIBE.
- Read Oracle: SELECT.
Multimedia
edit- 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
edit- 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.
- Run the query
- 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.
- Run the query
- 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
edit- The SQL SELECT statement returns a result set of records from one or more tables.[2]
- The basic SELECT statement syntax is SELECT [DISTINCT] <column> [AS <alias>, ...] FROM <table> [AS <alias>, ...].[3][4]
- An asterisk ("*") can be used to specify that the query should return all columns of the queried tables.[5]
- SQL statements are terminated with a semicolon (";").[6]
- The optional DISTINCT keyword removes duplicate rows from the result set.[7]
- The DESCRIBE command lists column information for a given table or view.[8]
- SQL data types include character strings, bit strings, numbers, and temporal (date/time) types.[9]
- String and date/time literals are enclosed in single quotes (').[10]
- String concatenation is accomplished using the concatenation operator ||.[11]
- Aliases may be enclosed in double quotes (") for case sensitivity or to include multiple words.[12]
- Oracle SQL statements are not case sensitive, but string literals are case sensitive.[13]
- The DUAL table is a special one-row, one-column table present by default in Oracle and other database installations.[14]
Key Terms
edit- concatenation
- The operation of joining character strings end-to-end.[15]
- join
- A relational algebra term which refers to a combination of result sets based on equal values for common (shared) attribute names.[16]
- null
- A special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database.[17]
- projection
- A relational algebra term which refers to restricting a result set to a subset of the available attributes or columns.[18]
- selection
- A relational algebra term which refers to restricting a result set to a subset of the available records or rows.[19]
Assessments
edit- Flashcards: Quizlet: Oracle 1Z0-051 Exam - Select Statement
See Also
editReferences
edit- ↑ Oracle: Database 11g: SQL Fundamentals I Exam Topics
- ↑ Wikipedia: Select (SQL)
- ↑ Wikipedia: SQL
- ↑ Wikipedia: Select (SQL)
- ↑ Wikipedia: SQL
- ↑ Wikipedia: SQL
- ↑ Wikipedia: Select (SQL)
- ↑ Wikipedia: Information schema
- ↑ Wikipedia: SQL
- ↑ Wikipedia: SQL
- ↑ Oracle: Concatenation Operator
- ↑ Ramklass and Watson. OCA Oracle Database 11g SQL Fundamentals I Exam Guide: Exam 1Z0-051. Oracle Press. ISBN 9780071597869
- ↑ Ramklass and Watson. OCA Oracle Database 11g SQL Fundamentals I Exam Guide: Exam 1Z0-051. Oracle Press. ISBN 9780071597869
- ↑ Wikipedia: DUAL table
- ↑ Wikipedia: Concatenation
- ↑ Wikipedia: Relational algebra
- ↑ Wikipedia: Null (SQL)
- ↑ Wikipedia: Projection (relational algebra)
- ↑ Wikipedia: Selection (relational algebra)