Understanding Tables in Oracle

This tutorial provides an overview of tables in Oracle, including their structure, how to create and manage them, and how to use them to store and manipulate data.

1. What is a Table?

In Oracle, a table is a database object used to store data in rows and columns. Each column represents a different field or attribute, and each row represents a single record. Tables are fundamental to relational databases and are used to organize and manage data.

2. Creating a Table

To create a table in Oracle, you use the CREATE TABLE statement. Here is the basic syntax:

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

For example, to create a table named "employees" with columns for employee ID, first name, last name, and hire date, you would use the following command:

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    hire_date DATE
);

This command creates a table with four columns, specifying the data type for each column and setting the "employee_id" column as the primary key.

3. Inserting Data into a Table

To insert data into a table, you use the INSERT INTO statement. Here is the basic syntax:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

For example, to insert a new row into the "employees" table, you would use the following command:

INSERT INTO employees (employee_id, first_name, last_name, hire_date)
VALUES (1, 'John', 'Doe', TO_DATE('2024-01-01', 'YYYY-MM-DD'));

This command inserts a new row into the "employees" table with the specified values.

4. Querying Data from a Table

To retrieve data from a table, you use the SELECT statement. Here is the basic syntax:

SELECT column1, column2, ... FROM table_name [WHERE condition] [ORDER BY column1, column2, ...];

For example, to retrieve all columns from the "employees" table, you would use the following command:

SELECT * FROM employees;

To filter the data, you can use the WHERE clause:

SELECT * FROM employees WHERE department_id = 10;

To sort the data, you can use the ORDER BY clause:

SELECT * FROM employees ORDER BY last_name ASC;

5. Updating Data in a Table

To update existing rows in a table, you use the UPDATE statement. Here is the basic syntax:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

For example, to update the department ID of an employee, you would use the following command:

UPDATE employees SET department_id = 20 WHERE employee_id = 1;

This command updates the "department_id" of the employee with ID 1 to 20.

6. Deleting Data from a Table

To delete rows from a table, you use the DELETE statement. Here is the basic syntax:

DELETE FROM table_name WHERE condition;

For example, to delete an employee from the "employees" table, you would use the following command:

DELETE FROM employees WHERE employee_id = 1;

This command deletes the row from the "employees" table where the "employee_id" is 1.

7. Dropping a Table

To delete a table and all its data, you use the DROP TABLE statement. Here is the basic syntax:

DROP TABLE table_name;

For example, to delete the "employees" table, you would use the following command:

DROP TABLE employees;

8. Altering a Table

To modify the structure of an existing table, you use the ALTER TABLE statement. Here are a few examples:

8.1. Adding a Column

To add a new column to a table, use the following command:

ALTER TABLE employees ADD (salary NUMBER);

This command adds a new column named "salary" to the "employees" table.

8.2. Modifying a Column

To change the data type or constraints of an existing column, use the following command:

ALTER TABLE employees MODIFY (last_name VARCHAR2(100));

This command changes the data type of the "last _name" column in the "employees" table to VARCHAR2(100).

8.3. Dropping a Column

To remove a column from a table, use the following command:

ALTER TABLE employees DROP COLUMN salary;

This command drops the "salary" column from the "employees" table.

9. Example: Creating and Using a Table

Let's create a table named "students" to store information about students, and then insert, query, update, and delete data from this table.

9.1. Creating the Students Table

CREATE TABLE students (
    student_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    enrollment_date DATE
);

9.2. Inserting Data into the Students Table

INSERT INTO students (student_id, first_name, last_name, enrollment_date)
VALUES (1, 'Alice', 'Smith', TO_DATE('2024-01-01', 'YYYY-MM-DD'));

INSERT INTO students (student_id, first_name, last_name, enrollment_date)
VALUES (2, 'Bob', 'Johnson', TO_DATE('2024-02-01', 'YYYY-MM-DD'));

9.3. Querying Data from the Students Table

SELECT * FROM students;

STUDENT_ID  FIRST_NAME  LAST_NAME  ENROLLMENT_DATE
----------  ----------  ---------  ---------------
1           Alice       Smith      2024-01-01
2           Bob         Johnson    2024-02-01

9.4. Updating Data in the Students Table

UPDATE students SET last_name = 'Williams' WHERE student_id = 2;

9.5. Deleting Data from the Students Table

DELETE FROM students WHERE student_id = 1;

9.6. Dropping the Students Table

DROP TABLE students;