Built-in Functions SQL designed to facilitate and speed up data processing. The peculiarity is that they can be specified directly in the expression. All built-in functions can be conditionally divided into groups.

Math functions:

ABS(meaning) - returns the absolute value of the number;

Round(value, precision) - returns a numeric value rounded up to the specified argument accuracy the number of decimal places;

SIGN(meaning) - returns a minus if the number is negative, and a plus otherwise;

POWER(value, degree) – raises a number to a power;

SQRT(meaning) - extracts the square root of a number;

CEILING(meaning)- returns the nearest integer greater than or equal to the value;

- FLOOR(meaning) Returns the nearest integer less than or equal to the value.

String functions:

ASCII(line) - returns ASCII code of the first character of the string;

CHAR(number) – return a character by ASCII code;

LEN (line) – returns the length of the string in characters, excluding trailing spaces;

LTRIM(line)/ RTRIM(line)- removes spaces at the beginning/end of a string;

LEFT(string, number)/ RIGHT(string, number)- returns the specified argument number the number of characters in the string, starting from the left/right edge;

SUBSTRING(line, position, length) - returns a substring of the specified length from the string, starting from the specified position;

LOWER(line) /UPPER(line) - returns a string converted to lower / uppercase etc.

Functions for working with dates:

GETDATE() - Returns a value that contains the date and time of the computer on which the instance of SQL Server is running;

DAY(value_date)– returns a number from the specified date;

MONTH(value_date)- returns the number of the month from the specified date;

YEAR(value_date)- returns the value of the year from the specified date;

DATENANE( part, value_date) - returns character string, representing the specified part ( day, month, Houretc.) from the specified date;

DATEPART( part, value_date) - returns an integer representing the specified part ( day, month, Houretc.) from the specified date.

Data Type Conversion Functions

CAST (meaning AS datatype)

CONVERT(datatype, meaning)

Argument meaning in functions, specifies the value to be converted.

7.3. Data Definition Language Commands

The data definition language contains commands for creating, modifying, and deleting a database and its objects.

Create a table

The creation of a new table is performed by the command CREATE TABLE. The command describes the structure of the table, each column of the table, and the integrity constraints that must be set for the table.

Command syntax:

CREATE TABLE table_name (( column_description |calculated_column_name AS expression | table_level_integrity_constraints) [, ...])

The table name is an identifier with a maximum length of 128 characters.

A table can contain a calculated column, in which case the value of the column is determined by an expression stored in the table structure. A computed column cannot be modified, so it cannot be NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, or DEFAULT.

The syntax for describing a table column is:

column_name datatype[(the size)]

[(DEFAULT default_value | IDENTITY [(value, step)]}]

[column_level_integrity_constraints]

DEFAULT - allows you to specify the value assigned to the column in the newly added record.

IDENTITY Indicates that an auto-numbering-enabled column (counter column) is being created. Only one counter column can be defined in a table. The value parameter specifies the initial value of the counter, and the step parameter specifies the increment step. If these parameters are not set, they have the value 1. IDENTITY can only be set for columns that have integer or decimal types. Inserting values ​​into an IDENTITY column is not allowed.

There are two groups of integrity constraints handled by the DBMS:

Declarative integrity constraints that are declared when a table is created or modified;

Procedural integrity constraints that are handled by triggers.

Declarative integrity constraints can be table-level constraints and table-level constraints. A column-level constraint applies to only one column. Each declarative integrity constraint can be given a name.

The description of column level constraints has the following syntax:

((PRIMARY KEY | UNIQUE | NOT NULL ) |FOREIGN KEY REFERENCES table_name( column_name)

|CHECK boolean_expression)

The name of the data integrity constraint must be unique within the database. Consider the constraints that can be defined at the column level:

PRIMARY KEY constraint. All table primary key values ​​must be unique and not null. A table can only have one primary key. If it is composite, then integrity constraints on the primary key are set at the table level;

UNIQUE column value uniqueness constraints. This means that a table cannot have two records that have the same value in that column;

A NOT NULL constraint that prevents the column from storing a NULL value;

