4 what functions are used in sql language. SQL String Functions - Usage Examples. SELECT statement syntax and processing order

In this article, you can look at the types of functions in the SQL language. Functions can be aggregate or scalar. Let's start...

COUNT()

COUNT()- a function that returns the number of records (rows) of the table. Writing a function specifying a column will return the number of records in that particular column, excluding NULL records. Function syntax:

COUNT(column_name);

Recording the function with the "*" mask will return the number of all records in the table. Syntax:

COUNT(*);

Consider examples. We have the following Universities table:

ID	UniversityName	Students	Faculties	professors	location	site
1	Perm State National Research University	12400	12	1229	Perm	psu.ru
2	Saint Petersburg State University	21300	24	13126	Saint-Petersburg	spbu.ru
3	Novosibirsk State University	7200	13	1527	Novosibirsk	nsu.ru
4	Moscow State University	35100	39	14358	Moscow	msu.ru
5	Higher School of Economics	20335	12	1615	Moscow	hse.ru
6	Ural Federal University	57000	19	5640	Yekaterinburg	urfu.ru
7	National Research Nuclear University	8600	10	936	Moscow	mephi.ru

Example 1. Display the number of records in a table using the COUNT function:

SELECT COUNT(*)
FROM Universities; // outputs 7

Example 2. Find the number of universities located in Moscow using the COUNT function:

SELECT COUNT(*) FROM Universities WHERE Location = "Moscow"; // outputs 3

AVG()

AVG()- a function that returns the average value of the column. This function is applicable only for numeric columns. Syntax:

AVG(column_name);

Consider an example. Let's take our Universities table. Example. Using the AVG function to find the average number of students (Students) of all universities:

SELECT AVG(Students) FROM Universities; // outputs 23133

MIN()

MIN()- a function that returns the minimum value of a column. The function has the following syntax:

MIN(column_name);

Consider an example. Let's take our Universities table. Example. Using the MIN function to find the minimum value of the Professores column:

SELECT MIN(Professors) FROM Universities; // output 936

MAX()

MAX()- a function that returns the maximum value of a table column. The function has the following syntax:

MAX(column_name);

Consider an example. Let's take our Universities table. Example. Using the MAX function to find the maximum value of the students column:

SELECT MAX(Students) FROM Universities; // prints 57000

SUM()

SUM()- a function that returns the sum of the values of a table column. Used only for numeric columns. Function syntax:

SUM(expression);

Parameter ALL- is the default setting. The sum of all lines is considered. When specifying a parameter DISTINCT- Only unique values are counted.

Consider examples. Let's take our Universities table. Example 1. Using the SUM function, find the number of students (Students) of all universities in the table:

SELECT SUM(Students) FROM Universities; // outputs 161935

Example 2. Using the SUM function, count the unique values of the Faculties column:

SELECT SUM(DISTINCT Faculties) FROM Universities; // displays 117 (there are 2 identical entries in the Faculties column, under ID 1 and 5. The value of the column under ID 5 is not summed.)

ROUND()

ROUND()- function for rounding decimal numbers. Works only with numeric columns or arbitrary real numbers. Function syntax:

ROUND(expression, length);

expression- the name of the column or columns, as well as a real number. length- Specifies the rounding precision for the number.

Consider an example. Let's take the Planets table:

ID	Planet Name	Radius	SunSeason	OpeningYear	Having Rings	opener
1	Mars	3396	687.58	1659	no	Christiaan Huygens
2	Saturn	60268	10759.22	-	Yes	-
3	Neptune	24764	60190.91	1846	Yes	John Couch Adams
4	Mercury	2439	115.88	1631	no	Nicolaus Copernicus
5	Venus	6051	243.56	1610	no	Galileo Galilei

Example. Use the ROUND function to round the SunSeason column to one decimal place:

SELECT ROUND(SunSeason, 1) FROM Planets;

UCASE()

UCASE()- a function that returns the values of a column or columns in uppercase letters. In MS DBMS SQL Server the analogue of UCASE() is the UPPER function with the same syntax:

UCASE(column_name)

Consider an example. Let's take our Planets table. Example. Print the names of planets in uppercase that do not have rings using the UCASE function:

SELECT UCASE(PlanetName) FROM Planets WHERE HavingRings = "No";

LCASE()

LCASE() is a function that returns the values of a column or columns in lowercase letters. In the MS SQL Server DBMS, the analogue of the SQL LCASE() operator is the LOWER function with the same syntax:

