Some Advanced SQL Queries

All topics about programming / development in T-SQL or other languages for SQL Server.
Post Reply
DaveP5
Posts: 29
Joined: Sat Jun 22, 2019 7:58 pm
Answers: 1

Mon Jun 24, 2019 2:27 pm

Database programming using SQL (Structured Query Language) is essential to build dynamic websites. Database servers are becoming more and more powerful by getting involved in computations rather than just passively storing data. This means, some of the computational tasks are being taken care of by the database servers themselves. This has been made possible by the usage of advanced SQL query types. Let us discuss advanced SQL query types.

1. SQL Queries Using 'Group By' Clause

Consider that a table stores names of students, marks and subjects. Writing a simple query to retrieve the names and corresponding marks is not difficult at all. But, if the requirement is to show student names along with average marks across subjects, a simple SQL query will not be enough. One way is to retrieve the entire data and perform the required computations in the business layer. If you had not known already, business layer is the one where server code (code written using languages like PHP,J2EE,Dot Net) resides. But, if you know to write queries having 'Group By' clause, you could very well do the computations in the database layer itself.

2. SQL Triggers

Queries are usually invoked by server programming languages like PHP,J2EE etc. However, there could be times when one has to invoke a particular query depending on the output of a previous query. These are kinds of automated tasks using SQL Triggers are handy in highly concurrent (busy) applications.

3. SQL Stored Procedures

Optimizing the number of lines of code is essential to improve productivity and to reduce throughput time. One of the factors that influence the number of lines of code is the size of SQL queries. Stored Procedures can significantly reduce the number of lines of code required by SQL queries. They are modular functions that can be called from anywhere with specified parameters. With stored procedures, the code becomes modular and reusable.

4. SQL Aggregate Functions

Aggregate functions help programmers to perform operations on an array of data in a column. They can operate simultaneously on multiple rows. An example for aggregate function is 'average()'. This function, when applied on an array of columns, the result would be a computed average of all the values. In the absence of such a function, the business layer programmer has to compute average using following steps. 1) Firstly, the programmer has to count the number of columns to be operated on. 2) Secondly, the programmer has to strip out null values from the selected columns. 3) Finally he has to calculate average using mathematical formulas.
Post Reply

Social Media