Geekly Articles each Day
📜 ⬆️ ⬇️

SQL Language Tutorial (DDL, DML) on the example of MS SQL Server dialect. Part five

Previous parts




In this part we will look at


Here we will look at the general operation of data modification operators:

At the very end, you are waiting for “Appendix 1 - a bonus on the SELECT operator” and “Appendix 2 - OVER and analytical functions”, in which some advanced constructions will be shown:


Data modification operations are very strongly associated with the constructions of the SELECT statement, since in essence, a sample of the data being modified goes with it. Therefore, to understand this material, an important place has a confident knowledge of the constructions of the SELECT statement.

This part, as I said, will be more overview. Here I will describe only those basic forms of data modification operators, which I myself regularly use. Therefore, one should not count on the completeness of the presentation, here only the necessary minimum will be shown, which beginners can use as a direction for deeper study. For more information on each operator, contact MSDN. Although it is possible to someone and in such a volume of information will be quite enough.
')
Since direct modification of information in the DDB requires a great responsibility from the person, and also because users usually modify the database information through different workstations, and do not have full access to the database, this part is more devoted to beginning IT specialists, and I will be very brief here. But of course, if you could master the SELECT statement, then I think, and the modification operators will be in your power, since after the SELECT statement there is nothing too complicated here, and for the most part it should be perceived on an intuitive level. But sometimes the complexity is not the modification operators themselves, but the fact that they must be performed in groups, within the framework of a single transaction, i.e. when you additionally need to consider data integrity. In any case, you can read and try to do the examples for informational purposes, besides, in the end you will be able to get a more detailed base on which you can work out certain constructions of the SELECT statement.

We will carry out changes in the structure of our database.


Let's do a small update on the structure and data of the Employees table:

--       2-    ALTER TABLE Employees ALTER COLUMN Salary numeric(20,2) --          ALTER TABLE Employees ALTER COLUMN BonusPercent tinyint


And also for demonstration purposes, we will expand the schema of our database, and for one we will repeat the DDL. The assignments of tables and fields are indicated in the comments:

 --      CREATE TABLE EmployeesSalaryHistory( EmployeeID int NOT NULL, --   ID  DateFrom date NOT NULL, --    DateTo date, --   .  NULL     . Salary numeric(20,2) NOT NULL, --      CONSTRAINT PK_EmployeesSalaryHistory PRIMARY KEY(EmployeeID,DateFrom), CONSTRAINT FK_EmployeesSalaryHistory_EmployeeID FOREIGN KEY(EmployeeID) REFERENCES Employees(ID) ) GO --        CREATE TABLE EmployeesSalary( EmployeeID int NOT NULL, SalaryDate date NOT NULL, --   SalaryAmount numeric(20,2) NOT NULL, --   Note nvarchar(50), --  --        1    CONSTRAINT PK_EmployeesSalary PRIMARY KEY(EmployeeID,SalaryDate), --    Employees CONSTRAINT FK_EmployeesSalary_EmployeeID FOREIGN KEY(EmployeeID) REFERENCES Employees(ID) ) GO --     CREATE TABLE BonusTypes( ID int IDENTITY(1,1) NOT NULL, Name nvarchar(30) NOT NULL, CONSTRAINT PK_BonusTypes PRIMARY KEY(ID) ) GO --       CREATE TABLE EmployeesBonus( EmployeeID int NOT NULL, BonusDate date NOT NULL, --   BonusAmount numeric(20,2) NOT NULL, --   BonusTypeID int NOT NULL, BonusPercent tinyint, Note nvarchar(50), --  --        1    CONSTRAINT PK_EmployeesBonus PRIMARY KEY(EmployeeID,BonusDate,BonusTypeID), --    Employees  BonusTypes CONSTRAINT FK_EmployeesBonus_EmployeeID FOREIGN KEY(EmployeeID) REFERENCES Employees(ID), CONSTRAINT FK_EmployeesBonus_BonusTypeID FOREIGN KEY(BonusTypeID) REFERENCES BonusTypes(ID) ) GO


Here we had to get such a range as a result:



By the way, then this polygon (when it will be filled with data) you can use in order to test various requests on it - here you can try various JOIN connections, UNION unions, and groupings with data aggregation.

INSERT - insert new data


This operator has 2 main forms:
  1. INSERT INTO table (list_fields) VALUES (list of values ) - inserts in the new row table the values ​​of which fields are formed from the listed values
  2. INSERT INTO table (list_field) SELECT list_ of values FROM ... - inserts into the table new rows, the values ​​of which are formed from the values ​​of the rows returned by the query.


In the MS SQL dialect, the word INTO can be released, which I really like and I always use it.

Besides, it is worth noting that the first form in the MS SQL dialect from version 2008 allows you to insert several rows into the table at once:

 INSERT (_) VALUES (_1), (_2), … (_N)


INSERT - Form 1. Go directly to the practice.


Fill the EmployeesSalaryHistory table with the data provided to us:

 INSERT EmployeesSalaryHistory(EmployeeID,DateFrom,DateTo,Salary) VALUES --  .. (1000,'20131101','20140531',4000), (1000,'20140601','20141230',4500), (1000,'20150101',NULL,5000), --  .. (1001,'20131101','20140630',1300), (1001,'20140701','20140930',1400), (1001,'20141001',NULL,1500), --  .. (1002,'20140101',NULL,2500), --  .. (1003,'20140601',NULL,2000), --  .. (1004,'20140701','20150131',1400), (1004,'20150201','20150131',1500), --  .. (1005,'20150101',NULL,2000)


So we inserted 11 new entries into the EmployeesSalaryHistory table.

 SELECT * FROM EmployeesSalaryHistory

EmployeeIDDateFromDatetoSalary
10002013-11-012014-05-314000.00
10002014-06-012014-12-304500.00
10002015-01-01Null5000.00
10012013-11-012014-06-301300.00
10012014-07-012014-09-301400.00
10012014-10-01Null1500.00
10022014-01-01Null2500.00
10032014-06-01Null2000.00
10042014-07-012015-01-311400.00
10042015-02-012015-01-311500.00
10052015-01-01Null2000.00

Although we in this case could not specify a list of fields, because we insert the data of all fields and in the same form as they are listed in the table, i.e. we could write:

 INSERT EmployeesSalaryHistory VALUES --  .. (1000,'20131101','20140531',4000), (1000,'20140601','20141230',4500), (1000,'20150101',NULL,5000), …


But I would not recommend using this approach, especially if this request will be used regularly, for example, when calling from some AWP. Again, this is fraught with the fact that the table structure can change, new fields can be added to it, or the sequence of fields can be changed, which is even more dangerous, because this can lead to logical errors in the inserted data. Therefore, it is better not to be lazy once again and explicitly list all the fields into which you want to insert a value.

A few notes about INSERT:


In the previous installments, we periodically used the IDENTITY_INSERT option. Let's use this option here to create rows in the BonusTypes table, whose ID field is specified with the IDENTITY option:

 --    / IDENTITY  SET IDENTITY_INSERT BonusTypes ON INSERT BonusTypes(ID,Name)VALUES (1,N''), (2,N''), (3,N'') --  / IDENTITY  SET IDENTITY_INSERT BonusTypes OFF


Let's insert information on the accrual of employees of the RFP, kindly provided to us by an accountant:

 --  .. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1000,'20131130',4000,NULL), (1000,'20131231',4000,NULL), (1000,'20140115',2000,N''), (1000,'20140131',2000,NULL), (1000,'20140228',4000,NULL), (1000,'20140331',4000,NULL), (1000,'20140430',4000,NULL), (1000,'20140531',4000,NULL), (1000,'20140630',6500,N' +  2500  2014.07'), (1000,'20140731',2000,NULL), (1000,'20140831',4500,NULL), (1000,'20140930',4500,NULL), (1000,'20141031',4500,NULL), (1000,'20141130',4500,NULL), (1000,'20141230',4500,NULL), (1000,'20150131',5000,NULL), (1000,'20150228',5000,NULL), (1000,'20150331',5000,NULL) 

 --  .. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1001,'20131130',2600,N' +   2013.12'), (1001,'20140228',2600,N' 2  2014.01, 2014.02'), (1001,'20140331',1300,NULL), (1001,'20140430',1300,NULL), (1001,'20140510',300,N''), (1001,'20140520',500,N''), (1001,'20140531',500,NULL), (1001,'20140630',1300,NULL), (1001,'20140731',1400,NULL), (1001,'20140831',1400,NULL), (1001,'20140930',1400,NULL), (1001,'20141031',1500,NULL), (1001,'20141130',1500,NULL), (1001,'20141230',3000,N' +   2015.01'), (1001,'20150228',1500,NULL), (1001,'20150331',1500,NULL) 

 --  .. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1002,'20140131',2500,NULL), (1002,'20140228',2500,NULL), (1002,'20140331',2500,NULL), (1002,'20140430',2500,NULL), (1002,'20140531',2500,NULL), (1002,'20140630',2500,NULL), (1002,'20140731',2500,NULL), (1002,'20140831',2500,NULL), (1002,'20140930',2500,NULL), (1002,'20141031',2500,NULL), (1002,'20141130',2500,NULL), (1002,'20141230',2500,NULL), (1002,'20150131',2500,NULL), (1002,'20150228',2500,NULL), (1002,'20150331',2500,NULL) 

 --  .. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1003,'20140630',2000,NULL), (1003,'20140731',2000,NULL), (1003,'20140831',2000,NULL), (1003,'20140930',2000,NULL), (1003,'20141031',2000,NULL), (1003,'20141130',2000,NULL), (1003,'20141230',2000,NULL), (1003,'20150131',2000,NULL), (1003,'20150228',2000,NULL), (1003,'20150331',2000,NULL) 

 --  .. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1004,'20140731',1400,NULL), (1004,'20140831',1400,NULL), (1004,'20140930',1400,NULL), (1004,'20141031',1400,NULL), (1004,'20141130',1400,NULL), (1004,'20141212',400,N''), (1004,'20141230',1400,NULL), (1004,'20150131',1400,NULL), (1004,'20150228',1500,NULL), (1004,'20150331',1500,NULL) 

 --  .. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1005,'20150131',2000,NULL), (1005,'20150228',2000,NULL), (1005,'20150331',2000,NULL)


I think it makes no sense to bring the contents of the table.

INSERT - Form 2


This form allows you to insert into the table the data obtained by the request.

For the demonstration, we will fill the table with bonus accruals in one large query:

 INSERT EmployeesBonus(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent) --    SELECT hist.EmployeeID,bdate.BonusDate,hist.Salary/100*emp.BonusPercent,1 BonusTypeID,emp.BonusPercent FROM EmployeesSalaryHistory hist JOIN ( VALUES --     -    ('20131130'), ('20131231'), ('20140131'), ('20140228'), ('20140331'), ('20140430'), ('20140531'), ('20140630'), ('20140731'), ('20140831'), ('20140930'), ('20141031'), ('20141130'), ('20141230'), ('20150131'), ('20150228'), ('20150331') ) bdate(BonusDate) ON bdate.BonusDate BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231') JOIN Employees emp ON hist.EmployeeID=emp.ID WHERE emp.BonusPercent IS NOT NULL AND emp.BonusPercent>0 AND NOT EXISTS( --  ,   -        SELECT * FROM ( VALUES (1001,'20140115'), (1001,'20140430'), (1001,'20141031'), (1001,'20141130'), (1001,'20150228') ) exclude(EmployeeID,BonusDate) WHERE exclude.EmployeeID=emp.ID AND exclude.BonusDate=bdate.BonusDate ) UNION ALL --    2014  -      SELECT hist.EmployeeID, '20141231' BonusDate, hist.Salary/100* CASE DepartmentID WHEN 2 THEN 10 -- 10%     WHEN 3 THEN 15 -- 15%    - ELSE 5 --    5% END BonusAmount, 2 BonusTypeID, CASE DepartmentID WHEN 2 THEN 10 -- 10%     WHEN 3 THEN 15 -- 15%    - ELSE 5 --    5% END BonusPercent FROM EmployeesSalaryHistory hist JOIN Employees emp ON hist.EmployeeID=emp.ID WHERE CAST('20141231' AS date) BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231') AND emp.HireDate<='20140601' UNION ALL --   SELECT EmployeeID,BonusDate,BonusAmount,3 BonusTypeID,NULL BonusPercent FROM ( VALUES (1001,'20140930',300), (1002,'20140331',500), (1002,'20140630',500), (1002,'20140930',500), (1002,'20141230',500), (1002,'20150331',500), (1004,'20140831',200) ) indiv(EmployeeID,BonusDate,BonusAmount)


50 entries were to be inserted into the EmployeesBonus table.

The result of each query combined with UNION ALL constructs you can analyze yourself. If you have studied the basic constructs well, then you should be able to understand everything, except for possibly constructions with VALUES (tabular value designer), which appeared with MS SQL 2008.

A couple of words about the construction of VALUES


 SELECT EmployeeID,BonusDate,BonusAmount,3 BonusTypeID,NULL BonusPercent FROM ( VALUES (1001,'20140930',300), (1002,'20140331',500), (1002,'20140630',500), (1002,'20140930',500), (1002,'20141230',500), (1002,'20150331',500), (1004,'20140831',200) ) indiv(EmployeeID,BonusDate,BonusAmount)


If necessary, this construction can be replaced by a similar query written through UNION ALL:

 SELECT 1001 EmployeeID,'20140930' BonusDate,300 BonusAmount,3 BonusTypeID,NULL BonusPercent UNION ALL SELECT 1002,'20140331',500,3,NULL UNION ALL SELECT 1002,'20140630',500,3,NULL UNION ALL SELECT 1002,'20140930',500,3,NULL UNION ALL SELECT 1002,'20141230',500,3,NULL UNION ALL SELECT 1002,'20150331',500,3,NULL UNION ALL SELECT 1004,'20140831',200,3,NULL


I think comments are superfluous and it will not be difficult for you to deal with this yourself.

So, go ahead.

INSERT + CTEs


Together with INSERT, you can use CTE expressions. For example, let's rewrite the same query by transferring all subqueries to the WITH block.

To begin, completely clear the EmployeesBonus table using the TRUNCATE TABLE operation:

 TRUNCATE TABLE EmployeesBonus


