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
CREATE :
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;
SQL stands for Structured Query Language. and it is generally referred to as SEQUEL. SQL is simple language to learn. SQL is a Nonprocedural language, as compared to the procedural or third generation languages (3GLs) such as COBOL and C. SQL was developed by IBM in the 1970s.
The American National Standards Institute (ANSI) published its first SQL standard in 1986 and a second widely adopted standard in 1989. ANSI released updates in 1992, known as SQL92 and SQL2, and again in 1999, termed both SQL99 and SQL3. Each time, ANSI added new features and incorporated new commands and capabilities into the language.
SQL is a simple, yet powerful, language used to create, access, and manipulate data and structure in the database.
SQL Statements categories: DDL - Data Definition Language.
DDL is used to define, alter, or drop database objects and their privileges. DDL statements will implicitly perform a commit.
DDL Statements:
| Create | It is used to create objects(tables, views) in the database. |
| Alter | It is used to alter the structure of the database objects. |
| Drop | delete database objects (It will invalidate the dependent objects ,it also drops indexes, triggers and referential integrity constraints ). |
| Truncate | remove all records from a table, including all spaces allocated for the records are removed (It is fast as compared to Delete and does not generate undo information as Delete does. It performs an implicit commit as it is a DDL. It resets the high water mark.) |
| Grant | assigning privileges |
DML - Data Manipulation Language.
DML is used to access, create, modify or delete data in the structures of the database.
DML Statements:
| Select | Select data from the database |
| Insert | It is used to insert data into a table |
| Update | It is used to update existing data within a table |
| Delete | It removes rows from the table. |
DCL - Data Control Language
Following are the examples of Data control Statements.
DCL Statements:
|
| Commit | It will end the current transaction making the changes permanent and visible to all users.. |
| Savepoint | It will identify a point(named SAVEPOINT) in a transaction to which you can later roll back |
| Rollback | It will undo all the changes made by the current transaction. |
| Set- Transaction | It is used to define the properties of a transaction |