LCASE(column_name);

Consider an example. Let's take our Planets table. Example. Using the LCASE function, print the names of planets that have rings in lower case:

SELECT LCASE(PlanetName) FROM Planets WHERE HavingRings = "Yes";

LEN()

LEN()- a function that returns the length of the value in the record field. The function excludes trailing spaces from the count. Syntax:

LEN(column_name);

Consider an example. Let's take our Planets table. Example. Print the discoverer of the planet (Opener) and the length of the name of the planet discovered by him, using the LEN function:

SELECT Opener, LEN(PlanetName) FROM Planets;

MID()

MID()- a function that displays a certain number of characters in the text field of the table. Function syntax:

MID(column_name,start[,length]);

Parameter start specifies the position of the start character. Parameter length sets the number of characters to print, starting from the position specified in the start parameter.

Consider an example. Let's take our old Universities table. Example. Print the first 3 characters of the city name (Location) using the MID function:

SELECT MID(Location, 1, 3) FROM Universities;

NOW()

NOW()- a function that returns the system time and date. Function syntax:

Consider an example. Let's take our old Universities table. Example. Output how many students are currently studying at each university. using the NOW function:

SELECT UniversityName, Students, NOW() AS CurDate FROM Universities;

To consolidate the material, perform an example of each function independently. If you have any questions, write in the comments and I will definitely answer them.

Basic SQL Commands Every Programmer Should Know

SQL or Structured Query Language (structured query language) is designed to manage data in a relational database system (RDBMS). This article will cover commonly used SQL commands that every programmer should be familiar with. This material is ideal for those who want to brush up on their knowledge of SQL before a job interview. To do this, analyze the examples given in the article and remember that you went through databases in pairs.

Note that some database systems require a semicolon at the end of each statement. The semicolon is the standard pointer to the end of each statement in SQL. The examples use MySQL, so the semicolon is required.

Sample Database Setup

Create a database to show how the commands work. To work, you will need to download two files: DLL.sql and InsertStatements.sql . After that, open a terminal and log into the MySQL console with the following command (the article assumes that MySQL is already installed on the system):

MySQL -u root -p

Then enter the password.

Run the following command. Let's name the database "university":

CREATE DATABASE university; US university; SOURCE ; SOURCE

Database Commands

1. View available databases

SHOW DATABASES;

2. Create a new database

CREATE DATABASE;

3. Selecting a database to use

USE ;

4. Import SQL commands from .sql file

SOURCE ;

5. Deleting the database

DROP DATABASE ;

Working with tables

6. View tables available in the database

SHOW TABLES;

7. Create a new table

CREATE TABLE ( , , PRIMARY KEY ( ), FOREIGN KEY ( ) REFERENCES ());

Integrity Constraints When Using CREATE TABLE

You may need to create restrictions on certain columns in a table. When creating a table, you can set the following restrictions:

table cell cannot be NULL;
primary key - PRIMARY KEY (col_name1, col_name2, ...) ;
foreign key - FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn) .

You can specify more than one primary key. In this case, you get a composite primary key.

Example

Create an "instructor" table:

CREATE TABLE instructor (ID CHAR(5), name VARCHAR(20) NOT NULL, dept_name VARCHAR(20), salary NUMERIC(8,2), PRIMARY KEY (ID), FOREIGN KEY (dept_name) REFERENCES department(dept_name));

8. Information about the table

You can view various information (value type, key or not) about the columns of a table with the following command:

DESCRIBE ;

9. Adding data to the table

INSERT INTO (, , , …) VALUES ( , , , …);

When you add data to each column of a table, you do not need to specify column names.

INSERT INTO VALUES ( , , , …);

10. Update table data

UPDATE SET = , = , ... WHERE ;

11. Deleting all data from the table

DELETE FROM ;

12. Deleting a table

DROP TABLE ;

Commands for creating queries

13. SELECT

SELECT is used to get data from a specific table:

SELECT , , … FROM ;

The following command can display all the data from the table:

SELECT * FROM ;

14. SELECT DISTINCT

Table columns can contain duplicate data. Use SELECT DISTINCT to get only non-duplicate data.

SELECT DISTINCT , , … FROM ;

15. WHERE

Can be used keyword WHERE in SELECT to specify conditions in the query:

SELECT , , … FROM WHERE ;

You can specify the following conditions in a request:

text comparison;
comparison of numerical values;
logical operations AND (and), OR (or) and NOT (negation).

Example

Try the following commands. Pay attention to the conditions specified in WHERE:

SELECT * FROM course WHERE dept_name='Comp. Sci.'; SELECT * FROM course WHERE credits>3; SELECT * FROM course WHERE dept_name="Comp. Sci." AND credits>3;

16. GROUP BY

The GROUP BY operator is often used with aggregate functions such as COUNT , MAX , MIN , SUM , and AVG to group output values.

SELECT , , … FROM GROUP BY ;

Example

Let's display the number of courses for each faculty:

SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name;

17. HAVING

The HAVING keyword was added to SQL because WHERE cannot be used with aggregate functions.

SELECT , , ... FROM GROUP BY HAVING

Example

Let's display a list of faculties that have more than one course:

SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name HAVING COUNT(course_id)>1;

18. ORDER BY

ORDER BY is used to sort query results in ascending or descending order. ORDER BY sorts in ascending order unless ASC or DESC is specified.

SELECT , , … FROM ORDER BY , , …ASC|DESC;

Example

Let's display a list of courses in ascending and descending order of the number of credits:

SELECT * FROM course ORDER BY credits; SELECT * FROM course ORDER BY credits DESC;

19. BETWEEN

BETWEEN is used to select data values from a certain range. Numeric and text values, as well as dates, can be used.

SELECT , , … FROM WHERE BETWEEN AND ;

Example

Let's display a list of instructors whose salary is more than 50,000 but less than 100,000:

SELECT * FROM instructor WHERE salary BETWEEN 50000 AND 100000;

20.LIKE

The LIKE operator is used in WHERE to specify a pattern to look for a similar value.

There are two free operators that are used in LIKE:

% (none, one or more characters);
_ (one character).

SELECT , , … FROM WHERE LIKE ;

Example

Let's display a list of courses whose names contain "to" , and a list of courses whose names start with "CS-":

SELECT * FROM course WHERE title LIKE '%to%'; SELECT * FROM course WHERE course_id LIKE "CS-___";

21. IN

With IN, you can specify multiple values for the WHERE clause:

SELECT , , … FROM WHERE IN ( , , …);

Example

Let's display the list of students from directions Comp. Sci., Physics and Elec. English:

SELECT * FROM student WHERE dept_name IN('Comp. Sci.', 'Physics', 'Elec. Eng.');

22. JOIN

JOIN is used to link two or more tables using common attributes within them. The image below shows various ways joins in SQL. Notice the difference between a left outer join and a right outer join:

SELECT , , … FROM JOIN ON = ;

Example 1

Here is a list of all courses and the corresponding department information:

SELECT * FROM course JOIN department ON course.dept_name=department.dept_name;

Example 2

Here is a list of all required courses and details about them:

SELECT prereq.course_id, title, dept_name, credits, prereq_id FROM prereq LEFT OUTER JOIN course ON prereq.course_id=course.course_id;

Example 3

Let's display a list of all courses, regardless of whether they are required or not:

SELECT course.course_id, title, dept_name, credits, prereq_id FROM prereq RIGHT OUTER JOIN course ON prereq.course_id=course.course_id;

23 View

View is a virtual SQL table created as a result of executing an expression. It contains rows and columns and is very similar to a normal SQL table. View always shows the latest information from the database.

Creation

CREATE VIEW AS SELECT , , … FROM WHERE ;

Removal

DROP VIEW ;

Example

Let's create a view consisting of courses with 3 credits:

24. Aggregate functions

These functions are used to obtain an aggregate result related to the data in question. The following are commonly used aggregate functions:

COUNT (col_name) - returns the number of rows;
SUM (col_name) - returns the sum of the values in the given column;
AVG (col_name) - returns the average value of the given column;
MIN (col_name) - returns the smallest value of the given column;
MAX(col_name) - returns highest value this column.

25. Nested subqueries

Nested subqueries are SQL queries that include SELECT , FROM , and WHERE statements nested within another query.

Example

Let's find the courses that were taught in the fall of 2009 and in the spring of 2010:

SELECT DISTINCT course_id FROM section WHERE semester = 'Fall' AND year= 2009 AND course_id IN (SELECT course_id FROM section WHERE semester = 'Spring' AND year= 2010);

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

Exists 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 vary across platforms, 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 each time the function is called, and different challenges functions within the same query can work with different sets of rows.