Now we will rewrite the query, taking the queries to the WITH block:

 WITH cteBonusType1 AS( --    SELECT hist.EmployeeID,bdate.BonusDate,hist.Salary/100*emp.BonusPercent BonusAmount,1 BonusTypeID,emp.BonusPercent FROM EmployeesSalaryHistory hist JOIN ( VALUES --     -    ('20131130'), ('20131231'), ('20140131'), ('20140228'), ('20140331'), ('20140430'), ('20140531'), ('20140630'), ('20140731'), ('20140831'), ('20140930'), ('20141031'), ('20141130'), ('20141230'), ('20150131'), ('20150228'), ('20150331') ) bdate(BonusDate) ON bdate.BonusDate BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231') JOIN Employees emp ON hist.EmployeeID=emp.ID WHERE emp.BonusPercent IS NOT NULL AND emp.BonusPercent>0 AND NOT EXISTS( --  ,   -        SELECT * FROM ( VALUES (1001,'20140115'), (1001,'20140430'), (1001,'20141031'), (1001,'20141130'), (1001,'20150228') ) exclude(EmployeeID,BonusDate) WHERE exclude.EmployeeID=emp.ID AND exclude.BonusDate=bdate.BonusDate ) ), cteBonusType2 AS( --    2014  -      SELECT hist.EmployeeID, '20141231' BonusDate, hist.Salary/100* CASE DepartmentID WHEN 2 THEN 10 -- 10%     WHEN 3 THEN 15 -- 15%    - ELSE 5 --    5% END BonusAmount, 2 BonusTypeID, CASE DepartmentID WHEN 2 THEN 10 -- 10%     WHEN 3 THEN 15 -- 15%    - ELSE 5 --    5% END BonusPercent FROM EmployeesSalaryHistory hist JOIN Employees emp ON hist.EmployeeID=emp.ID WHERE CAST('20141231' AS date) BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231') AND emp.HireDate<='20140601' ), cteBonusType3 AS( --   SELECT EmployeeID,BonusDate,BonusAmount,3 BonusTypeID,NULL BonusPercent FROM ( VALUES (1001,'20140930',300), (1002,'20140331',500), (1002,'20140630',500), (1002,'20140930',500), (1002,'20141230',500), (1002,'20150331',500), (1004,'20140831',200) ) indiv(EmployeeID,BonusDate,BonusAmount) ) INSERT EmployeesBonus(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent) SELECT * FROM cteBonusType1 UNION ALL SELECT * FROM cteBonusType2 UNION ALL SELECT * FROM cteBonusType3


As you can see, the removal of large subqueries into the WITH block has simplified the main query - made it more understandable.

UPDATE - update data


This operator in MS SQL has 2 forms:
  1. UPDATE table SET ... WHERE sampling_condition - updating the rows of the table for which sampling condition is fulfilled. If the WHERE clause is not specified, all rows will be updated. This is the classic form of the UPDATE statement.
  2. UPDATE alias SET ... FROM ... - updates the data of the table participating in the FROM clause, which is specified by the specified alias. Of course, here you can not use pseudonyms, using table names instead, but with a pseudonym in my opinion is more convenient.


Let's use the first form to give the dates of each employee in order. Perform 6 separate UPDATE operations:

 --      UPDATE Employees SET HireDate='20131101' WHERE ID=1000 UPDATE Employees SET HireDate='20131101' WHERE ID=1001 UPDATE Employees SET HireDate='20140101' WHERE ID=1002 UPDATE Employees SET HireDate='20140601' WHERE ID=1003 UPDATE Employees SET HireDate='20140701' WHERE ID=1004 --      FirstName UPDATE Employees SET HireDate='20150101',FirstName=NULL WHERE ID=1005


We already managed to use the second form where the pseudonym was used in the first part, when we updated the PositionID and DepartmentID fields, to the values ​​returned by the subqueries:

 UPDATE e SET PositionID=(SELECT ID FROM Positions WHERE Name=e.Position), DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department) FROM Employees e


Now of course this and the next query will not work, because we have removed the Position and Department fields from the Employees table. This would be the way to submit this request using connection operations:

 UPDATE e SET PositionID=p.ID, DepartmentID=d.ID FROM Employees e LEFT JOIN Positions p ON p.Name=e.Position LEFT JOIN Departments d ON d.Name=e.Department


I hope the essence of the update is clear here, the rows of the Employees table will be updated here.

First, you can make a selection to see which data will be updated and which values:

 SELECT e.ID, e.PositionID,e.DepartmentID, --   e.Position,e.Department, p.ID,d.ID, --   p.Name,d.Name FROM Employees e LEFT JOIN Positions p ON p.Name=e.Position LEFT JOIN Departments d ON d.Name=e.Department


And then rewrite it in UPDATE:

 UPDATE e SET PositionID=p.ID, DepartmentID=d.ID FROM Employees e LEFT JOIN Positions p ON p.Name=e.Position LEFT JOIN Departments d ON d.Name=e.Department


Eh, I can not, so let's see how it works clearly.

To do this, recall the DDL and temporarily create the Position and Department fields in the Employees table:

 ALTER TABLE Employees ADD Position nvarchar(30),Department nvarchar(30)


Fill the data into them, after looking with the help of SELECT, what happens:

 SELECT e.ID, e.Position, p.Name NewPosition, e.Department, d.Name NewDepartment FROM Employees e LEFT JOIN Positions p ON p.ID=e.PositionID LEFT JOIN Departments d ON d.ID=e.DepartmentID


Now rewrite and execute the update:

 UPDATE e SET e.Position=p.Name, e.Department=d.Name FROM Employees e LEFT JOIN Positions p ON p.ID=e.PositionID LEFT JOIN Departments d ON d.ID=e.DepartmentID


See what happened (the values ​​should appear in 2 fields - Position and Department, located at the end of the table):

 SELECT * FROM Employees


Now this query:

 UPDATE e SET PositionID=(SELECT ID FROM Positions WHERE Name=e.Position), DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department) FROM Employees e


And this one:

 UPDATE e SET PositionID=p.ID, DepartmentID=d.ID FROM Employees e LEFT JOIN Positions p ON p.Name=e.Position LEFT JOIN Departments d ON d.Name=e.Department


Work out successfully.

Do not forget to just look at it (this is a very good habit):

 SELECT e.ID, e.PositionID,e.DepartmentID, --   e.Position,e.Department, p.ID,d.ID, --   p.Name,d.Name FROM Employees e LEFT JOIN Positions p ON p.Name=e.Position LEFT JOIN Departments d ON d.Name=e.Department


And of course you can use the WHERE clause here:

 UPDATE e SET PositionID=p.ID, DepartmentID=d.ID FROM Employees e LEFT JOIN Positions p ON p.Name=e.Position LEFT JOIN Departments d ON d.Name=e.Department WHERE d.ID=3 --     -


Everything, were convinced that everything works. If you want, you can again delete the Position and Department fields.

The second form can also be used with a subquery:

 UPDATE e SET HireDate='20131101', MiddleName=N'' FROM (SELECT MiddleName,HireDate FROM Employees WHERE ID=1000) e


In this case, the subquery must explicitly return the rows of the Employees table that will be updated. DISTINCT groupings or clauses cannot be used in a subquery, since in this case, we will not get explicit rows from the Employees table. And accordingly, all the updated fields should be contained in the SELECT clause, unless of course you have specified "SELECT *".

You can also use CTE expressions with UPDATE. For example, let's move our subquery to the WITH block:

 WITH cteEmp AS( SELECT MiddleName,HireDate FROM Employees WHERE ID=1000 ) UPDATE cteEmp SET HireDate='20131101', MiddleName=N''


Go ahead.

DELETE - delete data


The principle of operation of DELETE is similar to the principle of operation of UPDATE, and also in MS SQL you can use 2 forms:
  1. DELETE table WHERE collection_condition - deletes table rows for which selection_ condition is satisfied. If the WHERE clause is not specified, all rows will be deleted. This can be said the classic form of the DELETE statement (only in some DBMS do you need to write the DELETE FROM table WHERE selection condition).
  2. DELETE alias FROM ... - deletes the data of the table participating in the FROM clause, which is specified by the specified alias. Of course, here you can not use pseudonyms, using table names instead, but with a pseudonym in my opinion is more convenient.


For example, using the first option:

 --       DELETE Positions WHERE ID IN(6,7)