Foreign key constraint FOREIGN KEY (referential integrity constraint). For a column that is a foreign key, use REFERENCES to specify the name of the table to which the link is being established, and the name of the column of this table that will be linked to. Such a table is the main (parent) table in relation to the created table. The column in the main table whose values ​​are being linked must have a PRIMARY KEY constraint.

If the foreign table key consists of multiple fields, then the FOREIGN KEY constraint must be specified at the table level. In this case, you should list all the columns included in the foreign key, specify the name of the main table and the names of the columns of the main table that the foreign key refers to.

Referential integrity establishes the rules for adding and modifying data in a table using a foreign key and its corresponding primary key constraint. The ON UPDATE and ON DELETE clauses for a foreign key define the following rules for modifying related data:

NO ACTION - allows you to change (delete) only those values ​​in the main table that do not have corresponding foreign key values ​​in the child table. This rule is in effect by default;

CASCADE means that each value of the foreign key of the child table will be automatically changed (deleted) when the value of the primary key of the parent table is modified;

SET NULL means that in case of change (deletion) of the primary key of the parent table, in all referring rows of the child table, the values ​​of the foreign key will automatically be assigned NULL values;

SET DEFAULT means that in case of change (deletion) of the primary key of the parent table, in all referring rows of the child table, the values ​​of the foreign key will automatically be assigned default values.

Let's complete the example of the training database "University", the design of which was considered in Chap. 4.3 tables DISCIPLINE and GENERAL STATEMENT. Tables 6 and 7 describe the logical structure of the tables.

Table 6

Logical structure of the information object DISCIPLINE

Table 7

The logical structure of the information object GENERAL STATEMENT

		Key sign	Field Format
	Name				Accuracy
Record book number	Registered student record book number		text
Discipline Code	Discipline Code		Numerical	long integer
			numerical

Let's give requests for creating tables in accordance with the one shown in fig. 35 infological database model.

Rice. 35. Database schema "University"

As you can see from the database schema, the FACULTY table is an independent table, so it is created first. A request to create a table, taking into account the description of the logical structure in Table. 4 (p. 61) will look like:

CREATE TABLE faculty

([department number] tinyint PRIMARY KEY , [department name] char(50))

The SPECIALTY table is also independent, we create it second. When creating a query, it uses the description of the logical structure in Table. 5 (p. 62).

CREATE TABLE [specialty] (

[specialty number] int PRIMARY KEY,

[name of specialty] char (60),

[cost of education] )

The GROUP table is dependent on the FACULTY and SPECIALTY table. We use table 3 (p. 61) when creating a query and take into account that the columns faculty number and specialty number are foreign keys:

CREATE TABLE [group] (

[group number] smallint PRIMARY KEY,

[specialty number] int FOREIGN KEY REFERENCES specialty( room special- news)ON DELETE CASCADE ON UPDADE CASCADE,

[faculty number] tinyint FOREIGN KEY REFERENCES faculty( room faculty) ON DELETE CASCADE ON UPDADE CASCADE, [course number] tinyint)

The STUDENT table is a GROUP dependent table. Based on the data in Table 2 (p. 60), we will make a query. We also note that the column group number are foreign keys:

CREATE TABLE [student] (

[group number] smallint NOT NULL FOREIGN KEY REFERENCES group( room groups) ,

[last name] char(15) NOT NULL ,

[date of birth] datetime NOT NULL ,

[commercial] bit NOT NULL ,

[registration name] char(9))

The data in the GENERAL RECORD table depend on the STUDENT and DISCIPLINE tables. In this table, the primary key is composite and each of the primary key columns is a foreign key (see Table 7 and Figure 35).

Let's use the description of the logical structure of the discipline table given in Table 6 and make a query:

CREATE TABLE [discipline] (

[discipline code] int PRIMARY KEY,

[discipline name] char(50))

Now you can create a query to create a general statement table. Because the table's primary key is a composite key, the PRIMARY KEY constraint must be set at the table level. For example, let's set FOREIGN KEY constraints also at the table level. The request will look like:

CREATE TABLE [general sheet] (

[discipline code] int,

[gradebook number] char(8),

[grade] NOT NULL , PRIMARY KEY ([discipline code],[classbook number]), FOREIGN KEY ([discipline code]) REFERENCES [discipline] ([discipline code]), FOREIGN KEY ([classbook number]) REFERENCES [student] ([grade book number]))

Changing the structure of a table

Changing the structure of a table is done with the ALTER TABLE command. Using the command, you can change the properties of existing columns, delete them or add new columns to the table, manage integrity restrictions, both at the column level and at the table level. The assignment of many parameters and keywords is similar to the assignment of the corresponding parameters and keywords of the CREATE TABLE command.

Deleting a table

Dropping a table is done using the DROP TABLE command. Command syntax:

DROP TABLE table

For example, a query to delete the STUDENT table looks like this:

DROP TABLE Student

Deleting a table SHOULD take into account the relationships established in the database between the tables. If another table refers to the table being deleted using the FOREIGN KEY integrity constraint, then the DBMS will not allow its deletion.

Create an index

Indexes are used to speed up access to specific data in a database table. An index is a structure that orders the values ​​in one or more columns of a database table, such as the Last Name column of the STUDENT table. If you are searching for a specific student by last name, the index helps you get the information you need faster than searching through all the rows in the table.

An index provides pointers to the data values ​​stored in specific columns of a table and arranges those pointers according to the specified sort order. Searching for data in a table using an index is similar to searching an index in a book. First, a search is made for a specific value in the index, and then the corresponding jump is performed through the pointer to the line containing this value.

An index is created using the CREATE INDEX command:

CREATE INDEX

name_ index ON name _tables(column [,…])

where UNIQUE indicates that the index should only store unique values.

An index can be created on one or more columns (composite index). Composite indexes allow you to distinguish between records that have the same value in the same column.

Example: Create a composite index on the STUDENT table for fields Last Name and Date of Birth

CREATE INDEX Ind_Fam ON

Student(Last Name, [Date of Birth] DESC)

You should only create an index on a table if you intend to frequently query the data in the indexed columns. Indexes take up disk space and slow down row additions, deletions, and updates.

Deleting a Table Index

The DROP command removes an index from a table. The syntax for the DROP command to drop an index is:

DROP INDEX index ON table

Before an index can be dropped from a table or the table itself, it must be closed.

Example: Delete index Ind_Fam from STUDENT table

DROP INDEX Ind_Fam ON Student

Let's learn to sum up. No, these are not the results of learning SQL, but the results of the values ​​of the columns of the database tables. SQL aggregate functions operate on the values ​​of a column to produce a single result value. The most commonly used SQL aggregate functions are SUM, MIN, MAX, AVG, and COUNT. There are two cases in which aggregate functions should be used. First, aggregate functions are used by themselves and return a single result value. Second, aggregate functions are used with the SQL GROUP BY clause, that is, with grouping by fields (columns) to obtain the resulting values ​​in each group. Consider first the cases of using aggregate functions without grouping.

SQL SUM function

The SQL SUM function returns the sum of the values ​​of a column in a database table. It can only be applied to columns whose values ​​are numbers. The SQL queries to get the resulting sum start like this:

SELECT SUM (COLUMNAME) ...

This expression is followed by FROM (TABLE_NAME), and then a condition can be specified using the WHERE clause. In addition, DISTINCT can be prefixed to a column name to indicate that only unique values ​​will be considered. By default, all values ​​are taken into account (for this, you can specifically specify not DISTINCT, but ALL, but the word ALL is optional).

Example 1 There is a database of the company with data on its departments and employees. The Staff table also has a column with employee salary data. The selection from the table has the following form (to enlarge the picture, click on it with the left mouse button):

To get the sum of all salaries, use the following query:

SELECT SUM (Salary) FROM Staff

This query will return the value 287664.63.

And now . In the exercises, we are already starting to complicate the tasks, bringing them closer to those that are encountered in practice.

SQL MIN function

The SQL MIN function also operates on columns whose values ​​are numbers and returns the minimum of all values ​​in the column. This function has a syntax similar to that of the SUM function.

Example 3 The database and table are the same as in example 1.

