BASIC SQL Commands

• CREATE It is used to create a new table in the database.

Syntax:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]); 

Example:

CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE); 

• DROP: It is used to delete both the structure and record stored in the table.

Syntax:

DROP TABLE ;

Example:

DROP TABLE EMPLOYEE;

• ALTER: It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.

Syntax:

To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN-definition;    

To modify existing column in the table:

ALTER TABLE MODIFY(COLUMN DEFINITION....);  

EXAMPLE

ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));  

ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));  

• TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.

Syntax:

TRUNCATE TABLE table_name;  

Example:

TRUNCATE TABLE EMPLOYEE; 

• INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table.

Syntax:

INSERT INTO TABLE_NAME    

(col1, col2, col3,.... col N)  

VALUES (value1, value2, value3, .... valueN);  

Or

INSERT INTO TABLE_NAME    

VALUES (value1, value2, value3, .... valueN);    

For example:

INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");

•  UPDATE: This command is used to update or modify the value of a column in the table.

Syntax:

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]   

For example:

UPDATE students    

SET User_Name = 'Sonoo'    

WHERE Student_Id = '3'  

• DELETE: It is used to remove one or more row from a table.

Syntax:

DELETE FROM table_name [WHERE condition];  

For example:

DELETE FROM javatpoint  

WHERE Author="Sonoo";  

• SELECT: This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.

Syntax:

SELECT expressions    

FROM TABLES    

WHERE conditions;  

For example:

SELECT emp_name  

FROM employee  

WHERE age > 20;  

• Commit: Commit command is used to save all the transactions to the database.

Syntax:

COMMIT;  

Example:

DELETE FROM CUSTOMERS  

WHERE AGE = 25;  

COMMIT;  

• Rollback: Rollback command is used to undo transactions that have not already been saved to the database.

Syntax:

ROLLBACK;  

Example:

DELETE FROM CUSTOMERS  

WHERE AGE = 25;  

ROLLBACK;  

• SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;