An Introduction to SQL – its Role & Properties

All of us have come across this word database many a time. Any idea about how it is built? Do you want to create and operate your own information in a database? Then you have clicked the right site. Basically all you need is a language to communicate with the database. SQL helps to deal with it.

What is SQL?

• SQL is short for Structured Query Language, is a standard query language which lets you access and operate data stored in the databases.
• SQL is not a database but a database language used to manage data from Relational Database Management Systems such as MySQL, Oracle, Informix, Sybase,  Postgres, DB2, MongoDB and SQL Server.
• SQL is spelled as S-Q-L but it is also pronounced as SEQUEL (see-kwel).
• It is an ANSI(American National Standards Institute) standard.
  Note – Although SQL is an ANSI standard, the majority of the database engines have their own syntax. But to be compliant with the standard they all follow some basic commands like Select, Insert, Delete, Update, Where etc as per the standard.

Through SQL we can –

• Execute queries against a database i.e. basic CRUD operations.
• Create new databases
• Create new tables in a database
• Retrieve data from a database
• Insert records in a database
• Delete records from a database
• Update records in a database
• Create views in a database
• Create stored procedures in a database
• Set permissions on tables, procedures, and views.

Database and Relational Database

• The database is a set of data organized in a particular way such that it provides efficient retrieval to the computer.
• Relational Database is defined as a collection of data items organized as a set of formally-described tables from which any data can be retrieved without reorganizing it.

What is RDBMS(Relational Database Management System)

✓ RDBMS is a system for creating and manipulating database similar to DBMS but the applications here are stored in the form of tables. It is also termed as database engine/system and is based on the relational model which is specified by Edgar F. Codd. A table is basically a collection of related data entries composed of records(rows) and fields(columns).

Examples of RDBMS have already been mentioned above. Let me insert the statistical analysis of RDBMS to understand its scope in recent technologies.

Role of SQL:

• Very Interactive query language,  flexible, convenient and easy-to-use tool
• It is a Database gateway language, which allows one DBMS to communicate with another.
• It serves as Client/Server language, communicates over a network with DB servers that stores shared data.
• It is an Internet data access language, web servers and application servers use SQL embedded with PHP/Perl
• It acts as Distributed database language, Distributed DBMS use SQL to share data across many connected systems.

Properties of SQL – ACID:

There are some properties to remember while using a database and performing operations on it. These are used while evaluating the database-

• ‘A’ stands for ATOMICITY – It states that either the entire transactions take place at once or does not happens at all or also termed as “all or none property”.
  Example:
  Consider in a banking system, Account P has Rs.200 and Q has Rs.500. P is transferring Rs.100 to Q. That is firstly it debits Rs.100 from P and credits that amount to Q. If P is successfully debited but second operations failed. Then P would contain Rs.100 whereas Q stills remain with Rs.500. This is unacceptable therefore atomicity property should ensure it.

• ‘C’ stands for CONSISTENCY – It is a guarantee that the transactions never leaves or stops in a half-finished state.
• ‘I’ stands for ISOLATION – It means one transaction should start execution only when the other finished execution for every pair of transactions.
  Example:
  Consider the same example, Suppose Q is transferring Rs.100 to both P and another account (Say R).If both these transactions run concurrently and both read Rs.500 balance, then the final balance of Q would be Rs.400 instead of Rs.300. This is wrong and isolation should ensure that the second transaction reads the correct balance. In this case, consistency should also be checked.

• ‘D’ stands for DURABILITY – guarantees that the database will keep track of updates and modifications in such a way that the server can recover from an abnormal termination.

SQL Process Flow:

When SQL query is executed, it first calls the database server. The work of server is managing database, transaction control, load balancing etc. The server fetches data from a physical database(DB) and returns the result.

Gaining knowledge is the first step to wisdom!! Sharing is the first step to humanity!! ☺
Do share and subscribe and comment below for any suggestion, query or opinion.
Keep Coding! Happy Coding 🙂

Recommended -