Create DB Table View Procedure

IF DB_ID('Wages') IS NOT NULL
DROP DATABASE Wages
GO
create database Wages

if OBJECT_ID('StudentInfo','U') IS NOT NULL
DROP TABLE StudentInfo
GO

CREATE TABLE StudentInfo(
 ID int identity(1,1) NOT NULL PRIMARY KEY,
 Name nvarchar(10) not null,
 CompanyID int,
 PostDate datetime,
 Wage int,
 --FOREIGN KEY (CompanyID) REFERENCES WageInfo(CompanyID)
)

if OBJECT_ID('WageInfo','U') IS NOT NULL
DROP TABLE WageInfo
GO

CREATE TABLE WageInfo(
 CompanyID int identity(1,1) primary key,
 CompanyName nvarchar(50) not null,
 Poste nvarchar(50) not null
)

Alter table WageInfo add FOREIGN KEY (CompanyID) REFERENCES WageInfo(CompanyID)

INSERT INTO WageInfo(CompanyName,Poste)
values
('NCS','DEVELOPER'),
('WISTRON','DEVELOPER'),
('IBM1','DEVELOPER'),
('IBM2','DEVELOPER'),
('IBM3','DEVELOPER');

select * from WageInfo

INSERT INTO StudentInfo(Name,CompanyID,PostDate,Wage)
values
('Name1',1,GETDATE(),2000),
('Name2',2,GETDATE(),2500),
('Name3',3,GETDATE(),3000),
('Name4',4,GETDATE(),2000),
('Name4',NULL,NULL,NULL);

delete from StudentInfo
select * from StudentInfo

--view1

IF EXISTS(SELECT * FROM sysobjects where name='V_EMPLOYED_STUDENT' )
DROP VIEW V_EMPLOYED_STUDENT
GO

CREATE VIEW V_EMPLOYED_STUDENT
AS

select Name,Poste,CompanyName,Wage,PostDate
from StudentInfo A INNER JOIN WageInfo B
ON A.CompanyID=B.CompanyID

GO

--view2
IF EXISTS(SELECT * FROM sysobjects where name='V_NOT_EMPLOYED_STUDENT' )
DROP VIEW V_NOT_EMPLOYED_STUDENT
GO

CREATE VIEW V_NOT_EMPLOYED_STUDENT
AS

select * from StudentInfo where CompanyID is not null

GO
--proc

IF OBJECT_ID('PROC_HALF_DEVELOPER','P') IS NOT NULL
BEGIN
 DROP PROCEDURE PROC_HALF_DEVELOPER
END
GO

CREATE PROCEDURE PROC_HALF_DEVELOPER

AS

declare @Half_CT decimal
declare @CT decimal

select @Half_CT=COUNT(*)/2 from StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID
where B.Poste='DEVELOPER'

select @CT=COUNT(*) from StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID
where B.Poste='DEVELOPER' and (A.Wage<3000 or A.Wage<4000 or A.Wage<6000)

--having COUNT(*)>=@Half_CT
--select @Half_CT,@CT

while(@CT>=@Half_CT)
begin

 update A
 SET A.Wage=A.Wage+50
 FROM StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID
 where B.Poste='DEVELOPER'

 select @CT=COUNT(*) from StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID
 where B.Poste='DEVELOPER' and (A.Wage<3000 or A.Wage<4000 or A.Wage<6000)

end

GO

--proc

IF OBJECT_ID('PROC_AVG_DEVELOPER','P') IS NOT NULL
BEGIN
 DROP PROCEDURE PROC_AVG_DEVELOPER
END
GO

CREATE PROCEDURE PROC_AVG_DEVELOPER

AS

declare @Wage_AVG decimal
declare @n int

select @Wage_AVG=AVG(A.Wage) from StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID

while(@Wage_AVG<=3500 )
begin

 update A
 SET A.Wage=A.Wage+50
 from StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID
 where B.Poste='DEVELOPER'

 select @Wage_AVG=AVG(A.Wage) from StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID
 where B.Poste='DEVELOPER'

end

GO