With the second option, remove the remaining unused posts. For purposes of demonstration, the request is intentionally overly complex. First, let's see what exactly to retire (always try to do a check, otherwise you can unintentionally delete the excess, or even all the information from the table):

 SELECT pos.* FROM ( SELECT DISTINCT PositionID FROM Employees ) emp RIGHT JOIN Positions pos ON pos.ID=emp.PositionID WHERE emp.PositionID IS NULL --      Employees


Make sure everything is fine. Rewriting the DELETE request:

 DELETE pos --     FROM ( SELECT DISTINCT PositionID FROM Employees ) emp RIGHT JOIN Positions pos ON pos.ID=emp.PositionID WHERE emp.PositionID IS NULL --      Employees


A subquery can also act as a Positions table, as long as it uniquely returns rows that will be deleted. Let's add garbage to the Positions table for demonstration:

 INSERT Positions(Name) VALUES('Test 1'),('Test 2')


Now for the demonstration we use instead of the Positions table, a subquery in which only certain rows from the Positions table are selected:

 DELETE pos --     FROM ( SELECT DISTINCT PositionID FROM Employees ) emp RIGHT JOIN ( SELECT ID FROM Positions WHERE ID>4 --     ) pos ON pos.ID=emp.PositionID WHERE emp.PositionID IS NULL --      Employees


We can also use CTE expressions (subqueries executed in the WITH block). Let's add garbage to the Positions table again:

 INSERT Positions(Name) VALUES('Test 1'),('Test 2')


And look at the same query with a CTE expression:

 WITH ctePositionc AS( SELECT ID FROM Positions WHERE ID>4 --     ) DELETE pos --     FROM ( SELECT DISTINCT PositionID FROM Employees ) emp RIGHT JOIN ctePositionc pos ON pos.ID=emp.PositionID WHERE emp.PositionID IS NULL --      Employees


Conclusion on INSERT, UPDATE and DELETE


That's basically all that I wanted to tell you about the main data modification operators - INSERT, UPDATE and DELETE.

I believe that these operators are very easy to understand intuitively when you are able to use the constructions of the SELECT statement. Therefore, the story about the SELECT statement was stretched into 3 parts, and the story about the modification operators was written in such a cursory form.

And as you saw, with the modification operators, the flight of fantasy is also not limited. But still try to write as simply and clearly as possible, be sure to first check which records will be processed with the help of SELECT, since usually data modification is a very big responsibility.

In addition, I will say that in the MS SQL dialect with all modification operations, you can use the TOP clause (INSERT TOP ..., UPDATE TOP ..., DELETE TOP ...), but I have never had to resort to this form, since here it is not clear exactly which TOP records will be processed.

If we really need to process the TOP records, then I probably would rather use the indication of the TOP option in the subquery and use the ORDER BY method in it in order to explicitly know which TOP records will be processed. For example, add garbage again:

 INSERT Positions(Name) VALUES('Test 1'),('Test 2')


And delete the last 2 entries:

 DELETE emp FROM ( SELECT TOP 2 * -- 2.   2   FROM Positions ORDER BY ID DESC -- 1.    ) emp


I give more examples here to demonstrate the capabilities of the SQL language. In real requests, try to express your intentions very precisely, so that the execution of your request does not lead to data corruption. I say once again - be very careful, and do not be lazy to do preliminary checks.

SELECT ... INTO ... - save the result of the query in a new table


This design allows you to save the result of the sample in the new table. It is something intermediate between DDL and DML.

The column types of the created table will be determined based on the column types of the set obtained by the SELECT query. If the results of expressions are present in the sample, then they must be given aliases that will serve as column names.

Let's select the following data and save it in the EmployeesBonusTarget table (before FROM, just write INTO and specify the name of the new table):

 SELECT bonus.EmployeeID, bonus.BonusDate, bonus.BonusAmount-bonus.BonusAmount BonusAmount, --   bonus.BonusTypeID, bonus.BonusPercent, bonus.Note INTO EmployeesBonusTarget --      EmployeesBonusTarget FROM EmployeesBonus bonus JOIN Employees emp ON bonus.EmployeeID=emp.ID WHERE emp.DepartmentID=3


You can update the list of tables in the object inspector and see the new table EmployeesBonusTarget:



In fact, I specifically created the table EmployeesBonusTarget, I will use it to demonstrate the MERGE operator.

A few more words about the SELECT ... INTO ...


