Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE dbeksamen
- DROP TABLE IF EXISTS timeregistration
- DROP TABLE IF EXISTS employeeprojects
- DROP TABLE IF EXISTS employee
- DROP TABLE IF EXISTS boss
- DROP TABLE IF EXISTS project
- DROP TABLE IF EXISTS customer
- CREATE TABLE customer
- (
- id INT IDENTITY PRIMARY KEY,
- customername VARCHAR(50)
- )
- CREATE TABLE project
- (
- id INT IDENTITY PRIMARY KEY,
- projectname VARCHAR(50),
- projectstatus CHAR(9) CHECK(projectstatus IN ('active','inactive')),
- customerid INT NOT NULL REFERENCES customer,
- fixedprice INT,
- estimatedhours INT,
- hourlyrate INT
- )
- CREATE TABLE boss
- (
- id INT IDENTITY PRIMARY KEY,
- name VARCHAR(50)
- )
- CREATE TABLE employee
- (
- id INT IDENTITY PRIMARY KEY,
- employeename VARCHAR(50),
- bossid INT NOT NULL REFERENCES boss,
- timeoff INT
- )
- CREATE TABLE employeeprojects
- (
- id INT IDENTITY PRIMARY KEY,
- employeeid INT NOT NULL REFERENCES employee,
- projectid INT NOT NULL REFERENCES project
- )
- CREATE TABLE timeregistration
- (
- id INT IDENTITY PRIMARY KEY,
- starttime datetime,
- endtime datetime,
- employeeid INT NOT NULL REFERENCES employee,
- projectid INT NOT NULL REFERENCES project,
- note VARCHAR(500)
- )
- GO
- INSERT INTO customer VALUES ('microsoft'), ('apple')
- INSERT INTO project VALUES ('projectOne', 'active', 1, 5000, 10, NULL),
- ('projectTwo', 'inactive', 1, NULL, NULL, 500),
- ('projectThree', 'active', 2, 15000, 30, NULL),
- ('projectFour', 'active', 2, 6000, 11, NULL)
- INSERT INTO boss VALUES ('jackson'), ('peterson')
- INSERT INTO employee VALUES ('Ravn', 1, 2), ('Frost', 2, 5), ('Dennis', 1, 3)
- INSERT INTO employeeprojects VALUES (1, 1), (1,2), (2,3), (3,4)
- INSERT INTO timeregistration VALUES ((CONVERT(datetime,'19-05-19 06:00:00 PM',5)), (CONVERT(datetime,'19-05-19 08:00:00 PM',5)), 1, 1, '2 hours on projectOne'),
- ((CONVERT(datetime,'13-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 1, 2, '2 hours on projectTwo'),
- ((CONVERT(datetime,'13-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 2, 3, '2 hours on projectThree'),
- ((CONVERT(datetime,'13-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 3, 4, '2 hours on projectFour')
- -- Ex2) ---------------------------------------------------
- GO
- CREATE INDEX Employeetbl_timeoff
- ON employee (timeoff ASC)
- GO
- SELECT * FROM employee WITH(INDEX(Employeetbl_timeoff))
- CREATE INDEX Projecttbl_fixedprice
- ON project (fixedprice ASC)
- GO
- SELECT * FROM project WITH(INDEX(Projecttbl_fixedprice))
- -- Ex3) ---------------------------------------------------
- GO
- SELECT e.employeename
- FROM employee e
- JOIN timeregistration t ON e.id = t.employeeid
- JOIN project p ON t.projectid = p.id
- JOIN customer c ON c.id = p.customerid
- WHERE c.customername = 'apple'
- --Ex4) -----------------------------------------------------
- GO
- CREATE OR ALTER TRIGGER timeregistrationTrigger
- ON timeregistration
- instead OF INSERT
- AS
- BEGIN
- DECLARE @daysbefore INT
- DECLARE @employeeonproject INT
- DECLARE @projectstatus CHAR(9)
- SELECT @daysbefore = DATEDIFF(DAY, GETDATE(), (SELECT starttime FROM inserted))
- IF(@daysbefore <= -7)
- BEGIN
- RAISERROR('Noget gik galt', 16, 1)
- RETURN
- END
- SELECT @employeeonproject = ep.employeeid
- FROM employeeprojects ep
- WHERE ep.employeeid = (SELECT employeeid FROM inserted) AND ep.projectid = (SELECT projectid FROM inserted)
- IF(@employeeonproject IS NULL)
- BEGIN
- RAISERROR('Noget gik galt', 16, 1)
- RETURN
- END
- SELECT @projectstatus = p.projectstatus
- FROM project p
- WHERE p.id = (SELECT projectid FROM inserted)
- IF(@projectstatus = 'inactive')
- BEGIN
- RAISERROR('Noget gik galt', 16, 1)
- RETURN
- END
- INSERT INTO timeregistration VALUES (
- (SELECT starttime FROM inserted),
- (SELECT endtime FROM inserted),
- (SELECT employeeid FROM inserted),
- (SELECT projectid FROM inserted),
- (SELECT note FROM inserted))
- END
- --test til krav1)
- INSERT INTO timeregistration VALUES ((CONVERT(datetime,'14-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 1, 1, '2 hours on projectOne')
- --test til krav 2)
- INSERT INTO timeregistration VALUES ((CONVERT(datetime,'20-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 2, 1, '2 hours on projectOne')
- --test til krav 3)
- INSERT INTO timeregistration VALUES ((CONVERT(datetime,'20-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 1, 2, '2 hours on projectOne')
- --Ex5) ------------------------------------------------------------------
- GO
- CREATE OR ALTER FUNCTION dbo.antalhverdageiMinutter(@aar INT ,@maaned INT)
- -- returnerer antal minutter i hverdage i den pågældende måned
- RETURNS INT
- AS
- BEGIN
- DECLARE @res INT
- DECLARE @firstdayofmonth DATE = datefromparts(@aar,@maaned,1)
- DECLARE @firstdayofnextmonth DATE = dateadd(mm,1,@firstdayofmonth)
- DECLARE @lastdateofmonth DATE = dateadd(dd,-1,@firstdayofnextmonth)
- IF DAY(@lastdateofmonth) = 28
- SET @res = 20
- ELSE
- IF DAY(@lastdateofmonth) = 29
- IF datename(DW,@firstdayofmonth) IN ('Saturday','Sunday')
- SET @res = 20
- ELSE
- SET @res = 21
- ELSE
- IF DAY(@lastdateofmonth) = 30
- IF datename(DW,@firstdayofmonth) = 'Saturday'
- SET @res = 20
- ELSE
- IF datename(DW,@firstdayofmonth) IN ('Friday','Sunday')
- SET @res = 21
- ELSE SET @res = 22
- ELSE
- IF DAY(@lastdateofmonth) = 31
- IF datename(DW,@firstdayofmonth) IN ('Friday','Saturday')
- SET @res = 21
- ELSE
- IF datename(DW,@firstdayofmonth) IN ('Thursday','Sunday')
- SET @res = 22
- ELSE SET @res = 23
- RETURN 444*@res
- END
- GO
- CREATE OR ALTER proc updatetimeoff
- @monthParameter INT,
- @yearParameter INT
- AS
- BEGIN
- DECLARE @employeeid INT
- DECLARE @timeoff INT
- DECLARE @starttime datetime
- DECLARE @endtime datetime
- DECLARE @lastid INT = 1
- DECLARE @minutes INT = 0
- DECLARE @finalminutes INT = 0
- DECLARE @numberOfRows INT = 0
- DECLARE MY_CURSOR cursor
- LOCAL static forward_only
- FOR
- SELECT e.id, e.timeoff, t.starttime, t.endtime
- FROM employee e
- JOIN timeregistration t ON t.employeeid = e.id
- OPEN MY_CURSOR
- fetch NEXT FROM MY_CURSOR INTO @employeeid, @timeoff, @starttime, @endtime
- while (@@FETCH_STATUS = 0)
- BEGIN
- IF MONTH(@starttime) = @monthParameter AND YEAR(@starttime) = @yearParameter
- BEGIN
- IF @lastid = @employeeid
- BEGIN
- IF DATENAME(DW, @starttime) = 'Sunday'
- BEGIN
- SELECT @minutes += DATEDIFF(MINUTE, @starttime, @endtime)
- END
- ELSE
- BEGIN
- SELECT @minutes += (DATEDIFF(MINUTE, @starttime, @endtime)) * 2
- END
- END
- ELSE
- BEGIN
- UPDATE employee
- SET timeoff += (@minutes - dbo.antalhverdageiMinutter(@yearParameter, @monthParameter))
- WHERE id = @lastid
- SET @lastid += 1
- SET @minutes = 0
- SET @finalminutes = 0
- IF DATENAME(DW, @starttime) = 'Sunday'
- BEGIN
- SELECT @minutes += DATEDIFF(MINUTE, @starttime, @endtime)
- END
- ELSE
- BEGIN
- SELECT @minutes += (DATEDIFF(MINUTE, @starttime, @endtime)) * 2
- END
- END
- END
- fetch NEXT FROM MY_CURSOR INTO @employeeid, @timeoff, @starttime, @endtime
- END
- close MY_CURSOR
- END
- EXEC updatetimeoff 5, 2019
- SELECT * FROM employee
- -- Ex8) ---------------------------------
- GO
- EXEC sp_addlogin 'admin','123'
- EXEC sp_addlogin 'employee','123'
- EXEC sp_grantdbaccess 'admin'
- EXEC sp_grantdbaccess 'employee'
- EXEC sp_addrole 'administrators'
- EXEC sp_addrole 'employees'
- EXEC sp_addrolemember 'administrators','admin'
- EXEC sp_addrolemember 'employees','employee'
- GRANT SELECT, INSERT, UPDATE, DELETE
- ON DATABASE::dbeksamen
- TO [admin]
- GRANT SELECT, INSERT, UPDATE, DELETE
- ON object::dbeksamen.dbo.timeregistration
- TO [employee]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement