What is SQL?
SQL stands for structure query language. SQL is a query language used for accessing and modifying information in the database. Before discussing about Structure Query Language’s- DDL,DML,DQL & TCL ,lets have a quick introduction to some basic staffs of SQL.
SQL Database Design:
IBM developed SQL in 1970 .it has become standard Universal language used by most of the relational database management system RDBMS .some of the RDBMS systems are Microsoft SQL ,Oracle SQL, Sybase, My SQL ,Microsoft Access etc.
Features of SQL:
In a simple manner SQL is Non Procedural English like Language for storing retrieving and updating data in the database.
SQL processes data in a group of records rather than one record at a time.
What is non procedural language?
SQL is a non procedural language .using this type of language, a programmer does not need to instruct the RDBMS how to evaluate the results .you only have to tell the RDBMS what you want to get.
Each RDBMS has its inbuilt query optimizer that passes your SQL statements and find out the optimum path to the required data.
Some jobs of SQL:
create tables and other database objects.
What is RDBMS?
RDBMS stands for Relational Database Management System. RDBMS is the foundation of SQL and for all modern database systems like MS SQL, MySQL, IBM DB2 to Oracle and Microsoft Access .A relational database management system, which is based on the relational model, was introduced by Mr. EF Codd.
What is table?
In RDBMS every data gets stored in tables. Tables are situated within the database and database is situated within the SQL server. Tables can be said as database objects, which consist of some related data within its fields and records.
What is field?
Every table in the database can be broken up into smaller entities called fields. The fields are something like columns in Microsoft Excel.
What is record?
Records are the rows in the table.Whenever you insert some data in the Table, that data gets stored along the records or rows.
Classifications of SQL commands:
SQL commands can be classified into five main groups.
These are DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), DQL (Data Query Language), TCL (Transaction control language)
Data Definition Language, COMMANDS and Description:
CREATE – This command is used to create a new TABLE i.e. a new object in database.
This is an EMPLOYEE table in an Organization. It consists of five Fields , namely Emp_ID, Emp_Name, Emp_Address, Emp_Designation, Emp_Salary.
CREATE TABLE <table_name>
To create this table we can use following query.
CREATE TABLE EMPLOYEE
Emp_ID int not null,
Here, number, char, varchar -are the data types and the permissible lengths of the values of the data variables are defined within the brackets( next to data type’s names ).
ALTER – This command is used to modify an existing TABLE that is database object.
Suppose you want to modify the above table. Example- Add another column for ‘Employee date of birth’ (Emp_DOB) .Like as below.
ALTER TABLE <table name> ADD <column name> <data type>
Following command is used to add another that column.
ALTER TABLE EMPLOYEE
ADD Emp_DOB date ;
Here ‘date ‘ is the datatype for Emp_DOB.
DROP – This command is used to delete an entire TABLE or a COLUMN of a table in the database.
This command is used to delete an entire table or other database objects.It’s also used to delete any column within a table.
Syntax– For deleting entire table –
Syntax – For deleting a column in a table –
ALTER TABLE <table_name> DROP <column_name>
- Following statement will drop the entire EMPLOYEE table from the database.
DROP EMPLOYEE ;
- Following statement will drop the ‘Emp_Salary’ column from EMPLOYEE table .
ALTER TABLE EMPLOYEE DROP Emp_Salary ;
Following command is used to see the created table structure ,all the columns and the data types of those columns etc.
SELECT * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = ‘EMPLOYEE’
To Search all the columns of the EMPLOYEE table use the following query.
WHERE TABLE_NAME=’EMPLOYEE’ ;
To find the EMPLOYEE table from the whole database use the following query.
SELECT * FROM
TABLE_NAME = ‘EMPLOYEE’
SELECT ‘there is a table with the required name ‘ AS search_result ELSE SELECT ‘no such table in the database’ AS search_result;
Data Manipulation Language, COMMANDS and Descriptions:
INSERT – This command is used to create a Record within a Table
INSERT INTO <table_name> (column 1,column 2,column 3,…..column n) VALUES (value 1,value 2,value 3,……,value n);
INSERT INTO table_name VALUES(value 1, value 2, value 3,………., value n)
- To insert an employee detail into the above table we have to use the following query.
INSERT INTO EMPLOYEE
VALUES ( Emp_name,Emp_name,Emp_address,Emp_designation,Emp_salary)
VALUES(1,’William’,’San Francisco’,’Programmer’,5000.00) ;
INSERT INTO EMPLOYEE VALUES(1,’William’,’San Francisco’,’Programmer’,5000.00)
UPDATE- This command is used to modify Record within a table
This type of Query is used to update any row or record in a table.
UPDATE <table_name> SET <column_name> = <value> WHERE <reference_column_name> = <value_of_reference_column>;
- To update the salary in the first row of the above table use the following query.
UPDATE EMPLOYEE SET Emp_salary=6000.00 WHERE Emp_ID=1;
DELETE- This command is used to delete a record from a Table
DELETE FROM <table_name> WHERE <reference_column_name> =<reference_value>;
- To delete the first row of the above table you can use following query.
DELETE FROM EMPLOYEE WHERE Emp_ID=1;
To delete duplicate row use this command.
WITH cte_EMPLOYEE AS
DELETE FROM cte_EMPLOYEE WHERE row_num>1;
Data Control Language, COMMANDS and Description:
GRANT- This command is used to give some privilege to particular user.
This command is used to give permission to perform SELECT,DELETE,CREATE etc. operation on particular table by any user.
GRANT <privileges_name> ON <database_object> TO <users>;
- To give permission of running SELECT and CREATE statements on EMPLOYEE table to user1, use the following query.
GRANT SELECT,CREATE ON EMPLOYEE TO user1;
REVOKE- This command is used to take back the privilege from the user.
This command is used to restrict any user from performing SELECT,CREATE,DELETE etc. operation on any database object like, table.
REVOKE <privileges_name> ON <database_object_name> FROM <user_name>
- To restrict user1 from running SELECT statement on EMPLOYEE table, use the following query.
REVOKE SELECT ON EMPLOYEE FROM user1;
Data Query Language, COMMANDS and Description:
SELECT- This command is used to retrieve certain records from one or more tables.
- This command is used to retrieve data from any table.Several conditions can be applied along with this command ;such as, retrieve data withing a given date range or, retrieve data of some particular records from the table, where some conditions are matched.
- SELECT * FROM <table_name>
- SELECT * FROM <table_name> WHERE <reference_column_name>=<value _in the reference_column>;
- To retrieve all the data from the EMPLOYEE table use the following query.
SELECT * FROM EMPLOYEE;
- To retrieve the data of the first row of the EMPLOYEE table, use the following query.
SELECT * FROM EMPLOYEE WHERE Emp_ID=1;
Transaction Control Language, COMMANDS Description:
COMMIT- This command is used to save the changes occurred in table.
Example-First of all make a delete operation on the above table and run the COMMIT commands .The changes made in the database is confirmed and the changes are permanent.
>DELETE FROM EMPLOYEE WHERE Emp_ID=1;
After doing the commit operation ,there is no way to get back to the previous stage.
ROLLBACK- This command is used to restore the table to its previous form at the time ,when the last query was run.
This command is used to get back to the previous stages after running any query.Suppose you have done delete operation on any table. After that if you run the ROLLBACK command then no changes will be made on the database ,that means the delete operation will not have any effect on the table ultimately.
Run the following commands.
>DELETE FROM EMPLOYEE WHERE Emp_ID=2;
Now, if you do SELECT operation then you can see that , no effect has been made by the DELETE operation.
SAVEPOINT- This command is used to create a ‘restore point’ or ‘save point’, before making any changes to the table. At a later time one can use this ‘save point’ as a restore point to ROLLBACK the table.
Syntax:– SAVE TRANSACTION <save_point _name>
Save point is similar to the restore point in Computer.Suppose you have created a save point (sp1). Then you have done a delete operation on particular table.Now you make another save point(sp2) and insert some data in the table. Then again you create a save point (sp3) and do another changes in the table.
Now, if you want to go back to the initial stage i.e. restore the table to its original form, then you can use the save point sp1 as a reference point to restore the table.
In the following codes we have used save point and its very easy to understand the function of save point from it.
>SAVE TRANSACTION SP1;
>INSERT INTO EMPLOYEE (Emp_ID,Emp_Name,Emp_Salary) VALUES(5,’Rocky’,3000.00);
>SELECT * FROM EMPLOYEE;
>SAVE TRANSACTION SP2;
>DELETE FROM EMPLOYEE WHERE Emp_ID=3;
>SELECT * FROM EMPLOYEE;
>UPDATE EMPLOYEE SET Emp_Salary=’8000.00′;
>SELECT * FROM EMPLOYEE;
>SAVE TRANSACTION SP3;
>ROLLBACK TRANSACTION SP1;
>SELECT * FROM EMPLOYEE;