You need to know the minimum wages employees of department number 42. To do this, write the following request:

The query will return the value 10505.90.

And again exercise for independent solution . In this and some other exercises, you will need not only the Staff table, but also the Org table containing data on the company's divisions:

Example 4 The Org table is added to the Staff table, containing data on the company's divisions. Output the minimum number of years a single employee has worked in a department located in Boston.

SQL MAX function

The SQL MAX function works similarly and has a similar syntax, which is used when you want to determine the maximum value among all values ​​of a column.

Example 5

It is required to find out the maximum salary of employees of department number 42. To do this, write the following query:

The query will return the value 18352.80

Now is the time exercises for self-determination.

Example 6 Again we are working with two tables - Staff and Org. Display the name of the department and the maximum amount of commissions received by one employee in the department that belongs to the group of departments (Division) Eastern. Use JOIN (joining tables) .

SQL AVG Function

What was said about the syntax for the previous described functions is also true about the SQL AVG function. This function returns the average of all values ​​in a column.

Example 7 The database and table are the same as in the previous examples.

Let it be required to find out the average length of service of employees of department number 42. To do this, we write the following query:

The result will be 6.33

Example 8 We work with one table - Staff. Display the average salary of employees with experience from 4 to 6 years.

SQL COUNT function

The SQL COUNT function returns the number of records in a database table. If you specify SELECT COUNT(COLUMNAME) ... in the query, then the result will be the number of records without taking into account those records in which the column value is NULL (undefined). If you use an asterisk as an argument and start a SELECT COUNT(*) ... query, the result will be the number of all records (rows) in the table.

Example 9 The database and table are the same as in the previous examples.

You want to know the number of all employees who receive commissions. The number of employees whose Comm column values ​​are not NULL will return the following query:

SELECT COUNT (Comm) FROM Staff

The result will be the value 11.

Example 10 The database and table are the same as in the previous examples.

If you want to know the total number of records in the table, then use the query with an asterisk as an argument to the COUNT function:

SELECT COUNT (*) FROM Staff

The result will be the value 17.

Next exercise for self-determination you need to use a subquery.

Example 11. We work with one table - Staff. Display the number of employees in the Plains department.

Aggregate Functions with SQL GROUP BY

Now let's look at using aggregate functions together with the SQL GROUP BY clause. The SQL GROUP BY clause is used to group the resulting values ​​by columns in a database table. The site has lesson dedicated to this operator separately .

Example 12. There is a database portal ads. It has an Ads table that contains data about the ads that have been submitted for the week. The Category column contains data about large ad categories (for example, Real Estate), and the Parts column contains data about smaller parts that are included in the category (for example, Apartments and Villas parts are parts of the Real Estate category). The Units column contains data on the number of ads submitted, and the Money column contains the amount of money earned for submitting ads.

Category	part	Units	Money
Transport	motor vehicles	110	17600
Real estate	Apartments	89	18690
Real estate	Dachas	57	11970
Transport	Motorcycles	131	20960
building materials	Boards	68	7140
electrical engineering	TVs	127	8255
electrical engineering	Refrigerators	137	8905
building materials	Regips	112	11760
Leisure	Books	96	6240
Real estate	Houses	47	9870
Leisure	Music	117	7605
Leisure	Games	41	2665

Using the SQL GROUP BY clause, find the amount of money generated by submitting ads in each category. We write the following query:

SELECT Category, SUM (Money) AS Money FROM Ads GROUP BY Category

Example 13 The database and table are the same as in the previous example.

Using the SQL GROUP BY clause, find out which part of each category had the most ads. We write the following query:

SELECT Category, Part, MAX (Units) AS Maximum FROM Ads GROUP BY Category

The result will be the following table:

Total and individual values ​​in one table can be obtained combining query results using the UNION operator .

Relational databases and SQL language

Like most programming languages, SQL has functions for handling data. It is worth noting that, unlike SQL statements, functions are not standardized for all types of DBMS, that is, to perform the same operations on data, different DBMSs have their own function names. This means that the query code written in one DBMS may not work in another, and this must be taken into account in the future. Most of all, this concerns functions for processing text values, converting data types, and manipulating dates.

