Mastering Data Retrieval: The SELECT Statement

The SELECT statement is the cornerstone of SQL, used for retrieving data from one or more tables in an Oracle database. It allows users to query and display data based on various criteria. Understanding the SELECT statement is fundamental for anyone working with Oracle 19c SQL.

Basic SELECT Queries

Basic SELECT queries are the simplest form of data retrieval, allowing you to fetch all data from a table or specific columns.

Selecting All Columns (SELECT * FROM table_name;)

This fundamental Oracle SQL query retrieves every column and all rows from a specified table. It's often used for quick data exploration or when you need to view the entire dataset of a table. While convenient, it's generally not recommended for production applications due to potential performance impacts, especially on wide tables with many columns.

Example 1: Oracle 19c SQL SELECT all columns from employees table

-- This SQL query retrieves all columns and all rows from the 'employees' table.
-- It's useful for a quick overview of the entire dataset within the table.
SELECT *
FROM employees;

Explanation The SELECT * clause indicates that all columns from the specified table should be returned. The FROM employees clause specifies that the data should be retrieved from the employees table. This query is a common starting point for data exploration in Oracle 19c.

Example 2: Oracle 19c SQL SELECT all columns from departments table

-- Retrieve all information (all columns) for all departments.
-- This query is ideal for understanding the structure and content of the 'departments' table.
SELECT *
FROM departments;

Explanation Similar to the previous example, this query fetches every column and every row from the departments table. It's a quick way to inspect all the data stored within the departments table in your Oracle 19c database.

Example 3: Oracle 19c SQL SELECT all columns from products table

-- Get all details for all products listed in the 'products' table.
-- Useful for inventory checks or a complete product catalog view.
SELECT *
FROM products;

Explanation This query is designed to retrieve all data points for every product stored in the products table. The SELECT * is a wildcard that tells the Oracle database to fetch all available columns, providing a complete record for each product.

Example 4: Oracle 19c SQL SELECT all columns from customer_orders table

-- Display all data fields for all customer orders.
-- This helps in comprehensive analysis of order details.
SELECT *
FROM customer_orders;

Explanation This Oracle 19c SQL statement retrieves every piece of information recorded for each customer order. By using SELECT *, we ensure that all columns, such as order ID, customer ID, order date, and total amount, are displayed.

Example 5: Oracle 19c SQL SELECT all columns from job_history table

-- Access all historical job information for all employees.
-- Useful for auditing or tracking employee career progression.
SELECT *
FROM job_history;

Explanation This query serves to pull all data from the job_history table, including details like employee ID, start date, end date, job ID, and department ID for every recorded job change. It provides a full historical record in Oracle 19c.

Selecting Specific Columns (SELECT col1, col2 FROM table_name;)

When you only need a subset of the columns from a table, specifying them explicitly using SELECT col1, col2, ... is highly efficient. This reduces the amount of data transferred and processed, leading to better performance, especially with large datasets. It also improves readability by clearly indicating what data you intend to retrieve. This is a best practice for Oracle 19c SQL performance tuning.

Example 1: Oracle 19c SQL SELECT specific columns from employees table

-- Retrieve only the first name, last name, and email address of employees.
-- This is a common requirement for contact lists or user directories.
SELECT first_name, last_name, email
FROM employees;

Explanation This SELECT statement specifically requests the first_name, last_name, and email columns from the employees table. Instead of retrieving all data, it targets only the necessary information, which is more efficient for Oracle 19c database operations.

Example 2: Oracle 19c SQL SELECT department name and location ID

-- Get the name and location identifier for each department.
-- Useful for understanding department geographical distribution.
SELECT department_name, location_id
FROM departments;

Explanation This query fetches two specific columns: department_name and location_id from the departments table. This approach focuses the data retrieval to only what is relevant, optimizing query execution in Oracle 19c.

Example 3: Oracle 19c SQL SELECT product name and price

-- Display the name and list price for all products.
-- Essential for pricing catalogs or product reviews.
SELECT product_name, list_price
FROM products;

Explanation This statement precisely selects the product_name and list_price columns from the products table. This targeted retrieval is more efficient than SELECT * when you only need these particular data points.

Example 4: Oracle 19c SQL SELECT order ID and order date

