Dotnet Stuff

This section is to introduce you to the SQL Basics. SQL is the standard query language designed for relational database operations. At the end of this article, you will get a brief and clear understanding on what is SQL?
This post addresses following topics,

Introduction To SQL
Why SQL? What SQL Can Do?
SQL Processing stages

Introduction To SQL

SQL stands for Structured Query Language. SQL is a programming language for retrieval and management of data in Relational Database systems. SQL is an ANSI (American National Standards Institute) standard.

SQL is the usual query language for all relational database management systems such as SQL Server, MySQL, MS Access, Oracle, Postgress, Informix and Sybase.

SQL consists of data definition and data manipulation languages. Using the data definition language of SQL, one can design and modify database schema, whereas data manipulation language allows SQL to insert and retrieve data from databases.

There are many different versions of the SQL such as,

JET SQL for MS Access
T-SQL for MS SQL Server
PL-SQL for Oracle

Why we need SQL? What Can SQL do?

SQL is the language of communication in relational database management world.SQL statements enable the users to perform tasks such as creating relational databases and manipulating the data in databases.

SQL is required for the following database tasks and more,

• Create new databases and drop/delete databases
• Create tables,Update table nad delete tables in database
• Create Views,stored procedures,functions, and triggers in database
• Set permissions on tables, procedures and views
• Insert,Update data in database and Delete data from database
• Data extractions and manipulations by executing queries against a database

Stages of SQL Processing

Image Source : Docs.Oracle.com

SQL statement processing flow happens in the following stages ,

1. SQL parsing
2. SQL Query optimization
3. Row source generation
4. SQL statement Execution

All the above stages are not for all statements.Depending upon the type of statements,some of these stages will be excluded by the database.

SQL Parsing :
As soon as an SQL statement is issued,the first step is to parse the SQL statement.The SQL processing stage involves in separating the parts of a SQL statement into a data structure which other routines can process.

The application invoke a parse call to the respective database to prepare the SQL statement for execution.There are two types of SQL parsing – hard parsing and soft parsing.

Hard parse happens when the SQL statement to be executed cannot be shared .That is, the statement is not in the shared pool or the statement is in a shared pool but can’t be shared.

Soft parsing is when a session attempts to execute a SQL statement which can be shared and that is in a shared pool.Soft parse does direct statement execution since the database skips the optimization and row source generation steps during soft parse.

Database performs the following checks during SQL Parsing,

Syntax Check : Syntax correctness of the SQL statement is checked in this. Syntax is the grammar of the statement.

Semantic Check : The Semantic check determines whether an SQL statement is meaningful. Semantics is the meaning.

Syntax correctness of the statement not ensures semantic correctness.So a syntax correct statement can fail during semantic check stage.

Eg:- “Drop Table Emp;” is syntactically correct SQL statement. But can fail semantically, if there is no table named “Emp” in that database.

Shared Pool Check : Shared pool check to decide whether the database can avoid resource-intensive steps of the SQL statement processing.

SQL Optimization:In this stage, the database performs a hard parse at least once for every unique DML statement and performs the optimization during this parse.DDL statements are optimized only if it includes a DML component such as a subquery.

SQL Row Source Generation: The row source generation stage receives the optimal execution plan from the optimization stage.The row source generator makes an iterative execution plan that the database SQL engine executes and generates the result set.

Summary

This article covered SQL Basics such as What is SQL, Why SQL and different SQL Processing stages. Hope you enjoyed this article.Leave your feedback in the comment section below.