Notes

CREATE TABLE student.Note (
note_id BIGINT(15) NOT NULL AUTO_INCREMENT,
note_title VARCHAR(45) NOT NULL,
note_content VARCHAR(500) NULL,
note_status VARCHAR(10) NULL,
note_creation_date DATE NULL,
PRIMARY KEY (note_id));


CREATE TABLE student.Category (
category_id BIGINT(15) NOT NULL AUTO_INCREMENT,
category_name VARCHAR(45) NOT NULL,
category_descr VARCHAR(45) NULL,
category_creation_date DATE NULL,
category_creator BIGINT(15) NULL,
PRIMARY KEY (category_id),
FOREIGN KEY (category_creator) REFERENCES User(user_id)
);

CREATE TABLE student.Reminder (
reminder_id BIGINT(15) NOT NULL AUTO_INCREMENT,
reminder_name VARCHAR(45) NOT NULL,
reminder_descr VARCHAR(200) NULL,
reminder_type VARCHAR(20) NULL,
reminder_creation_date DATE NULL,
reminder_creator BIGINT(15) NULL,
PRIMARY KEY (reminder_id),
FOREIGN KEY (reminder_creator) REFERENCES User(user_id)
);

CREATE TABLE student.User (
user_id BIGINT(15) NOT NULL AUTO_INCREMENT,
user_name VARCHAR(45) NOT NULL ,
user_added_date DATE NULL,
user_password VARCHAR(45) NULL,
user_mobile VARCHAR(15) NULL,
PRIMARY KEY (user_id));

CREATE TABLE UserNote (
usernote_id BIGINT(15) NOT NULL AUTO_INCREMENT,
user_id BIGINT(15) NOT NULL,
note_id BIGINT(15) NOT NULL,
PRIMARY KEY (usernote_id),
FOREIGN KEY (user_id) REFERENCES User(user_id),
FOREIGN KEY (note_id) REFERENCES Note(note_id)
);


CREATE TABLE student.Notereminder (
notereminder_id BIGINT(15) NOT NULL AUTO_INCREMENT,
note_id BIGINT(15) NOT NULL,
reminder_id BIGINT(15) NOT NULL,
PRIMARY KEY (notereminder_id),
FOREIGN KEY (note_id) REFERENCES Note(note_id),
FOREIGN KEY (reminder_id) REFERENCES Reminder(reminder_id)
);

CREATE TABLE student.NoteCategory (
notecategory_id BIGINT(15) NOT NULL AUTO_INCREMENT,
note_id BIGINT(15) NOT NULL,
category_id BIGINT(15) NOT NULL,
PRIMARY KEY (notecategory_id),
FOREIGN KEY (note_id) REFERENCES Note(note_id),
FOREIGN KEY (category_id) REFERENCES Category(category_id)
);


INSERT INTO student.Note (note_id, note_title, note_content, note_status, note_creation_date) VALUES ('1', 'Note1', 'Note1-content', 'Inprogress', '2007-02-03');
INSERT INTO student.Note (note_id, note_title, note_content, note_status, note_creation_date) VALUES ('2', 'Note2', 'Note2-content', 'InActive', '2021-01-31');
INSERT INTO student.Note (note_id, note_title, note_content, note_status, note_creation_date) VALUES ('3', 'Note3', 'Note3-content', 'Active', '2021-02-02');
INSERT INTO student.Note (note_id, note_title, note_content, note_status, note_creation_date) VALUES ('4', 'Note4', 'Note4-content', 'Active', '2021-03-06');

INSERT INTO student.Category ( category_name, category_descr, category_creation_date, category_creator) VALUES ( 'Category1', 'Category1-desc', '2021-02-06', '101');
INSERT INTO student.Category (category_name, category_descr, category_creation_date, category_creator) VALUES ( 'Category2', 'Category2-desc', '2020-06-21', '102');
INSERT INTO student.Category ( category_name, category_descr, category_creation_date, category_creator) VALUES ( 'Category3', 'Category3-desc', '2020-05-05', '103');
INSERT INTO student.Category ( category_name, category_descr, category_creation_date, category_creator) VALUES ( 'Category4', 'Category4-desc', '2020-06-08', '104');


INSERT INTO student.Reminder ( reminder_name, reminder_descr, reminder_type, reminder_creation_date, reminder_creator) VALUES ( 'rem1', 'rem1-desc', 'erveryday', '2004-03-26', '1');
INSERT INTO student.Reminder ( reminder_name, reminder_descr, reminder_type, reminder_creation_date, reminder_creator) VALUES ( 'rem2', 'rem2-desc', 'oneday', '2004-06-25', '2');



INSERT INTO student.User (user_id,user_name,user_added_date,user_password,user_mobile) VALUES (1,'Pradeep','1991-12-12','svns',8904128500);
INSERT INTO student.User (user_id,user_name,user_added_date,user_password,user_mobile) VALUES (2,'Sai','1991-12-12','sure',8217862238);


INSERT INTO student.UserNote ( user_id, note_id) VALUES ( '1', '1');
INSERT INTO student.UserNote (user_id, note_id) VALUES ( '2', '2');


INSERT INTO student.Notereminder ( note_id, reminder_id) VALUES ( '1', '101');
INSERT INTO student.Notereminder ( note_id, reminder_id) VALUES ( '2', '201');
INSERT INTO student.Notereminder ( note_id, reminder_id) VALUES ( '3', '301');

INSERT INTO student.NoteCategory ( note_id, category_id) VALUES ( '1', '101');
INSERT INTO student.NoteCategory ( note_id, category_id) VALUES ( '2', '201');
INSERT INTO student.NoteCategory ( note_id, category_id) VALUES ( '3', '301');









-------------------------------------------------------------------------------------------------------------
--Fetch the row from User table based on Id and Password.
select *from student.User where user_id=1 and user_password="root123";

--Fetch all the rows from Note table based on the field note_creation_date.
select * from student.Note where note_creation_date = '2021-03-21';

--Fetch all the Categories created after the particular Date.
select * from student.Category where category_creation_date >'2021-03-21';


--Fetch all the Note ID from UserNote table for a given User.
select student.note_id from Usernote where user_id=2;

--Write Update query to modify particular Note for the given note Id.
update student.Note set note_title="Note1",note_content="Note1-content" ,note_status="completed" where note_id=2;

--Fetch all the Notes from the Note table by a particular User.
select * from student.User u INNER JOIN UserNote usernote on u.user_id and usernote.user_id where u.user_id=1;

--Fetch all the Notes from the Note table for a particular Category.
SELECT * FROM student.Note INNER JOIN NoteCategory ON Note.note_id=NoteCategory.note_id where NoteCategory.category_id=6;

--Fetch all the reminder details for a given note id.
select * from student.Reminder INNER JOIN Notereminder nr on r.reminder_id=nr.reminder_id where r.reminder_id=1;

--Fetch the reminder details for a given reminder id.
select * from student.Remainder where reminder_id=2;

----------------------------------------------------------

Write a query to create a new Note from particular User (Use Note and UserNote tables - insert statement).
insert into student.Note(note_title,note_content,note_status,note_creation_date) values( "Note5", "note5-content", "Progress", "2021-03-20");
INSERT INTO student.User (user_id,user_name,user_added_date,user_password,user_mobile) VALUES (8,'SRI','2021-03-12','EEEE',8017862238);


--Write a query to create a new Note from particular User to particular Category(Use Note and NoteCategory tables - insert statement)
insert into student.Note(note_title,note_content,note_status,note_creation_date) values( "Note5", "note5-content", "Progress", "2021-03-20");
insert into student.NoteCategory(note_id, category_id) values(last_insert_id(), 5);

--Write a query to set a reminder for a particular note (Use Reminder and NoteReminder tables - insert statement)
insert into student.Reminder (reminder_name,reminder_descr,reminder_type,reminder_creation_date,reminder_creator) values("rem5", "rem5-desc", "1hr", "2021-06-20", 1);
insert into student.Notereminder (note_id,reminder_id) values(2,last_insert_id());

--Write a query to delete particular Note added by a User(Note and UserNote tables - delete statement)
delete from student.UserNote where user_id=2 AND note_id=2;
delete from student.Notereminder where note_id=2;
delete from student.NoteCategory where note_id=2;
delete from student.Note where note_id=2;


--Write a query to delete particular Note from particular Category(Note and NoteCategory tables - delete statement)
delete from student.NoteCategory where category_id=5 AND note_id=2;
delete from student.Notereminder where note_id=2;
delete from student.UserNote where note_id=2;
delete from student.Note where note_id=2;


--Create a trigger to delete all matching records from UserNote, NoteReminder and NoteCategory table when :
--1. A particular note is deleted from Note table (all the matching records from UserNote, NoteReminder and NoteCategory should be removed automatically)
DELIMITER //
create trigger del_note before delete on Note FOR EACH ROW Begin delete from UserNote where note_id=old.note_id; delete from Notereminder where note_id=old.note_id; delete from NoteCategory where note_id=old.note_id; END; //
DELIMITER ;

delete from Note where note_id=6;


--2. A particular user is deleted from User table (all the matching notes should be removed automatically)
DELIMITER //
create trigger del_user before delete on User FOR EACH ROW Begin delete from UserNote where note_id=old.user_id; delete from NoteReminder where note_id=old.user_id; delete from Category where category_creator=old.user_id; delete from NoteCategory where note_id=old.user_id; END; //
DELIMITER ;

delete from User where user_id=1;