Use the value of an attribute from another table as a default value of an attribute from another table in MYSQL

I wanted to use the value on student_lastname in table tbl_student as a default value of sis_password in table tbl_sis_account but I am out of idea on how to do it. I tried putting “Select query” after the “Default” but it doesn’nt work, anyway here’s the sql:

DROP TABLE IF EXISTS tbl_sis_account;
CREATE TABLE `tbl_sis_account`(
     sis_account_id INT(15) NOT NULL AUTO_INCREMENT,
     sis_username INT(15) NOT NULL,
     sis_password VARCHAR(8) DEFAULT '====>Value of attribute student_lastname<====',
     PRIMARY KEY(`sis_account_id`),
     CONSTRAINT `sis_username_student_fk` FOREIGN KEY (`sis_username`) REFERENCES `tbl_student` 
     (`student_id`) ON UPDATE CASCADE
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
SELECT * FROM tbl_sis_account;

DROP TABLE IF EXISTS tbl_student;
CREATE TABLE `tbl_student` (
    `student_id` INTEGER(15) NOT NULL AUTO_INCREMENT,
    `student_firstname` VARCHAR(50) NOT NULL,
    `student_midname` VARCHAR(50) NOT NULL,
    `student_lastname` VARCHAR(50) NOT NULL,
    PRIMARY KEY(`student_id`)
)ENGINE=INNODB AUTO_INCREMENT=20201 DEFAULT CHARSET=utf8mb4;
SELECT * FROM tbl_student;

Answer

I’ve figured the solution for this problem, for a while now. Forgot to post the answer though, coz I am no longer using this method. But here’s what I did.

On the tbl_student I created a “After Insert trigger“

 BEGIN 
    INSERT INTO tbl_sis_account (student_id,sis_password) values (new.student_id, concat(new.student,new.student_lastname));
    END

so the inserted result on tbl_sis_account is

student_id  |  sis_password
20200001    |  202000001Doe