Not to mention whether you're working on programming or not learning about SQL, but virtually every developer will eventually encounter it. You

Programming and learning SQL is divided into three parts, which covers the basics of SQL standards. In the last article we discussed some database terminology and 4 basic data Query types. In addition, we explain the use of WHERE clauses and conditional statements, and we provide specific examples of various types of queries.


In this article, we will explain some of the other SQL functions and clauses for you to use in basic select data queries.


Select option Refine Results
As we have read from the previous article, the SELECT statement has a wide variety of options that can be used to control how data is returned. These options exist in the form of clauses, keywords, and functions.

A clause is a statement that modifies the result. clause is not a necessary statement, but it refines the content of the data and its display. The WHERE clause is such a clause.

Keywords trigger the intrinsic function of the database. These keywords are sometimes necessary even for queries. For example "INSERT into table_name (column1) VALUES (' data1 ');" This is the case for into and value in the statement. We'll learn about distinct keywords, which can trigger some very useful optional features.

Some of the most commonly used clauses, keywords, and functions are summarized below. Then I will give an example of each part.

Order by– The clause that returns the result according to the specified column
distinct– only keywords that return only the only rows within the result set
COUNT--A function that returns the number of data rows in a matching query
avg– This function returns the average of the specified column
sum– This function adds up the numbers in the specified column
min– This function to return the smallest non-null value in a column
max– This function to return the maximum value in a column
GROUP by– A clause that collects the results of a query function by column
Sort query results with ORDER BY
The ORDER BY clause lets the database sort the query results so that you do not have to write your own application for "manual" sorting. The ORDER BY clause must be placed at the end of the query statement. Its basic usage is as follows:

SELECT * from Contacts order by first_name;

You can use the ORDER BY clause to return multiple columns of results in any selection statement. You can also use it to connect to other clauses:
SELECT first_name, last_name from Contacts WHERE first_name BETWEEN ' a ' and ' K ' ORDER by last_name;

You can sort multiple columns of data. Precedence is dropped from left to right, so the order of the columns in the query statement is important.
SELECT * from Contacts-Company, last_name, first_name;

The query results are sorted by default in ascending numbers or letters. You can change the ORDER BY clause by adding DESC keywords to the descending order. In the following example, the highest net_amount is in the first (descending) order. If two or more rows of data contain the same Net_amount value, the last_name value in the peers first appears in the alphabet, because the last_name column is sorted in ascending order.
SELECT * from Sales ORDER by Net_amount DESC, last_name, first_name;

After sorting by the column names that you define, most of the databases will then be sorted by the first column in the datasheet followed by the order to the right. Specific implementations vary, so if sorting is more important in the application then you should explicitly define the columns to be sorted.

Another noteworthy problem is that with the ORDER BY clause (and the WHERE clause), the data column you are using to sort the results is not necessarily a part of the return result set. The following example is completely valid as long as all referenced columns exist in the datasheet:

SELECT Company, first_name, Net_amount from Sales order by start_date, last_name;

Distinct returns no duplicate results


Distinct keywords only return data rows that are not duplicates within the result set. For example, sometimes you might want to find a company in the sales table, but you don't want to see each entry. So you can use distinct to return one row of data for each company name:

SELECT DISTINCT company from Sales;

When using distinct, it applies to all request columns. If you plan to list all the salespeople in the table and the companies they represent rather than each sales record, you can use the following statements. Note that this can also return several entries for the same company, and so on.

SELECT DISTINCT Company, last_name, first_name from Sales;

You can also use distinct in conjunction with SELECT statements when narrowing and sorting results. To determine what is displayed, the database first confirms that the refined request matches the data row and then applies the distinct feature. The ORDER BY clause is processed after all the result sets are determined. As the following example shows, only data rows with Net_amount greater than 100 are returned. Because the distinct retains the 1th row of data that matches the query criteria and discards the other matching rows, the Net_amount referenced by the ORDER BY statement looks as if it produced a random result.

SELECT DISTINCT Company, last_name, first_name from Sales WHERE net_amount > m by Company, Net_amount;


Function application logic
A function that returns a single value is called a clustered function (aggregate function). When you access the results of the following aggregate functions through your application, the "field name" that contains the result is the actual function you are using. For example, when you analyze your database results, the key values of the resulting array might look like this:

$keyname = "COUNT (*)";
$resultkey = "AVG (net_amount)";

