MySQL Assignment Solution on Perapera Online Website

• 12th Aug, 2022
• 15:09 PM

create database mydb;
-- -----------------------------------------------------
-- Table `mydb`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`user` (
  `userID` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  `gender` VARCHAR(6) NULL,
  `year_of_birth` YEAR NULL,
  `native_language` VARCHAR(10) NULL,
  `birth_country` VARCHAR(25) NULL,
  `living_country` VARCHAR(45) NULL,
  `current_timezone` FLOAT NULL,
  `credits` INT NULL,
  `teacher` VARCHAR(6) NULL,
  `introduction` VARCHAR(150) NULL,
  PRIMARY KEY (`userID`));

-- -----------------------------------------------------
-- Table `mydb`.`language_abilities`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`language_abilities` (
  `user_userID` INT NOT NULL,
  `language` VARCHAR(10) NOT NULL,
  `competency` VARCHAR(15) NOT NULL,
  `status` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`user_userID`, `language`),
  CONSTRAINT `fk_language_abilities_user1`
    FOREIGN KEY (`user_userID`)
    REFERENCES `mydb`.`user` (`userID`));

-- -----------------------------------------------------
-- Table `mydb`.`communication_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`communication_type` (
  `type` VARCHAR(10) NOT NULL,
  `communication_userid` VARCHAR(45) NULL,
  `user_userID` INT NOT NULL,
  PRIMARY KEY (`type`, `user_userID`),
  CONSTRAINT `fk_communication_type_user1`
    FOREIGN KEY (`user_userID`)
    REFERENCES `mydb`.`user` (`userID`));

-- -----------------------------------------------------
-- Table `mydb`.`add_credits_transactions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`add_credits_transactions` (
  `user_userID` INT NOT NULL,
  `transactionID` INT NOT NULL,
  `date_time` DATETIME NOT NULL,
  `credit_amount` INT NOT NULL,
  `status` VARCHAR(15) NOT NULL,
  PRIMARY KEY (`user_userID`, `transactionID`),
  CONSTRAINT `fk_add_credits_transactions_user1`
    FOREIGN KEY (`user_userID`)
    REFERENCES `mydb`.`user` (`userID`));

-- -----------------------------------------------------
-- Table `mydb`.`teachers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`teachers` (
  `user_userID` INT NOT NULL,
  `t_userID` INT NOT NULL,
  unique(`t_userID`),
  `url` VARCHAR(250) NULL,
  `writeup` VARCHAR(255) NULL,
  `rates` INT NULL,
  `avg_rating` DECIMAL NULL,
  `income` INT NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`user_userID`, `t_userID`),
  CONSTRAINT `fk_teachers_user`
    FOREIGN KEY (`user_userID`)
    REFERENCES `mydb`.`user` (`userID`));

-- -----------------------------------------------------
-- Table `mydb`.`teacher_time_slots`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`teacher_time_slots` (
  `a_date_time` DATETIME NOT NULL,
  `status` VARCHAR(15) NULL,
  `teachers_t_userID` INT NOT NULL,
  `language` VARCHAR(15) NULL,
  PRIMARY KEY (`a_date_time`, `teachers_t_userID`),
  CONSTRAINT `fk_teacher_time_slots_teachers1`
    FOREIGN KEY (`teachers_t_userID`)
    REFERENCES `mydb`.`teachers` (`t_userID`));

-- -----------------------------------------------------
-- Table `mydb`.`lesson_requirement`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`lesson_requirement` (
  `lesson_ID` INT NOT NULL,
  `teachers_date_time` DATETIME NOT NULL,
  `t_userID` INT NOT NULL,
  `user_userID` INT NOT NULL,
  `status` VARCHAR(15) NOT NULL,
  PRIMARY KEY (`lesson_ID`, `teachers_date_time`, `t_userID`, `user_userID`),
CONSTRAINT `fk_lesson_requirement_teacher_time_slots1`
    FOREIGN KEY (`teachers_date_time` , `t_userID`)
    REFERENCES `mydb`.`teacher_time_slots` (`a_date_time` , `teachers_t_userID`),
  CONSTRAINT `fk_lesson_requirement_user1`
    FOREIGN KEY (`user_userID`)
    REFERENCES `mydb`.`user` (`userID`));

-- -----------------------------------------------------
-- Table `mydb`.`lesson_rating`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`lesson_rating` (
  `lesson_ID` INT NOT NULL,
  `rating` INT NOT NULL,
  `comment` VARCHAR(150) NULL,
  PRIMARY KEY (`lesson_ID`),
  CONSTRAINT `fk_lesson_rating_lesson_requirement1`
    FOREIGN KEY (`lesson_ID`)
    REFERENCES `mydb`.`lesson_requirement` (`lesson_ID`));

-- -----------------------------------------------------
-- Table `mydb`.`credit_transfer_logs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`credit_transfer_logs` (
  `date_time` DATETIME NOT NULL,
  `from_userID` INT NOT NULL,
  `to_userID` INT NOT NULL,
  `lesson_ID` INT NOT NULL,
  `amount` INT NOT NULL,
  PRIMARY KEY (`from_userID`, `to_userID`, `lesson_ID`),
  CONSTRAINT `fk_credit_transfer_logs_user1`
    FOREIGN KEY (`from_userID`)
    REFERENCES `mydb`.`user` (`userID`),
  CONSTRAINT `fk_credit_transfer_logs_lesson_requirement1`
    FOREIGN KEY (`lesson_ID`)
    REFERENCES `mydb`.`lesson_requirement` (`lesson_ID`),
  CONSTRAINT `fk_credit_transfer_logs_To_user_ID`
    FOREIGN KEY (`to_userID`)
    REFERENCES `mydb`.`user` (`userID`));

