Outer join examples:

SELECT * FROM SalesPeople INNER JOIN Customer ON SalesPeople.City=Customer.City
. SELECT * FROM Customer LEFT OUTER JOIN SalesPeople ON SalesPeople.City=Customer.City
. SELECT * FROM Customer FULL OUTER JOIN SalesPeople ON SalesPeople.City=Customer.City

Cartesian joins and self-joins
. If, when including several tables, certain options for joining tables are not used, then such joins are called Cartesian. They are used to get rows from two different tables. Then, for example, when joining two tables, each of which contains 20 rows, the resulting table will contain 100 rows - each of the rows of one table with each of the rows of another table. SELECT * FROM Customer, Orders.
. Joins of identical tables are called self-joins.

WHERE clause

1. Create inner connections
Communication between tables is carried out using comparison operators, and the list of output columns includes qualifying names for columns of the same name from the source tables.

Main types of connections:
. Equijoins are joins of tables based on equalities. Linking between tables on key columns ensures referential integrity. If the connection uses a primary and a foreign key, then there is always a one-to-many relationship (ancestor / descendant).

. Theta compounds is a join where inequality (, >=, SQL Server Notes) is used as the comparison operator
In SQL Server, left, right, and full joins can be specified in the WHERE clause with [*]=[*]. In fact, an outer join is implemented, which in other DBMS is implemented in the FROM section.

Examples of internal connections

SELECT C.CName, S.SName, S.City FROM SalesPeople S, Customer C WHERE S.City=C.City
SELECT SName, CName FROM SalesPeople, Customer WHERE SName

2. Filtering rows of the output set
The WHERE clause also allows you to define, i.e. a boolean condition that can be either true or false. In addition, one or both of the compared values ​​in the predicate may be NULL, in which case the result of the comparison may be UNKNOWN. The SELECT statement retrieves only those rows from tables for which TRUE is true, excluding rows for which it is FALSE or UNKNOWN.

SQL - Lesson 4. Selecting data - SELECT statement

SELECT select_what FROM select_from;

Let's first look at all the columns from the users table:

SELECT * FROM users;

That's all our data that we entered into this table. But suppose we only want to look at the id_user column (for example, in the last lesson, we needed to know what id_users are in the users table to populate the topics table). To do this, we will specify the name of this column in the query:

SELECT id_user FROM users;

Well, if we want to see, for example, the names and e-mails of our users, then we will list the columns of interest separated by commas:

SELECT name, email FROM users;

Similarly, you can see what data our other tables contain. Let's see what topics we have:

SELECT * FROM topics;

Now we have only 4 topics, and if there are 100 of them? I would like them to be displayed, for example, alphabetically. There is a keyword for this in SQL. ORDER BY followed by the name of the column by which sorting will occur. The syntax is the following:

SELECT column_name FROM table_name ORDER BY sort_column_name;

The default sorting is ascending, but this can be changed by adding the keyword DESC

Now our data is sorted in descending order.

You can sort by several columns at once. For example, the following query will sort the data by the topic_name column, and if there are several identical rows in this column, then the id_author column will be sorted in descending order:

Compare the result with the result of the previous query.

Very often we do not need all the information from the table. For example, we want to know which topics were created by the user sveta (id=4). There is a keyword for this in SQL. WHERE, the syntax for such a request is as follows:

For our example, the condition is the user ID, i.e. we only want rows that have 4 in the id_author column (user ID sveta):

Or we want to know who created the "bicycles" theme:

Of course, it would be more convenient to display the author's name instead of the author's id, but the names are stored in another table. In later lessons, we will learn how to select data from multiple tables. In the meantime, let's learn what conditions can be specified using the WHERE keyword.

LIKE operator metacharacters

The most common metacharacter is % . It means any characters. For example, if we want to find words that start with the letters "vel", then we will write LIKE "vel%", and if we want to find words that contain the characters "club", then we will write LIKE "%club%". For example:

Another commonly used metacharacter is _ . Unlike %, which denotes few or no characters, underscore denotes exactly one character. For example:

Pay attention to the space between the metacharacter and "fish", if you skip it, the request will not work, because metacharacter _ stands for exactly one character, and a space is also a character.

