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
$1500
jan-10-2000
San Diego
$
jan-11-2000
Los Angeles
$300
jan-12-2000
Boston
$700
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)
$2750
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 $1800
San Diego $
Boston $700
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 $1800
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