COUNT
The Count function calculates the number of rows of data in the result collection. Like other functions, it takes a parameter. The following basic example tells you the number of rows in the datasheet: SELECT COUNT (*) from Sales;

You can also use it to compute the number of rows in any result set.

SELECT COUNT (*) from Sales WHERE net_amount > 100;

If you want to see how many rows of a particular column contain non-null values, you might use the Count function on that column. Note that the total number of data rows in the table is returned unless the database is set to the default padding null when the field is empty. In addition, listed columns can cause errors if they exceed one.

SELECT COUNT [Company] from Sales;

Count can also be used to calculate the number of rows in the distinct result collection.
SELECT COUNT (DISTINCT Company, last_name) from Sales;

The count statement is typically used in a program to determine the number of loops in a for loop.


Avg
AVG Returns the average of all the fields in a column, which must be a numeric data type. The function uses the name of the column as its argument, and the function returns "0" If the column field data type is a non-numeric type. SELECT AVG (Net_amount) from Sales;

You can limit the scope of the function by combining the clauses.

SELECT AVG (Net_amount) from the Sales WHERE company like '%abcd co% ';

Just like all clustered functions, the order BY statement is ignored.

SUM

Sum works the same way as AVG, except that the function returns the sum of all the field values in the result set.
SELECT SUM (net_amount) from Sales WHERE net_amount > 100;

The AVG, SUM, Min, and Max functions return an error without specifying a column, so you cannot use the "*" wildcard character.

MIN
Min Returns the smallest non-null value in the specified column. If the specified column is a numeric data type, the result will be the smallest number. If it is a string data type, the function returns the 1th value that appears in alphabetical order. SELECT MIN (net_amount) from Sales WHERE last_name = "Smith";
SELECT MIN (last_name) from Sales;


MAX

Max works just like the Min function, except that the function returns the largest Non-empty value. This function can also be used for strings or numeric columns
SELECT MAX (Net_amount) from Sales;
SELECT MAX from Sales WHERE net_amount > 100;

The Max function is also sometimes used to determine the next destination key ID on a column that contains an AutoIncrement key field. Unless you are running a private database, be careful when you use this information to insert the next entry, in case the other user first performs the data operation.
The GROUP by function is more useful


Although all of the above functions provide fairly useful information, you can use these functions in the column's character set if you have a GROUP BY clause to help you. Do not perform the Max function query again and again on every company in your sales table-you can get the same result with the GROUP BY clause:

SELECT Company, MAX (Net_amount) from Sales GROUP by company;

Doing so will get the maximum value for each company's Net_amount. You can also use this statement when selecting multiple column names, and you can also use Dorelle to group function results.

The following examples illustrate each of these ways. First, including the GROUP BY clause allows you to specify additional columns to display. However, you need to know that this example returns the 1th last_name value encountered in the group; Sum (Net_amount) shows the results of all companies and not just the rows of data that match last names. This is because we only use the company field to define our group.

SELECT Company, last_name, SUM (net_amount) from Sales GROUP by company;

In the above example, the Last_Name column does not actually provide any useful information, but it is intended to prepare the functionality to be used in the next example. You can create multiple-column-defined groups. This allows you to produce function results for a particular row in the result set, and the result set is created by all the specified group by columns:

SELECT Company, AVG (Net_amount), last_name from Sales GROUP by company, Last_Name;


The example above gives the average net_amount for each surname in each company. The order in which you list the group By column controls the ordering of the results, but the actual function value results are the same.


The following example shows how to organize the results without displaying the grouped columns. It is useful to do this on some occasions, for example, if you want to display an individual's sales but do not display a name, you can use the following example:

SELECT Company, COUNT (sale_id) from Sales GROUP by company, Last_Name;


Restricting queries using GROUP by
As you can see in the example above, you could use the WHERE clause to limit the scope of the query using the above concepts. The WHERE clause is evaluated first, and then the function is executed. That's what happens when you use a group.

SELECT Company, AVG (Net_amount), from the Sales WHERE net_amount > GROUP by Company;

The above example applies the AVG function only to rows of data that meet the where constraint conditions. Note that the WHERE clause must be placed before the GROUP BY clause. You can also use the having statement to restrict the returned collection of results after the grouped calculation.

SELECT Company, AVG (Net_amount), from the Sales WHERE last_name BETWEEN ' a ' and ' m ' GROUP by the company have AVG (Net_amount) &G T 500;


The above statement calculates the average number of net_amount per company and only calculates the sales of those salespeople whose last names meet the restrictions, and displays only results that are greater than 500.