SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

SQL Formatter

As a beginner, one does not focus much on Formatting the queries while writing their code. This leads to a great confusion while referring the code in future. For a better readability, we beautify the SQL statements using SQL Formatter. In view of user’s perspective we format the SQL queries.

The formatting is done by applying indentations, spaces in between, and tabs between the statements.

For example, let’s consider a small SQL query for better understanding.

select * from employee ORDER BY e_salary;

This can be formatted as,

select 
   *
from
   ORDER BY e_salary;

Let’s look another complex example.

select * from employee where EXISTS (select * from employee, department where employee.id = department.id and department_name=’cse’ 

By formatting the above query,

(select 
   *
from
employee
where EXISTS
(
	select 
	   *
           from
           employee, department
	where
	employye.id = department.id
	and
	department_name = ‘cse’

In this way we can understand the code more efficiently.

By this formatting we can understand the query more easily. So, for this purpose we use formatting Tools.

SQL formatter is a beautifying tool for large SQL queries. We have many different tools for formatting SQL queries. Some of the tools are

  1. Atom Beautify SQL Formatter
  2. Poor Man’s T-SQL Formatter
  3. Code Beautify SQL Formatter
  4. Instant SQL Formatter

Now let’s have a look of how these tools work.

Atom Beautify SQL Formatter:

This tool is used for formatting all languages like sql, html, css etc... After installation, one can use command Palette to choose the editor for a specific language. Then we need to select the code which is to be formatted. And after the selection, by clicking on Beautify On Save we can get the results.

Poor Man’s T-SQL Formatter:

This tool helps us in handling complete multi-batch scripts, including object definition scripts such as stored procedures, triggers etc...

We should use this tool by launching SSMS. Then, from tools select format T-SQL code.

Code Beautify SQL Formatter:

This tool is Specially for SQL queries. To Beautify large SELECT statements, we use Code Beautify SQL Formatter. This tool indents the code and spreads the code into multiple rows.

Instant SQL Formatter:

This tool gives out a clean, well-structured script as an output from the unorganised script. This tool not only formats SQL code but also this tool can convert between SQL and C#, java.

Indentation of code:

In this block we have 3 stages of indentation.

  1. None
  2. Block
  3. Smart

None:

If we select None, the cursor points to the starting point of the next line.

Block:

If we opt Block button, the cursor points over the previous line indentation in the next line.

Smart:

This option decides the indent styles automatically. This works on a query by default.

Rules for Formatting the SQL Code:

 Following standard rules for SQL formatting gives best results in greater efficiency.

Object Naming:

  • One should use a singular name for table/column.
  • Do not use SQL Keywords as Table name / column Name. If we use SQL keywords as the table name, then we should enclose the name with quotation marks.
  • Do not start a table/column name with an underscore.
  • Prefer using AS keyword mostly, as this increases the readability of the code.
  • Do not give same name for both table and column to avoid confusion.
  • Do not use special characters like  ‘&’, ‘$’, and ‘*’ .

Alignment:

  • While formatting, every Keyword should start in the next line.

Ex:

select student_name , 
            student_ID
from student;

Comments:

One should not write too many comments to reduce the complexity.

If it is necessary to include comments, then prefer Multi-line comments.

Ex:

select  person_age, 
	person_DOB    
                 /*Age, DOB are the columns in Person Table -- Multi-Line Comments*/
from Person;

Indentation:

One needs to use a new line after each query. It looks good if one uses a new line after every comma also.

Queries of SELECT:

For writing a select statement, if the select statements consists of many columns, then every column should be written in a new line with a comma at the end of the line. comma should not be placed at the starting of next line.

Ex:

select person_ID,
	person_age,
	person_name,
	person_DOB
from Person;

Queries of WHERE:

If a query has a where statement, then it should be written in the next line without any indentation.

Ex:

select person_ID,
	  person_salary
from Person
where person_Name = “Dhruv”;

Queries of INSERT:

For inserting records into a table, we use INSERT keyword. Indentation should be followed.

Ex:

Insert into Person(person_ID, person_Name, person_age) values
	(1, ‘Dhruv’, 12),
	(2, ‘Ayaan’, 17);

Queries of UPDATE:

For Updating the values in the table, we use UPDATE keyword and SET keyword. So, every time when we update, the set keyword should start from a new line.

Ex:

Update Person
Set person_ID=101
Where person_name = ‘Dhruv’;

Long SQL Query:

Long SQL queries contains many sub queries in it. So, formatting is necessary in such cases. Every sub query must be intended on a new line.

Ex:

select person_ID,
	person_Name
	case
	    when  person_age < 18 then ‘below 18’
	    when person_age >= 18 then ’18 or above’
	end as Age
from Person;

Join Statements:

We Join two tables using INNER JOIN, OUTER JOIN, INNER JOIN. Along with these we use ON Operator.

So, ON operator should be written on a new-line.

Ex:

select e.ID,
	e.Salary,
	d.ID
from  Employee as e
JOIN Department as d
     ON e. Name = d.Name;

NOTE:All SQL Keywords like SELECT, WHERE, HAVING, ORDER BY, GROUP BY should be written in a new line.

Usage of SQL formatter tools:

If the SQL queries are small and less complex, we can indent them manually and understand the query.

But In case of large databases, we write complex queries. So, it becomes difficult for us to format/indent the queries manually.

Advantages of SQL Formatter:

  • This tool transforms queries into more flexible way to read and understand them.
  • When a query is formatted with required spaces, it becomes easy to debug the error and to fix them.
  • It increases the efficiency of the Database Transactions.
  • It helps in scaling the Database.



ADVERTISEMENT
ADVERTISEMENT