This construction is sometimes convenient to use when generating very complex reports that require sampling from a variety of tables. In this case, the data is usually stored in temporary tables (#). Those. preliminarily using queries, we drop the data into temporary tables, and then use these temporary tables in other queries that form the final result:

 SELECT ID, CONCAT(LastName,' ',FirstName,' ',MiddleName) FullName, --   FullName Salary, BonusPercent, Salary/100*ISNULL(BonusPercent,0) Bonus --   Bonus INTO #EmployeesBonus --      FROM Employees 


 SELECT … FROM #EmployeesBonus b JOIN …


Sometimes this construct is convenient to use to make a complete copy of all the data in the current table:

 SELECT * INTO EmployeesBackup FROM Employees


This can be used, for example, as a safety net, before making major changes to the structure of the Employees table. You can save a copy of either all the data in the table, or only those data that the modification will affect. Those.if something goes wrong, you can recover the data from the Employees table from this copy. In such cases, it is certainly good to make a preliminary backup of the database at the current time, but this is not always possible because of the huge volumes, urgency, etc.

In order not to clutter up the main database, you can create a new database and make a copy of the table there:

 CREATE DATABASE TestTemp GO SELECT * INTO TestTemp.dbo.EmployeesBackup --   .. FROM Employees


In order to see the new TestTemp database, respectively, update the list of databases in the object inspector, and you can already find this table in it.

On a note.
In the Oracle database, there is also a construct for storing the result of the query in a new table, it looks like this:
 CREATE TABLE EMPLOYEES_BACK --        EMPLOYEES_BACK AS SELECT * FROM EMPLOYEES



MERGE - data fusion


This operator is well suited for data synchronization of 2 tables. Such a task may be needed when integrating different systems, when data is transferred in portions from one system to another.

In our case, let's say that the task is to synchronize the EmployeesBonusTarget table with the EmployeesBonus table.

Let's add some garbage to the EmployeesBonusTarget table:

 INSERT EmployeesBonusTarget(EmployeeID,BonusDate,BonusAmount,BonusTypeID,Note)VALUES (9999,'20150101',9999.99,0,N' '), (9999,'20150201',9999.99,0,N' '), (9999,'20150301',9999.99,0,N' '), (9999,'20150401',9999.99,0,N' '), (9999,'20150501',9999.99,0,N' '), (9999,'20150601',9999.99,0,N' ')


Now, using the MERGE operator, we will ensure that the data in the EmployeesBonusTarget table become the same as in the EmployeesBonus, i.e. let's make data synchronization.

We will synchronize on the basis of matching the data included in the primary key of the EmployeesBonus table (EmployeeID, BonusDate, BonusTypeID):
  1. If for the row of the EmployeesBonusTarget table there was no match by key, then you need to delete such rows from the EmployeesBonusTarget
  2. If a match is found, then you need to update the rows of EmployeesBonusTarget with the data of the corresponding row from EmployeesBonus
  3. If the string is in the EmployeesBonus, but it is not in the EmployeesBonusTarget, then it must be added to the EmployeesBonusTarget

Let's make the implementation of all this logic using the MERGE instruction:

 MERGE EmployeesBonusTarget trg --   USING EmployeesBonus src --   ON trg.EmployeeID=src.EmployeeID AND trg.BonusDate=src.BonusDate AND trg.BonusTypeID=src.BonusTypeID --   -- 1.    trg       src WHEN NOT MATCHED BY SOURCE THEN DELETE -- 2.    trg     src WHEN MATCHED THEN UPDATE SET trg.BonusAmount=src.BonusAmount, trg.BonusPercent=src.BonusPercent, trg.Note=src.Note -- 3.     trg,    src WHEN NOT MATCHED BY TARGET THEN --  BY TARGET  , .. NOT MATCHED = NOT MATCHED BY TARGET INSERT(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent,Note) VALUES(src.EmployeeID,src.BonusDate,src.BonusAmount,src.BonusTypeID,src.BonusPercent,src.Note);


This construction should end with ";".

After completing the query, compare the 2 tables, their data should be the same.

The MERGE construct is somewhat similar to a conditional CASE statement; it also contains WHEN blocks, when the conditions are met, an action takes place, in this case, deletion (DELETE), update (UPDATE) or append (INSERT). Data modification is performed in the receiver table.

The request may be the source. For example, we synchronize only the data of department 3 and, for example, exclude the block “NOT MATCHED BY SOURCE” so that the data does not succeed if it does not match:

 MERGE EmployeesBonusTarget trg --   USING ( SELECT bonus.* FROM EmployeesBonus bonus JOIN Employees emp ON bonus.EmployeeID=emp.ID WHERE emp.DepartmentID=3 ) src --  ON trg.EmployeeID=src.EmployeeID AND trg.BonusDate=src.BonusDate AND trg.BonusTypeID=src.BonusTypeID --   -- 2.    trg     src WHEN MATCHED THEN UPDATE SET trg.BonusAmount=src.BonusAmount, trg.BonusPercent=src.BonusPercent, trg.Note=src.Note -- 3.     trg,    src WHEN NOT MATCHED BY TARGET THEN --  BY TARGET  , .. NOT MATCHED = NOT MATCHED BY TARGET INSERT(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent,Note) VALUES(src.EmployeeID,src.BonusDate,src.BonusAmount,src.BonusTypeID,src.BonusPercent,src.Note);


I showed the work of the construction of the MERGE in its most general form. With it, you can implement a variety of schemes for merging data, for example, you can include additional conditions in WHEN blocks (WHEN MATCHED AND ... THEN). This is a very powerful design that allows, in appropriate cases, to reduce the amount of code and to combine, within one operator, the functionality of all three operators - INSERT, UPDATE and DELETE.

And naturally, with the MERGE construct, CTE expressions can also be used:

 WITH cteBonus AS( SELECT bonus.* FROM EmployeesBonus bonus JOIN Employees emp ON bonus.EmployeeID=emp.ID WHERE emp.DepartmentID=3 ) MERGE EmployeesBonusTarget trg --   USING cteBonus src --  ON trg.EmployeeID=src.EmployeeID AND trg.BonusDate=src.BonusDate AND trg.BonusTypeID=src.BonusTypeID --   -- 2.    trg     src WHEN MATCHED THEN UPDATE SET trg.BonusAmount=src.BonusAmount, trg.BonusPercent=src.BonusPercent, trg.Note=src.Note -- 3.     trg,    src WHEN NOT MATCHED BY TARGET THEN --  BY TARGET  , .. NOT MATCHED = NOT MATCHED BY TARGET INSERT(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent,Note) VALUES(src.EmployeeID,src.BonusDate,src.BonusAmount,src.BonusTypeID,src.BonusPercent,src.Note);


In general, I tried to set a direction for you, in more detail, if necessary, study it yourself.

Using the OUTPUT construct


The OUTPUT construction makes it possible to obtain information on rows that have been added, deleted or changed as a result of the execution of DML commands INSERT, DELETE, UPDATE and MERGE. This construction represents an extension for data modification operations and in each DBMS can be implemented in its own way, or be absent altogether.

The OUTPUT design has 2 main forms:
  1. OUTPUT expression_list - used to return the result as a set
  2. OUTPUT expression_list INTO host_table (field_list) - used to insert the result into the specified table


Consider the first form.

Add new entries to the Positions table:

 INSERT Positions(Name) OUTPUT inserted.* VALUES (N'Test 1'), (N'Test 2'), (N'Test 3')


, Positions .

«inserted» . «inserted.*» , Positions (ID Name).

OUTPUT «inserted._», :

 INSERT Positions(Name) OUTPUT inserted.ID,inserted.Name,'I' VALUES (N'Test 4'), (N'Test 5'), (N'Test 6')


DML DELETE, «deleted»:

 DELETE Positions OUTPUT deleted.ID,deleted.Name,'D' WHERE Name LIKE N'Test%'


DML UPDATE, :


Let's demonstrate on the Employees table:

 UPDATE Employees SET LastName=N'', FirstName=N'' OUTPUT deleted.ID, deleted.LastName [ ], deleted.FirstName [ ], inserted.ID, inserted.LastName [ ], inserted.FirstName [ ] WHERE ID=1005

IDOld nameOld NameIDNew SurnameNew name
1005NullNull1005AlexandrovAlexander


In the case of MERGE, we can also use “inserted” and “deleted” to access the values ​​of the processed rows.

For example, let's create the PositionsTarget table, which will be followed by an example with MERGE:

 SELECT CAST(ID AS int) ID, --      IDENTITY Name+'-old' Name --   INTO PositionsTarget FROM Positions WHERE ID=2 --


Add garbage to PositionsTarget:

 INSERT PositionsTarget(ID,Name)VALUES (100,N'Qwert'), (101,N'Asdf')


Run the MERGE command with the OUTPUT construct:

 MERGE PositionsTarget trg --   USING Positions src --   ON trg.ID=src.ID --   -- 1.    trg       src WHEN NOT MATCHED BY SOURCE THEN DELETE -- 2.    trg     src WHEN MATCHED THEN UPDATE SET trg.Name=src.Name -- 3.     trg,    src WHEN NOT MATCHED BY TARGET THEN --  BY TARGET  , .. NOT MATCHED = NOT MATCHED BY TARGET INSERT(ID,Name) VALUES(src.ID,src.Name) OUTPUT deleted.ID Old_ID, deleted.Name Old_Name, inserted.ID New_ID, inserted.Name New_Name, CASE WHEN deleted.ID IS NOT NULL AND inserted.ID IS NOT NULL THEN 'U' WHEN deleted.ID IS NOT NULL THEN 'D' WHEN inserted.ID IS NOT NULL THEN 'I' END OperType;

Old_idOld_nameNew_IDNew_nameOpertype
NullNulloneAccountantI
2Old director2DirectorU
NullNull3ProgrammerI
NullNullfourSenior programmerI
100QwertNullNullD
101AsdfNullNullD

I think the purpose of the first form is clear - to make a modification and get the result in the form of a set that can be returned to the user.

Consider the second form.

The OUTPUT construction has a more important purpose - it allows not only to receive, but also to fix (OUTPUT ... INTO ...) information about what has already happened after the fact, that is, after performing the modification operation. It may be useful in the case of logged actions. In some cases, it can be used as a good alternative to triggers (for transparency of actions).

Let's create a demo table to log changes to the Positions table:

 CREATE TABLE PositionsLog( LogID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_PositionsLog PRIMARY KEY, ID int, Old_Name nvarchar(30), New_Name nvarchar(30), LogType char(1) NOT NULL, LogDateTime datetime NOT NULL DEFAULT SYSDATETIME() )


And now with the help of the construction (OUTPUT ... INTO ...) we will make an entry in this table:

 --  INSERT Positions(Name) OUTPUT inserted.ID,inserted.Name,'I' INTO PositionsLog(ID,New_Name,LogType) VALUES (N'Test 1'), (N'Test 2') --  UPDATE Positions SET Name+=' - new' --     "+=",  Name=Name+' - new' OUTPUT deleted.ID, deleted.Name, inserted.Name, 'U' INTO PositionsLog(ID,Old_Name,New_Name,LogType) WHERE Name LIKE N'Test%' --  DELETE Positions OUTPUT deleted.ID,deleted.Name,'D' INTO PositionsLog(ID,Old_Name,LogType) WHERE Name LIKE N'Test%'


See what happened:

 SELECT * FROM PositionsLog


TRUNCATE TABLE – DDL-


DDL- – . MSDN.

MSDN. TRUNCATE TABLE – , . TRUNCATE TABLE DELETE WHERE, TRUNCATE TABLE .

( IDENTITY), , . , , 1. , DELETE.

TRUNCATE TABLE FOREIGN KEY. , , , .


Example:

 TRUNCATE TABLE EmployeesBonusTarget



, .

, .

, , , . SELECT.

, , , SELECT … INTO …

, .

1 – SELECT


, , .

:

, . , , .

GROUP BY+CASE PIVOT

, GROUP BY CASE-. , :

 --      GROUP BY SELECT EmployeeID, SUM(CASE WHEN MONTH(BonusDate)=1 THEN BonusAmount END) BonusAmount1, SUM(CASE WHEN MONTH(BonusDate)=2 THEN BonusAmount END) BonusAmount2, SUM(CASE WHEN MONTH(BonusDate)=3 THEN BonusAmount END) BonusAmount3, SUM(CASE WHEN MONTH(BonusDate)=4 THEN BonusAmount END) BonusAmount4, SUM(CASE WHEN MONTH(BonusDate)=5 THEN BonusAmount END) BonusAmount5, SUM(CASE WHEN MONTH(BonusDate)=6 THEN BonusAmount END) BonusAmount6, SUM(CASE WHEN MONTH(BonusDate)=7 THEN BonusAmount END) BonusAmount7, SUM(CASE WHEN MONTH(BonusDate)=8 THEN BonusAmount END) BonusAmount8, SUM(CASE WHEN MONTH(BonusDate)=9 THEN BonusAmount END) BonusAmount9, SUM(CASE WHEN MONTH(BonusDate)=10 THEN BonusAmount END) BonusAmount10, SUM(CASE WHEN MONTH(BonusDate)=11 THEN BonusAmount END) BonusAmount11, SUM(CASE WHEN MONTH(BonusDate)=12 THEN BonusAmount END) BonusAmount12, SUM(BonusAmount) TotalBonusAmount FROM EmployeesBonus WHERE BonusDate BETWEEN '20140101' AND '20141231' --    2014  GROUP BY EmployeeID


, PIVOT:

 --      PIVOT SELECT EmployeeID, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12], ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)+ ISNULL([5],0)+ISNULL([6],0)+ISNULL([7],0)+ISNULL([8],0)+ ISNULL([9],0)+ISNULL([10],0)+ISNULL([11],0)+ISNULL([12],0) TotalBonusAmount FROM ( /*          : -  BonusMonth  BonusAmount     PIVOT -  ,      EmployeeID,      */ SELECT EmployeeID, MONTH(BonusDate) BonusMonth, BonusAmount FROM EmployeesBonus WHERE BonusDate BETWEEN '20140101' AND '20141231' ) q PIVOT(SUM(BonusAmount) FOR BonusMonth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) p