We continue to study the SQL query language, and today we will talk about SQL string functions. We will look at basic and commonly used string functions such as: LOWER, LTRIM, REPLACE and others, we will consider everything, of course, with examples.

SELECT name || surname AS FIO FROM table

Or to separate with a space enter

SELECT name || " " || surname AS FIO FROM table

those. two vertical bars combine two columns into one, and to separate them with a space, I put a space between them ( any character can be used, such as dash or colon) in apostrophes and combined also with two vertical bars ( Transact-SQL uses + instead of two vertical bars).

INITCAP function

Next comes a very useful function, INITCAP- which returns a value in a string in which each word begins with a capital letter and continues with small letters. This is necessary if you do not follow the rules for filling in one or another column and in order to display the whole thing in nice view you can use this function, for example, in your table there are entries in the name column of the following form: IVAN Ivanov or Peter Petrov, you use this function.

SELECT INITCAP (name) AS FIO FROM table

And you get it like this.

UPPER function

A similar function, only returning all capitalized characters, is UPPER.

SELECT UPPER (name) AS FIO FROM table

name – column name;
20 - number of characters ( field length);
‘-‘ is the character to be padded to the required number of characters.

RPAD function

Let's take a look at the inverse function. RPAD- the action and syntax is the same as for LPAD, only the characters on the right are complemented ( in LPAD on the left).

SELECT RPAD (name, 20, "-") AS name FROM table

Ivan—————-

Sergei-----

LTRIM function

Next comes also in some cases a useful function, LTRIM- this function removes the leftmost characters you specify. For example, you have a “city” column in your database, in which the city is indicated as “Moscow”, and there are also cities that are indicated simply as “Moscow”. But you need to display the report only in the form of "Moscow" without "city", but how to do this if there are such and such? You're just specifying a kind of pattern "g." and if the leftmost characters start with "r", then those characters will simply not be output.

SELECT LTRIM (city) AS gorod FROM table

This function looks at the characters on the left, if there are no characters according to the pattern at the beginning of the line, then it returns the original value of the cell, and if there are, it deletes them.

RTRIM function

Also, let's look at the inverse function right away. RTRIM– the same as LTRIM only characters are searched on the right.

Note! In Transact-SQL, the RTRIM and LTRIM functions remove spaces from the right and left, respectively.

REPLACE function

Now consider such an interesting function as REPLACE- it returns a string in which all matches of characters are replaced with your characters that you specify. What can it be used for, for example, you have columns in the database in which there are some separator characters, let's say "/". For example, Ivan / Ivanov, and you would like to display Ivan-Ivanov, then write

SELECT REPLACE (name, "/", "-") FROM table

and you will have a character substitution.

This function replaces only the full match of characters, if for example you specify "-" i.e. three dashes, it will only look for three dashes, and it will not replace each individual dash, unlike the following function.

TRANSLATE function

TRANSLATE is a string function that replaces all characters in a string with the characters you specify. Based on the name of the function, you can guess that this is a full line feed. The difference between this function and REPLACE is that it replaces each character you specify, i.e. you have three characters, let's say abc and c using TRANSLATE You can replace it with abc, so you have a=a, b=b, c=c, and by this principle all matches of characters will be replaced. And if you replaced using REPLACE, then you were looking for only a complete match of the abc characters located in a row.

SUBSTR function

SUBSTR – given function, returns only the range of characters you specify. In other words, let's say a string of 10 characters, but you don't need all ten, but let's say you need only 3-8 ( third to eighth). With this function, you can easily do this. For example, you have some fixed-length identifier in the database (like: AA-BB-55-66-CC) and each combination of characters means something. And at one fine moment you were told to display only 2 and 3 combinations of characters, for this you write a query of the following form.

SELECT SUBSTR (ident, "4", "8") FROM table

those. we output all characters from 4 to 8, and after this query you will get this:

LENGTH function - string length

The following function may also come in handy, this is LENGTH- which simply counts the number of characters in a string. For example, you need to find out how many characters in each cell of the column "name" is allowed, the table is as follows.

SELECT LENGTH (name) FROM table

after this request you will get this.

Here we are with you and examined the main SQL string functions. In the following articles, we will continue our study of SQL.

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. 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.

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

The query will return the value 10505.90.

And again exercise for self-determination. 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 about the company's divisions. Print 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 the same syntax, which is used when you want to determine the maximum value among all the 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

It's 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 belonging 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 categories (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
The property	Apartments	89	18690
The property	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
The property	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