-- -----------------------------------------------------
-- Table `mydb`.`admin_verification_log`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`admin_verification_log` (
  `date_time` DATETIME NOT NULL,
  `status` VARCHAR(45) NULL,
  `user_userID` INT NOT NULL,
  PRIMARY KEY (`user_userID`),
  CONSTRAINT `fk_admin_verification_log_user1`
    FOREIGN KEY (`user_userID`)
    REFERENCES `mydb`.`user` (`userID`));

-- -----------------------------------------------------
-- Table `mydb`.`teachers_has_language`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`teachers_has_language` (
  `teachers_t_userID` INT NOT NULL,
  `teaching_language` VARCHAR(15) NOT NULL,
  PRIMARY KEY (`teachers_t_userID`, `teaching_language`),
  CONSTRAINT `fk_teachers_has_teaching_language_teachers1`
    FOREIGN KEY (`teachers_t_userID`)
    REFERENCES `mydb`.`teachers` (`t_userID`));

insert into user (userID,name,email,password,gender,year_of_birth,native_language,birth_country,living_country,credits,teacher)
values (334556,'pandit','pandit@gmail.com','xsr34n','male',1999,'French','China','India',2000,'keiyo');
insert into user (userID,name,email,password,gender,year_of_birth,native_language,birth_country,living_country,credits,teacher)
values (11223344,'HOWARD','Howardlion@gmail.com','gghg','male',1998,'Chinese','China','China',2000,'keiyo');
insert into user (userID,name,email,password,gender,year_of_birth,native_language,birth_country,living_country,credits,teacher)
values (32235556,'panther','pqr@gmail.com','srsda34n','male',1997,'French','China','India',2000,'yuki');
insert into user (userID,name,email,password,gender,year_of_birth,native_language,birth_country,living_country,credits,teacher)
values (97824556,'yee woo','sdsd@gmail.com','223sr34n','male',1999,'Chinese','India','China',2000,'yuki');
insert into user (userID,name,email,password,gender,year_of_birth,native_language,birth_country,living_country,credits,teacher)
values (71134556,'Pain','pain@gmail.com','#$%sr34n','male',1998,'French','China','India',2000,'keiyo');
insert into user (userID,name,email,password,gender,year_of_birth,native_language,birth_country,living_country,credits)
values (222156,'yuki','yuki@gmail.com','78$%sr34n','female',1992,'French','India','India',2000);
insert into user (userID,name,email,password,gender,year_of_birth,native_language,birth_country,living_country,credits)
values (7834556,'keiyo','pain@gmail.com','&*#$%sr34n','female',1991,'French','India','India',2000);

 insert into language_abilities(user_userID,language,competency,status)
 values (334556,'French','Basic','learning');
 INSERT INTO `mydb`.`language_abilities` (`user_userID`, `language`, `competency`, `status`) VALUES ('11223344', 'Chinese', 'Intermediate', 'learning');