It's enough for today. In the next lesson, we will learn how to query two or more tables. In the meantime, try to make your own queries against the posts table (messages).

So, there are three tables in the forum database:

users (users);

Topics (topics);

· posts (messages).

You need to see what data they contain. To do this, there is an operator in SQL SELECT. The syntax for using it is as follows:

SELECT select_what FROM select_from;

Instead of “what_to_select”, you must specify either the name of the column whose values ​​you want to see, or the names of several columns separated by commas, or the asterisk symbol (*), meaning the selection of all columns of the table. Instead of "from_choose" you should specify the name of the table.

First, let's look at all the columns from the users table:

SELECT * FROM users;

This is all the data that has been entered into the table.

Let's assume that we only need to look at the id_user column (because we need to know what id_users are in the users table to populate the topics table). To do this, specify the name of this column in the query:

SELECT id_user FROM users;

If you need to see, for example, the names and e-mails of users, then you need to list the columns of interest separated by commas:

SELECT name, email FROM users;

Similarly, you can see what data other tables contain.

First, let's see what themes are:

SELECT * FROM topics;

Now there are only 4 topics in the table, but what if there are 100 of them? I would like them to be displayed, for example, alphabetically. There is a keyword for this in SQL. ORDER BY followed by the name of the column to sort by. The syntax is the following:

SELECT column_name FROM table_name ORDER BY sort_column_name;

The default sorting is ascending, but this can be changed by adding the keyword DESC.

The data is now sorted in descending order.

You can sort by several columns at once. For example, the following query will sort the data by the topic_name column, and if there are several identical rows in this column, then the id_author column will be sorted in descending order:

Compare the result with the result of the previous query.

Very often the user does not need all the information from the table. For example, you need to find out which topics were created by the user sveta (id = 4). There is a keyword for this in SQL. WHERE, the syntax for such a request is as follows:

SELECT column_name FROM table_name WHERE condition;

For our example, the condition is the user ID, i.e. only those rows are needed, in the id_author column of which is 4 (user ID sveta):

SELECT * FROM topics WHERE id_author=4;

Now you need to find out who created the theme "bicycles":

Of course, it would be more convenient to display the author's name instead of the author's id, but the names are stored in another table. Next, let's look at how to select data from multiple tables. In the meantime, let's learn what conditions can be specified using the WHERE keyword.

Operator	Description
= (equal)	Values ​​equal to the specified are selected. Example: SELECT * FROM topics WHERE id_author=4; Result:
> (more)	Values ​​greater than the specified are selected. Example: SELECT * FROM topics WHERE id_author > 2; Result:
< (меньше)	Values ​​less than the specified are selected. Example: SELECT * FROM topics WHERE id_author< 3; Результат:
>= (greater than or equal to)	Values ​​greater than and equal to the specified value are selected. Example: SELECT * FROM topics WHERE id_author >= 2; Result:
<= (меньше или равно)	Values ​​less than or equal to the specified value are selected. Example: SELECT * FROM topics WHERE id_author<= 3; Результат:
!= (not equal)	Values ​​not equal to the specified are selected. Example: SELECT * FROM topics WHERE id_author != 1; Result:
IS NOT NULL	Rows that have values ​​in the specified field are selected. Example: SELECT * FROM topics WHERE id_author IS NOT NULL; Result:
IS NULL	Rows that do not have a value in the specified field are selected. Example: SELECT * FROM topics WHERE id_author IS NULL; Result: Empty set - no such strings.
BETWEEN (between)	Values ​​between the specified values ​​are selected. Example: SELECT * FROM topics WHERE id_author BETWEEN 1 AND 3; Result:
IN (value contained)	The values ​​corresponding to the specified ones are selected. Example: SELECT * FROM topics WHERE id_author IN (1, 4); Result:
NOT IN (value not contained)	Values ​​other than those specified are selected. Example: SELECT * FROM topics WHERE id_author NOT IN (1, 4); Result:
LIKE (match)	Values ​​that match the sample are selected. Example: SELECT * FROM topics WHERE topic_name LIKE "vel%"; Result: Possible metacharacters of the LIKE operator will be discussed below.
NOT LIKE	Values ​​that do not match the sample are selected. Example: SELECT * FROM topics WHERE topic_name NOT LIKE "vel%"; Result:

Practical work No. 9

Lesson topic: Creating SQL queries.

Purpose of the lesson: Learn how to use the SQL language to create queries.

Lesson plan :

1. Creation of SQL queries for selection.

2. Creation of SQL queries for a selection with a selection condition.

3. Creation of SQL queries with calculation.

4. Creation of SQL queries for grouping.

SQL ( Structured Query language) - a structured query language that provides tools for creating and processing data in a database.

In order to create a query using SQL language tools, you must perform the following steps:

Create a query using the constructor.

· In the Query Builder, right-click in the table display area and select "SQL Mode" from the context menu.

1. CreationSQL queries for selection.

Let's create a simple query to select goods and orders for these goods using SQL.

· Open the SQL query editor.

In the editor window, enter the following query procedure

SELECT Orders.[Order ID], Products.[Product name], Orders. Quantity, Goods. Price, Products.[Unit], Orders.[Order Date]

FROM Products INNER JOIN Orders ON Products.[Product ID] = Orders. Product;

· Save the query as SQL query.

· Check that the query works correctly.

Let's analyze the syntax of this operation:

SELECT is a statement that tells the database that this operation is a query (almost all queries begin with this word). After the SELECT statement, a listing of those fields that will be included in the query usually begins. For example, the record SELECT Orders.[Order Code] will mean that the query will include the field Order code from the order table. If the field name contains spaces, it must be enclosed in square brackets.

FROM is the operator responsible for the data source. If you need to specify relationships between tables, then you need to use the INNER JOIN operator.

INNER JOIN - Joins records from two tables if the join fields of these tables contain the same values. For example, the entry “FROM Products INNER JOIN Orders ON Products.[Product Code] = Orders. Product" means that in this request procedure, the field Product code From the Products table is the record source for the field Product tables "Orders"

Exercise: Create an SQL query that allows you to display information about customers who bought the product.

2. Creation of SQL queries for a selection with a selection condition.

Using the SQL language, we will create a query that allows you to display information about employees who sold goods in quantities greater than 3.

To create queries with a selection condition, the WHERE clause is used.

Create an SQL select query containing the following fields: Full Name Employee Order code Which one to serve Quantity. Check if it works correctly.

· Now you need to add a selection condition. Open the SQL editor and add "WHERE (((Orders. Quantity)>3))" to the end of the query procedure.

· Save the query under the name "Select with SQL selection condition". Check if the request works.

Exercise: Create an SQL query that selects all products for which the price is not stopped and the price does not exceed 20 currency units.

3. Creation of SQL queries with calculation.

Let's create an SQL query that allows you to display the customer's last name, product name, order amount (price*quantity).

The request procedure looks like this:

SELECT Clients. Surname, Products.[Product Name], Orders! Quantity*Products! Price AS [Order Amount]

FROM Products INNER JOIN (Customers INNER JOIN Orders ON Clients.[Customer ID]=Orders. Customer) ON Products.[Product ID]=Orders. Product;

4. Creation of SQL queries for grouping.

Let's create a query that will allow us to determine the total amount of goods ordered by each client.

The GROUP BY clause is used to group records.

The request procedure looks like this:

SELECT Clients. Last Name, Sum(Orders! Quantity*Items! Price) AS [Sum of Orders]

FROM Products INNER JOIN (Customers INNER JOIN Orders ON Clients.[Customer ID] = Orders. Customer) ON Products.[Product ID] = Orders. Product

GROUP BY Clients. Surname;

Control questions:

1. What is SQL?

2. How to create a SQL query?

3. Structural components of the SQL language.

4. List the main operators of the SQL language.

5. General information about SELECT, WHERE, FROM, INNER JOIN, GROUP BY statements.

Task for successful students: Try to create a SQL query yourself that allows you to delete all records from the "Products" table, the price of which is less than 3 monetary units

Conclusion: In the course of this work, the main ways and methods of creating SQL queries were studied. The structural components of the query language were considered, and the syntax for using operators was described.