Usually DBMS is supported standard set function types, namely:

• Text functions that are used for text processing (selecting part of the characters in the text, determining the length of the text, converting characters to upper or lower case ...)
• Numeric functions. Used to perform mathematical operations over numbers
• Date and time functions (perform date and time manipulation, calculate the period between dates, check dates for correctness, etc.)
• Statistical functions (for calculating the maximum / minimum values, averages, counting quantities and sums...)
• System functions (provide various service information about the DBMS, user, etc.).

1. SQL Functions for Text Processing

The implementation of SQL in the Access DBMS has the following functions for text processing:

Let's convert the product names to uppercase using the function UCase():

SELECT Product, UCase(Product) AS Product_UCase FROM Sumproduct

Select the first three characters in the text using the function LEFT():

SELECT Product, LEFT(Product, 3) AS Product_LEFT FROM Sumproduct

2. SQL functions for processing numbers

Number processing functions are designed to perform mathematical operations on numeric data. These functions are intended for algebraic and geometric calculations, so they are used much less often than date and time processing functions. However, numeric functions are the most standardized across all versions of SQL. Let's take a look at the list of numeric functions:

We've only listed a few of the main features, but you can always refer to your database's documentation for a full list of supported features and their detailed descriptions.

For example, let's write a query to get the square root of the numbers in a column Amount using the function SQR():

SELECT Amount, SQR(Amount) AS Amount_SQR FROM Sumproduct

3. SQL functions for processing dates and times

The date and time manipulation functions are one of the most important and frequently used SQL functions. Databases store date and time values ​​in a special format, so they cannot be used directly without additional processing. Each DBMS has its own set of functions for processing dates, which, unfortunately, does not allow them to be transferred to other platforms and SQL implementations.

List of some functions for handling date and time in DBMS Access:

Let's look at an example of how the function works DatePart():

SELECT Date1, DatePart("m", Date1) AS Month1 FROM Sumproduct

Function DatePart() It has additional parameter, which allows us to display the necessary part of the date. In the example, we used the parameter "m", which displays the month number (in the same way we can display the year - "yyyy", quarter - "q", day - "d", week - "w", hour - "h", minutes - "n", seconds - "s" etc.).

4. SQL Statistical Functions

Statistical functions help us get ready-made data without sampling it. SQL queries with these functions are often used to analyze and create various reports. An example of such selections can be: determining the number of rows in a table, getting the sum of values ​​for a certain field, searching for the largest / smallest or average value in a specified table column. Also note that statistical functions are supported by all DBMSs without much change in spelling.

List of statistical functions in DBMS Access:

COUNT():

SELECT COUNT(*) AS Count1 FROM Sumproduct- returns the number of all rows in the table

SELECT COUNT(Product) AS Count2 FROM Sumproduct- returns the number of all non-empty rows in the field Product

We intentionally removed one value in a column Product to show the difference in how the two queries work.

Function usage examples SUM():

SELECT SUM(Quantity) AS Sum1 FROM Sumproduct WHERE Month = "April "

With this request, we reflected the total number of goods sold in April.

SELECT SUM(Quantity*Amount) AS Sum2 FROM Sumproduct

As you can see, in aggregate functions, we can perform calculations on multiple columns using standard mathematical operators.

Function usage example MIN():

SELECT MIN(Amount) AS Min1 FROM Sumproduct

Function usage example MAX():

SELECT MAX(Amount) AS Max1 FROM Sumproduct

Function usage example AVG():

SELECT AVG(Amount) AS Avg1 FROM Sumproduct

Functions are a special type of command in the SQL command set, and each dialect has its own implementation of the command set. As a result, we can say that functions are commands consisting of one word and returning a single value. The value of a function may depend on the input parameters, such as in the case of a function that calculates the average of a list of values ​​in a database. However, many functions do not take any input parameters, for example, the function that returns the current system time is CURRENTJ1ME.

The ANSI standard supports several useful features. This chapter provides a description of these functions, as well as detailed description and examples for each platform. In addition, each platform has a long list of its own, internal functions that go beyond the SQL standard. This chapter provides parameters and descriptions of all the internal functions of each of the platforms.

