sql function with input parameters. SQL String Functions - Usage Examples
Built-in functions SQL designed to facilitate and speed up data processing. The special feature is that they can be specified directly in the expression. All built-in functions can be divided into groups.
Mathematical functions:
ABS(meaning) – returns the absolute value of a number;
Round(value, accuracy) – returns a numeric value rounded to the value specified by the argument accuracy number of decimal places;
SIGN(meaning) – returns minus if the number is negative, and plus otherwise;
POWER(meaning, 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 line;
CHAR(number) – return the 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 line;
LEFT(string, number)/ RIGHT(string, number)– returns the specified argument number number of characters in the line, starting from the left/right edge;
SUBSTRING(line, position, length) – returns a substring of the specified length from a string, starting from the specified position;
LOWER(line) /UPPER(line) – returns a string converted to lowercase/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 month number from the specified date;
YEAR(value_date)– returns the year value from the specified date;
DATENANE( part, value_date) – returns a 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 data_type)
CONVERT(data_type, meaning)
Argument meaning in functions specifies the value that needs to be converted.
7.3. Data Definition Language Commands
The Data Definition Language contains commands that are used to create, modify, and delete a database and its objects.
Creating a table
Creating a new table is done with 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 on 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 of no more than 128 characters.
A table can contain a calculated column, in which case the value of the column is determined by an expression that is stored in the table structure. A calculated column cannot be modified, so it cannot have NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, or DEFAULT integrity constraints.
The syntax for describing a table column is:
column_name data_type[(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 a column is being created that supports automatic numbering (counter column). 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 specified, they have the value 1. IDENTITY can only be specified for those columns that are of integer or decimal types. Inserting values into the IDENTITY column is not allowed.
There are two groups of integrity constraints processed by the DBMS:
Declarative integrity constraints, which are declared when a table is created or modified;
Procedural integrity constraints that are processed by triggers.
Declarative integrity constraints can be table-level constraints or table-level constraints. Column-level restrictions apply to only one column. Each declarative integrity constraint can be given a name.
Column level constraint definitions have the following syntax:
((PRIMARY KEY | UNIQUE | NOT NULL ) |FOREIGN KEY REFERENCES table_name( column_name)
|CHECK boolean_expression)
The data integrity constraint name must be unique within the database. Let's look at the constraints that can be defined at the column level:
PRIMARY KEY primary key constraint. All table primary key values must be unique and different from Null. A table can only have one primary key. If it is composite, then integrity constraints on the primary key are specified at the table level;
Constraints on the uniqueness of a UNIQUE column value. This means that a table cannot have two records that have the same value in that column;
A NOT NULL constraint that prohibits storing a NULL value in a column;
FOREIGN KEY foreign key constraint (referential integrity constraint). For a column that is a foreign key, REFERENCES specifies the name of the table to which the relationship is being established and the name of the column of that table on which the relationship will be established. This table is the main (parent) table in relation to the created table. The main table column whose values are being linked must have a PRIMARY KEY constraint set.
If the key of an external table consists of several 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, indicate the name of the main table and the names of the columns of the main table that are referenced by the foreign key.
Referential integrity establishes rules for adding and changing 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 changing 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 foreign key value of a child table will be automatically changed (deleted) when the primary key value of the parent table is modified;
SET NULL means that if the primary key of the parent table is changed (deleted), in all referencing rows of the child table, the foreign key values will be automatically assigned NULL values;
SET DEFAULT means that if the primary key of the parent table is changed (deleted), in all referencing rows of the child table, the foreign key values will be automatically assigned default values.
Let us supplement the example of the educational database “University”, the design of which was discussed in Chapter. 4.3 tables DISCIPLINE and GENERAL REPORT. Tables 6 and 7 describe the logical structure of the tables.
Table 6
Logical structure of the DISCIPLINE information object
Table 7
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 us present requests for creating tables in accordance with the one shown in Fig. 35 infological database model.
Rice. 35. Database scheme “University”
As can be seen from the database schema, the FACULTY table is an independent table, so it is created first. Request to create a table taking into account the description of the logical structure in the 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 the table. 5 (p.62).
CREATE TABLE [specialty] (
[specialty number] int PRIMARY KEY,
[name of specialty] char (60),
[cost of education] )
The GROUP table is a table dependent on FACULTY and SPECIALTY. 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( number special- ness)ON DELETE CASCADE ON UPDADE CASCADE,
[faculty number] tinyint FOREIGN KEY REFERENCES faculty( number 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 create a query. We also take into account that the column group number are foreign keys:
CREATE TABLE [student] (
[group number] smallint NOT NULL FOREIGN KEY REFERENCES group( number 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 REPORT table depends 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 create a query:
CREATE TABLE [discipline] (
[discipline code] int PRIMARY KEY,
[discipline name] char(50))
Now you can create a query to create the general statement table. Since the primary key of a table is composite, the PRIMARY KEY constraint must be specified at the table level. For example, let's set FOREIGN KEY restrictions also at the table level. The request will look like:
CREATE TABLE [general statement] (
[discipline code] int,
[grade book number] char(8),
[grade] NOT NULL , PRIMARY KEY ([discipline code],[grade book number]), FOREIGN KEY ([discipline code]) REFERENCES [discipline] ([discipline code]), FOREIGN KEY ([grade book number]) REFERENCES [student] ([grade book number]))
Changing the table structure
Changing the table structure is performed 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, and manage integrity constraints both at the column level and at the table level. The meaning of many of the parameters and keywords is the same as the corresponding parameters and keywords of the CREATE TABLE command.
Delete a table
Dropping a table is done using the DROP TABLE command. Command syntax:
DROP TABLE table
For example, a request to delete the STUDENT table looks like this:
DROP TABLE Student
When deleting a table, you SHOULD take into account the relationships established in the database between the tables. If the table being deleted is referenced by another table using the FOREIGN KEY integrity constraint, the DBMS will not allow its deletion.
Creating an index
Indexes are used to speed up access to specific data in a database table. An index is a structure that organizes the values in one or more columns of a database table, such as the Last Names 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 rows of the table.
An index provides pointers to data values stored in specific columns of a table and arranges those pointers according to a specified sort order. Searching for data in a table using an index is similar to searching an index in a book. First, the index is searched for a specific value, and then the corresponding pointer is navigated to the row containing that value.
The index is created using the CREATE INDEX command:
CREATE INDEX
Name_ index ON name _tables(column [,...])
where UNIQUE specifies that the index should store only unique values.
An index can be created on one or more columns (composite index). Composite indexes allow you to differentiate between records that have the same values in the same column.
Example: Create a composite index on the STUDENT table for the Last Name and Date of Birth fields
CREATE INDEX Ind_Fam ON
Student(Last name, [Date of birth] DESC)
You should only create an index on a table if you expect to frequently query the data in the indexed columns. Indexes take up disk space and slow down row addition, deletion, and update operations.
Removing a table index
The DROP command removes an index from a table. The syntax of the DROP command to remove an index is:
DROP INDEX index ON table
Before you can remove an index from a table or the table itself, you must close the table.
Example: Remove index Ind_Fam from table STUDENT
DROP INDEX Ind_Fam ON Student
| SQL Math Functions | Mathematical function |
|---|---|
| Description | ABS(X) |
| Returns the absolute value of the number X | ACOS (X) |
| Returns the arc cosine of X | ASIN(X) |
| Returns the arcsine of the number X | ATAN(X) |
| Returns the arctangent of X | COS(X) |
| Returns the cosine of the number X | EXP(X) |
| Returns the exponent of X | SIGN(X)<0,0, если Х=0, +1 , если Х>0 |
| Returns -1 if X | LN(X) Returns natural logarithm |
| numbers X | MOD(X,Y) |
| Returns the remainder when X is divided by Y | CEIL (X) |
| Returns the smallest integer greater than or equal to X | ROUND(X,n) |
| Rounds the number X to a number with n decimal places | SIN(X) |
| Returns the sine of X | SQRT(X) |
| Returns the square root of a number X | TAN(X) |
| Returns the tangent of X | FLOOR (X) |
| Returns the largest integer less than or equal to X | LOG(a,X) |
| Returns the logarithm of X to base A | SINH(X) |
| Returns the hyperbolic sine of X | COSH(X) |
| Returns the hyperbolic cosine of X | TANH(X) |
| Returns the hyperbolic tangent of X | TRANC(X,n) |
| Truncates the number X to a number with n digits after the decimal point | POWER(A,X) |
Returns the value of A raised to the power of X
The set of built-in functions may vary depending on the version of the DBMS from one manufacturer and also in DBMSs from different manufacturers. For example, in the SQLBase DBMS, Century Inc. There is a function @ATAN2(X,Y) that returns the arctangent of Y/X, but there is no function SIGN(X) .
Arithmetic expressions are needed to obtain data that is not directly stored in the columns of database tables, but whose values are required by the user. Let's say you need a list of employees showing the pay each employee received, including bonuses and penalties.
SELECT ENAME, SAL, COMM, FINE, SAL + COMM - FINE FROM EMPLOYEE ORDER BY DEPNO;
The arithmetic expression SAL + COMM - FINE is output as a new column in the result table, which is calculated as a result of the query. Such columns are also called derived (calculated) attributes or fields.
SQL provides you with a wide range of functions for manipulating string data (string concatenation, CHR, LENGTH, INSTR and others). A list of the main functions for processing string data is given in Table 8.3.
| SQL Functions for String Processing | Mathematical function |
|---|---|
| Function | CHR(N) |
| Returns the ASCII code character for decimal code N | ASCII(S) |
| Returns the decimal ASCII code of the first character of a string | INSTR(S2.S1.pos[,N] |
| Returns the position of string S1 in string S2 greater than or equal to pos.N - number of occurrences | LENGHT(S) |
| Returns the length of a string | LOWER(S) |
| Replaces all characters in a string with uppercase characters | INITCAP(S) |
| Sets the first character of each word in a string to uppercase and the remaining characters of each word to uppercase | SUBSTR(S,pos,[,len]) |
| Selects a substring of length len in the string S, starting at position pos | UPPER(S) |
| Converts capital letters in a string to uppercase letters | LPAD(S,N[,A]) |
| Returns the string S left padded with A up to N characters. Character - default filler - space | Rpad(S,N[,A]) |
| Returns the string S , right padded with A up to N characters. Character - default filler - space | LTRIM(S,) |
| Returns the left-truncated string S. Characters are removed as long as the character being removed is included in the string - pattern S1 (default - space) | RTRIM(S,) |
| Returns the right-truncated string S. Characters are removed as long as the character being removed is included in the string - pattern S1 (default - space | TRANSLATE(S,S1,S2)<>Returns the string S with all occurrences of string S1 replaced by string S2. If S1 |
| S2 , then characters that do not match are excluded from the resulting string | REPLACE(S,S1,[,S2]) |
| Returns the string S for which all occurrences of the string S1 are replaced by the substring S2. If S2 is not specified, then all occurrences of the substring S1 are removed from the resulting string | NVL(X,Y) |
If X is NULL , then returns Y either a string, a number, or a date, depending on the original type of Y
The names of the same functions may differ in different DBMSs. For example, the Oracle DBMS function SUBSTR(S, pos, [, len]) in the SQLBase DBMS is called @SUBSTRING(S, pos, len) . The SQLBase DBMS has functions that are not available in the Oracle DBMS (see.
The main functionality of the SQL language is given below. Definition of data.
Database creation. To create a new database, use the CREATE DATABASE statement. The statement structure specifies the name of the database to be created.
Creating tables. The base table is created using the CREATE TABLE statement. This statement specifies the field names, data types for them, and length (for some data types). SQL uses the following data types:
INTEGER – integer;
CHAR – character value;
VARCHAR – character value, only non-blank characters are stored;
DECIMAL – decimal number;
FLOAT – floating point number;
DOUBLE PRECISION – double precision floating point;
DATETIME – date and time;
BOOL – Boolean value.
The table creation statement specifies restrictions on column values and on the table. Possible restrictions are shown in table. 4.8
Table 4.8 Limitations on defined data
For a relational data model, specifying a foreign key (FOREIGNKEY) is essential. When declaring foreign keys, you must impose appropriate restrictions on the column, for example, NOT NULL.
In an SQL statement, CHECK denotes semantic constraints that ensure data integrity, such as limiting the set of valid values for a particular column.
You cannot use the create table statement more than once on the same table. If, after its creation, inaccuracies are discovered in its definition, then changes can be made using the ALTER TABLE statement. This statement is designed to change the structure of an existing table: you can remove or add a field to an existing table.
Data manipulation. SQL allows a user or application program to change the contents of a database by inserting new data, deleting or modifying existing data.
Inserting new data is a procedure for adding rows to a database and is performed using the INSERT statement.
Data modification involves changes to values in one or more columns of a table and is performed using an UPDATE statement. Example:
SET amount=amount+1000.00
WHERE amount>0
Removing rows from a table using the DELETE statement. The operator syntax is:
FROM table
The WHERE clause is optional, but if it is not included, all entries in the table will be deleted. It is useful to use the SELECT statement with the same syntax as the DELETE statement to pre-check which records will be deleted.
Ensuring data integrity. The SQL language allows you to define fairly complex integrity constraints, the satisfaction of which will be checked for all database modifications. Monitoring the results of transactions, processing errors that occur, and coordinating parallel work with the database of several applications or users is provided by the COMMIT (records the successful completion of the current transaction and the beginning of a new one) and ROLLBACK (the need for a rollback - automatic restoration of the database state to the beginning of the transaction) operators.
Data sampling is one of the most important database functions that corresponds to the SELECT statement. An example of using the operator was discussed in the previous section.
In SQL, you can create nested sequences of queries (subqueries). There are certain types of queries that are best implemented using subqueries. These queries include so-called existence checks. Let's assume that you want to get data about students who do not have a seven-point grade. If an empty set is returned, then this means only one thing - each student has at least one such grade.
Linking tables. SQL statements allow you to retrieve data from more than one table. One way to do this is to link tables using one common field.
The SELECT statement must contain a constraint on matching the values of a specific column (field). Then only those rows in which the values of the specified column match will be retrieved from the related tables. The column name is indicated only together with the table name; otherwise the statement will be ambiguous.
You can use other types of table linking: the INTER JOIN operator ensures that the resulting set of records contains matching values in related fields. Outer joins (OUTER JOIN) allow you to include in the query result all the rows from one table and the corresponding rows from another
Access control. SQL ensures synchronization of database processing by various application programs, protecting data from unauthorized access.
Data access in a multi-user environment is controlled using GRANT and REVOKE statements. In each statement it is necessary to specify the user, the object (table, view) in relation to which the permissions are set, and the permissions themselves. For example, the GRANT statement gives user X the ability to retrieve data from the PRODUCT table:
GRANT SELECT ON PRODUCT TO X
The REVOKE statement revokes all previously granted permissions.
Embedding SQL in Application Programs. Real applications are usually written in other languages that generate SQL code and pass it to the DBMS as ASCII text.
The IBM standard for SQL products regulates the use of the built-in SQL language. When writing an application program, its text is a mixture of commands from the main programming language (for example, C, Pascal, Cobol, Fortran, Assembler) and SQL commands with a special prefix, for example. ExecSQL. The structure of SQL statements has been expanded to accommodate host language variables in an SQL construct.
The SQL processor modifies the type of program in accordance with the requirements of the compiler of the main programming language. The function of the compiler is to translate (translate) a program from the source programming language into a language close to machine language. After compilation, the application program (application) is an independent module.
SQL dialects
Modern relational DBMSs use dialects of the SQL language to describe and manipulate data. A subset of the SQL language that allows you to create and describe a database is called DDL (Data Definition Language).
Initially, the SQL language was called SEQUEL (Structured English Query Language), then SEQUEL/2, and then simply SQL. Today, SQL is the de facto standard for relational DBMSs.
The first language standard appeared in 1989 - SQL-89 and was supported by almost all commercial relational DBMSs. It was general in nature and subject to broad interpretation. The advantages of SQL-89 can be considered the standardization of the syntax and semantics of operators for sampling and data manipulation, as well as the fixation of means for limiting the integrity of the database. However, it was missing such an important section as database schema manipulation. The incompleteness of the SQL-89 standard led to the appearance in 1992. the next version of the SQL language.
SQL2 (or SQL-92) covers almost all the necessary issues: database schema manipulation, transaction and session management, support for client-server architectures or application development tools.
The next step in the development of the language is the SQL 3 version. This version of the language is complemented by a trigger mechanism, the definition of an arbitrary data type, and an object extension.
Currently there are three levels of the language: beginner, intermediate and complete. Many manufacturers of their DBMS use their own SQL implementations, based at least on the initial level of the corresponding ANSI standard, and containing some extensions specific to a particular DBMS. In table 4.9 provides examples of SQL dialects.
Table 4.9 SQL dialects
| DBMS | Query language |
| System R DBMS | SQL |
| DB2 | SQL |
| Access | SQL |
| SYBASE SQL Anywhere | Watcom-SQL |
| SYBASE SQL Server | Transact_SQL |
| My SQL | SQL |
| Oracle | PL/SQL |
Object-oriented databases use the object query language OQL (Object Query Language). The OQL language was based on the SELECT command of the SQL2 language and added the ability to direct a query to an object or collection of objects, as well as the ability to call methods within a single query.
The compatibility of many used SQL dialects determines the compatibility of the DBMS. Thus, the SYBASE SQL Anywhere DBMS is as compatible as possible for a DBMS of this class with the SYBASE SQL Server DBMS. One of the aspects of this compatibility is the support in SYBASE SQL Anywhere of such a dialect of the SQL language as Transact-SQL. This dialect is used in SYBASE SQL Server and can be used in SYBASE SQL Anywhere along with the native SQL dialect - Watcom-SQL.
Control questions
1. How can a DBMS be classified?
2. What database models exist?
3. What are the main elements of information models?
4. What types of relationships between entities exist?
5. What are ER diagrams and what are they used for?
6. What does the table normalization procedure allow you to do?
7. What are the language and software tools of the DBMS?
8. What type of MS Access DBMS is it?
9. What are the main objects of the MS Access DBMS?
10. What are the main SQL operators used for?
Let's learn to summarize. No, these are not the results of studying 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 resulting value. The most commonly used SQL aggregate functions are SUM, MIN, MAX, AVG, and COUNT. It is necessary to distinguish between two cases of using aggregate functions. First, aggregate functions are used on their own and return a single result value. Second, aggregate functions are used with the SQL GROUP BY clause, that is, grouping by fields (columns) to obtain the resulting values in each group. Let's first consider cases of using aggregate functions without grouping.
SQL SUM function
The SQL SUM function returns the sum of the values in a database table column. It can only be applied to columns whose values are numbers. The SQL queries to get the resulting sum start like this:
SELECT SUM (COLUMN_NAME) ...
This expression is followed by FROM (TABLE_NAME), and then a condition can be specified using the WHERE clause. Additionally, the column name can be preceded by DISTINCT, which means that only unique values will be counted. By default, all values are taken into account (for this you can specifically specify not DISTINCT, but ALL, but the word ALL is not required).
Example 1. There is a company database with data about its divisions and employees. The Staff table also has a column with data on employee salaries. The selection from the table looks like this (to enlarge the picture, click on it with the left mouse button):
To obtain 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 beginning to complicate the tasks, bringing them closer to those 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.
We need to find out the minimum wage for employees of department number 42. To do this, write the following request:
The query will return the value 10505.90.
And again exercise for self-solution. In this and some other exercises, you will need not only the Staff table, but also the Org table, containing data about the company’s divisions:
Example 4. The Org table is added to the Staff table, containing data about the company's departments. Print the minimum number of years worked by one employee 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 need to determine the maximum value among all values in a column.
Example 5.
You need to find out the maximum salary of employees of department number 42. To do this, write the following request:
The query will return the value 18352.80
It's time exercises for independent solution.
Example 6. We again work with two tables - Staff and Org. Display the name of the department and the maximum value of the commission received by one employee in the department belonging to the group of departments (Division) Eastern. Use JOIN (joining tables) .
SQL AVG function
What is stated regarding the syntax for the previous functions described is also true for 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.
Suppose you want to find out the average length of service of employees of department number 42. To do this, 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 4 to 6 years of experience.
SQL COUNT function
The SQL COUNT function returns the number of records in a database table. If you specify SELECT COUNT(COLUMN_NAME) ... in the query, 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) of 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 be returned by the following query:
SELECT COUNT (Comm) FROM Staff
The result will be 11.
Example 10. The database and table are the same as in the previous examples.
If you want to find out the total number of records in the table, then use a query with an asterisk as an argument to the COUNT function:
SELECT COUNT (*) FROM Staff
The result will be 17.
In the next exercise for independent solution you will need to use a subquery.
Example 11. We work with one table - Staff. Display the number of employees in the planning department (Plains).
Aggregate Functions with SQL GROUP BY
Now let's look at using aggregate functions together with the SQL GROUP BY statement. The SQL GROUP BY statement is used to group result values by columns in a database table. The website has a lesson dedicated separately to this operator .
Example 12. There is a database of the advertisement portal. It has an Ads table containing data about ads 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 included in the categories (for example, the Apartments and Summer Houses parts are parts of the Real Estate category). The Units column contains data on the number of advertisements submitted, and the Money column contains data on the amount of money received for submitting advertisements.
| Category | Part | Units | Money |
| Transport | Cars | 110 | 17600 |
| Real estate | Apartments | 89 | 18690 |
| Real estate | Dachas | 57 | 11970 |
| Transport | Motorcycles | 131 | 20960 |
| Construction materials | Boards | 68 | 7140 |
| Electrical engineering | TVs | 127 | 8255 |
| Electrical engineering | Refrigerators | 137 | 8905 |
| Construction materials | Regips | 112 | 11760 |
| Leisure | Books | 96 | 6240 |
| Real estate | At home | 47 | 9870 |
| Leisure | Music | 117 | 7605 |
| Leisure | Games | 41 | 2665 |
Using the SQL GROUP BY statement, find the amount of money earned by posting ads in each category. We write the following request:
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 statement, find out which part of each category had the most listings. We write the following request:
SELECT Category, Part, MAX (Units) AS Maximum FROM Ads GROUP BY Category
The result will be the following table:
Total and individual values can be obtained in one table combining query results using the UNION operator .
Relational Databases and SQL Language
Basic SQL Commands Every Programmer Should Know
SQL or Structured Query Language is a language used 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, look at the examples given in the article and remember that you studied 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 every statement in SQL. The examples use MySQL, so a semicolon is required.
Setting up a database for examples
Create a database to demonstrate how teams 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 using the following command (the article assumes that MySQL is already installed on the system):
Mysql -u root -p
Then enter your password.
Run the following command. Let's call the database “university”:
CREATE DATABASE university; USE university; SOURCE You may need to create restrictions on certain columns in a table. When creating a table, you can set the following restrictions: You can specify more than one primary key. In this case, you will get a composite primary key. Create a table "instructor": 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)); You can view various information (value type, key or not) about table columns with the following command: DESCRIBE When you add data to each column in a table, you do not need to specify column names. INSERT INTO SELECT is used to retrieve data from a specific table: SELECT The following command can display all the data from the table: SELECT * FROM Table columns may contain duplicate data. Use SELECT DISTINCT to retrieve only non-duplicate data. SELECT DISTINCT You can use the WHERE keyword in SELECT to specify conditions in a query: SELECT The following conditions can be specified in the request: 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; The GROUP BY operator is often used with aggregate functions such as COUNT, MAX, MIN, SUM, and AVG to group output values. SELECT Let's display the number of courses for each faculty: SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name; The HAVING keyword was added to SQL because WHERE cannot be used with aggregate functions. SELECT 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; ORDER BY is used to sort query results in descending or ascending order. ORDER BY will sort in ascending order unless ASC or DESC is specified. SELECT Let's display a list of courses in ascending and descending order of credits: SELECT * FROM course ORDER BY credits; SELECT * FROM course ORDER BY credits DESC; BETWEEN is used to select data values from a specific range. Numeric and text values, as well as dates, can be used. SELECT 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; The LIKE operator is used in WHERE to specify a search pattern for a similar value. There are two free operators that are used in LIKE: Let's display a list of courses whose names contain "to" and a list of courses whose names begin with "CS-": SELECT * FROM course WHERE title LIKE ‘%to%’; SELECT * FROM course WHERE course_id LIKE "CS-___"; Using IN you can specify multiple values for the WHERE clause: SELECT Let's display a list of students from Comp majors. Sci., Physics and Elec. Eng.: SELECT * FROM student WHERE dept_name IN ('Comp. Sci.', 'Physics', 'Elec. Eng.'); JOIN is used to link two or more tables using common attributes within them. The image below shows the different ways to join in SQL. Note the difference between a left outer join and a right outer join: SELECT We will display a list of all courses and relevant information about the faculties: SELECT * FROM course JOIN department ON course.dept_name=department.dept_name; We will display 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; We will 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; 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 regular SQL table. View always shows the latest information from the database. Let's create a view consisting of courses with 3 credits: These functions are used to obtain an aggregate result related to the data in question. The following are commonly used aggregate functions: Nested subqueries are SQL queries that include SELECT , FROM , and WHERE clauses nested within another query. Let's find courses that were taught in the fall of 2009 and 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);Commands for working with databases
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 a .sql file
SOURCE 5. Delete the database
DROP DATABASE Working with tables
6. View the tables available in the database
SHOW TABLES; 
7. Create a new table
CREATE TABLE Integrity Constraints When Using CREATE TABLE
Example
8. Table information
9. Adding data to the table
INSERT INTO 10. Updating table data
UPDATE 11. Removing all data from the table
DELETE FROM 12. Delete a table
DROP TABLE Commands for creating queries
13. SELECT
14. SELECT DISTINCT
15. WHERE
Example
16. GROUP BY
Example
17. HAVING
Example
18. ORDER BY
Example
19. BETWEEN
Example
20. LIKE
SELECT Example
21. IN
Example
22. JOIN
Example 1
Example 2
Example 3
23. View
Creation
CREATE VIEW Removal
DROP VIEW Example
24. Aggregate functions
25. Nested subqueries
Example
