Selecting Data in Oracle SQL

Key Concepts

Selecting data in Oracle SQL involves retrieving specific information from a database table. The SELECT statement is used to query data, and it can be combined with various clauses to filter, sort, and aggregate the results. Understanding the following key concepts is essential for effective data retrieval:

1. SELECT Statement

The SELECT statement is the fundamental command used to retrieve data from a database. It specifies the columns to be retrieved and the table from which to retrieve them. The basic syntax is:


SELECT column1, column2, ...
FROM table_name;

2. WHERE Clause

The WHERE clause is used to filter records based on specified conditions. It ensures that only the rows meeting the criteria are included in the result set. The syntax is:


SELECT column1, column2, ...
FROM table_name
WHERE condition;

3. ORDER BY Clause

The ORDER BY clause is used to sort the result set by one or more columns. By default, it sorts in ascending order, but you can specify descending order using the DESC keyword. The syntax is:


SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

4. DISTINCT Keyword

The DISTINCT keyword is used to retrieve unique values from a column. It eliminates duplicate rows from the result set. The syntax is:


SELECT DISTINCT column1, column2, ...
FROM table_name;

Detailed Explanation

1. SELECT Statement

The SELECT statement is the starting point for any query. It specifies the columns you want to retrieve from a table. For example, to retrieve all columns from an "Employees" table, you would use:


SELECT * FROM Employees;

To retrieve specific columns, such as "FirstName" and "LastName," you would use:


SELECT FirstName, LastName FROM Employees;

2. WHERE Clause

The WHERE clause allows you to filter data based on specific conditions. For example, to retrieve employees who work in the "Sales" department, you would use:


SELECT FirstName, LastName
FROM Employees
WHERE Department = 'Sales';

You can also use logical operators like AND and OR to combine multiple conditions. For example, to retrieve employees in the "Sales" department who earn more than $50,000, you would use:


SELECT FirstName, LastName
FROM Employees
WHERE Department = 'Sales' AND Salary > 50000;

3. ORDER BY Clause

The ORDER BY clause is used to sort the result set. For example, to retrieve employees sorted by their last name in ascending order, you would use:


SELECT FirstName, LastName
FROM Employees
ORDER BY LastName;

To sort by last name in descending order, you would use:


SELECT FirstName, LastName
FROM Employees
ORDER BY LastName DESC;

You can also sort by multiple columns. For example, to sort by department and then by last name within each department, you would use:


SELECT FirstName, LastName, Department
FROM Employees
ORDER BY Department, LastName;

4. DISTINCT Keyword

The DISTINCT keyword is used to retrieve unique values. For example, to retrieve a list of unique departments from the "Employees" table, you would use:


SELECT DISTINCT Department
FROM Employees;

This query will return a list of departments without any duplicates.

Examples

Example 1: Retrieve all employees in the "Sales" department sorted by their last name:


SELECT FirstName, LastName
FROM Employees
WHERE Department = 'Sales'
ORDER BY LastName;

Example 2: Retrieve a list of unique job titles from the "Employees" table:


SELECT DISTINCT JobTitle
FROM Employees;

Example 3: Retrieve employees who earn more than $60,000 and work in the "Marketing" department:


SELECT FirstName, LastName
FROM Employees
WHERE Salary > 60000 AND Department = 'Marketing';