Sample Tables and JOIN Examples for Hands-on Oracle Practice

When learning to operate an Oracle database, creating sample tables and manipulating data is extremely effective. This article introduces SQL statements to create sample tables usable in Oracle and also explains simple SELECT statements and practical JOIN examples using them. Furthermore, we provide deeper learning by adding advanced join examples useful in real-world scenarios.

Creating Sample Tables
Simple SELECT Statement Examples
JOIN Examples Using the Sample Tables
1. 1. Joining Employee and Department Information
2. 2. Joining Order Information and Product Information
Advanced JOIN Example
1. Sales Aggregation by Product Category
Summary
1. Key Points for Practice

Creating Sample Tables

Use the following SQL statements to create basic sample tables. These are suitable for practicing various data manipulations.

1. “EMPLOYEES” Table for Employee Management

CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID NUMBER(6) PRIMARY KEY,
    FIRST_NAME VARCHAR2(50),
    LAST_NAME VARCHAR2(50),
    HIRE_DATE DATE,
    SALARY NUMBER(8, 2),
    DEPARTMENT_ID NUMBER(4)
);

INSERT INTO EMPLOYEES VALUES (1, 'Taro', 'Yamada', TO_DATE('2023-01-01', 'YYYY-MM-DD'), 500000, 10);
INSERT INTO EMPLOYEES VALUES (2, 'Hanako', 'Tanaka', TO_DATE('2022-06-15', 'YYYY-MM-DD'), 600000, 20);
INSERT INTO EMPLOYEES VALUES (3, 'Jiro', 'Suzuki', TO_DATE('2021-03-20', 'YYYY-MM-DD'), 450000, 10);

2. “PRODUCTS” Table for Product Management

CREATE TABLE PRODUCTS (
    PRODUCT_ID NUMBER(5) PRIMARY KEY,
    PRODUCT_NAME VARCHAR2(100),
    CATEGORY VARCHAR2(50),
    PRICE NUMBER(10, 2),
    STOCK NUMBER(5)
);

INSERT INTO PRODUCTS VALUES (101, 'Laptop', 'Electronics', 120000.50, 50);
INSERT INTO PRODUCTS VALUES (102, 'Smartphone', 'Electronics', 80000.00, 150);
INSERT INTO PRODUCTS VALUES (103, 'Desk Chair', 'Furniture', 15000.75, 20);

3. “ORDERS” Table for Order Management

CREATE TABLE ORDERS (
    ORDER_ID NUMBER(8) PRIMARY KEY,
    ORDER_DATE DATE,
    CUSTOMER_NAME VARCHAR2(100),
    PRODUCT_ID NUMBER(5),
    QUANTITY NUMBER(3),
    TOTAL_PRICE NUMBER(10, 2),
    CONSTRAINT FK_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)
);

INSERT INTO ORDERS VALUES (1001, TO_DATE('2023-01-05', 'YYYY-MM-DD'), 'Alice', 101, 2, 240001.00);
INSERT INTO ORDERS VALUES (1002, TO_DATE('2023-01-10', 'YYYY-MM-DD'), 'Bob', 102, 1, 80000.00);
INSERT INTO ORDERS VALUES (1003, TO_DATE('2023-02-15', 'YYYY-MM-DD'), 'Charlie', 103, 3, 45002.25);

4. “DEPARTMENTS” Table for Managing Department Information

CREATE TABLE DEPARTMENTS (
    DEPARTMENT_ID NUMBER(4) PRIMARY KEY,
    DEPARTMENT_NAME VARCHAR2(100),
    MANAGER_ID NUMBER(6)
);

INSERT INTO DEPARTMENTS VALUES (10, 'Sales', 1);
INSERT INTO DEPARTMENTS VALUES (20, 'HR', 2);
INSERT INTO DEPARTMENTS VALUES (30, 'IT', NULL);

Simple SELECT Statement Examples

1. Retrieving All Data

A basic query to retrieve all data within a table.

EMPLOYEES Table

SELECT * FROM EMPLOYEES;

Example Result:

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  HIRE_DATE   SALARY    DEPARTMENT_ID
1            Taro        Yamada     2023-01-01  500000    10
2            Hanako      Tanaka     2022-06-15  600000    20
3            Jiro        Suzuki     2021-03-20  450000    10

2. Retrieving Data with Conditions

An example of extracting data that matches specific criteria.

Retrieve employees whose SALARY is 500,000 or more

SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY >= 500000;

Example Result:

FIRST_NAME  LAST_NAME  SALARY
Taro        Yamada     500000
Hanako      Tanaka     600000

3. Sorting Data

Retrieves data sorted by a specific column.

Retrieve employee information in descending order of SALARY

SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC;

Example Result:

FIRST_NAME  LAST_NAME  SALARY
Hanako      Tanaka     600000
Taro        Yamada     500000
Jiro        Suzuki     450000

JOIN Examples Using the Sample Tables

1. Joining Employee and Department Information

An example of retrieving the department name an employee belongs to by linking employee information and department information.

SELECT
    e.EMPLOYEE_ID,
    e.FIRST_NAME || ' ' || e.LAST_NAME AS FULL_NAME,
    d.DEPARTMENT_NAME
FROM
    EMPLOYEES e
LEFT JOIN
    DEPARTMENTS d
ON
    e.DEPARTMENT_ID = d.DEPARTMENT_ID;

Example Result:

EMPLOYEE_ID  FULL_NAME       DEPARTMENT_NAME
1            Taro Yamada     Sales
2            Hanako Tanaka   HR
3            Jiro Suzuki     Sales

2. Joining Order Information and Product Information

An example of retrieving the name of the ordered product by linking order information to product information.

SELECT
    o.ORDER_ID,
    o.CUSTOMER_NAME,
    p.PRODUCT_NAME,
    o.QUANTITY,
    o.TOTAL_PRICE
FROM
    ORDERS o
INNER JOIN
    PRODUCTS p
ON
    o.PRODUCT_ID = p.PRODUCT_ID;

Example Result:

ORDER_ID  CUSTOMER_NAME  PRODUCT_NAME  QUANTITY  TOTAL_PRICE
1001      Alice          Laptop        2         240001.00
1002      Bob            Smartphone    1         80000.00
1003      Charlie        Desk Chair    3         45002.25

Advanced JOIN Example

Sales Aggregation by Product Category

An example that aggregates sales by product category and also allows checking the stock quantity.

SELECT
    p.CATEGORY,
    SUM(o.TOTAL_PRICE) AS TOTAL_SALES,
    SUM(p.STOCK) AS TOTAL_STOCK
FROM
    PRODUCTS p
LEFT JOIN
    ORDERS o
ON
    p.PRODUCT_ID = o.PRODUCT_ID
GROUP BY
    p.CATEGORY;

Example Result:

CATEGORY     TOTAL_SALES  TOTAL_STOCK
Electronics  320001.00    200
Furniture    45002.25     20

Summary

By utilizing these sample tables and join examples, you can efficiently polish your practical SQL skills.

Key Points for Practice

Understand basic queries: Learn SELECT statement syntax and how to set conditions.
Apply JOINs: Experience the differences between INNER JOIN, LEFT JOIN, and RIGHT JOIN.
Aggregate and Analyze: Dive deeper into data using GROUP BY and aggregate functions.

Try these in your actual Oracle environment to acquire knowledge you can use in your work!

[reference]
CREATE TABLE – SQL Language Reference