--Fetch the row from User table : Notes

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

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

--Write Update query to modify particular Note for the given note Id.
update 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 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 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 Reminder r 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 Remainder where reminder_id=2;

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

--Write a query to delete particular Note from particular Category(Note and NoteCategory tables - delete statement)
insert into Note (`note_title`,`note_content`,`note_status`,`note_creation_date`) values("Note5", "Note5-content", 'active', "2010-06-10");
insert into UserNote (user_id, note_id) values(1, last_insert_id());

--Write a query to create a new Note from particular User to particular Category(Use Note and NoteCategory tables - insert statement)
insert into Note(`note_title`,`note_content`,`note_status`,`note_creation_date`) values( "Note5", "note5-content", "Progress", "2000-06-20");
insert into 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 Reminder (`reminder_name`,`reminder_descr`,`reminder_type`,`reminder_creation_date`,`reminder_creator`) values("rem5", "rem5-desc", "1hr", "2000-06-20", 1);
insert into 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 UserNote where user_id=2 AND note_id=2;
delete from Notereminder where note_id=2;
delete from NoteCategory where note_id=2;
delete from Note where note_id=2;

--Write a query to delete particular Note from particular Category(Note and NoteCategory tables - delete statement)
delete from NoteCategory where category_id=5 AND note_id=2;
delete from Notereminder where note_id=2;
delete from UserNote where note_id=2;
delete from 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;

--DML commands

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

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

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

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

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

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

create database keepnote;

CREATE TABLE `keepnote`.`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 `keepnote`.`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 `keepnote`.`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 `keepnote`.`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 `keepnote`.`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`)
);

CREATE TABLE `keepnote`.`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 `keepnote`.`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`)
);

what is notes.io

Notes.io is a web-based application for taking notes. You can take your notes and share with others people. If you like taking long notes, notes.io is designed for you. To date, over 8,000,000,000 notes created and continuing...

With notes.io;

* You can take a note from anywhere and any device with internet connection.
* You can share the notes in social platforms (YouTube, Facebook, Twitter, instagram etc.).
* You can quickly share your contents without website, blog and e-mail.
* You don't need to create any Account to share a note. As you wish you can use quick, easy and best shortened notes with sms, websites, e-mail, or messaging services (WhatsApp, iMessage, Telegram, Signal).
* Notes.io has fabulous infrastructure design for a short link and allows you to share the note as an easy and understandable link.

Fast: Notes.io is built for speed and performance. You can take a notes quickly and browse your archive.

Easy: Notes.io doesn’t require installation. Just write and share note!

Short: Notes.io’s url just 8 character. You’ll get shorten link of your note when you want to share. (Ex: notes.io/q )

Free: Notes.io works for 12 years and has been free since the day it was started.

You immediately create your first note and start sharing with the ones you wish. If you want to contact us, you can use the following communication channels;

Email: [email protected]

Twitter: http://twitter.com/notesio

Instagram: http://instagram.com/notes.io

Facebook: http://facebook.com/notesio

Regards;
Notes.io Team

	Shortened Note Link

Long File

For written notes was greater than 18KB Unable to shorten.

To be smaller than 18KB, please organize your notes, or sign in.