SQL oracle Commands for 22319 dms notes

so in the last blog, we had learned some information about SQL and learned about how to sign in for the oracle workspace using apex oracle.
So in this session, we are going to learn about oracle commands. let's get started.

Table

so, The following is the syntax of the table.

create tabletable_name
(
    column1 datatype
    column2 datatype
    column3 datatype
    .....  
);

so here, is an example of creating a table.

  create table person
 (
    personid int,
    last name varchar(25),
    first name varchar(25),
    address varchar(25),
    city varchar(25)
 );

Here, personid is an integer so we have used integer we can also use the number as a datatype. Varchar is used to give characters such as first_name or city_name. int and varchar are datatypes we can store data inside them.

so, what's 25 there? so that is a limit value so if anyone wants to add first_name that can only be of 25 letters only.

The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 25 characters.

The SQL DROP TABLE Statement

The DROP TABLE statement is used to drop an existing table in a database.

      The drop means deleting the table from the database we will be seeing in the next examples.
 

DROP TABLE table_name;

Note: Be careful before dropping a table. Deleting a table will result in the loss of complete information stored in the table!

SQL DROP TABLE Example =>

The following SQL statement drops the existing table "person"

 DROP TABLE person;

SQL TRUNCATE TABLE 

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

TRUNCATE TABLE table_name;

Example for the truncate table :

TRUNCATE TABLE person;

SQL ALTER TABLE Statement 

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

ALTER TABLE - ADD Column To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype;

The following SQL adds an "Email" column to the "Customers" table:

 Example:

ALTER TABLE Customers
ADD Email varchar(25);

ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLEtable_name
DROP COLUMN column_name; 

The following SQL deletes the "Email" column from the "person" table:

ALTER TABLEperson
DROP COLUMN Email;

ALTER TABLE - ALTER/MODIFY COLUMN

To change the data type of a column in a table, use the following syntax:

ALTER TABLEtable_name
MODIFYcolumn_name datatype;