SQL numeric Functions

SQL numeric Functions

1. AVG: arithmetic mean
AVG (expr)
Expr
Field name or expression.
For example:
To calculate the average height of an employee who exceeds 165 cm, you can use the following SQL statement.
Select AVG (height)
As average height
From employee table where height> 165;
2. Count: calculates the number of records.
Count (expr)
Expr
Field name or expression.
For example:
To count the number of employees in the business department and query the employee name, you can use the following procedure.
Select count (name) as employee name
From employee form
Where Department name = 'business ';
3. First and last: return the first and last data of a field.
First (expr)
Last (expr)
Expr
Field name or expression.
For example:
If you want to find the first data in the item Quantity Field and the last data in the item price field, you can use the following query method.
Select first (item quantity), last (item price)
From Order Form
4. Max, and Min: return the maximum and minimum values of a field.
The usage is the same as that of first and last.
5. Sum: return the sum of a specific field or operation.
Sum (expr)
Expr
Field name or expression.
For example:
You can use the following procedure to calculate the total shipment price.
Select
Sum (unit price * goods quantity)
As total price from Order Form
Multi-layer SQL query

As the name suggests, multi-layer SQL queries are: "One SQL statement can contain another SQL query statement to form an internal nested Query type ."
Comparison [any | all | some] (sqlstatement)
Expression [not] In (sqlstatement)
[Not] exists (sqlstatement)
Comparison
The operation that compares the expression with the query result of the inner layer.
Expression
Returns the search expression for the result of an inner-layer query.
Sqlstatement
For an SQL query composed of select statements, you must use () to enclose the statements.
For example:
We first query all the units in the order form, and then compare the units in the product form with each other to query all the records above the unit price in the order form.
Select * from product table
Where unit price> Any (select unit price from order table where discount> =. 25 );
SQL and database maintenance

Create a table
The basic syntax in SQL has been introduced last time, but most of them prefer to query and filter database data. However, there are still many things we can do through SQL commands, next we will introduce how to use SQL syntax commands to create a table in a database.

