Uses of SQL

Introduction to Uses Of SQL :

This article will brief you in details about Uses of SQL in practical life and few of its use cases. So before digging deep inside the uses of SQL, we should know a few things about it. SQL stands for Structured Query Language. Donald D. Chamberlin and Raymond F. Boyce after learning about the relational model in early 1970s developer a query language popularly known as SQL at IBM. This version was initially called as SEQUEL (Structured English Query Language) was designed to retrieve and manipulate data stored in IBM’s quasi-relational database management systems developed during the 1970s. It is a language used programming and designed for managing data that are stored in RDBMS (relational data stream management system). It is particularly useful in handling structured data, by structured data it means the data that can be stored in table format. Other forms are semi-structured data (ex: xml etc.) and unstructured data (ex: audios, videos etc.). Originally based on relational algebra and tuple relational calculus, Uses of SQL consist of DQL, DDL, DCL, and DML. Thus the scope of SQL includes data query, data manipulation, data definition, and data access control.

SQL is a declarative language which means it is a programming paradigm, a style of building the structure and elements of computer programs that expresses the logic of a computation without describing its control flow. In 1986, it became a standard of the American National Standards Institute (ANSI), and of the International Organization for Standardization (ISO) in 1987. Since then, the standard has been revised many a time to include a larger set of features including the existing ones. Even though SQL was made standardize long ago still most SQL code is not completely portable among different database systems without adjustments. As I have already mentioned that SQL is designed for a specific purpose: to query data contained in a relational database. However, in addition to the standard SQL/PSM extensions and proprietary SQL extensions, procedural and object-oriented programmability is available on many SQL platforms via DBMS integration with other languages.

Some of the famous Databases are listed below:

Source	Common name	Full name
ANSI/ISO Standard	SQL/PSM	SQL/Persistent Stored Modules
Interbase / Firebird	PSQL	Procedural SQL
IBM DB2	SQL PL	SQL Procedural Language (implements SQL/PSM)
IBM Informix	SPL	Stored Procedural Language
IBM Netezza	NZPLSQL[20]	(based on Postgres PL/pgSQL)
Invantive	PSQL[21]	Invantive Procedural SQL (implements SQL/PSM and PL/SQL)
Microsoft / Sybase	T-SQL	Transact-SQL
Mimer SQL	SQL/PSM	SQL/Persistent Stored Module (implements SQL/PSM)
MySQL	SQL/PSM	SQL/Persistent Stored Module (implements SQL/PSM)
MonetDB	SQL/PSM	SQL/Persistent Stored Module (implements SQL/PSM)
NuoDB	SSP	Starkey Stored Procedures
Oracle	PL/SQL	Procedural Language/SQL (based on Ada)
PostgreSQL	PL/pgSQL	PostgreSQL (implements SQL/PSM)
SAP R/3	ABAP	Advanced Business Application Programming
SAP HANA	SQLScript	SQLScript
Sybase	Watcom-SQL	SQL Anywhere Watcom-SQL Dialect
Teradata	SPL	Stored Procedural Language

Uses OF SQL in details:

Examples given below are based on dummy table with table name “student_records” having columns id, name, address, and mobile.

Constraints: “id” is used as the primary key of the table and one more column with name “mobile” and this column contains only unique data;

1. DQL:

It stands for Data Query Language. It is used to retrieve data from the database.

The SQL statement is SELECT.

Example:

SELECT * from student_records;

2. DDL:

It stands for Data Definition Language. This is used to define database schema, thus it deals with the description of database schema and is used to create and modify the structure of database objects in the database. Thus the SQL statements are CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME.

Example:

CREATE TABLE student_records
(id integer NOT NULL DEFAULT,
name character varying,
address character varying,
mobile numeric,
CONSTRAINT student_records_pkey PRIMARY KEY (id)
)
DROP TABLE student_records;
ALTER TABLE student_records ALTER COLUMN mobile character varying;

3. DML:

It stands for Data Manipulation Language. It is used to store, modify, delete and update data in the database. Thus the SQL statements are INSERT, UPDATE and DELETE.

Example:

INSERT into student_records values (1,”name”,”address”, mobile);
UPDATE student_records set address = “new address” where name= “name”;
DELETE from student_records where mobile = [enter_mobile_number];
TRUNCATE table student_records;

4. DCL:

It stands for Data Control Language. It is used to grant access to data stored in the database.

SQL statements are GRANT and REVOKE.

Syntax:

REVOKE privilege_name
On object_name
From {user_name | PUBLIC | role_name};
GRANT privilege_name
On object_name
To {user_name | PUBLIC | role_name}
[WITH GRANT OPTION];

**NOTE: To use GRANT command specific role_name should be created first.

5. Database Transaction Management:

Transaction Management means to maintain the transaction related to the database i.e. following the basic rules for ACID properties of the database. The transaction has only two results i.e. either success or failure. Thus the SQL Statement is TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT.

6. Procedures, User-defined Functions, Triggers, Indexes, and others:

We can write procedures, user-defined functions, triggers, indexes, cursors as per the requirements which is nothing but SQL statements to make our work easy to meet the business requirements.

7. Reporting Purpose:

SQL queries are very important from report perspective which every project have. We can write queries for standalone reports also for fetching data for the report.

8. Manual Analysis:

SQL queries are very important for analysis when manual interventions are necessary. With the uses of SQL queries we can filter out the necessary data from the structured data and it could be used for analysis.

9. SQL with NTC Hosting:

Even the MySQL hosting service provides the ability for the construction of big and powerful websites, web-based applications and programs. MySQL open source database solution and insist on speed, stability and scalability, then MySQL hosting solution is needed.

10. SQL Join:

A SQL join is an instruction to combine data from two sets of data (i.e. two or more tables).

• (INNER) JOIN: Returns matching values in both tables as output.
• LEFT (OUTER) JOIN: It returns all records of the left table and the matching records of the right table as output.
• RIGHT (OUTER) JOIN: It returns all records 0f the right table, and the matching records of the left table as output.
• FULL (OUTER) JOIN: It returns all records if there is a match in either of the table i.e. left or right.

11. SQL Union:

The result-set of two or more SELECT statements can be joined by UNION.

12. SQL Wildcards:

A wildcard character is a special character in SQL which are used to substitute any other character(s) in a string.

SQL Wildcard Operators:  ‘%’ and ‘_’ are called wildcards operators.

NOTE:

• % – Representing zero, one, or multiple characters in a character value ( used while filtering).
• _ – Representing a single character.

Conclusion – Uses of SQL

So it can be concluded at the end that even today with the fast-growing technologies and evolution of no SQL databases in the market, Uses of SQL still plays an important role for structured data. SQL provides flexibility of querying the tabular data with SQL queries which is a great help in many ways.

Recommended Articles

This has been a guide to Uses of SQL. Here we have Explained the different uses of SQL with examples. You may also look at the following articles:

1. SQL Server vs PostgreSQL
2. IS SQL Microsoft?
3. Cheat Sheet MySQL
4. Careers in SQL Server