In addition, most platforms have the ability to create custom functions. Per additional information for user-defined functions, see "CREATE/ALTER FUNCTION/PROCEDURE Statements"

Function types

Exist different ways function classifications. The following subsections describe important differences to help you understand how the features work.

Deterministic and non-deterministic functions

Functions can be deterministic or non-deterministic. A deterministic function always returns the same result for the same set of input values. Non-deterministic functions may return different results on different calls, even if they are given the same input values.

Why is it so important that with the same input parameters got the same results? This is important because it defines the way functions are used in views, user-defined functions, and stored procedures. Restrictions on different platforms may be different, but sometimes only deterministic functions can be used in these objects. For example, SQL Server can create an index on an expression on a column, as long as the expression does not contain non-deterministic functions. The rules and restrictions vary from platform to platform, so please refer to the manufacturer's documentation when using the functions.

Aggregate and scalar functions

Another way to categorize functions is by their ability to operate on a single string only, or on a collection of values, or on sets of strings. Aggregate functions operate on a collection of values ​​and return a single total value. Scalar functions return a single value, depending on the scalar input arguments. Some scalar functions, such as CURRENTJTIME, require no arguments.

Window Functions

Window functions can be considered similar to aggregate functions in that they can operate on multiple rows at once. The difference is in how these lines are specified. Aggregate functions operate on rowsets specified in the GROUP BY clause. In the case of window functions, the rowset is specified with each function call, and different function calls within the same query can operate on different rowsets.

The steps of this stage of learning SQL queries are designed to demonstrate the fact that SQL can not only make complex selections and sort data, but also calculate the results of mathematical functions, perform text transformation, group records, etc. More precisely, not SQL can do all this, but those supporting it. SQL with its standards only formulates the requirements for these same DBMS.

Step 15 SUM, AVG, MIN, MAX, COUNT…

This step will show you how to use the simplest functions in SQL, such as sum, min, max, average, and so on. Let's start right away with an example of deriving the average seniority of all employees.

SELECT AVG(D_STAFF.S_EXPERIENCE) AS [AVERAGE STAFF EXPERIENCE] FROM D_STAFF

SQL function AVG.

Similarly, you can calculate the minimum and maximum values ​​(MIN, MAX), the total amount (SUM), etc. I advise you to try it out using the tutorial. It is worth trying to define additional criteria for selecting records involved in determining the final value of the function using the WHERE clause.

The functions listed above use the entire query result to determine their value. Such functions are called aggregate . Also, there are a number of functions whose arguments are not all the values ​​of the column defined in the query, but each individual value of each individual row of the result. An example of such a function is the SQL function for calculating the length text field LEN:

SELECT S_NAME, LEN(D_STAFF.S_NAME) AS [LENGTH] FROM D_STAFF

Can be used superposition of SQL functions, as shown below, and calculate the maximum length value of the S_NAME field.

SELECT MAX(LEN(D_STAFF.S_NAME)) AS [MAX LENGTH] FROM D_STAFF

SQL function MAX.

Well, in conclusion, all together.

SELECT SUM(D_STAFF.S_EXPERIENCE) AS [SUM], AVG(D_STAFF.S_EXPERIENCE) AS [AVERAGE], MIN(D_STAFF.S_EXPERIENCE) AS [MINIMUM], MAX(D_STAFF.S_EXPERIENCE) AS [MAXIMUM], COUNT(*) AS [NUMBER OF RECORDS], MAX(LEN(D_STAFF.S_NAME)) AS [MAXIMUM LENGTH] FROM D_STAFF

An example of using aggregate SQL functions.

Notice the argument to the COUNT function. I specified (*) as an argument, because I want to get exactly total number records. If you specify, for example, COUNT(S_NAME), then the result will be the number of non-empty S_NAME values ​​(S_NAME IS NOT NULL). It would be possible to write COUNT(DISTINCT S_NAME) and get the number of unique S_NAME values, but MS Access, unfortunately, does not support this option. In our example, COUNT(S_NAME) and COUNT(*) give exactly the same result.

