Quick Start for SQL language (iii)

SQL language | Getting started our day-to-day use of SQL language in the work process, the most use or from the established database to query information. Next, let's look at how to implement various database query operations using SQL language.

SELECT ... From

For ease of explanation, we create the following datasheet named Store_information in the database.

Store_information

Store_name
Sales
Date

Los Angeles
00
jan-10-2000

San Diego
0
jan-11-2000

Los Angeles
0
jan-12-2000

Boston
0
jan-12-2000


The simplest command for database queries in the SQL language is select ... From, the syntax format is:

Select "COLUMN_NAME" from "table_name"

For example, if we want to query all store names in the Store_information datasheet, you can use the following command:

SELECT Store_name from Store_information

The query results appear as follows:

Store_name

Los Angeles

San Diego

Los Angeles

Boston

If the user wants to query more than one field at a time, you can separate the field names you want to query by adding them to the SELECT keyword in the middle.

DISTINCT

The Select keyword enables users to query all the data for a specified field in a data table, but it can sometimes be unavoidable that duplicate information is present. You can use the DISTINCT keyword in the SQL language if you want to query only for information that has a different record value. The syntax format is as follows:

Select DISTINCT "column_name"

From "table_name"

For example, we can use the following command to query all records with different record values for the Store_information datasheet.

SELECT DISTINCT store_name from Store_information

The results of the query are as follows:

Store_name

Los Angeles

San Diego

Boston

WHERE

In addition to selecting records with different record values, sometimes we might want to query the data in the database based on certain criteria. For example, we might want to query a store that has a sales volume of more than 1000 dollars in the Store_information datasheet. To do this, we can use the SQL language where keyword to set the query criteria. The syntax format is as follows:

Select "Column_name"

From "table_name"

WHERE "Condition"

As a result, we can use the following command to query store information for more than 1000 dollars:

SELECT store_name from store_information WHERE Sales > 1000

The query results appear as follows:

Store_name

Los Angeles

Operation function

Now, we have learned that in SQL language database query operations can be used to determine the value of a flexible query criteria. To enhance the ability to support operations, SQL provides a wide range of practical operational functions for the user to use. For example, we can call the sum or AVG directly in the SQL command for the two functions that calculate the totals and averages, respectively. The syntax format is as follows:

SELECT ' function type ' (' column_name ')

From "table_name"

If we want to query the total sales of all stores in the Store_information datasheet, you can use the following command:

SELECT SUM (Sales) from store_information

The query results appear as follows:

SUM (Sales)

50

COUNT

In addition to the sum and AVG functions, the Count function is another more commonly used operation in the SQL language. The Count function can be used to calculate the number of records contained in a specified field in a datasheet. The syntax format is:

SELECT COUNT ("column_name")

From "table_name"

For example, if we want to query the number of records in a store_information datasheet about a store, you can use the following command:

SELECT COUNT (store_name)

From Store_information

The query results appear as follows:

Count (Store_name)

4

The Count function can be used with the DISTINCT keyword to query the number of records in a specified field in a data table that have different record values. For example, if we want to query the number of different stores in the Store_information datasheet, you can use the following command:

SELECT COUNT (DISTINCT store_name)

From Store_information

The query results appear as follows:

Count (DISTINCT store_name)

3

GROUP by

Now let's take a closer look at the aggregate functions in the SQL language. Earlier, we used the SUM function to calculate total sales for all stores, and what if we wanted to calculate the respective total sales for each store? To do this we need to do two things: first, we need to query the store name and sales two fields, and then we use the SQL language group by command to group sales by different stores to calculate the total sales for different stores. The syntax format of the GROUP by command is:

Select "Column_name1", SUM ("column_name2")

From "table_name"

GROUP by "column_name1"

We can use the following command to achieve the above query purpose:

SELECT Store_name, SUM (Sales)

From Store_information

GROUP by Store_name

The query results appear as follows:

Store_name SUM (Sales)

Los Angeles 00

San Diego 0

Boston 0

Note

The GROUP by keyword is typically used in SQL commands that query multiple fields simultaneously and perform arithmetic operations on the fields.

