Create Tables (create table - describe - all_tables - drop table)

There are several database objects: tables, constraints, views, sequences, indexes, clusters, triggers, snapshots, procedures, functions, packages, synonyms, users, profiles, privileges, roles, etc.

Tables in SQL - Oracle

A database stores its information in tables, which is the basic unit of storage.

A table is a data structure that organizes data in columns and rows; each column is a field (or attribute) and each row, a record. The intersection of a column with a row contains a specific data, a single value.

Each record contains one data for each column in the table. Each field (column) must have a name. The field name refers to the information it will store.

Each field (column) must also define the type of data it will store.

Tables are part of a database.

We will work with the database already created.

To see the existing tables we type:

 select * from all_tables;

A table appears that shows us in each row, the data of a specific table; in the column "TABLE_NAME" appears the name of each existing table.

When creating a table we must determine what fields (columns) it will have and what type of data each of them will store, that is, their structure.

The basic and general syntax to create a table is as follows:

 create table TABLENAME (

  FIELD NAME1 DATA TYPE,

  ...

  CHAMPION NAME TYPE OF DATA

 );

The table must be defined with a name that identifies it and with which we will access it.

We create a table called "users" and in parentheses we define the fields and their types:

 create table users (

  name varchar2 (30),

  varchar2 key (10)

 );

Each field with its type must be separated by commas from the following, except the last one.

When creating a table we must indicate its name and define at least one field with its data type. In this "users" table we define 2 fields:

- name: which will contain a string of characters of 30 characters in length, which will store the username and

- password: another string of characters of length of 10, which will store the password of each user.

Each user will occupy a record in this table, with their respective name and password.

For table names, you can use any character allowed for directory names, the first must be an alphabetic character and cannot contain spaces. The maximum length is 30 characters.

If we try to create a table with an existing name (there is another table with that name), it will show a message indicating that such name is already being used by another object and the statement will not be executed.

To see the structure of a table we use the command "describe" next to the name of the table:

 describe users;

The following information appears:

Name  Null Type

-------- -----------------------

NAME  VARCHAR2 (30)

VARCHAR2 KEY (10)

This is the structure of the "users" table; it shows us each field, its type and length and other values ​​that we will not analyze for the moment.

Drop Table in SQL - Oracle

To drop a table we use "drop table" next to the name of the table to drop:

drop table TABLENAME;

In the following example we delete the "users" table:

drop table users;

If we try to delete a table that does not exist, an error message appears indicating such a situation and the statement is not executed.