Classification of SQL
SQL and Classification of SQL in Oracle Database.
SQL is Structured Query Language , which is used for storing, manipulating and retrieving data stored in a relational database .SQL is the standard language for RDBMS. All the Relational Database Management Systems (RDMS) like Oracle, MySQL, Sybase, Informix, IBM DB2 and Microsoft SQL Server use SQL as their standard database language. Oracle is one of the more secured database as compared to other databases.
Importance of SQL :
SQL and PL/SQL is a backend process where all data is stored and retrieved in GUI which created either by any programming languages like Java, C++, PHP etc. so we need to have very secure database so that there will be no impact for users. SQL allows users to access data in the relational database management systems. SQL is used to communicate with a database .SQL and PL/SQL allows users to create and drop databases tables , views , stored procedures , functions , packages , trigger etc. SQL allows user to grant permission in any object created in database .
Note: PL/SQL is a Programming Language in SQL which I will cover later in coming blog once I complete SQL part.
SQL Process:
When you are executing an SQL command for any RDBMS. Below are the various components included in this process.
These components are −
Query Dispatcher
Optimization Engines
Classic Query Engine
SQL Query Engine, etc.
SQL Commands :
SQL command is very important to store, retrieve , manipulate and make some changes in transaction.
Classification and types of SQL Command :
1. DDL : DDL is a Data Definition language which basically work on structure of any object . DDL is mainly used for creating objects like table , views , index etc . Also we can say DDL is mainly required to create design of the database tables.
Types of DDL :
Create : This command is mainly used to create objects like table , views etc in database
Alter : Alter command used to Manipulate data types , dropping columns , adding constraints etc
Drop : If we want to drop any object we can use drop command
Truncate : To delete the records from table as it’s worked on Structure so it will delete all the records fast and we can’t filter or put any condition in truncate since it’s DDL and worked on structure.
Rename : If we created any table and as per requirement if we want to change the table name we can use rename command to change the table name.
2 . DML : DML is a Data Manipulation Language where we can modify , insert , fetch and delete the data. Manipulation we can do once we use DDL to create some objects like table , views etc.
Types of DML :
Select : To fetch or get the data from tables.
Insert : used to insert data into existing tables by using insert into table commands.
Update : Update is used to Manipulate or make changes in existing data in table.
Delete : if we want to delete some records which should not be inserted then by using condition we can remove that particular data by using delete statement.
3 . TCL : TCL is Transaction Control language which mainly used to manage transaction in database.
Types of TCL :
Commit: Commit Command is used to permanently saved any DML operation in database. Once we use commit we can't Rollback it.
Rollback: Rollback used to back the original data in table if we delete any records from the table by mistake.
Note : If we delete records by mistake and use the commit command then rollback will not able to get the original data.
Savepoint : Savepoint is used to temporarily save a transaction so that user can rollback to that point whenever required.
4 . DCL : DCL is a data control Language used to control access to data stored in a database (Authorization)
Types of DCL :
Grant : GRANT allows specified users to perform specified tasks. Like we can provide some permission like delete and insert to particular user by using grant command . Also we can provide all the access to particular user by using Grant Command.
Revoke : The REVOKE command removes user access rights or privileges to the database objects.
5 : Constraints
Constraint is very important as it’s allows user to restrict the data . Also we can avoid duplicacy of records by using Constraint.
Types of Constraint :
Primary Key : Primary Key used to restrict user not to allow any duplicate records as well as null values in a table . We can also create primary key while creating the design of the table and also after creating the table we can add primary key by using Alter command:
Purpose of using Primary key to avoid duplicity . Suppose you have bank account where we can create primary key in account number as well as in customer_id column so that there should be no duplicate account numbers as well as customer id .
Foreign Key : A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table that may be primary key.
Check : The CHECK constraint is used to limit the value range that can be placed in a column
Unique : Unique Key Avoid Duplicate Records in a table like Primary key but only difference is that we can allow null values in Unique Key.
Default : DEFAULT constraint is used to provide a default value for a column.
Not Null : Not Null is a constraint where we can’t allow user to put NULL values .
Note : This is all about Basic SQL and Classification of SQL . For Practical of all the commands of SQL which I discussed in this blog I will cover in next blog.
For now from this blog you can start with creating one simple table and insert some data into tables by using one DDL command and DML command .
Example :
Creating one simple table employee and inserting some sample data and after that viewing the data .
// Syntax for creating table Employee
Create table Employee
(emp_id number,
Empname varchar2(20),
Salary number );
Once we run above command table name employee is created.
// Inserting value in table employee by using DML insert command
Insert into employee values (100,'Gyan',40000);
Insert into employee values (200,'Deepak',50000);
Insert into employee values (300,'Prashant',60000);
Insert into employee values (400,'Krishna',30000);
// Viewing data by using Select Statement
Select * from employee;
Here it will give all the records from the table.
Select * from employee where salary>50000;
Here we put where condition to filter the record.
Note : * means all the column so if we want particular column to view the record we can use that column only like select emp_id,salary from employee.
Note : As discussed above this is simple SQL syntax for you to practice and in next blog I will come up with more SQL commands so that you can be able to understand Classification of SQL through command and can view the data as per your requirement.
Please go through this blog carefully and let me know if you have any questions, as this blog is important and once you start writing simple SQL statement and creating tables it’s easy for you to understand.
