enrolled in a yearly course has incorrect --data in records with ids between 20 and 100 (inclusive)
---------------------------------------------------------
--A table containing the students enrolled in a yearly course has incorrect
--data in records with ids between 20 and 100 (inclusive).
DROP TABLE IF EXISTS enrollments;
CREATE TABLE enrollments
(id int NOT NULL PRIMARY KEY,
year int NOT NULL,
studentId int NOT NULL)
-- insert sample data
Declare @Id int
Set @Id = 1
Declare @studentId int
Set @studentId = 10
Declare @LowerLimitForYear int
Declare @UprerLimitForYear int
Set @LowerLimitForYear= 2000
Set @UprerLimitForYear= 2100
Declare @RandomYear int
while(@Id <= 200)
Begin
select @RandomYear = ROUND((@UprerLimitForYear -@LowerLimitForYear)* RAND() +1,0)*100
Insert into enrollments
Values(@Id,@RandomYear,@studentId)
Set @Id = @Id +1
Set @studentId = @studentId +10
END
--Write a query that updates the field 'year'
-- of every faulty record to 2015.
update enrollments
set [year] = 2015
where id between 20 and 100