SQL : Basic Concepts of Query Processing

Are you wondering about SQL, Query, Database Query, or Query processing? Haven’t you heard about these SQL terms before? No problem! Let’s come over here and explore every term related to SQL.

Table of Contents

Structured Query Language

SQL  – Structured Query Language (SQL) deals with SQL queries and managing SQL queries to retrieve the data from the database.

What is a database in SQL?

A database is a collection of tables in SQL that contains data in a structured way. A table contains rows and columns, also known as tuples and attributes.

What is Database Query?

The collection of organized SQL commands written to perform a specific task. In other words, a database query is a request sent for data from the database user to the database server.

Example:

				
					SELECT name, class
FROM student_data
WHERE section_id = 2;

				
			

The following SQL statement selects all the students from the section_id = 2 in the student_data table.

What is the aim of query processing?

Query processing aims to convert high-level language to low-level language with an efficient plan at minimum cost for retrieving data from the database per the user’s requirement.

Query Processing Steps

query-processing-steps

 

Explanation:

Scanner – The scanner identifies the language terms such as SQL Keywords, attribute names, and relation names in the text of the query.

Parser – The parser checks the query syntax to determine whether it is defined according to the syntax rules of the query language.

Validation – The query must be validated by checking that all attributes and relation names are valid and semantically meaningful names in the database being queried.

Query Optimization – The process of choosing a suitable execution strategy for processing a query. This module has the task of producing an execution plan at low minimum cost.

Query Code Generator – It generates the code to execute the plan.

Runtime – It has the task of running the query code whether in compiled or interpreted mode,lf a runtime error results an error message is generated by the runtime database processor.

Points to be remembered:

  • The main aim of query optimization is to choose the most efficient way of implementing the relational algebra operations at the lowest possible cost.
     
  • The query optimizer should not depend solely on heuristic rules. Still, it should also estimate the cost of executing the different strategies and find the strategy with the minimum cost estimate.
     
  • The cost of an operation is heavily dependent on its selectivity, that is, the proportion of select operation(s) that forms the output. 

Purpose of Query Optimization

  • The optimizer attempts to generate the best execution plan for a SQL statement.
     
  • The best execution plan is the plan with the lowest cost among all considered candidate plans.
     
  • The cost computation accounts for factors of query execution such as I/O, CPU, and communication.
     
  • The best method of execution depends on various conditions, including how the query is written, the size of the data set, the layout of the data, and which access structures exist.
     
  • The optimizer determines the best plan for a SQL statement by examining multiple access methods, such as full table scans or index scans, and different join methods, such as nested loops and hash joins.
 

Query Processing Phases

Query processing is mainly divided into four phases:

  • Decomposition
  • Optimisation
  • Code generation
  • Execution

Decomposition: Decomposition is the first phase of query processing, here the high-level language is into a relational algebra query and checks whether the query’s syntax, name, and attribute are correct or not. The main stages of decomposition are:

  • Analysis ( Programming Compiler analyzed the lexical and syntactical correctness
    of query)

  • Normalisation (Convert query into normalised form for easy manipulation)

    Two Types
    ○ Conjunctive Normal Form (connected with AND operator)
    ○ Disjunctive Normal form (Connected with OR operator)

  • Semantic analysis (Reject incorrectly normalised query)

    Mainly by using two graphical techniques
    ○ Relation connected graph (showing the incorrect query).
    ○ Normalised attribute connection graph (showing query is contradictory)

  • Simplification
    ○ Detect redundant qualifications
    ○ Eliminate common sub-expressions
    ○ Transfer the query into semantically equivalent
    ○ Access restriction
    ○ View Definitions
    ○ Integrity Constraints

  • Query Restructuring (Query is restructured for providing efficient implementation)

Looking for Quiz: SQL Quiz: Basic Concepts of Query Processing

Conclusion:

So, we have gone through the basics of SQL; it will help you somewhat to be clear with the basics of SQL. Started from SQL to ending to Query Processing Steps, including Database Query.

Please comment below if you find anything incorrect in the above-discussed topic and have further questions.

Connect on:

Recent Post

Popular Post

Top Articles

Archives
Categories

Share on