INSERT INTO `mydb`.`language_abilities` (`user_userID`, `language`, `competency`, `status`) VALUES ('32235556', 'French', 'Advanced', 'learning');
INSERT INTO `mydb`.`language_abilities` (`user_userID`, `language`, `competency`, `status`) VALUES ('97824556', 'Chinese', 'Intermediate', 'learning');
INSERT INTO `mydb`.`language_abilities` (`user_userID`, `language`, `competency`, `status`) VALUES ('71134556', 'French', 'Basic', 'learning');

insert into communication_type(type,user_userID)
values ('skype',334556);
INSERT INTO `mydb`.`communication_type` (`type`, `user_userID`) VALUES ('line', '11223344');
INSERT INTO `mydb`.`communication_type` (`type`, `user_userID`) VALUES ('skype', '32235556');
INSERT INTO `mydb`.`communication_type` (`type`, `user_userID`) VALUES ('facetime', '97824556');
INSERT INTO `mydb`.`communication_type` (`type`, `user_userID`) VALUES ('wechat', '71134556');

insert into add_credits_transactions(user_userID,transactionID,date_time,credit_amount,status)
values (334556,11223,'2020-6-21 11:23:21',500,'Completed');
INSERT INTO `mydb`.`add_credits_transactions` (`user_userID`, `transactionID`, `date_time`, `credit_amount`, `status`) VALUES ('11223344', '25847', '2020-06-11 11:23:21', '700', 'Completed');
INSERT INTO `mydb`.`add_credits_transactions` (`user_userID`, `transactionID`, `date_time`, `credit_amount`, `status`) VALUES ('32235556', '14152', '2020-05-01 11:23:21', '455', 'Completed');
INSERT INTO `mydb`.`add_credits_transactions` (`user_userID`, `transactionID`, `date_time`, `credit_amount`, `status`) VALUES ('97824556', '77894', '2020-07-10 11:23:21', '699', 'Completed');
INSERT INTO `mydb`.`add_credits_transactions` (`user_userID`, `transactionID`, `date_time`, `credit_amount`, `status`) VALUES ('71134556', '99865', '2020-06-22 11:23:21', '458', 'Completed');

insert into teachers(user_userID,t_userID,avg_rating,income,name)
values (222156,222156,5,5000,'yuki');
insert into teachers(user_userID,t_userID,avg_rating,income,name)
values (7834556,7834556,5,6000,'keiyo');

insert into teacher_time_slots(a_date_time,status,teachers_t_userID,language)
values ('2020-7-11 11:00:00','Booked',222156,'French');
insert into teacher_time_slots(a_date_time,status,teachers_t_userID,language)
values ('2020-7-12 12:00:00','Booked',222156,'Chinese');
insert into teacher_time_slots(a_date_time,status,teachers_t_userID,language)
values ('2020-7-13 11:00:00','Booked',7834556,'French');
insert into teacher_time_slots(a_date_time,status,teachers_t_userID,language)
values ('2020-7-14 12:00:00','Booked',7834556,'Chinese');
insert into teacher_time_slots(a_date_time,status,teachers_t_userID,language)
values ('2020-7-15 13:00:00','Booked',7834556,'French');
insert into teacher_time_slots(a_date_time,status,teachers_t_userID,language)
values ('2020-5-11 11:00:00','done',7834556,'French');
insert into teacher_time_slots(a_date_time,status,teachers_t_userID,language)
values ('2020-5-12 12:00:00','done',7834556,'French');
insert into teacher_time_slots(a_date_time,status,teachers_t_userID,language)
values ('2020-6-13 11:00:00','done',7834556,'French');
insert into teacher_time_slots(a_date_time,status,teachers_t_userID,language)
values ('2020-6-14 12:00:00','done',222156,'French');
insert into teacher_time_slots(a_date_time,status,teachers_t_userID,language)
values ('2020-5-15 13:00:00','done',222156,'French');
set foreign_key_checks=0;
insert into lesson_requirement(lesson_ID,teachers_date_time,t_userID,user_userID,status)
values (91,'2020-5-11 11:00:00',222156,334556,'done');
insert into lesson_requirement(lesson_ID,teachers_date_time,t_userID,user_userID,status)
values (92,'2020-5-12 12:00:00',222156,11223344,'done');
insert into lesson_requirement(lesson_ID,teachers_date_time,t_userID,user_userID,status)
values (93,'2020-6-13 11:00:00',7834556,32235556,'done');
insert into lesson_requirement(lesson_ID,teachers_date_time,t_userID,user_userID,status)
values (94,'2020-6-14 12:00:00',7834556,97824556,'done');
insert into lesson_requirement(lesson_ID,teachers_date_time,t_userID,user_userID,status)
values (95,'2020-5-15 13:00:00',7834556,71134556,'done');
insert into lesson_requirement(lesson_ID,teachers_date_time,t_userID,user_userID,status)
values (101,'2020-7-11 11:00:00',222156,334556,'booked');
insert into lesson_requirement(lesson_ID,teachers_date_time,t_userID,user_userID,status)
values (102,'2020-7-12 12:00:00',222156,11223344,'booked');
insert into lesson_requirement(lesson_ID,teachers_date_time,t_userID,user_userID,status)
values (103,'2020-7-13 11:00:00',7834556,32235556,'booked');
insert into lesson_requirement(lesson_ID,teachers_date_time,t_userID,user_userID,status)
values (104,'2020-7-14 12:00:00',7834556,97824556,'booked');
insert into lesson_requirement(lesson_ID,teachers_date_time,t_userID,user_userID,status)
values (105,'2020-7-15 13:00:00',7834556,71134556,'booked');

