· SQL refers to "Structured Query Language"
· It is an Structured Query Language because it follows ANSI, ISO standards.
· It is an editor which helps the user to communicate with the Oracle Server.
· User communicates with the help of queries. A query is a request / question / command submitted to oracle server to perform operations over DB.
· SQL is developed by IBM, olden days it was called as “Sequel”.
· It is the language common for all RDBMS software’s like Oracle, SQL Server, MySQL.
· Apart from SQL, we also have QBE and QUEL which are used to communicate with the RDBMS server.
- QBE refers to Query by Example. Used in MS-ACCESS.
- QUEL refers to Query Language.
Depending on the operations, SQL is categorized into following sub languages:
1. DML ( Data Manipulation Language )
2. DDL ( Data Definition Language )
3. DRL ( Data Retrieval Language )
4. TCL ( Transaction Control Language )
5. DCL ( Data Control Language )
Data Definition Language (DDL):
- DDL is a set of instructions to perform operations over Data Definition.
- Data Definition is also called as Metadata. (data about data)
- Commands in DDL are:
(i) CREATE
(ii) ALTER
(iii) DROP
(iv) TRUNCATE
(v) RENAME
Syntax:
Create table <table_name>
(
<column_name> <data_type> (size),
<column_name> <data_type> (size),
<column_name> <data_type> (size),
--
--
);
Ex:
Create table Employee
(
EmpNo Number(4),
EmpName varchar2(20),
Salary Number(7,2),
Desg varchar2(20),
DeptNo Number(2)
);
Scenarios:
1. Create a table from the existing table ?
CREATE TABLE EMP_TARGET
AS
SELECT * FROM EMP;
It creates a new table with name EMP_TARGET. It will create the table along with
the data.
2. Copy the table definition but not the data ?
CREATE TABLE EMP_TARGET
AS
SELECT * FROM EMP WHERE 1=2;
ALTER :
- It is used to modify data definition of a table.
- Using alter command :
1. Add columns
2. Drop columns
3. Rename a column
4. Modify a column
a. Increasing or decreasing size
b. Changing data type
c. Changing Null to Not Null
d. Changing Not Null to Null
1. Adding columns using Alter ?
Syntax:
ALTER TABLE <table_ name>
ADD ( column_name datatype(size));
Ex:
Alter Table Employee
Add ( DOB Date, Gender CHAR(1));
2. Dropping a column using Alter ?
Syntax:
ALTER TABLE <table_ name>
DROP ( column_name datatype(size));
Ex:
Alter Table Employee
DROP ( DOB, GENDER);
3. Rename a column using Alter ?
Syntax:
ALTER TABLE <table_ name>
RENAME COLUMN <old_name> to <new_name>;
Ex:
Alter Table Employee
RENAME column HireDate to DateOfJoin;
4. Modifying a column using Alter ?
a) Increasing or decreasing the column size.
Syntax:
ALTER TABLE <table_ name>
MODIFY ( column_name datatype(size)…);
Ex:
Alter Table Employee
MODIFY ( ename varchar2(20));
b) Changing datatype.
Ex:
Alter Table Employee
MODIFY ( COMM varchar2(20));
c) Changing from NULL to NOT NULL.
Ex:
Alter Table Employee
MODIFY ( ename NOT NULL);
d) Changing from NOT NULL to NULL.
Ex:
Alter Table Employee
MODIFY ( ename NULL);
DROP :
Syntax:
DROP TABLE <tableName>
Ex:
DROP TABLE EMP;
- Prior to 10g, we don’t have the option of restoring.
- From 10g, we can even restore the Dropped tables. This is possible with the concept of “Flashback”.
- In 10g, when the table is dropped, it is moved to recyclebin in Oracle server.
- To view the contents of the Recyclebin, execute the following commands:
Select * from recyclebin;
OR
Show recyclebin;
- To restore the tables from Recyclebin, execute the following commands:
FLASHBACK table EMP to before DROP;
This restores the table back along with the data.
- To delete the table permanently, execute the following commands:
PURGE TABLE EMP;
This deletes the table permanently. Hence FLASHBACK is not possible in this case.
- To empty Recycle bin, execute the following commands:
DROP TABLE EMP PURGE;