Create table statement
We can use this command to create a new table, but the premise is that the database must already exist.
Create Table (field1 type [(size)] [index1] [, field2 type [(size)] [index2] [,...] [, nultifieldindex [,...])

Table
The name of the table to be created.
Field1, field2
The new field name in the new table must be less than one field.
Type
The data type of the field.
Size
The size of the field.
Index1, index2
Use the Constraint Condition Clause to define the index name of a single field.
Multifieldindex
Use the Constraint Condition Clause to define the index name of multiple fields.
For example:
Create a table with employee name and department fields.
Create Table employee table (name test, Department test, employee No. Integer constraint employee field index primary key)

In this example, we create a table named "Employee table" and define the primary key value of the table to restrict repeated data input.

Create Table Indexes
Create index statement
This command is mainly used to index an existing table. Its usage is as follows:
Create [unique] Index on table (field [ASC | DESC] [, field [ASC | DESC],...])
[With {primary | disallownull | ignorenull}]
Index
The name of the index to be created.
Table
Name of the table to be indexed.
Field
Field name of the index to be created. You can also use DESC to reserve words to determine the order of indexes.
For example:
Create an index in the employee table.
Create index new index name
On employee form (name Department );

Update table fields
Constraint Condition Clause
The constraint function is similar to the index function, although the constraint can also establish associations between tables.
Single field index:
Constraint name {primary key | unique | references foreigntable [(foreignfield1, foreignfield2)]}

Multi-field index:
Constraint name
{Primary key (primary1 [, primary2 [,...])
| Unique (unique1 [, unique2 [,...])
| Foreign key (ref1 [, ref2 [,...])
| References foreigntable [(foreignfield1 [, foreignfield2 [,...])]}
Name
The name of the constraint to be created.
Primary1, primary2
It is used to design a field name (more than one) as the primary key value ).
Unique1, unique2
It is used to design a unique key value field name (more than one ).
Foreign key
Field name, or refer to the field names in other tables.
Foreigntable
As described above, the table to be referenced.
Foreignfield1, foreignfield2
The fields specified by the ref1 and ref2 fields in the referenced table. If the field to be referenced is the primary key value in the reference table, you can also omit this condition clause.
For example:
To create a new employee data table that contains three fields: name, Department name, and birthday, and create a unique index for these three fields, you can use the following SQL statement.
Create Table employee data table
(Name test, Department name test, birthday datetime, constraint employee data table restriction unique (name, Department name, birthday ));

The preceding commands are related to database table creation in SQL. You can use these commands to create a complete database table through SQL statements, this section describes the SQL statements used to maintain, add, and delete databases.


Delete table
Delete statement
We can use the delete statement to delete records in the table. (Note: after a record is deleted, it cannot be restored. Therefore, the condition settings must be correct)
Delete [Table. *]
From tableexpression
Where criteria
Table
The table name to be deleted can also be replaced.
Tableexpression
The name of one or more tables. This parameter can be a single table name or result from an inner join, left join, or rightjoin operation.
Criteria
Determines the criteria for deleting records in the table.
For example:
If we want to delete the record named 'Lee name' in the employee table, we can use the following SQL statement.
Delete * from employee table
Where name = 'Lee name ';

Database Table-related operation commands
SQL can be used as a tool to query and create database tables. It also provides excellent functions for creating, deleting, and maintaining databases and tables, if the reader uses SQL commands appropriately, it will be of great help to improve the overall efficiency. Therefore, the advantages of SQL statements are often the following: "When we perform complex and multi-step processing on multiple tables, we may only need one SQL statement to fulfill all the requirements and objectives, however, the following chapters will help you understand the highlights.

Select... into statement
We can use this command to create a query statement for a new table by using the existing table query.
Select field1 [, field2 [,...] into newtable [IN externaldatabase]
From Source
Field1, field2
Name of the field to be copied to the new table.
Newtable
The name of the new table to be created, not the existing table.
Externaldatabase
If the table is in another external database, the name of the database.
Source
The source table name that records data copies. It can be a single table or a SQL query statement.
For example:
You can use the following SQL statement to create a new "training roster" table.
Select employee form. Name, employee form. Department
Into training roster from employee table
Where title = 'new employee ';

Inner Join Operations
When a common field data is equal, the records of the two tables are combined.
Select fields
From Table1 inner join Table2
On table1.field1 compopr table2.field2
Table1, Table2
Name of the table to be combined.
Field1, field2
Name of the field to be combined. (The data type must be the same)
Compopr
Comparison operators are as follows: "=", "<", ">", "<=", and "<>.
For example:
If you want to combine the classification table and product table, refer to the following SQL statement.
Select category name, product name
From Category Table inner join product table
On classification table. Category No. = product table. Category No;

Union operations
We can use the Union operations to establish the join query conditions. The Union operations can combine more than two tables or query results.
[Table] query1 Union [all] [Table] query2 [Union [all]
[Table] queryn [...]
Query1, query2, queryn
It is a SELECT statement, an existing query name, or an existing table name.
For example:
You can use the following SQL statement to record the customer table with more than 1000 orders and perform Union operations with the new customer table.
Table new customer table Union all
Select *
From customer table
Where order quantity> 1000;

Alter statement
After a table is created, we can use the alter statement to modify the field design of the table.
Alter table table
{Add {Column field type [(size)] [constraint Index]
| Constraint multifieldindex}
| Drop {Column FIELD | constraint indexname }}
Table
Name of the table to be alter.
Field
The name of the field to be added or deleted.
Type
Field data type.
Size
Field size.
Index
The index of this field.
For example:
Create a new "salary" field in the employee table.
Alter table employee table
Add column salary currency;
For example:
Delete A "salary" field in the employee table.
Alter table employee table drop column salary;


Drop statement
Delete the specified table or field or delete the index.
Drop {Table | Index on table}
Table
The name of the table to which the table or index is attached.
Index
The name of the index to be deleted from the table.
For example:
Delete the numbered index from the employee table.
Drop index myindex on employees;
For example:
Delete the entire table from the database.
Drop table employee table;


Insert into statement
Create a new data entry in the table.
Create query for multiple records:
Insert into target [IN externaldatabase] [(field1 [, field2 [,...])]
Select [source.] field1 [, field2 [,...]
From tableexpression
Create a query for a single record:
Insert into target [(field1 [, field2 [,...])]
Values (value1 [, value2 [,...])
Target