NOTE.SQL

Use ProjectDB;


--> CREATING THE TABLE
CREATE TABLE NOTE(
	NoteId INT IDENTITY(4001,1) NOT NULL PRIMARY KEY,
	ClientId INT NOT NULL FOREIGN KEY REFERENCES CLIENT(ClientId),
	ModifiedBy INT NOT NULL FOREIGN KEY REFERENCES Advocate(AdvocateId),
	CreatedBy INT NOT NULL FOREIGN KEY REFERENCES ADVOCATE (AdvocateId),
	Note VARCHAR(100) NOT NULL DEFAULT 'TASK DETAILS'

	
);


--> INSERT DATAS INTO THE TABLE
INSERT INTO NOTE VALUES(3001,1001,1002,'This is the first ever note created in casiac')
INSERT INTO NOTE VALUES(3002,1001,1002,'This is a cute note the best teammate tara')
INSERT INTO NOTE VALUES(3003,1001,1002,'Mahy needs to be sued for his UI')
INSERT INTO NOTE VALUES(3001,1001,1002,'Yusha did a very good job')

--> SELECT STATEMENTS TO VIEW<--
--> ALL THE DATAS

SELECT * FROM NOTE

--> ALL THE DATA WITH THE DETAILS

SELECT NoteId ,
CLIENT.FirstName + ' '  + CLIENT.LastName AS 'CLIENT NAME',
MODDER.FirstName + ' ' + MODDER.LastName AS 'Modified BY',
CREATOR.FirstName + ' '+ CREATOR.LastName AS ' CREATED BY',
Note 
FROM NOTE , CLIENT , Advocate MODDER , Advocate CREATOR

WHERE NOTE.ClientId = CLIENT.ClientId AND NOTE.ModifiedBy = MODDER.AdvocateId AND NOTE.CreatedBy = CREATOR.AdvocateId