Syntax of SQL Calculations

Database applications can present the results of calculations and queries to users.

When developers create database applications they often use SQL statements. SQL statements can query data, insert new data records and edit existing items. SQL queries can include complex clauses designed to retrieve whatever data records an application task needs. SQL queries can also carry out calculations using either arithmetic operators or SQL functions. Most SQL queries, however complex, are a series of discrete, simple units.

Queries

An SQL query allows a computing program to retrieve a specific set of data items from a source. SQL query syntax uses the select clause as in the following example code:
SELECT first_name FROM client;

This query would work with a database in which a table named "client" has a column named "first_name." When it executes this query will return all of the records from the specified column. To retrieve everything from a particular table SQL queries can use the following code:
SELECT * FROM client;

This query will retrieve the records for every column in the table. The application can then process this data, for example by displaying it to the program user within a graphical user interface.

Arithmetic

SQL queries can use arithmetic operators to include numerical calculations. The following sample code demonstrates a simple SQL statement involving a calculation:
SELECT (4*8);

When this query executes it will return "32" as the result of the calculation. When using calculations in conjunction with database queries programmers can create complex results. The following sample code demonstrates querying a data record with a calculation:
SELECT (current_bill*0.9) FROM customer;

This technique could be used to show a customer the savings they could make after a 10 percent discount on their current bill. Calculations like this allow programmers to output calculated data without altering the existing data within the database.

Aggregate

An aggregate function in SQL performs calculations on the values within a database resulting in a single value based on these calculations. For example, the following sample code retrieves the smallest value in the set of database records specified using the table and column name:
SELECT MIN(current_bill) FROM customer;

This query would return the smallest bill total currently stored in the customer table. The following sample code demonstrates calculating the number of rows in a table:
SELECT COUNT(*) FROM order;

The result of this query is a number representing how many rows are listed in the table. Aggregate functions can calculate averages as well as first and last values.

Scalar

SQL scalar functions allow programmers to amend the results of a query using calculations. The following sample code demonstrates rounding the values from a particular column:

SELECT ROUND(current_bill, 0) FROM customer;

This query results in the current bill values rounded with no decimal places. SQL scalar functions can also carry out conversions on text values and return the lengths of particular database fields.

References

• W3Schools: SQL SELECT Statement
• MySQL Documentation: Arithmetic Operators
• W3Schools: SQL Functions
• W3Schools: SQL MIN() Function
• W3Schools: SQL COUNT() Function
• W3Schools: SQL ROUND() Function

Resources

• W3Schools: Introduction to SQL
• W3Schools: SQL Syntax
• Tizag: SQL Tutorial - Operators