PIVOT SUM, , (COUNT, AVG, MIN, MAX, …).

UNPIVOT

, UNPIVOT. DemoPivotTable:

 SELECT EmployeeID, SUM(CASE WHEN MONTH(BonusDate)=1 THEN BonusAmount END) BonusAmount1, SUM(CASE WHEN MONTH(BonusDate)=2 THEN BonusAmount END) BonusAmount2, SUM(CASE WHEN MONTH(BonusDate)=3 THEN BonusAmount END) BonusAmount3, SUM(CASE WHEN MONTH(BonusDate)=4 THEN BonusAmount END) BonusAmount4, SUM(CASE WHEN MONTH(BonusDate)=5 THEN BonusAmount END) BonusAmount5, SUM(CASE WHEN MONTH(BonusDate)=6 THEN BonusAmount END) BonusAmount6, SUM(CASE WHEN MONTH(BonusDate)=7 THEN BonusAmount END) BonusAmount7, SUM(CASE WHEN MONTH(BonusDate)=8 THEN BonusAmount END) BonusAmount8, SUM(CASE WHEN MONTH(BonusDate)=9 THEN BonusAmount END) BonusAmount9, SUM(CASE WHEN MONTH(BonusDate)=10 THEN BonusAmount END) BonusAmount10, SUM(CASE WHEN MONTH(BonusDate)=11 THEN BonusAmount END) BonusAmount11, SUM(CASE WHEN MONTH(BonusDate)=12 THEN BonusAmount END) BonusAmount12, SUM(BonusAmount) TotalBonusAmount INTO DemoPivotTable --      FROM EmployeesBonus WHERE BonusDate BETWEEN '20140101' AND '20141231' GROUP BY EmployeeID


, :

 SELECT * FROM DemoPivotTable


UNPIVOT:

 --  UNPIVOT SELECT *, CAST(REPLACE(ColumnLabel,'BonusAmount','') AS int) BonusMonth FROM DemoPivotTable UNPIVOT(BonusAmount FOR ColumnLabel IN(BonusAmount1,BonusAmount2,BonusAmount3,BonusAmount4, BonusAmount5,BonusAmount6,BonusAmount7,BonusAmount8, BonusAmount9,BonusAmount10,BonusAmount11,BonusAmount12)) u


, NULL .

, .

GROUP BY ROLLUP GROUP BY GROUPING SETS

.

:

 -- GROUP BY ROLLUP   GROUPING SELECT --GROUPING(YEAR(bonus.BonusDate)) g1, --GROUPING(bonus.EmployeeID) g2, --GROUPING(emp.Name) g3, CASE WHEN GROUPING(YEAR(bonus.BonusDate))=1 THEN ' ' WHEN GROUPING(bonus.EmployeeID)=1 THEN '  '+CAST(YEAR(bonus.BonusDate) AS varchar(4))+' ' END RowTitle, emp.Name, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=1 THEN bonus.BonusAmount END) BonusAmountQ1, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=2 THEN bonus.BonusAmount END) BonusAmountQ2, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=3 THEN bonus.BonusAmount END) BonusAmountQ3, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=4 THEN bonus.BonusAmount END) BonusAmountQ4, SUM(bonus.BonusAmount) TotalBonusAmount FROM EmployeesBonus bonus JOIN Employees emp ON bonus.EmployeeID=emp.ID GROUP BY ROLLUP(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) --     GROUPING HAVING NOT(GROUPING(YEAR(bonus.BonusDate))=0 AND GROUPING(bonus.EmployeeID)=0 AND GROUPING(emp.Name)=1)


, GROUPING, g1, g2 g3, , HAVING.

:

 -- GROUP BY ROLLUP   GROUPING_ID SELECT /* GROUPING_ID (a, b, c) input = GROUPING(a) + GROUPING(b) + GROUPING(c)  001 =  1  011 =  3  111 =  7 */ --GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) gID, CASE GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) WHEN 7 THEN ' ' WHEN 3 THEN '  '+CAST(YEAR(bonus.BonusDate) AS varchar(4))+' ' END RowTitle, emp.Name, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=1 THEN bonus.BonusAmount END) BonusAmountQ1, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=2 THEN bonus.BonusAmount END) BonusAmountQ2, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=3 THEN bonus.BonusAmount END) BonusAmountQ3, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=4 THEN bonus.BonusAmount END) BonusAmountQ4, SUM(bonus.BonusAmount) TotalBonusAmount FROM EmployeesBonus bonus JOIN Employees emp ON bonus.EmployeeID=emp.ID GROUP BY ROLLUP(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) --     GROUPING_ID HAVING GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name)<>1


, gID HAVING.

:

 -- GROUP BY GROUPING SETS   GROUPING_ID SELECT /* GROUPING_ID (a, b, c) input = GROUPING(a) + GROUPING(b) + GROUPING(c)  001 =  1  011 =  3  111 =  7 */ --GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) gID, CASE GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) WHEN 7 THEN ' ' WHEN 3 THEN '  '+CAST(YEAR(bonus.BonusDate) AS varchar(4))+' ' END RowTitle, emp.Name, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=1 THEN bonus.BonusAmount END) BonusAmountQ1, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=2 THEN bonus.BonusAmount END) BonusAmountQ2, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=3 THEN bonus.BonusAmount END) BonusAmountQ3, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=4 THEN bonus.BonusAmount END) BonusAmountQ4, SUM(bonus.BonusAmount) TotalBonusAmount FROM EmployeesBonus bonus JOIN Employees emp ON bonus.EmployeeID=emp.ID GROUP BY GROUPING SETS( (YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name), --   (YEAR(bonus.BonusDate)), --    () --   )


GROUPING SET , HAVING.

Those. , GROUP BY ROLLUP GROUP BY GROUPING SETS, .

FULL JOIN

, :

 --   FULL JOIN WITH cteBonus AS( SELECT YEAR(BonusDate) BonusYear, EmployeeID, SUM(CASE WHEN DATEPART(QUARTER,BonusDate)=1 THEN BonusAmount END) BonusAmountQ1, SUM(CASE WHEN DATEPART(QUARTER,BonusDate)=2 THEN BonusAmount END) BonusAmountQ2, SUM(CASE WHEN DATEPART(QUARTER,BonusDate)=3 THEN BonusAmount END) BonusAmountQ3, SUM(CASE WHEN DATEPART(QUARTER,BonusDate)=4 THEN BonusAmount END) BonusAmountQ4, SUM(BonusAmount) TotalBonusAmount FROM EmployeesBonus GROUP BY YEAR(BonusDate),EmployeeID ), cteSalary AS( SELECT YEAR(SalaryDate) SalaryYear, EmployeeID, SUM(CASE WHEN DATEPART(QUARTER,SalaryDate)=1 THEN SalaryAmount END) SalaryAmountQ1, SUM(CASE WHEN DATEPART(QUARTER,SalaryDate)=2 THEN SalaryAmount END) SalaryAmountQ2, SUM(CASE WHEN DATEPART(QUARTER,SalaryDate)=3 THEN SalaryAmount END) SalaryAmountQ3, SUM(CASE WHEN DATEPART(QUARTER,SalaryDate)=4 THEN SalaryAmount END) SalaryAmountQ4, SUM(SalaryAmount) TotalSalaryAmount FROM EmployeesSalary GROUP BY YEAR(SalaryDate),EmployeeID ) SELECT ISNULL(s.SalaryYear,b.BonusYear) AccYear, ISNULL(s.EmployeeID,b.EmployeeID) EmployeeID, s.SalaryAmountQ1,s.SalaryAmountQ2,s.SalaryAmountQ3,s.SalaryAmountQ4, s.TotalSalaryAmount, b.BonusAmountQ1,b.BonusAmountQ2,b.BonusAmountQ3,b.BonusAmountQ4, b.TotalBonusAmount, ISNULL(s.TotalSalaryAmount,0)+ISNULL(b.TotalBonusAmount,0) TotalAmount FROM cteSalary s FULL JOIN cteBonus b ON s.EmployeeID=b.EmployeeID AND s.SalaryYear=b.BonusYear


, FULL JOIN. , WITH.

2 – OVER


OVER , , SELECT ( ). OVER SELECT, .. , , WHERE.

OVER . . , , OVER ( , ) .

, , :

 --    SET IDENTITY_INSERT Positions ON INSERT Positions(ID,Name)VALUES (10,N''), (11,N'') SET IDENTITY_INSERT Positions OFF 


 --   INSERT Employees(ID,Name,DepartmentID,PositionID,HireDate,Salary,Email)VALUES (1006,N' ..',4,10,'20150215',1800,'a.antonov@test.tt'), (1007,N' ..',5,11,'20150405',1200,'m.maksimov@test.tt'), (1008,N' ..',5,11,'20150410',1200,'d.danolov@test.tt'), (1009,N' ..',5,11,'20150415',1200,'o.ostapov@test.tt')


OVER ,

 SELECT ID, Name, DepartmentID, Salary, --      SUM(Salary) OVER() AllSalary, --        SUM(Salary) OVER(PARTITION BY DepartmentID) DepartmentSalary, --         CAST(Salary/SUM(Salary) OVER(PARTITION BY DepartmentID)*100 AS numeric(20,3)) SalaryPercentOfDepSalary, -- -   COUNT(*) OVER() AllEmplCount, -- -    COUNT(*) OVER(PARTITION BY DepartmentID) DepEmplCount FROM Employees

IDNameDepartmentIDSalaryAllSalaryDepartmentSalarySalaryPercentOfDepSalaryAllEmplCountDepEmplCount
1005..Null2000.0019900.002000.00100.000tenone
1000Ivanov I.I.one5000.0019900.005000.00100.000tenone
1002..22500.0019900.002500.00100.000tenone
1003..32000.0019900.005000.0040.000ten3
1004..31500.0019900.005000.0030.000ten3
1001..31500.0019900.005000.0030.000ten3
1006..four1800.0019900.001800.00100.000tenone
1007..five1200.0019900.003600.0033.333ten3
1008..five1200.0019900.003600.0033.333ten3
1009..five1200.0019900.003600.0033.333ten3

«PARTITION BY» , «GROUP BY».

, , , «PARTITION BY DepartmentID,PositionID», «PARTITION BY DepartmentID,YEAR(HireDate)».

, – AVG, MIN, MAX, COUNT DISTINCT.



ROW_NUMBER.

Name LastName,FirstName,MiddleName:

 SELECT ID, Name, --     Name ROW_NUMBER() OVER(ORDER BY Name) EmpNoByName, --     LastName,FirstName,MiddleName ROW_NUMBER() OVER(ORDER BY LastName,FirstName,MiddleName) EmpNoByFullName FROM Employees ORDER BY Name

IDNameEmpNoByNameEmpNoByFullName
1005..one6
1003..27
1006..3one
1008..four2
1000Ivanov I.I.fiveeight
1007..63
1004..7four
1009..eightfive
1001..99
1002..tenten

OVER «ORDER BY».

, OVER «PARTITION BY»:

 SELECT emp.ID, emp.Name EmpName, dep.Name DepName, --     ,    Name ROW_NUMBER() OVER(PARTITION BY dep.ID ORDER BY emp.Name) EmpNoInDepByName FROM Employees emp LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID ORDER BY dep.Name,emp.Name

IDEmpNameDepNameEmpNoInDepByName
1005..Nullone
1000Ivanov I.I.Administrationone
1002..Accountingone
1003..ITone
1004..IT2
1001..IT3
1008..Logisticsone
1007..Logistics2
1009..Logistics3
1006..one


– , . 2 , (RANK) (DENSE_RANK).

 SELECT emp.ID, emp.Name EmpName, emp.PositionID, -- -     COUNT(*) OVER(PARTITION BY emp.PositionID) EmpCountInPos, --    -     -     RANK() OVER(ORDER BY emp.PositionID) RankValue, --    –   () DENSE_RANK() OVER(ORDER BY emp.PositionID) DenseRankValue FROM Employees emp LEFT JOIN Positions pos ON emp.PositionID=pos.ID

