Ms sql server queries. An example of creating a query (Query) in a MS SQL Server database

Last update: 07/05/2017

In the previous topic, a simple database with one table was created in SQL Management Studio. Now let's define and execute the first SQL query. To do this, open SQL Management Studio, right-click on the top-level element in Object Explorer (server name) and select New Query from the context menu that appears:

After this, a window for entering SQL commands will open in the central part of the program.

Let's run a query on the table that was created in the previous topic, in particular, we'll get all the data from it. Our database is called university, and the table is dbo.Students, so to get data from the table we enter the following query:

SELECT * FROM university.dbo.Students

The SELECT statement allows you to select data. FROM specifies the source from which to take the data. In fact, with this query we are saying “SELECT all FROM the university.dbo.Students table”. It is worth noting that the name of the table uses its full path indicating the database and schema.

After entering the request, click on the Execute button on the toolbar, or you can press the F5 key.

As a result of executing the query, a small table will appear at the bottom of the program that will display the results of the query - that is, all the data from the Students table.

If we need to make multiple queries against the same database, then we can use the USE command to commit the database. In this case, when querying tables, it is enough to specify their name without the database name and schema:

USE university SELECT * FROM Students

In this case, we are executing the query as a whole for the server; we can access any database on the server. But we can also perform queries only within a specific database. To do this, right-click on the desired database and select New Query from the context menu:

If in this case we wanted to query the Students table used above, we would not have to specify the database name and schema in the query, since these values ​​would already be clear.

SQL Server Management Studio provides a complete tool for creating all types of queries. With its help you can create, save, load and edit queries. In addition, you can work on queries without connecting to any server. This tool also provides the ability to develop queries for different projects.

You can work with queries using either the Query Editor or the Solution Explorer. This article covers both of these tools. In addition to these two components of SQL Server Management Studio, we'll look at debugging SQL code using the built-in debugger.

Query editor

To open the Query Editor panel Query Editor, on the SQL Server Management Studio toolbar, click the New Query button. This panel can be expanded to display buttons for creating all possible queries, not just Database Engine queries. By default, a new Database Engine query is created, but you can also create MDX, XMLA, and other queries by clicking the corresponding button on the toolbar.

The status bar at the bottom of the Query Editor panel indicates the status of the editor's connection to the server. If you do not connect to the server automatically, when you launch the Query Editor, a Connect to Server dialog box appears, allowing you to select the server to connect to and the authentication mode.

Editing queries offline provides more flexibility than when connected to a server. To edit queries, it is not necessary to connect to the server, and the query editor window can be disconnected from one server (using the menu command Query --> Connection --> Disconnect) and connected to another without opening another editor window. To select offline editing mode, in the server connection dialog that opens when you launch the editor for a specific query type, simply click the Cancel button.

You can use the Query Editor to perform the following tasks:

creating and executing Transact-SQL statements;

saving created Transact-SQL language statements to a file;

creating and analyzing execution plans for common queries;

graphically illustrating the execution plan of the selected query.

The Query Editor contains a built-in text editor and a toolbar with a set of buttons for different actions. The main query editor window is divided horizontally into a query panel (at the top) and a results panel (at the bottom). Transact-SQL statements (that is, queries) to be executed are entered in the top pane, and the results of the system's processing of those queries are displayed in the bottom pane. The figure below shows an example of entering a query into the query editor and the results of executing that query:

The first USE request statement specifies to use the SampleDb database as the current database. The second statement, SELECT, retrieves all rows from the Employee table. To run this query and display the results, on the Query Editor toolbar, click the Execute button or press F5.

You can open several Query Editor windows, i.e. make multiple connections to one or more instances of the Database Engine. A new connection is created by clicking the New Query button on the SQL Server Management Studio toolbar.

The status bar at the bottom of the Query Editor window displays the following information related to the execution of query statements:

the status of the current operation (for example, "Request completed successfully");

database server name;

current user name and server process ID;

current database name;

time spent executing the last request;

number of lines found.

One of the main advantages of SQL Server Management Studio is its ease of use, which also applies to the Query Editor. The Query Editor provides many features to make coding Transact-SQL statements easier. In particular, it uses syntax highlighting to improve the readability of Transact-SQL statements. All reserved words are shown in blue, variables are shown in black, strings are shown in red, and comments are shown in green.

