Create a Table in Oracle | Tips and Tricks | SQL PLSQL
In Oracle SQL, there are multiple methods to create a table. Here are some common approaches:
Method 1: Using the CREATE TABLE Statement
The most straightforward method is to use the `CREATE TABLE` statement. You specify the table name, columns, and their data types:
```sql
CREATE TABLE table_name (
column1 datatype1,
column2 datatype2,
...
);
```
Example:
```sql
CREATE TABLE employees (
employee_id NUMBER,
employee_name VARCHAR2(50),
job_title VARCHAR2(50),
salary NUMBER
);
```
Method 2: Using the AS Clause with a SELECT Statement
You can create a table based on the result of a query using the `CREATE TABLE AS SELECT` (CTAS) statement. This method is useful when you want to create a table that mirrors the structure and data of an existing table:
```sql
CREATE TABLE new_table AS
SELECT column1, column2, ...
FROM existing_table
WHERE condition;
```
Example:
```sql
CREATE TABLE high_paid_employees AS
SELECT *
FROM employees
WHERE salary 50000;
```
Method 3: Using the LIKE Clause
The `LIKE` clause is used to create a table that is similar to an existing table, including its columns, constraints, and storage attributes:
```sql
CREATE TABLE new_table
LIKE existing_table;
```
Example:
```sql
CREATE TABLE employees_backup
LIKE employees;
```
Method 4: Using SQL Developer or Other Database Tools
Graphical database tools like Oracle SQL Developer provide a user-friendly interface to create tables. In these tools, you can use a wizard or design view to define the table's structure and properties visually.
Method 5: Using External Files (External Tables)
Oracle also allows you to create tables that reference data stored in external files. This is achieved using the `CREATE TABLE ... ORGANIZATION EXTERNAL` statement.
```sql
CREATE TABLE external_table
(column1 datatype1, column2 datatype2, ...)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY external_directory
ACCESS PARAMETERS
(FIELDS TERMINATED BY ',' (column1, column2, ...))
LOCATION ('external_file.csv')
);
```
Choose the method that best fits your requirements, whether you need to create a table from scratch, based on an existing table, or using external data sources.
On this page of the site you can watch the video online Create a Table in Oracle | Tips and Tricks | SQL PLSQL with a duration of hours minute second in good quality, which was uploaded by the user Cloud Research & Software Engineering 28 February 2024, share the link with friends and acquaintances, this video has already been watched 25 times on youtube and it was liked by 2 viewers. Enjoy your viewing!
