SQL Introduction | Introduction of Structured Query Language

SQL stands for Structured Query Language.  It is a standard language for Relational Database System (RDBMS).

Structured Query Language (SQL) is the database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database.

SQL is the most commonly used language to communicate with databases and extract data for application development, reporting, and analytics.

Today almost all RDBMS (MySql, Oracle, SQL Server, Sybase, MS Access) use SQL as the standard database query language. SQL is used to perform all types of data operations in RDBMS.

SQL became a standard of the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987.

SQL Commands:  

DDL (Data Definition Language):

DDL commands are used for creating and altering the database and database object in the relational database management system, like CREATE DATABASE, CREATE TABLE, ALTER TABLE, etc. The most used DDL commands are CREATE, DROP, ALTER, and TRUNCATE.

CREATE: 

This command is used to create the database and its objects like a table, index, view, trigger, stored procedure, etc.

Example:

CREATE TABLE Student(roll_no number(5), Name VARHCAR2(20), Address VARCHAR (50));

ALTER: 

This command is used to alter the existing database or its object structures.

Example:

ALTER TABLE Student ADD (PINCODE VARCHAR2(6));

TRUNCATE: 

This command is used to remove all records from the table. TRUNCATE command empties a table.

Example:

TRUNCATE TABLE Student;

DROP: 

This command is used to delete objects from the database.

Example:

DROP TABLE Student;

DML (Data Manipulation Language):

This command deals with the manipulation of data present in the database. DML commands are used for adding, removing, updating data in the database system. The most used DML commands are SELECT, INSERT, UPDATE, DELETE

SELECT: 

Select command is used to retrieve records from one or more table.

Example:

SELECT * FROM student;

INSERT:

This command is used to add data to the database table.

Example:

INSERT INTO Student  VALUES (1, ‘Aman Kumar’, ‘Karnataka’, 576101);

UPDATE:

UPDATE command is used to update data in the database table. A condition can be added using the WHERE clause to update a specific row.

Example:

UPDATE Student SET Address = ‘DELHI’ WHERE roll_no =1;

DELETE: 

This command is used to delete records from the database table. A condition can be added using the WHERE clause to remove a specific row which meets the condition.

Example:

DELETE FROM Student WHERE roll_no =1;

DCL (Data Control Language):

DCL commands are used to make changes to a user’s permissions on a table. It is used to control the kind of data access to the database. Most used DCL commands are GRANT and REVOKE.

GRANT: 

GRANT is used to provide access right to the user.

Example:

GRANT SELECT ON Student TO user1;

REVOKE:

REVOKE command is used to take back access right from the user. It cancels access right of the user from the database object.

Example:

REVOKE SELECT ON Employee FROM user1;

 

TCL (Transaction Control Language):

TCL commands are used for handling transactions in the database. TCL commands can rollback and commit data modification in the database. The most used TCL commands are COMMIT, ROLLBACK and SAVEPOINT 

COMMIT: 

COMMIT command is used to permanently save any transaction into the database.

When we use any DML command  like INSERT, UPDATE or DELETE, the changes made by these commands are not permanent, until the current session is closed, the changes made by these commands can be rolled back.

To avoid that, we use the COMMIT command to mark the changes as permanent.

Example:

DELETE FROM Student WHERE roll_no =1;

COMMIT;

 

ROLLBACK: 

ROLLBACK command is used to undo the modification.

Example:

DELETE FROM Student WHERE roll_no =1;

ROLLBACK;

 

SAVEPOINT: 

SAVEPOINT command is used to temporarily save a transaction, the transaction can roll back to this point when it's needed.

Example:

SAVEPOINT A;

DELETE FROM Student WHERE Address = ‘KARNATAKA’;

SAVEPOINT B;

DELETE FROM Student WHERE Address = ‘DELHI’;

ROLLBACK TO B;

COMMIT;

Will undo the second delete operation.

SQL Joins:

The SQL Joins statement is used to combine records from two or more tables into a new table. A JOIN is a means for combining fields from two tables by using values common to each. SQL Joins are used to retrieve data from multiple tables.

There are different types of joins available in SQL:

Inner Join: It is the simple and most common type of SQL join. Inner Join returns all rows from both the tables that satisfy the specified join condition. This join match records in both tables.

Left Join: This join returns all the records from the left table and matching rows from the right table. The rows for which there is no matching row on right side, the result-set will contain null. Left Join is also known as Left Outer Join.

Right Join: This join returns all the records from the right-hand table and matched rows from the left-hand table. The rows for which there is no matching row on left side, the result-set will contain null. Right Join is also known as Right Outer Join.

Full Join: This join returns all the rows from both the tables. The rows for which there is no matching, the result-set will contain null values. Full Join is also known as Full Outer Join.

SQL Index:

An index is a schema object. A SQL index is used to retrieve data from the database very fast. It is a data structure that the database uses to find records more quickly.

The Index in SQL is a special table used to speed up the searching of the data in the database tables. Indexes are built on one or more columns of a table. Each index maintains a list of values within that field that are sorted in ascending or descending order. An index is simply a reference to data in a table. The index concept in SQL is same as the index concept in the novel or a book.

For example, in order to reference all pages in a book that discusses a certain topic, we go to the index first, which lists all the topics alphabetically and is then referred to one or more specific page numbers.

SQL Indexes can search the information of the large database quickly. Creating an index involves the CREATE INDEX statement, which allows us to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order.

SQL Views:

Views are logical tables extracted from existing tables. It can be queried just like a table, but does not require disk space. A view is simply a SQL query stored as an object.

A view is a virtual table that contains data from one or multiple tables. Like a table, a view contains rows and columns. To create the view, we can select the fields from one or more tables present in the database.

The following are the main advantages of the view:

1. Views are virtual or logical table and do not occupy space in systems.

2. Views enable us to hide some of the columns from the table.

3. It simplifies complex queries because it can extract data from multiple tables and present it as a single table.

4. It helps in data security that shows only authorized information to the users.

Creating Views: 

A view can be created with the CREATE VIEW statement. Views can be created from a single table, multiple tables or another view. To create a view, a user must have the appropriate system privilege according to the specific implementation.

Syntax:

CREATE VIEW view_name as query;

Example:

CREATE VIEW EMP_VIEW AS

SELECT emp_no,emp_name from emp;

To Display the View:

Syntax:

SELECT *FROM view_name;

Example:

SELECT *FROM emp_view;

Deleting Views: 

We can drop the view if it is no longer needed. A view can be deleted using the Drop View statement.

Syntax:

DROP VIEW view_name;

Example:

DROP VIEW emp_view

 

Updating Views:

There are certain conditions needed to be satisfied to update a view. If any one of these conditions is not met, then we will not be allowed to update the view.

1. The SELECT statement should not have GROUP BY clause.

2. The SELECT statement should not have ORDER BY clause.

3. The SELECT statement should not have the keyword DISTINCT.

     4. The SELECT statement should not have set operators (Union, intersect, minus).

     5. The view should not be created using nested queries or complex queries.