In addition, the query editor is equipped with context-sensitive help called Dynamic Help, through which you can obtain information about a specific instruction. If you don't know the syntax of an instruction, select it in the editor, and then press the F1 key. You can also highlight the parameters of various Transact-SQL statements to get help about them from Books Online.

SQL Management Studio supports SQL Intellisense, which is a type of auto-completion tool. In other words, this module suggests the most likely completion of partially entered Transact-SQL statement elements.

The object explorer can also help you edit queries. For example, if you want to know how to create a CREATE TABLE statement for the Employee table, right-click the table in Object Explorer and select Script Table As --> CREATE to --> New Query Editor Window from the context menu that appears. tables --> Using CREATE --> New Query Editor Window). The Query Editor window containing the CREATE TABLE statement created in this way is shown in the figure below. This feature also applies to other objects, such as stored procedures and functions.

The Object Browser is very useful for graphically displaying the execution plan of a particular query. The query execution plan is the execution option selected by the query optimizer among several possible options for executing a specific query. Enter the required query in the top panel of the editor, select a sequence of commands from the Query --> Display Estimated Execution Plan menu, and the execution plan for this query will be shown in the bottom panel of the editor window.

Solution Explorer

Query editing in SQL Server Management Studio is based on the solutions method. If you create an empty query using the New Query button, it will be based on an empty solution. You can see this by running a sequence of commands from the View --> Solution Explorer menu immediately after opening an empty query.

The decision may be related to none, one, or several projects. An empty solution, not associated with any project. To associate a project with a solution, close the empty solution, Solution Explorer, and Query Editor, and create a new project by running File --> New --> Project. In the New Project window that opens, select the SQL Server Scripts option in the middle pane. A project is a way of organizing files in a specific location. You can assign a name to the project and choose a location for its location on disk. When you create a new project, a new solution is automatically launched. You can add a project to an existing solution using Solution Explorer.

For each project created, Solution Explorer displays the Connections, Queries, and Miscellaneous folders. To open a new Query Editor window for a given project, right-click its Queries folder and select New Query from the context menu.

Debugging SQL Server

SQL Server, starting with SQL Server 2008, has a built-in code debugger. To begin a debugging session, select Debug --> Start Debugging from the SQL Server Management Studio main menu. We will look at how the debugger works using an example using a batch of commands. A batch is a logical sequence of SQL statements and procedural extensions that is sent to the Database Engine to execute all of the statements it contains.

The figure below shows a package that counts the number of employees working on project p1. If this number is 4 or more, a corresponding message is displayed. Otherwise, the first and last names of the employees are displayed.

To stop the execution of a package at a specific instruction, you can set breakpoints, as shown in the figure. To do this, click to the left of the line you want to stop on. When debugging begins, execution stops at the first line of code, which is marked with a yellow arrow. To continue execution and debugging, select the Debug --> Continue menu command. The batch instructions will continue to execute until the first breakpoint, and the yellow arrow will stop at that point.

Information related to the debugging process is displayed in two panels at the bottom of the Query Editor window. Information about different types of debugging information is grouped in these panels into several tabs. The left pane contains the Autos tab, Locals tab, and up to five Watch tabs. The right pane contains the Call Stack, Threads, Breakpoints, Command Window, Immediate Window, and Output tabs. The Locals tab displays variable values, the Call Stack tab displays call stack values, and the Breakpoints tab displays breakpoint information.

To end the debugging process, execute a sequence of commands from the main menu Debug --> Stop Debugging or click the blue button on the debugger toolbar.

SQL Server 2012 adds several new features to the built-in debugger in SQL Server Management Studio. Now you can perform a number of the following operations in it:

Specify a breakpoint condition. Breakpoint condition is an SQL expression whose evaluated value determines whether code execution will stop at a given point or not. To specify a breakpoint condition, right-click the red breakpoint icon and select Condition from the context menu. The Breakpoint Condition dialog box opens, allowing you to enter the required Boolean expression. In addition, if you need to stop execution if the expression is true, you should set the Is True switch. If execution needs to be stopped if the expression has changed, then you need to set the When Changed switch.

