select * from authors


with row
as
(
select 1 as s, 1 as id
UNION ALL
select 2 as s, row.id + 1 as id from row
where id = 1
UNION ALL
select 3 as s, row.id + 1 as id from row
where id = 2
)
select * from row
 where s=2



with first
as
(
select au_id, 1 as level, 1 as depth from authors where au_id='172-32-1176'
union 
select au_id, 1 as level, 1 as depth from authors where au_id='172-32-1176' 
UNION ALL
select first.au_id,  2 as level, first.depth + 1 as depth
from first join authors on depth < 2
UNION ALL
select  first.au_id, 3 as level, first.depth + 1 as depth
from first join authors on depth < 3
)
select count(*) from first
group by level

--DROP TABLE EMPLOYEE
--DROP TABLE SHIFT

CREATE TABLE Shift
(
ShiftID INT PRIMARY KEY NONCLUSTERED,
Name CHAR(5)
)
INSERT INTO Shift VALUES (1, 'Day')
INSERT INTO Shift VALUES (2, 'Swing')
INSERT INTO Shift VALUES (3, 'Mid')


CREATE TABLE OfficeStaff
(
ID int PRIMARY KEY NONCLUSTERED,
BID int, -- boss id
Name VARCHAR(30),
Salary float
)


TRUNCATE TABLE OfficeStaff
INSERT INTO OfficeStaff VALUES (1, 1, 'BigBoss', 1000000.0)


DECLARE @index INT
SET @INDEX = 2
WHILE @index < 2000 and @index > 1
BEGIN
DECLARE @boss int
SET @boss = ROUND((RAND() * (@index-1)), 0)
IF @boss = 0 
SET @Boss = 1
Insert Into OfficeStaff VALUES
(@index, @boss,
'Worker' + CAST(@index as VARCHAR(20)),
ROUND(10000 * RAND(), 2))
SET @index = @index + 1
END


