How to Use SELECT Queries in Oracle Database 23ai

Working with a database is all about getting useful information out of it. Oracle Database 23ai gives you a powerful way to do this with the SELECT statement. This tutorial explains in detail how to write SELECT queries, step by step, in a way even a beginner can understand. You will also see how Oracle 23ai brings new features like JSON querying and vector search into SELECT statements, making data retrieval more intelligent.

Preparing Sample Data

Before we start writing queries, let us create some tables and insert data so that the examples in this tutorial return real results.

-- Create employees table
CREATE TABLE employees (
  employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  hire_date DATE,
  salary NUMBER(10,2),
  department_id NUMBER,
  commission_pct NUMBER
);

-- Insert sample employees
INSERT INTO employees VALUES (1, 'John', 'Smith', DATE '2020-01-15', 8000, 10, NULL);
INSERT INTO employees VALUES (2, 'Alice', 'Johnson', DATE '2019-03-10', 12000, 20, 0.10);
INSERT INTO employees VALUES (3, 'Michael', 'Brown', DATE '2021-06-20', 4500, 10, NULL);
INSERT INTO employees VALUES (4, 'Sophia', 'Davis', DATE '2022-11-05', 6000, 30, 0.05);
INSERT INTO employees VALUES (5, 'David', 'Wilson', DATE '2021-02-18', 9500, 20, NULL);

-- Create departments table
CREATE TABLE departments (
  department_id NUMBER PRIMARY KEY,
  department_name VARCHAR2(100)
);

-- Insert sample departments
INSERT INTO departments VALUES (10, 'Sales');
INSERT INTO departments VALUES (20, 'HR');
INSERT INTO departments VALUES (30, 'IT');

COMMIT;

We now have an employees table with five rows and a departments table with three rows. These will be used throughout the tutorial.

Selecting All Columns

If you want to see everything from a table, use the asterisk symbol *. This returns all columns in the table.

SELECT * 
FROM employees;

Result:

Selecting Specific Columns

To view only chosen details, list the column names after SELECT.

SELECT first_name, last_name, hire_date 
FROM employees;

Result:

Using WHERE to Filter Data

The WHERE clause narrows down rows to those that meet a condition.

SELECT first_name, last_name, salary 
FROM employees
WHERE salary > 5000;

Result:

Sorting Results with ORDER BY

The ORDER BY clause organizes results. You can sort in ascending (ASC, default) or descending (DESC) order.

SELECT first_name, last_name, salary 
FROM employees
ORDER BY salary DESC;

Result:

Using Aliases for Readability

Aliases rename columns or tables in the result for clarity.

SELECT first_name AS fname, last_name AS lname, salary AS monthly_pay
FROM employees;

Result:

Combining Conditions with AND and OR

Conditions can be combined to make queries more precise.

SELECT first_name, department_id, salary
FROM employees
WHERE department_id = 10 AND salary > 6000;

Result:

Aggregating Data with GROUP BY

The GROUP BY clause groups rows that have the same values and allows use of aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Result:

Filtering Groups with HAVING

When using GROUP BY, the HAVING clause filters results after aggregation.

SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1;

Result:

Joining Multiple Tables

SELECT queries often need data from more than one table. Joins combine rows from two or more tables using related columns.

SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

Result:

Subqueries in SELECT

A subquery is a query inside another query. It can provide data for filtering.

SELECT first_name, last_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Result:

SELECT with DISTINCT

DISTINCT removes duplicates from results.

SELECT DISTINCT department_id
FROM employees;

Result:

SELECT and NULL Handling

NULL represents missing or unknown values. To check for NULL, use IS NULL or IS NOT NULL.

SELECT first_name, commission_pct
FROM employees
WHERE commission_pct IS NULL;

Result:

Limiting Results with FETCH

Oracle allows limiting rows with FETCH FIRST syntax.

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;

Result:

Using SELECT for JSON Data in Oracle 23ai

Oracle 23ai enhances working with JSON directly inside tables. Suppose you store product details in a JSON column:

CREATE TABLE products (
  product_id NUMBER PRIMARY KEY,
  product_data JSON
);

INSERT INTO products VALUES (
  1,
  '{"name":"Laptop","specs":{"cpu":"Intel i7","ram":"16GB"}}'
);

INSERT INTO products VALUES (
  2,
  '{"name":"Phone","specs":{"cpu":"Snapdragon","ram":"8GB"}}'
);

You can query JSON with simple operators:

SELECT
  JSON_VALUE(product_data, '$.name')         AS product_name,
  JSON_VALUE(product_data, '$.specs.cpu')    AS cpu_type
FROM products;

Result:

SELECT with Vector Search in Oracle 23ai

Oracle 23ai introduces vector data types for AI-powered search. This is useful when finding similar items based on embeddings.

-- Create table with VECTOR column
CREATE TABLE documents (
  doc_id     NUMBER PRIMARY KEY,
  content    CLOB,
  embedding  VECTOR(3)
);

-- Insert multiple rows using MULTIVALUE INSERT (Oracle 23ai feature)
INSERT INTO documents (doc_id, content, embedding)
VALUES 
  (1, 'Oracle Database 23ai introduces AI features', TO_VECTOR('[0.1, 0.8, 0.5]')),
  (2, 'Learn how to run SQL queries effectively', TO_VECTOR('[0.2, 0.7, 0.6]'));

COMMIT;

Now you can run a similarity search:

SELECT doc_id, content,
       VECTOR_DISTANCE(embedding, TO_VECTOR('[0.1, 0.75, 0.55]')) AS similarity
FROM documents
ORDER BY similarity ASC
FETCH FIRST 2 ROWS ONLY;

Result:

SELECT with Analytical Functions

Analytical functions let you perform calculations across sets of rows while still showing detailed data.

SELECT first_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank_position
FROM employees;

Result:

SELECT with CASE Expressions

CASE provides conditional logic inside SELECT.

SELECT first_name, salary,
       CASE 
         WHEN salary > 10000 THEN 'High'
         WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium'
         ELSE 'Low'
       END AS salary_category
FROM employees;

Result:

Conclusion

The SELECT statement in Oracle Database 23ai remains the most important tool for extracting meaningful information. From simple column retrieval to advanced AI-driven queries with JSON and vector data, SELECT adapts to both traditional business needs and modern AI-powered applications. With these skills, you can analyze data faster, design smarter queries, and take full advantage of the innovations in Oracle 23ai.

Note: All the SQL examples provided in this tutorial were tested and verified on Oracle Database 23ai using Oracle SQL Developer version 24.

See also:

1. How to Install Oracle Database 23ai on Windows
2. Installing Oracle SQL Developer on Windows