Specify the number of hits at the breakpoint. The hit count is the condition for stopping execution at a given point based on the number of times that breakpoint was hit during execution. When the specified number of passes and any other condition specified for a given breakpoint is reached, the debugger performs the specified action. The execution abort condition based on the number of hits can be one of the following:

1. unconditional (default action) (Break always);

   if the number of hits is equal to the specified value (Break when the his count equals a specified value);

   if the number of hits is a multiple of a specified value (Break when the hit count equals a multiple of a specified value);

   Break when the his count is greater or equal to a specified value.

To set the number of hits during debugging, right-click the required breakpoint icon on the Breakpoints tab, select Hit Count from the context menu, then select one of the conditions in the Breakpoint Hit Count dialog box that opens from the previous list. For options that require a value, enter it in the text box to the right of the conditions drop-down list. To save the specified conditions, click OK.

Specify a breakpoint filter. A breakpoint filter limits breakpoint operation to only specified computers, processes, or threads. To set a breakpoint filter, right-click the breakpoint you want and select Filter from the context menu. Then, in the Breakpoint Filters dialog box that opens, specify the resources that you want to restrict execution of this breakpoint to. To save the specified conditions, click OK.

Specify an action at a breakpoint. The When Hit condition specifies the action to take when batch execution hits a given breakpoint. By default, when both the hit count condition and the stopping condition are satisfied, then execution is aborted. Alternatively, a pre-specified message can be displayed.

To specify what to do when a breakpoint is hit, right-click the red icon for the breakpoint and select When Hit from the context menu. In the When Breakpoint is Hit dialog box that opens, select the action you want to take. To save the specified conditions, click OK.

Use the Quick Watch window. You can view the value of a Transact-SQL expression in the QuickWatch window, and then save the expression in the Watch window. To open the Quick Watch window, select Quick Watch from the Debug menu. The expression in this window can either be selected from the Expression drop-down list or entered into this field.

Use the Quick Info tooltip. When you hover your mouse over a code ID, the Quick Info tool displays its declaration in a pop-up window.

Table expressions are called subqueries that are used where the presence of a table is expected. There are two types of table expressions:

derived tables;

generalized table expressions.

These two forms of table expressions are discussed in the following subsections.

Derived tables

Derived table is a table expression included in the FROM clause of a query. Derived tables can be used in cases where using column aliases is not possible because the SQL translator processes another statement before the alias is known. The example below shows an attempt to use a column alias in a situation where another clause is being processed before the alias is known:

USE SampleDb; SELECT MONTH(EnterDate) as enter_month FROM Works_on GROUP BY enter_month;

Trying to run this query will produce the following error message:

Msg 207, Level 16, State 1, Line 5 Invalid column name "enter_month". (Message 207: Level 16, State 1, Line 5 Invalid column name enter_month)

The reason for the error is that the GROUP BY clause is processed before the corresponding list of the SELECT statement is processed, and the alias for the enter_month column is unknown when the group is processed.

This problem can be solved by using a derived table that contains the preceding query (without the GROUP BY clause) because the FROM clause is executed before the GROUP BY clause:

USE SampleDb; SELECT enter_month FROM (SELECT MONTH(EnterDate) as enter_month FROM Works_on) AS m GROUP BY enter_month;

The result of executing this request will be like this:

Typically, a table expression can be placed anywhere in a SELECT statement where a table name might appear. (The result of a table expression is always a table or, in special cases, an expression.) The example below shows the use of a table expression in the select list of a SELECT statement:

The result of this query:

Generic table expressions

Common Table Expression (OTB) is a named table expression supported by the Transact-SQL language. Common table expressions are used in the following two types of queries:

non-recursive;

recursive.

These two types of requests are discussed in the following sections.

OTB and non-recursive queries

The non-recursive form of OTB can be used as an alternative to derived tables and views. Typically OTB is determined by WITH clauses and an additional query that references the name used in the WITH clause. In Transact-SQL, the meaning of the WITH keyword is ambiguous. To avoid ambiguity, the statement preceding the WITH statement should be terminated with a semicolon.

USE AdventureWorks2012; SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") AND Freight > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005 ")/2.5;