IDEmpNamePositionIDEmpCountInPosRankValueDenseRankValue
1005..Nulloneoneone
1002..oneone22
1000Ivanov I.I.2one33
1001..32fourfour
1004..32fourfour
1003..fourone6five
1006..tenone76
1007..eleven3eight7
1008..eleven3eight7
1009..eleven3eight7


: LAG() LEAD(), FIRST_VALUE() LAST_VALUE()

.

LAG() LEAD():

 SELECT ID CurrEmpID, Name CurrEmpName, --    LAG(ID) OVER(ORDER BY ID) PrevEmpID, LAG(Name) OVER(ORDER BY ID) PrevEmpName, LAG(ID,2) OVER(ORDER BY ID) PrevPrevEmpID, LAG(Name,2,'not found') OVER(ORDER BY ID) PrevPrevEmpName, --    LEAD(ID) OVER(ORDER BY ID) NextEmpID, LEAD(Name) OVER(ORDER BY ID) NextEmpName, LEAD(ID,2) OVER(ORDER BY ID) NextNextEmpID, LEAD(Name,2,'not found') OVER(ORDER BY ID) NextNextEmpName FROM Employees ORDER BY ID

CurrEmpIDCurrEmpNamePrevEmpIDPrevEmpNamePrevPrevEmpIDPrevPrevEmpNameNextEmpIDNextEmpNameNextNextEmpIDNextNextEmpName
1000Ivanov I.I.NullNullNullnot found1001..1002..
1001..1000Ivanov I.I.Nullnot found1002..1003..
1002..1001..1000Ivanov I.I.1003..1004..
1003..1002..1001..1004..1005..
1004..1003..1002..1005..1006..
1005..1004..1003..1006..1007..
1006..1005..1004..1007..1008..
1007..1006..1005..1008..1009..
1008..1007..1006..1009..Nullnot found
1009..1008..1007..NullNullNullnot found

, .

, «PARTITION BY» OVER, , «OVER(PARTITION BY emp.DepartmentID ORDER BY emp.ID)».

FIRST_VALUE() LAST_VALUE():

 SELECT ID CurrEmpID, Name CurrEmpName, DepartmentID, --     FIRST_VALUE(ID) OVER(PARTITION BY DepartmentID ORDER BY ID) FirstEmpID, FIRST_VALUE(Name) OVER(PARTITION BY DepartmentID ORDER BY ID) FirstEmpName, --     LAST_VALUE(ID) OVER(PARTITION BY DepartmentID ORDER BY ID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) LastEmpID, LAST_VALUE(Name) OVER(PARTITION BY DepartmentID ORDER BY ID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) LastEmpName FROM Employees ORDER BY DepartmentID,ID

CurrEmpIDCurrEmpNameDepartmentIDFirstEmpIDFirstEmpNameLastEmpIDLastEmpName
1005..Null1005..1005..
1000Ivanov I.I.one1000Ivanov I.I.1000Ivanov I.I.
1002..21002..1002..
1001..31001..1004..
1003..31001..1004..
1004..31001..1004..
1006..four1006..1006..
1007..five1007..1009..
1008..five1007..1009..
1009..five1007..1009..

, . , RANGE.

RANGE ROWS

«RANGE» «ROWS», , OVER. - . .

. , .


:
  1. (RANGE)
  2. (ROWS)


:
Option 1:
{ROWS | RANGE} {{UNBOUNDED | } PRECEDING | CURRENT ROW}

Option 2:
{ROWS | RANGE}
BETWEEN
{{UNBOUNDED PRECEDING | CURRENT ROW |
{UNBOUNDED | 1}{PRECEDING | FOLLOWING}}
AND
{{UNBOUNDED FOLLOWING | CURRENT ROW |
{UNBOUNDED | 2}{PRECEDING | FOLLOWING}}


Excel :

 SELECT ID, Salary, SUM(Salary) OVER() Sum1, --    - " "  " " SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN unbounded preceding AND unbounded following) Sum2, --       - " "  " " SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN unbounded preceding AND current row) Sum3, --        - " "  " " SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN current row AND unbounded following) Sum4, --     - "1 "  "3 " SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN 1 following AND 3 following) Sum5, --    - "1 "  "1 " SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN 1 preceding AND 1 following) Sum6, --   " "  "" SUM(Salary) OVER(ORDER BY ID ROWS 3 preceding) Sum7, --  " "  "" SUM(Salary) OVER(ORDER BY ID ROWS unbounded preceding) Sum8 FROM Employees ORDER BY ID

IDSalarySum1Sum2Sum3Sum4Sum5Sum6Sum7Sum8
10005000.0019900.0019900.005000.0019900.006000.006500.005000.005000.00
10011500.0019900.0019900.006500.0014900.006000.009000.006500.006500.00
10022500.0019900.0019900.009000.0013400.005500.006000.009000.009000.00
10032000.0019900.0019900.0011000.0010900.005300.006000.0011000.0011000.00
10041500.0019900.0019900.0012500.008900.005000.005500.007500.0012500.00
10052000.0019900.0019900.0014500.007400.004200.005300.008000.0014500.00
10061800.0019900.0019900.0016300.005400.003600.005000.007300.0016300.00
10071200.0019900.0019900.0017500.003600.002400.004200.006500.0017500.00
10081200.0019900.0019900.0018700.002400.001200.003600.006200.0018700.00
10091200.0019900.0019900.0019900.001200.00Null2400.005400.0019900.00

RANGE , , . ORDER BY .

 SELECT PositionID, Salary, SUM(Salary) OVER(PARTITION BY PositionID) Sum1, --      PositionID - " "  " " SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN unbounded preceding AND unbounded following) Sum2, --      PositionID     - " "  " " (<=PositionID) SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN unbounded preceding AND current row) Sum3, --           - " "  " " (>=PositionID) SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN current row AND unbounded following) Sum4, /*     RANGE  MS SQL  ,   Oracle  .   MSDN:  RANGE     <  > PRECEDING   <  > FOLLOWING. <  > PRECEDING   <  >         .       RANGE. <  > FOLLOWING   <  >         .       RANGE. */ --      - "+1"  "+3" ( BETWEEN PositionID+1 AND PositionID+3) --SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN 1 following AND 3 following) Sum5, --      - "-1"  "+1" ( BETWEEN PositionID-1 AND PositionID+1) --SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN 1 preceding AND 1 following) Sum6, --       - "-3"  "" ( BETWEEN PositionID-3 AND PositionID) --SUM(Salary) OVER(ORDER BY PositionID RANGE 3 preceding) Sum7, --    "  "  "" (<=PositionID) SUM(Salary) OVER(ORDER BY PositionID RANGE unbounded preceding) Sum8 FROM Employees ORDER BY PositionID

PositionIDSalarySum1Sum2Sum3Sum4Sum8
Null2000.002000.0019900.002000.0019900.002000.00
one2500.002500.0019900.004500.0017900.004500.00
25000.005000.0019900.009500.0015400.009500.00
31500.003000.0019900.0012500.0010400.0012500.00
31500.003000.0019900.0012500.0010400.0012500.00
four2000.002000.0019900.0014500.007400.0014500.00
ten1800.001800.0019900.0016300.005400.0016300.00
eleven1200.003600.0019900.0019900.003600.0019900.00
eleven1200.003600.0019900.0019900.003600.0019900.00
eleven1200.003600.0019900.0019900.003600.0019900.00


Conclusion


, , SQL (DDL, DML).

, , , SQL.

, , .

Thanks for attention! That's all for now.

Ps. , , !

Source: https://habr.com/ru/post/256169/

More articles:


All Articles