fMU0UHU8

1DROP TABLE TBL_TASK;
2
3CREATE TABLE TBL_TASK (
4	TASK_ID INT IDENTITY(1, 1) PRIMARY KEY,
5	TASK_NAME VARCHAR(100) NOT NULL,
6	CATEGORY_ID INT NOT NULL,			
7	TASK_PRIORITY INT DEFAULT(4),			-- 1, 2, 3, 4
8	TASK_STATUS INT DEFAULT(0),				-- 1 - COMPLETED, 0 - PENDING
9	TASK_CREATED_DATE BIGINT DEFAULT( CONVERT(BIGINT, GETDATE()) ),
10	USR_ID INT  
11)
12
13DROP TABLE CATEGORY;
14
15CREATE TABLE TBL_CATEGORY (
16	CATEGORY_ID INT IDENTITY(1, 1) PRIMARY KEY,
17	CATEGORY_NAME VARCHAR(100),
18)
19
20
21CREATE TABLE TBL_USER (
22	USR_ID INT IDENTITY(1, 1) PRIMARY KEY,
23	USR_NAME VARCHAR(100) NOT NULL,
24	USR_PASSWORD VARCHAR(100)
25)
26
27
28EXEC COMPLETE_TASK 'COMPLETE WORK';
29
30CREATE PROCEDURE COMPLETE_TASK
31	@TASK_NAME VARCHAR(100)
32AS
33BEGIN
34	IF EXISTS (SELECT * FROM TBL_TASK WHERE TASK_NAME = @TASK_NAME)
35
36	BEGIN
37		UPDATE TBL_TASK SET TASK_STATUS = 1 WHERE TASK_NAME = @TASK_NAME;
38	END
39END
40
41
42ALTER PROCEDURE COMPLETED_TASKS
43	@CATEGORY_NAME VARCHAR(100)
44AS
45BEGIN
46	DECLARE @CATEGORY_ID INT
47
48	SELECT @CATEGORY_ID = CATEGORY_ID FROM TBL_CATEGORY WHERE CATEGORY_NAME = @CATEGORY_NAME;
49
50	SELECT * FROM TBL_TASK WHERE TASK_STATUS = 1 AND CATEGORY_ID = @CATEGORY_ID;
51END
52
53EXEC COMPLETED_TASKS 'WORK'
54
55
56ALTER PROCEDURE CREATE_TASK
57	@TASK_NAME VARCHAR(100),
58	@USER_NAME VARCHAR(100), 
59	@CATEGORY_NAME VARCHAR(100), 
60	@TASK_PRIORITY INT = NULL
61AS 
62BEGIN
63	
64	DECLARE @USER_ID VARCHAR(100);
65
66	SELECT @USER_ID = USR_ID FROM TBL_USER WHERE USR_NAME = @USER_NAME;
67
68
69	IF NOT EXISTS (SELECT * FROM TBL_CATEGORY WHERE CATEGORY_NAME = @CATEGORY_NAME)
70
71	BEGIN
72		INSERT INTO TBL_CATEGORY (CATEGORY_NAME) VALUES (@CATEGORY_NAME);
73	END
74
75	DECLARE @CATEGORY_ID INT;
76
77	SELECT @CATEGORY_ID = CATEGORY_ID FROM TBL_CATEGORY WHERE CATEGORY_NAME = @CATEGORY_NAME;
78
79	IF EXISTS (SELECT * FROM TBL_TASK WHERE TASK_NAME = @TASK_NAME AND TASK_PRIORITY != @TASK_PRIORITY)
80
81	BEGIN 
82		UPDATE TBL_TASK SET TASK_PRIORITY = @TASK_PRIORITY WHERE TASK_NAME = @TASK_NAME AND USR_ID = @USER_ID;
83	END
84
85
86	IF NOT EXISTS (SELECT * FROM TBL_TASK WHERE TASK_NAME = @TASK_NAME)
87
88	BEGIN
89		INSERT INTO TBL_TASK (TASK_NAME, CATEGORY_ID) VALUES (@TASK_NAME, @CATEGORY_ID) 
90	END
91END
92
93EXEC CREATE_TASK 'CREATE LIST SP', 'WORK', 4;
94
95DELETE FROM TBL_TASK;
96
97SELECT * FROM TBL_TASK;
98SELECT * FROM TBL_CATEGORY;
99
100
101DECLARE @NAME VARCHAR(100) = 'SANJAY';
102
103SELECT @NAME AS NAME
104
105CREATE PROCEDURE DELETE_TASK
106	@TASK_NAME VARCHAR(100)
107AS
108BEGIN
109	DELETE FROM TBL_TASK WHERE TASK_NAME = @TASK_NAME
110END
111
112SELECT * FROM TBL_TASK;
113
114EXEC DELETE_TASK 'COMPLETE ADMIN PAGE';
115
116CREATE PROCEDURE UPDATE_TASK
117	@TASK_NAME VARCHAR(100), @TASK_STATUS INT = NULL, @TASK_PRIORITY INT = NULL, @UPDATED_TASK_NAME VARCHAR(100) = NULL
118AS
119BEGIN 
120	IF @TASK_PRIORITY IS NOT NULL
121	BEGIN 
122		UPDATE TBL_TASK SET TASK_PRIORITY = @TASK_PRIORITY WHERE TASK_NAME = @TASK_NAME
123	END
124
125	IF @TASK_STATUS IS NOT NULL
126	BEGIN 
127		UPDATE TBL_TASK SET TASK_STATUS = @TASK_STATUS WHERE TASK_NAME = @TASK_NAME
128	END
129
130	IF @UPDATED_TASK_NAME IS NOT NULL
131	BEGIN
132		UPDATE TBL_TASK SET TASK_NAME = @UPDATED_TASK_NAME WHERE TASK_NAME = @TASK_NAME
133	END
134END
135
136SELECT * FROM TBL_TASK;
137
138EXEC UPDATE_TASK 'GOTO HOME', @TASK_PRIORITY = 1; 
139
140CREATE PROCEDURE CREATE_USER 
141	@USER_NAME VARCHAR(100), 
142	@USER_PASSWORD VARCHAR(100) 
143AS
144BEGIN
145	INSERT TBL_USER (USR_NAME, USR_PASSWORD) SELECT @USER_NAME, @USER_PASSWORD;
146END
147
148EXEC CREATE_USER 'SANJAY', 'SANJAY_123';
149
150SELECT CONVERT(INT, CURRENT_TIMESTAMP);
151
152SELECT * FROM TBL_TASK;
153
154SELECT * FROM TBL_USER;
155
156DELETE FROM TBL_USER;
157
158
159SELECT * FROM TBL_USER;
160INSERT TBL_USER (USR_NAME, USR_PASSWORD) VALUES ('RIYA', 'RIYA_D');
161
162SELECT * FROM TBL_USER WHERE USR_NAME = 'RIYA'
163UNION
164SELECT * FROM TBL_USER WHERE USR_NAME = 'RIYA'