The query in this example selects orders whose total taxes (TotalDue) are greater than the average of all taxes and whose freight charges (Freight) are greater than 40% of the average taxes. The main property of this query is its length, since the subquery needs to be written twice. One possible way to reduce the size of the query construct is to create a view that contains a subquery. But this solution is a bit complicated because it requires creating a view and then deleting it after the query has finished executing. A better approach would be to create an OTB. The example below shows the use of non-recursive OTB, which shortens the query definition above:

USE AdventureWorks2012; WITH price_calc(year_2005) AS (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT year_2005 FROM price_calc) AND Freight > (SELECT year_2005 FROM price_ calc) /2.5;

The syntax of the WITH clause in non-recursive queries is as follows:

The cte_name parameter represents the OTB name that defines the resulting table, and the column_list parameter represents the list of columns of the table expression. (In the example above, the OTB is called price_calc and has one column, year_2005.) The inner_query parameter represents a SELECT statement that specifies the result set of the corresponding table expression. The defined table expression can then be used in the outer_query. (The outer query in the example above uses OTB price_calc and its year_2005 column to simplify the doubly nested query.)

OTB and recursive queries

This section presents material of increased complexity. Therefore, when reading it for the first time, it is recommended to skip it and return to it later. OTBs can be used to implement recursions because OTBs can contain references to themselves. The basic OTB syntax for a recursive query looks like this:

The cte_name and column_list parameters have the same meaning as in OTB for non-recursive queries. The body of a WITH clause consists of two queries joined by the operator UNION ALL. The first query is called only once, and it begins to accumulate the result of the recursion. The first operand of the UNION ALL operator does not reference OTB. This query is called a reference query or source.

The second query contains a reference to the OTB and represents its recursive part. Because of this, it is called a recursive member. In the first call to the recursive part, the OTB reference represents the result of the reference query. The recursive member uses the result of the first query call. After this, the system calls the recursive part again. A call to a recursive member stops when a previous call to it returns an empty result set.

The UNION ALL operator joins the currently accumulated rows, as well as additional rows added by the current call to the recursive member. (The presence of the UNION ALL operator means that duplicate rows will not be removed from the result.)

Finally, the outer_query parameter specifies the outer query that OTB uses to retrieve all calls to the join of both members.

To demonstrate the recursive form of OTB, we use the Airplane table defined and populated with the code shown in the example below:

USE SampleDb; CREATE TABLE Airplane(ContainingAssembly VARCHAR(10), ContainedAssembly VARCHAR(10), QuantityContained INT, UnitCost DECIMAL(6,2)); INSERT INTO Airplane VALUES ("Airplane", "Fuselage", 1, 10); INSERT INTO Airplane VALUES ("Airplane", "Wings", 1, 11); INSERT INTO Airplane VALUES ("Airplane", "Tail", 1, 12); INSERT INTO Airplane VALUES ("Fuselage", "Salon", 1, 13); INSERT INTO Airplane VALUES ("Fuselage", "Cockpit", 1, 14); INSERT INTO Airplane VALUES ("Fuselage", "Nose",1, 15); INSERT INTO Airplane VALUES ("Cabin", NULL, 1,13); INSERT INTO Airplane VALUES ("Cockpit", NULL, 1, 14); INSERT INTO Airplane VALUES ("Nose", NULL, 1, 15); INSERT INTO Airplane VALUES ("Wings", NULL,2, 11); INSERT INTO Airplane VALUES ("Tail", NULL, 1, 12);

The Airplane table has four columns. The ContainingAssembly column identifies the assembly, and the ContainedAssembly column identifies the parts (one by one) that make up the corresponding assembly. The figure below shows a graphic illustration of a possible type of aircraft and its component parts:

The Airplane table consists of the following 11 rows:

The following example uses the WITH clause to define a query that calculates the total cost of each build:

USE SampleDb; WITH list_of_parts(assembly1, quantity, cost) AS (SELECT ContainingAssembly, QuantityContained, UnitCost FROM Airplane WHERE ContainedAssembly IS NULL UNION ALL SELECT a.ContainingAssembly, a.QuantityContained, CAST(l.quantity * l.cost AS DECIMAL(6,2) ) FROM list_of_parts l, Airplane a WHERE l.assembly1 = a.ContainedAssembly) SELECT assembly1 "Part", quantity "Quantity", cost "Price" FROM list_of_parts;