Having

Another problem that users may want to address in the process of using SQL language is to limit the output of the results of sum or other aggregate functions. For example, we may only want to see information about stores that have a total sales of more than 1500 dollars in the Store_information datasheet, and then we need to use the HAVING clause. The syntax format is:

Select "Column_name1", SUM ("column_name2")

From "table_name"

GROUP by "column_name1"

Having (arithematic function condition)

(GROUP by clause optional)

Therefore, we can use the following command to achieve the purpose of the above query:

SELECT store_name, SUM (sales)

From Store_information

GROUP by Store_name

Having SUM (sales) > 1500

The query results appear as follows:

Store_name SUM (Sales)

Los Angeles 00

Note

Use a HAVING clause instead of the WHERE clause in the SQL language when setting the query condition of the aggregate function. Usually, the HAVING clause is placed at the end of the SQL command.

ALIAS

Below, we will focus on how to set aliases in SQL commands. Two types of aliases are commonly used in SQL languages, field aliases and data table aliases, respectively.

Simply put, using a field alias can help us effectively organize the output of the query. For example, in the multiple instances listed above, when we calculate the total store sales, sum (sales) appears in the display results. Although sum (sales) does not make it difficult for us to understand query results, it is less intuitive to display the results if we need to use multiple complex operations in a query. If we use the field alias then we will greatly improve the readability of the query results.

For a data table alias, we can set the alias by placing it directly behind the datasheet name in the FROM clause. The data table alias is extremely useful in the operations that we're going to be talking about connecting to multiple data tables.

The syntax format for field and datasheet aliases is as follows:

Select "Table_alias". Column_name1 "" Column_alias "

From "table_name" "Table_alias"

That is, the aliases are placed directly behind their corresponding names, separated by a space in the middle.

Take the Store_information datasheet as an example, we can set the following fields and table aliases in the SQL command used in the Group by section:

Select A1.store_name Store, SUM (sales) "Total Sales"

From Store_information A1

GROUP by A1.store_name

The query results appear as follows:

Store Total Sales

Los Angeles 00

San Diego 0

Boston 0

Connecting multiple data tables

Finally, let's take a look at the query for multiple data tables if you are using SQL to connect multiple tables. For ease of explanation, we created two data tables named Store_information and region, respectively, in the database.

Store_information

Store_name
Sales
Date

Los Angeles
00
jan-10-2000

San Diego
0
jan-11-2000

Los Angeles
0
jan-12-2000

Boston
0
jan-12-2000


Region

Region_name
Store_name

East
Boston

East
New York

West
Los Angeles

West
San Diego


Next, let's take a look at the connection through the datasheet to query sales by different regions.

We notice that the data table named region contains two field information for the area and store, while the data table named Store_information contains the sales information for each store. Therefore, in order to get the sales information by region, we need to combine the information of two different data tables to query. By analyzing the two data tables, we find that each datasheet contains a field named Store_name, so we can use the following command to implement the query:

SELECT A1.region_name Region, SUM (A2. Sales) Sales

From Geography A1, Store_information A2

WHERE A1.store_name = A2.store_name

GROUP by A1.region_name

The query results appear as follows:

REGION SALES

East 0

West 50

Description

The first two lines of the above query command are used to specify the target field for the query, the total number of record values in the region datasheet for the Region_name field and the Sales field in the Store_information datasheet. Here, we set the alias of two fields to region and sales, and the aliases of the two data tables are A1 and A2 respectively. If we use only the field alias instead of the data table alias, the first line of the above SQL command becomes the following form:

SELECT region.region_name Region, SUM (store_information.sales) Sales

From this we can see the effective use of the data table alias, can greatly simplify the operation of multiple data tables SQL command.

The 3rd behavior WHERE clause of the above query order is the clause that sets the join condition for two data tables. Because we want to make sure that the Store_name field in the region datasheet corresponds to the same name field in the Store_information datasheet, we specify that the record values for both fields should be equal. When connecting multiple data tables, it is important to set the connection condition of the data table exactly, and if the WHERE clause is set incorrectly, it may result in many unrelated data in the query result.