SQL consolidation exercise (several knowledge points used in the past few days)

The SQL consolidation exercise used several knowledge points a few days ago. When I checked the crossapply operator in the help document of SQLServer2005, I found that I used a lot of knowledge, which is a classic example. Write it down here to consolidate the knowledge points of the past few days. Create a table 1 employee table (Employees): CREATETABLEEmplo

The SQL consolidation exercise used a few knowledge points a few days ago. When I checked the cross apply operator in the help document of SQL Server 2005, I found that I used a lot of knowledge, which is a classic example. Write it down here to consolidate the knowledge points of the past few days. Create table 1 employee TABLE (Employees): create table Emplo

SQL consolidation exercise-a few knowledge points from the past few days

When I checked the cross apply operator in the help document of SQL Server 2005, I found that I felt that I had a lot of knowledge and had no record filing space. This is a classic example. Write it down here. The Hong Kong Space also aims to consolidate the knowledge points of the past few days.

Create an employee table first (Employees ):

Create table Employees (empid, mgrid intNULL, empname varchar (25) not null, salary, CONSTRAINT PK_Employees primary key (empid ),)

Insert data to the Employees table:

Employees, $10000.00), $5000.00), $5000.00), $5000.00), $2500.00), $2500.00), $2500.00), $2500.00 ), $2500.00), $2000.00), $2000.00), $2000.00), $1500.00)

SQL statement used to query all data inserted into the Employees table:

Employees

Result

Create a second Department table (Departments ):

Create table partitions ments (deptid, deptname VARCHAR (25) not null, deptmgrid Employees)

Insert data to the orders tables:

, 2), 7), 8), 9), 4), NULL)

SQL statement used to query all data inserted into the orders table:

Administrative ments

Result

The following table value function uses employee ID as the parameter and returns the employee and all his/her subordinates (using the CTE public table expression... as... ):

) (Empid, empname VARCHAR (25) not null, mgrid int null, lvl) Employees_Subtree (empid, empname, mgrid, lvl) AS (empid, empname, mgrid, 0 FROM employeese. empid, e. empname, e. mgrid, es. lvlemployees AS eJOIN employees_subtree AS esON e. mgrid = es. empid) Employees_Subtree

Return all subordinates of all levels of each department manager and use the following SQL statement (cross apply applies is used ):

Parameters as d cross apply fn_getsubtree (D. deptmgrid) AS ST

Result

, Virtual host