The WITH clause defines an OTB list named list_of_parts, consisting of three columns: assembly1, quantity, and cost. The first SELECT statement in the example is called only once to store the results of the first step of the recursion process. The SELECT statement on the last line of the example displays the following result.

Each of us regularly encounters and uses various databases. When we select an email address, we are working with a database. Databases are used by search services, banks to store customer data, etc.

But despite the constant use of databases, even for many software system developers there are still many blind spots due to different interpretations of the same terms. We'll give a brief definition of basic database terms before covering the SQL language. So.

Database - a file or collection of files for storing ordered data structures and their relationships. Very often a management system is called a database - it is only a repository of information in a specific format and can work with various DBMSs.

Table - Let's imagine a folder in which documents are stored, grouped according to a certain characteristic, for example, a list of orders for the last month. This is a table in a computer. A separate table has its own unique name.

Data type - the type of information allowed to be stored in a separate column or row. These can be numbers or text of a certain format.

Column and Row- We have all worked with spreadsheets, which also have rows and columns. Any relational database works with tables in a similar way. Rows are sometimes called records.

Primary key- Each row of a table can have one or more columns to uniquely identify it. Without a primary key, it is very difficult to update, change, and delete the desired rows.

What is SQL?

SQL(English - structured query language) was developed only for working with databases and is currently the standard for all popular DBMSs. The language syntax consists of a small number of operators and is easy to learn. But, despite its apparent simplicity, it allows the creation of sql queries for complex operations with a database of any size.

Since 1992, there has been a generally accepted standard called ANSI SQL. It defines the basic syntax and functions of operators and is supported by all DBMS market leaders, such as ORACLE. It is impossible to consider all the capabilities of the language in one short article, so we will briefly consider only basic SQL queries. Examples clearly show the simplicity and capabilities of the language:

• creating databases and tables;
• data sampling;
• adding records;
• modification and deletion of information.

SQL Data Types

All columns in a database table store the same data type. Data types in SQL are the same as in other programming languages.

We create tables and databases

There are two ways to create new databases, tables and other queries in SQL:

• via the DBMS console
• Using the interactive administration tools included with the database server.

A new database is created by the operator CREATE DATABASE<наименование базы данных>; . As you can see, the syntax is simple and concise.

We create tables inside the database using the CREATE TABLE statement with the following parameters:

• table name
• column names and data types

As an example, let's create a Commodity table with the following columns:

Create a table:

CREATE TABLE Commodity

(commodity_id CHAR(15) NOT NULL,

vendor_id CHAR(15) NOT NULL,

commodity_name CHAR(254) NULL,

commodity_price DECIMAL(8,2) NULL,

commodity_desc VARCHAR(1000) NULL);

The table consists of five columns. After the name comes the data type, the columns are separated by commas. The column value can accept empty values ​​(NULL) or must be filled (NOT NULL), and this is determined when the table is created.

Retrieving data from a table

The data fetch operator is the most commonly used SQL query. To obtain information, we must indicate what we want to select from such a table. First a simple example:

SELECT commodity_name FROM Commodity

After the SELECT statement we specify the name of the column to obtain information, and FROM defines the table.

The result of the query will be all rows of the table with Commodity_name values ​​in the order in which they were entered into the database, i.e. without any sorting. An additional ORDER BY clause is used to order the result.

To query for several fields, list them separated by commas, as in the following example:

SELECT commodity_id, commodity_name, commodity_price FROM Commodity

It is possible to get the value of all columns of a row as a query result. To do this, use the “*” sign:

SELECT * FROM Commodity

• Additionally SELECT supports:
• Sorting data (ORDER BY operator)
• Selection according to conditions (WHERE)
• Grouping term (GROUP BY)

Add a line

To add a row to a table, SQL queries with the INSERT operator are used. Addition can be done in three ways:

• add a new whole line;
• part of a string;
• query results.

To add a full row, you must specify the table name and the values ​​of the columns (fields) of the new row. Here's an example:

INSERT INTO Commodity VALUES("106 ", "50", "Coca-Cola", "1.68", "No Alcogol ,)

The example adds a new product to the table. Values ​​are listed after VALUES for each column. If there is no corresponding value for the column, then NULL must be specified. The columns are populated with values ​​in the order specified when the table was created.

If you add only part of a row, you must explicitly specify the names of the columns, as in the example:

INSERT INTO Commodity (commodity_id, vendor_id, commodity_name)

VALUES("106 ", ‘50", "Coca-Cola",)

We entered only the identifiers of the product, supplier and its name, and left the remaining fields blank.

Adding Query Results

INSERT is primarily used to append rows, but can also be used to append the results of a SELECT statement.

Changing data

To change information in the fields of a database table, you must use the UPDATE statement. The operator can be used in two ways:

• All rows in the table are updated.
• Only for a specific line.

UPDATE consists of three main elements:

• table in which changes need to be made;
• field names and their new values;
• conditions for selecting rows to change.

Let's look at an example. Let's say the price of a product with ID=106 has changed, so this line needs to be updated. We write the following operator:

UPDATE Commodity SET commodity_price = "3.2" WHERE commodity_id = "106"

We specified the name of the table, in our case Commodity, where the update will be performed, then after SET - the new value of the column and found the desired record by specifying the required ID value in WHERE.

To change multiple columns, the SET statement is followed by multiple column-value pairs separated by commas. Let's look at an example in which the name and price of a product are updated:

UPDATE Commodity SET commodity_name=’Fanta’, commodity_price = "3.2" WHERE commodity_id = "106"

To remove information in a column, you can assign it the value NULL if the table structure allows it. It must be remembered that NULL is precisely “no” value, and not zero in the form of text or number. Let's remove the product description:

UPDATE Commodity SET commodity_desc = NULL WHERE commodity_id = "106"

Removing rows

SQL queries to delete rows in a table are executed using the DELETE statement. There are two use cases:

• Certain rows in the table are deleted;
• All rows in the table are deleted.

An example of deleting one row from a table:

DELETE FROM Commodity WHERE commodity_id = "106"

After DELETE FROM we indicate the name of the table in which the rows will be deleted. The WHERE clause contains the condition by which rows will be selected for deletion. In the example, we delete the product line with ID=106. Specifying WHERE is very important because omitting this statement will delete all rows in the table. This also applies to changing the value of fields.

The DELETE statement does not specify column names or metacharacters. It deletes rows completely, but it cannot delete a single column.

Using SQL in Microsoft Access

Typically used interactively to create tables, databases, to manage, modify, analyze data in a database and to implement SQL Access queries through a convenient interactive query designer (Query Designer), using which you can build and immediately execute SQL statements of any complexity .

The server access mode is also supported, in which the Access DBMS can be used as a generator of SQL queries to any ODBC data source. This feature allows Access applications to interact with any format.

SQL extensions

Since SQL queries do not have all the capabilities of procedural programming languages, such as loops, branching, etc., DBMS manufacturers are developing their own version of SQL with advanced capabilities. First of all, this is support for stored procedures and standard operators of procedural languages.

The most common dialects of the language:

• Oracle Database - PL/SQL
• Interbase, Firebird - PSQL
• Microsoft SQL Server - Transact-SQL
• PostgreSQL - PL/pgSQL.

SQL on the Internet

The MySQL DBMS is distributed under the free GNU General Public License. There is a commercial license with the ability to develop custom modules. As a component, it is included in the most popular assemblies of Internet servers, such as XAMPP, WAMP and LAMP, and is the most popular DBMS for developing applications on the Internet.

It was developed by Sun Microsystems and is currently supported by Oracle Corporation. Databases up to 64 terabytes in size, SQL:2003 syntax standard, replication of databases and cloud services are supported.

SQL - Structured Query Language.
In this review we will look at the most common types of SQL queries.
The SQL standard is defined ANSI(American National Standards Institute).
SQL is a language aimed specifically at relational databases.

SQL partitioning:

DDL(Data Definition Language) - the so-called Schema Description Language in ANSI, consists of commands that create objects (tables, indexes, views, and so on) in the database.
DML(Data Manipulation Language) is a set of commands that determine what values ​​are represented in tables at any given time.
DCD(Data Management Language) consists of facilities that determine whether to allow a user to perform certain actions or not. They are part of ANSI DDL. Don't forget these names. These are not different languages, but sections of SQL commands grouped by their functions.

Data types:

SQL Server - Data Types	Description
bigint (int 8)	bigint (int 8)
binary(n)	binary(n) or image
character (synonym char)	national character or ntext
character varying(synonym char varying varchar)	national character varying or ntext
Datetime	datetime
decimal	aka numeric
double precision	double precision
integer (int 4) (synonym: int)	integer (int 4)
national character(synonym: national character, nchar)	national character
Numeric(synonyms: decimal, dec)
national character varying(synonyms: national char varying, nvarchar)	National character varying
Smalldatetime	datetime
smallint (int 2)	smallint (int 2)
Smallmoney
sql_variant	No longer supported
	Ntext Starting with SQL Server 2005, it is not recommended for use.
Timestamp	Not supported
tinyint (int 1)	tinyint (int 1)
Uniqueidentifier	uniqueidentifier
varbinary(n)	varbinary(n) or image
smalldatetime	datetime
smallint (int 2)	smallint (int 2)
smallmoney
sql_variant	Not supported
timestamp	Not supported
tinyint (int 1)	tinyint (int 1)
uniqueidentifier	uniqueidentifier
varbinary(n)	varbinary(n) or image

Data Type Table in SQL Server 2000

WHAT IS A REQUEST?

Request is a command you give to your database program. Queries are part of the DML language. All SQL queries consist of a single command. The structure of this command is deceptively simple because you must extend it to perform highly complex evaluations and data manipulations.

SELECT command:

SELECT“Select” is the most frequently used command; it is used to select data from the table.
Type of query using SELECT:

SELECT id, user_name, city, birth_day FROM users_base;

Such a query will display from the users_base table all the values ​​of the columns specified separated by commas after the SELECT command. Also, you can display all columns with one character, * i.e. SELECT * FROM users_base ; - such a query will display all data from the table.

SELECT command structure:

SELECT (Column names separated by commas that need to be displayed in the query) FROM (table name in the database)
- This is the simplest type of request. There are additional commands for convenient data retrieval (see below “Functions”)

DML commands:

Values ​​can be placed and removed from fields using three DML (Data Manipulation Language) commands:
INSERT(Insert)
UPDATE(Update, modification),
DELETE(Delete)

INSERT command:

INSERT INTO users_base (user_name, city, birth_day) VALUES ('Alexander', 'Rostov', '06/20/1991');

The INSERT command comes with the prefix INTO (in to), then in brackets are the names of the columns into which we must insert data, then comes the VALUES command (values) and in brackets the values ​​come in turn (it is necessary to observe the order of the values ​​with the columns , the values ​​must be in the same order as the columns you specified).

UPDATE command:

UPDATE users_base SET user_name = 'Alexey';

The UPDATE command updates values ​​in a table. First comes the UPDATE command itself, then the table name, after the SET command (set), then the column name and its value in quotes (quotes are placed if the value is in string format, if it is a numeric value and the column is not tied to the data type vchar or any other string types, the quotes have no meaning.)

DELETE command:

DELETE FROM users_base WHERE user_name = 'Vasily';

The DELETE command deletes the entire row and identifies the row using the WHERE criterion. In this case, this query would delete all rows in which the user_name column value was Vasily. We'll talk about the WHERE criterion and others a little later.

Criteria, functions, conditions, etc. what helps us in SQL:

WHERE clause is a clause of the SELECT command and other DML commands that allows you to set predicates whose condition can be either true or false for any row in the table. The command retrieves only those rows from the table for which this statement is true.
Example:
SELECT id, city, birth_day FROM users_base WHERE user_name = ‘Alexey’;- such a query will display only those rows that match the WHERE condition, namely all rows in which the user_name column has the value Alexey.

ORDER BY - condition for sorting the selected rows. Has 2 criteria ASC and DESC. ASC (sorting from A to Z or from 0 to 9)

DESC (opposite of ASC).
Example:
SELECT id, city, birth_day FROM users_base ORDER BY user_name ASC; - such a query will display values ​​sorted by the user_name column from A to Z (A-Z; 0-9)

This condition can also be used in conjunction with the WHERE clause.
Example:
SELECT id, city, birth_day FROM users_base WHERE user_name = ‘Alexey’ ORDER BY id ASC;

DISTINCT is an argument that provides you with a way to eliminate double values ​​from your SELECT clause. Those. if you have duplicate values ​​in a column, let’s say user_name, then DISTINCT will show you only one value, for example, you have 2 people named Alexey in your database, then a query using the DISTINCT function will show you only 1 value that is encountered first...
Example:
SELECT DISTINCT user_name FROM users_base;- such a query will show us the values ​​of all records in the user_name column, but they will not be repeated, i.e. if you had an infinite number of repeating values, then they will not be shown...

AND - takes two Boolean (in the form A AND B) as arguments and evaluates them against the truth whether they are both true.
Example:
SELECT * FROM users_base WHERE city = 'Rostov' AND user_name = 'Alexander';- will display all the values ​​from the table where the name of the city appears in one line (in this case, Rostov and the user name Alexander.

OR - takes two Boolean (in the form A OR B) as arguments and evaluates whether one of them is correct.

SELECT * FROM users_base WHERE city = 'Rostov' OR user_name = 'Alexander';- will display all values ​​from the table where the name of the city of Rostov or the username Alexander appears in the line.

NOT - takes a single Boolean (in the form NOT A) as arguments and changes its value from false to true or true to false.
SELECT * FROM users_base WHERE city = 'Rostov' OR NOT user_name = 'Alexander';- will display all values ​​from the table where the name of the city Rostov appears in one line or the user name is not exactly Alexander.

IN - defines a set of values ​​in which a given value may or may not be included.
SELECT * FROM users_base WHERE city IN ('Vladivostok', 'Rostov');- such a query will display all values ​​from the table that contain the names of the specified cities in the city column

Between is similar to the IN operator. Unlike defining by numbers from a set, as IN does, BETWEEN defines a range whose values ​​must decrease to make the predicate true.
SELECT * FROM users_base WHERE id BETWEEN 1 AND 10;- displays all values ​​from the table that will be in the range from 1 to 10 in the id column

COUNT - Produces the row numbers or non-NULL values ​​of the fields that the query selected.
SELECT COUNT (*) FROM users_base ;- will display the number of rows in this table.
SELECT COUNT (DISTINCT user_name) FROM users_base ;- will display the number of lines with user names (not repeated)

SUM - produces the arithmetic sum of all selected values ​​for a given field.
SELECT SUM (id) FROM users_base ;- will display the sum of the values ​​of all rows of the id column.

AVG - averages all selected values ​​of this field.
SELECT AVG (id) FROM users_base ;- will display the average of all selected values ​​in the id column

MAX - produces the largest of all selected values ​​for this field.

MIN - Produces the smallest of all selected values ​​for this field.

Creating tables:

CREATE TABLE users_base (id integer, user_name text, city text, birth_day datetime);- executing such a command will lead to the creation of the table for which I gave examples... Everything is simple here, we write the CREATE TABLE command, followed by the name of the table that we want to create, then in parentheses, separated by commas, the names of the columns and their data type. This is the standard way to create a table in SQL. Now I will give an example of creating tables in SQL Server 2005:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N."") AND type in (N"U"))
BEGIN
CREATE TABLE .(

NOT NULL,
NOT NULL,
NOT NULL,
PRIMARY KEY CLUSTERED
A.S.C.


END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N."") AND type in (N"U"))
BEGIN
CREATE TABLE .(
IDENTITY(1,1) NOT NULL,
NULL,
NULL,
PRIMARY KEY CLUSTERED
A.S.C.
)WITH (IGNORE_DUP_KEY = OFF) ON
) ON TEXTIMAGE_ON
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N."") AND type in (N"U"))
BEGIN
CREATE TABLE .(
IDENTITY(1,1) NOT NULL,
NULL,
NULL,
PRIMARY KEY CLUSTERED
A.S.C.
)WITH (IGNORE_DUP_KEY = OFF) ON
) ON
END

Syntax in SQL Server 2005 is another topic, I just wanted to show that I described the basics of SQL programming, you can reach the top by yourself knowing the basics.

If you have any questions on this topic, please write to me