Creating and Managing Tables in Oracle SQL
1. Creating Tables
Creating a table in Oracle SQL involves defining the structure of the table, including the column names, data types, and constraints. The CREATE TABLE statement is used to create a new table. Each column in the table must have a data type, which defines the kind of data that can be stored in that column.
Example:
CREATE TABLE employees (
employee_id NUMBER(10),
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE,
salary NUMBER(8, 2)
);
In this example, the employees table is created with five columns: employee_id, first_name, last_name, hire_date, and salary. Each column has a specific data type that defines the kind of data it can store.
2. Altering Tables
Altering a table involves modifying its structure after it has been created. This can include adding, modifying, or dropping columns, as well as adding or dropping constraints. The ALTER TABLE statement is used to make these changes.
Example:
ALTER TABLE employees
ADD (department_id NUMBER(10));
In this example, a new column department_id is added to the employees table. This allows you to store additional information about the department each employee belongs to.
Another example is modifying an existing column:
ALTER TABLE employees
MODIFY (salary NUMBER(10, 2));
Here, the salary column is modified to allow for a larger range of values.
3. Dropping Tables
Dropping a table involves removing the table and all its data from the database. This action is irreversible, so it should be used with caution. The DROP TABLE statement is used to drop a table.
Example:
DROP TABLE employees;
In this example, the employees table is dropped, and all the data stored in it is permanently deleted.
Understanding how to create, alter, and drop tables is fundamental to managing a database. These operations allow you to define the structure of your data, modify it as needed, and remove it when no longer required. Mastering these concepts is essential for becoming an Oracle Database SQL Certified Associate.