-- Retrieve only the order identifier and the date an order was placed.
-- Useful for quick summaries of order volume over time.
SELECT order_id, order_date
FROM customer_orders;

Explanation This query retrieves only the order_id and order_date columns from the customer_orders table. This is a precise way to get just the essential information about customer transactions in Oracle 19c.

Example 5: Oracle 19c SQL SELECT employee ID and job ID from job history

-- Fetch the employee ID and the job ID from their job history.
-- Useful for tracking specific job assignments for employees.
SELECT employee_id, job_id
FROM job_history;

Explanation This query specifically selects the employee_id and job_id columns from the job_history table. This focused retrieval is common when analyzing employee career paths within an Oracle 19c database.

Column Aliases (AS keyword, double quotes for special characters)

Column aliases allow you to assign a temporary, more descriptive, or user-friendly name to a column in your query's result set. This does not change the actual column name in the database table. Aliases are particularly useful for making query output more readable, especially when dealing with complex expressions, function calls, or when column names are not self-explanatory. The AS keyword is optional but improves readability. Double quotes are necessary if the alias contains spaces, special characters, or is case-sensitive. This is a common practice in Oracle 19c for improving data presentation.

Example 1: Oracle 19c SQL Column alias with AS keyword for clarity

-- Assigning aliases to columns for better readability in the result set.
-- 'first_name' becomes 'Given Name', 'last_name' becomes 'Family Name'.
SELECT first_name AS "Given Name",
       last_name AS "Family Name",
       email AS "Contact Email"
FROM employees;

Explanation In this Oracle 19c SQL query, first_name AS "Given Name" assigns the alias "Given Name" to the first_name column in the output. Similarly, last_name gets "Family Name" and email gets "Contact Email". The AS keyword makes the alias assignment explicit, and double quotes are used because the aliases contain spaces. This enhances the readability of the query results for end-users.

Example 2: Oracle 19c SQL Column alias without AS keyword

-- Aliasing columns without the optional 'AS' keyword.
-- 'department_name' becomes 'Department', 'location_id' becomes 'Location'.
SELECT department_name "Department",
       location_id "Location"
FROM departments;

Explanation This query demonstrates column aliasing without the AS keyword. department_name "Department" effectively renames the department_name column to "Department" in the result set. This is a more concise way to alias columns in Oracle 19c when the alias does not contain spaces or special characters, or when you explicitly need it to be case-sensitive.

Example 3: Oracle 19c SQL Column alias with special characters and spaces

-- Using aliases with spaces and special characters, requiring double quotes.
-- 'product_name' becomes 'Product Item', 'list_price' becomes 'Retail Price ($)'.
SELECT product_name AS "Product Item",
       list_price AS "Retail Price ($)"
FROM products;

Explanation Here, aliases like "Product Item" and "Retail Price ($)" contain spaces and a special character ($). Therefore, double quotes are mandatory around these aliases in Oracle 19c to ensure they are interpreted correctly as a single alias name. This makes the output more user-friendly for business reports.

Example 4: Oracle 19c SQL Aliasing for calculated columns

-- Aliasing a calculated column (total order value).
-- (quantity * unit_price) is aliased as 'Total Order Value'.
SELECT order_id,
       (quantity * unit_price) AS "Total Order Value"
FROM order_items;

Explanation This example showcases aliasing for a calculated column. The expression (quantity * unit_price) calculates the total value of an order item, and AS "Total Order Value" assigns a descriptive alias to this computed result. This is crucial for making complex calculations understandable in Oracle 19c query outputs.

Example 5: Oracle 19c SQL Aliasing to resolve ambiguity in joins (conceptual)

-- Although not a join example, conceptually showing how aliases help distinguish columns
-- if we had a table 'customers' with 'name' and 'employees' with 'name'.
-- In a real join, you'd specify table aliases: c.name AS "Customer Name", e.name AS "Employee Name".
SELECT employee_id AS "Employee ID",
       hire_date AS "Date Hired"
FROM employees;

Explanation While this specific example doesn't demonstrate a join, it illustrates the principle. If two tables in a join both had a column named name, aliasing employees.name AS "Employee Name" and departments.name AS "Department Name" would clearly differentiate them in the result set. In this basic SELECT example, it simply renames employee_id to "Employee ID" and hire_date to "Date Hired" for clarity in the Oracle 19c output.