Step 16 Text Transformation

Often, text values filled in by users software in different ways: who writes the full name. capitalized, who is not; someone writes everything capital letters. Many reporting forms require a unified approach, and not only reporting forms. To solve this problem, SQL has two functions UCASE and LCASE. An example request and the result of its processing are shown below:

SELECT UCASE(D_STAFF.S_NAME) AS , LCASE(D_STAFF.S_NAME) AS FROM D_STAFF

SQL functions UCASE and LCASE.

Step 17: SQL and string manipulation

There is also such a wonderful MID function that will help you solve the problem of extracting part of a string from the entire value of a text field. Here, too, the best comment would be an example - an example of "mocking" user profile names.

SELECT UCASE(MID(P_NAME,3,5)) FROM D_PROFILE

Superposition of SQL functions UCASE and MID.

We “cut out” 5 characters from the values ​​of the profile names, starting from the 3rd one, and got a bunch of repeating “garbage”. In order to leave only unique values, we will use keyword DISTINCT.

SELECT DISTINCT UCASE(MID(P_NAME,3,5)) AS FROM D_PROFILE

Selecting unique aggregate function values.

Sometimes it is necessary to use expressions with the LEN function as arguments to the MID function. In the following example, we are already displaying the last 5 characters in the profile names.

SELECT UCASE(MID(P_NAME,LEN(P_NAME)-4,5)) FROM D_PROFILE

Using the SQL function LEN.

Step 18. Using SQL functions in the criteria for selecting records. HAVING statement

Having dealt with the functions, the question almost immediately arises, how can they be used in the criteria for selecting records? Some functions, namely those that are not aggregates, are easy to use. Here, for example, is a list of employees whose full name more than 25 characters.

SELECT S_NAME FROM D_STAFF WHERE LEN(D_STAFF.S_NAME) > 25

Using the non-aggregate LEN function in SQL query conditions.

Well, if you, for example, need to display the identifiers of all positions that are occupied by more than one employee in the company, then this approach will not work. What I mean is that the following query may not make any sense, but it is incorrect from the point of view of a structured query. This is due to the fact that for the correct processing of such SQL queries using aggregate functions, one linear pass through employee records will not be enough.

SELECT S_POSITION FROM D_STAFF WHERE COUNT(S_POSITION)>1

For such cases, the HAVING keyword was introduced in SQL, which will help us solve the problem with positions and employees.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION HAVING COUNT(S_POSITION)>1

Using aggregate functions in SQL query conditions.

Step 19. Grouping data in SQL query results with the GROUP BY operator

The GROUP BY clause is required to group the values ​​of aggregate functions by the values ​​of their associated fields. It is needed when we want to use the aggregate function value in the record selection criteria (previous step). It is also needed when we want to include the aggregate function value in the query result. But in the very simple version grouping is equivalent to highlighting unique column values. Let's look at an example request.

SELECT S_POSITION FROM D_STAFF

And these are two options that allow you to display only unique S_POSITION values.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION

SELECT DISTINCT S_POSITION FROM D_STAFF

Well, now let's return to the grouping of function values ​​by the values ​​of the fields associated with them. Let's display for each user profile the number of records associated with it in the D_STAFF_PROFILE table.

SELECT PROFILE_ID AS , COUNT(PROFILE_ID) AS [NUMBER OF RECORDS] FROM D_STAFF_PROFILE GROUP BY PROFILE_ID

Using an aggregate SQL function with grouping.

The GROUP BY operator also allows you to group the result of a query by more than one field by listing them separated by commas. I hope that after all of the above, additional comments on the result of the last query are not needed.

SELECT S.S_POSITION AS , S.S_NAME AS [EMPLOYEE], COUNT(SP.STAFF_ID) AS [NUMBER OF RECORDS IN TABLE D_STAFF_PROFILE] FROM D_STAFF S, D_STAFF_PROFILE SP WHERE S.XD_IID=SP.STAFF_ID GROUP BY S.S_POSITION, S. S_NAME

Grouping SQL query result rows by multiple fields.