Notes

Skip to content
Why GitHub?
Team
Enterprise
Explore
Marketplace
Pricing
Search

Sign in
Sign up
pradeepsure
/
KeepNote-RDBMS-Assignment-boilerplate
03
Code
Issues
Pull requests
Actions
Projects
Security
Insights
KeepNote-RDBMS-Assignment-boilerplate/solution.txt

VenkataNagaSaiPradeepKumar.Sure solution
Latest commit 952b662 on Feb 7, 2019
History
0 contributors
193 lines (131 sloc) 8.73 KB

Query
*******

--Fetch the row from User table based on Id and Password.
select *from 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 Note where note_creation_date = '2019-11-21';

--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`)
);












© 2021 GitHub, Inc.
Terms
Privacy
Security
Status
Docs
Contact GitHub
Pricing
API
Training
Blog
About