insert into lesson_rating(lesson_ID,rating)
values (101,5);
insert into lesson_rating(lesson_ID,rating)
values (102,5);
insert into lesson_rating(lesson_ID,rating)
values (103,4);
insert into lesson_rating(lesson_ID,rating)
values (104,5);
insert into lesson_rating(lesson_ID,rating)
values (105,5);

insert into credit_transfer_logs(date_time,from_userID,to_userID,lesson_ID,amount)
values('2020-7-11 12:00:00',334556,222156,101,500);
insert into credit_transfer_logs(date_time,from_userID,to_userID,lesson_ID,amount)
values ('2020-7-12 13:00:00',11223344,222156,102,500);
insert into credit_transfer_logs(date_time,from_userID,to_userID,lesson_ID,amount)
values ('2020-7-13 12:00:00',32235556,7834556,103,500);
insert into credit_transfer_logs(date_time,from_userID,to_userID,lesson_ID,amount)
values ('2020-7-14 13:00:00',97824556,7834556,104,500);
insert into credit_transfer_logs(date_time,from_userID,to_userID,lesson_ID,amount)
values ('2020-7-15 14:00:00',71134556,7834556,105,500);

insert into teachers_has_language(teachers_t_userID,teaching_language)
values (222156,'French');
insert into teachers_has_language(teachers_t_userID,teaching_language)
values (222156,'Chinese');
insert into teachers_has_language(teachers_t_userID,teaching_language)
values (7834556,'French');
insert into teachers_has_language(teachers_t_userID,teaching_language)
values (7834556,'Chinese');
 
insert into admin_verification_log(date_time,status,user_userID)
values ('2020-7-10 13:00:00','Approved',334556);
insert into admin_verification_log(date_time,status,user_userID)
values ('2020-7-10 13:00:00','Approved',11223344);
insert into admin_verification_log(date_time,status,user_userID)
values ('2020-7-10 13:00:00','Approved',32235556);
insert into admin_verification_log(date_time,status,user_userID)
values ('2020-7-10 13:00:00','Approved',97824556);
insert into admin_verification_log(date_time,status,user_userID)
values ('2020-7-10 13:00:00','Approved',71134556);

commit;

select  date(a_date_time) as Date,time(a_date_time) as Time,language, name as teacher, (datediff(a_date_time,curdate()) ) as Days_To_Lesson from teachers t join teacher_time_slots ts on ts.teachers_t_userID=t.t_userID where ts.status='booked';
select la.language,birth_country,count(*) as Number_of_student from user u join language_abilities la on la.user_userId=u.userID group by birth_country,la.language order by Number_of_student desc,birth_country asc;
select name as teacher, count(*) as Total_Lesson_completed,avg_rating, income as Total_Income from teachers t join teacher_time_slots ts on ts.teachers_t_userID=t.t_userID where ts.status='booked' and avg_rating>=4.5 group by(teachers_t_userID);