Oracle SQL Basics

Oracle Database – SQL Basics

Database: A Database is a collection of Schema.

Schema: A Schema is a Collection of Database Objects like Tables, Views and Synonyms etc.

Before you create a table in any database, you must create a schema first.

Create a Schema in a Database:

Syntax:

Create user <schema name> Identified by <password>;
GRANT All privileges to <schema name>;
GRANT DBA to <schema name>;

Example:

Create user OT_SRC_DEV2 Identified by admin;
GRANT All privileges to OT_SRC_DEV2;
GRANT DBA to OT_SRC_DEV2;

Basic Commands in Oracle SQL:

DDL Commands: DATA DEFININATION LANGUAGE – CREATE, ALTER, DROP, RENAME, TRUNCATE
DML Commands: DATA MANIPULATION LANGUAGE – INSERT, UPDATE, DELETE
TCL Commands: TRANSACTION CONTROLM LANGUAGE – COMMIT, ROLLBACK, SAVEPOINT
DQL Commands: DATA QUERY LANGUAGE – SELECT
DCL Commands: GRANT, REVOKE

What is DDL?
Data Definition Language helps you to define the table structure. Let’s learn about DDL commands with syntax.

1. CREATE: CREATE statement is used to define the Table structure or Definition.

Syntax:

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

Example: 

CREATE TABLE STUDENTS
(
ST_ID NUMBER (3),
ST_NAME VARCHAR2(50),
MARKS NUMBER (4),
CITY VARCHAR2(50),
STATE VARCHAR2(50)
);

2. DROP:  Drop command remove table with the data permanently from the database.

Syntax:

DROP TABLE <TABLE_NAME>;

Example:

DROP TABLE STUDENTS;

3. ALTER:
Alter command allows you to alter the structure of the database. Alter command have the below 4 types
a. ALTER TABLE ADD – Add the columns to the table.
b. ALTER TABLE DROP – Remove the columns to the table
c. ALTER TABLE MODIFY – Change the datatype of the columns in the table.
d. ALTER TABLE RENAME – Rename the column in a table.

a. Add one new column to existing table:

ALTER TABLE STUDENTS ADD (   COUNTRY VARCHAR2(50)  )  ;

b. Add more than one new column to existing table

ALTER TABLE STUDENTS ADD (  PINCODE NUMBER(6) , PHONE_NO NUMBER(10)  );

c. Remove on existing column from the existing table

ALTER TABLE STUDENTS DROP (PHONE_NO);

d. Remove more than one existing column from the existing table

ALTER TABLE STUDENTS DROP (COUNTRY, PINCODE);

e. Change the datatype of one column in a table.

ALTER TABLE   STUDENTS MODIFY (COUNTRY VARCHAR2(500) );

f. Change the datatypes of more than one column in a table.

ALTER TABLE STUDENTS MODIFY ( PINCODE VARCHAR2(10) , PHONE_NO VARCHAR2(100) );

Rule: You can change the datatypes of empty columns only

g. Rename a column in a table 

ALTER TABLE STUDENTS RENAME COLUMN  ST_NAME TO STUDENT_NAME;

4. RENAME:  Rename Command is used to rename the table.

Syntax: 

RENAME OLD_TABLE_NAME TO NEW_TABLE_NAME

Example:

Rename Students to students_new

5. TRUNCATE: This command used to delete all the rows from the table and free the space containing the table.

Syntax:  Truncate table Tablename ;

Example: Truncate table students;

What is DML? 
Data Manipulation Language (DML) allows you to modify the table by inserting, modifying, and deleting its data. It is responsible for performing all types of data modification in a table

Here are some important DML commands in SQL:

• INSERT
• UPDATE
• DELETE

INSERT:   This command 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);

Examples:

INSERT INTO STUDENTS(ST_ID,ST_NAME,MARKS,CITY,STATE) VALUES (10,'RAJ',100,'BANGALORE','KAR');
INSERT INTO STUDENTS(ST_ID,ST_NAME,MARKS,CITY,STATE) VALUES (20,'RAM',200,'CHENNAI','TN');
INSERT INTO STUDENTS(ST_ID,ST_NAME,MARKS,CITY,STATE) VALUES (30,'DIVYA',300,'HYDERABAD','TS');
INSERT INTO STUDENTS(ST_ID,ST_NAME,MARKS,CITY,STATE) VALUES (40,'RAMYA',400,'MUMBAI','MH');
Commit;

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]

Example:

UPDATE STUDENTS SET CITY='GOA';
UPDATE STUDENTS SET ST_NAME='DIVYA' WHERE ST_NAME='RAMYA';
UPDATE STUDENTS SET ST_NAME='KAVYA', CITY='BANGALORE' WHERE ST_ID=10;
Commit;

DELETE:    This command is used to remove one or more rows from a table.

Syntax:

DELETE FROM table_name [WHERE condition];  ]

Example:

DELETE FROM STUDENTS;
DELETE FROM STUDENTS WHERE CITY='GOA';
Commit;

Rule: You must give Commit after you executes any DML command.

What is TCL?
Transaction control language or TCL commands deal with the transaction within the database.

Here are some important TCK commands in SQL:

• COMMIT
• ROLLBACK

COMMIT: This command is used to save all the transactions to the database. Will be used after DML commands.

Example:

DELETE FROM Students WHERE ST_ID=60;
COMMIT;

ROLLBACK: Rollback command allows you to undo transactions that have not already been saved to the database.

Example:

DELETE FROM Students WHERE ST_ID=60;
ROLLBACK;

What is DQL?

Data Query Language (DQL) is used to fetch the data from the database. It uses only one command.

SELECT: This command helps you to select the attribute based on the condition described by the WHERE clause.

Example:

SELECT * FROM EMP;
SELECT * FROM EMP WHERE EMP_ID=1;
SELECT * FROM EMP WHERE DEPT_ID=20;
SELECT * FROM EMP where ename='RAJ';
SELECT * FROM EMP where ename='santhi';
SELECT * FROM EMP where ename='123.456';
SELECT * FROM EMP WHERE SALARY>10000;
SELECT * FROM EMP WHERE SALARY>=10000;
SELECT * FROM EMP WHERE SALARY<10000;
SELECT * FROM EMP WHERE SALARY! =10000;
SELECT * FROM EMP WHERE SALARY<>10000;
SELECT * FROM EMP WHERE NOT SALARY=10000;
SELECT COUNT (*) FROM EMP;

DESC:   DESC Command is used to See the structure or definition of the table.

Example:

DESC Students;
DESCRIBE students;