Here are tips on how to format SQL query to spot errors. You need practice to write SQL query correctly. Experience is prime thing to write SQL in correct format. This makes other developers in the team can understand quickly.
Often software developers miss the formatting technique.
SQL Formatting Techniques
1. Wrong Format
2. Where to put commas in SQL Query
Select School, College, University, From CollegeTable;
2. Correct Format
Select School ,College, ,University From CollegeTable;
The commas ‘,’ in left ending is good practice. The reason is you can spot error quickly.
3. Sort Correctly
3. Correct Way of Sorting
To sort the result, you need to use ‘ORDER BY’. The default is Ascending. If you give number in ORDER BY, it sorts that number column.
Example is ORDER BY 2, means, it sorts the result based on second column.
All the NULL values placed lost during Ascending Sort. The DESC is for Descending sort.
4. Major Vs. Minor Sort
4. Major Vs Minor Sort
If you mentions two columns in ORDER BY, The sorting on FIRST column is MAJOR sort. The sort on SECOND column is called Minor sort.
- The ORDER BY clause sorts the result based on Alphabetical Order.
- To carry flexible sorting, you can use CASE in ORDER BY. So that you can sort based on CASE Condition.
5. Use Alias to Simplify Naming
5. Alias – rather than Column Name
You can use alias for actual column names. Example is ‘School S’. S is here Alias. So that you can save time and identify quickly during debugging.
- Column Name
- Data Types
Make sure that, all the SQL elements must be formatted Correctly. So that you can trace error easily.
Example of neatly formatted SQL
CREATE TABLE CUSTOMER ( CustomerID INTEGER NOT NULL, FirstName CHAR (15), LastName CHAR (20) NOT NULL, Street CHAR (25), City CHAR (20), State CHAR (2), Zipcode CHAR (10), Phone CHAR (13) ) ;
- How to Derive Year from DATEs Useful MySQL Query
- Linux How To Understand These BOOT, START, SHUTDOWN
- How to Define Various VSAM Clusters with IDCAMS