mysql 질문
-
게시물 수정 , 삭제는 로그인 필요
누구의 멘토링도 받지 않았지만 스스로 멘토링을 하고 있는 의사들을 모두 나열하세요. 결과 테이블에는 이름의 이니셜(대문자!) 및 성(원래 열 제목 포함)의 두 열이 있어야 합니다.
CREATE TABLE doctor (
ni_number CHAR(9) PRIMARY KEY,
fname VARCHAR(30),
lname VARCHAR(30),
date_of_birth DATE,
street VARCHAR(20),
postcode CHAR(8),
house_no CHAR(4),
city VARCHAR(30),
expertise SET('heart','knee','brain','hip','eye','nose','throat','lungs','liver','spleen','stomach','ear','shoulder','nerve','kidneys','foot'),
salary DEC(7,2),
mentored_by CHAR(9), FOREIGN KEY (mentored_by) REFERENCES doctor(ni_number)
);
INSERT INTO doctor VALUES ('DD234CD1X','Mike','Hammer','1940-06-13','Maple Street','BN29AB','12','Brighton','knee,liver',80000, NULL);
INSERT INTO doctor VALUES ('DD0094CYY','Barry','Gibbson','1961-10-30','Main Street','BN29AB','2','Hove','knee,lungs,brain',50000,'DD0094CYY');
INSERT INTO doctor VALUES ('AB2323CYA','Peter','Falk','1966-01-30','London Street','LA102W','88','Liverpool','brain',70000.50, NULL);
INSERT INTO doctor VALUES ('DD1199XYZ','John','Smith', NULL, NULL, NULL, NULL,'London','lungs,kidneys',54000,'DD234CD1X');
CREATE TABLE doctor (
ni_number CHAR(9) PRIMARY KEY,
fname VARCHAR(30),
lname VARCHAR(30),
date_of_birth DATE,
street VARCHAR(20),
postcode CHAR(8),
house_no CHAR(4),
city VARCHAR(30),
expertise SET('heart','knee','brain','hip','eye','nose','throat','lungs','liver','spleen','stomach','ear','shoulder','nerve','kidneys','foot'),
salary DEC(7,2),
mentored_by CHAR(9), FOREIGN KEY (mentored_by) REFERENCES doctor(ni_number)
);
INSERT INTO doctor VALUES ('DD234CD1X','Mike','Hammer','1940-06-13','Maple Street','BN29AB','12','Brighton','knee,liver',80000, NULL);
INSERT INTO doctor VALUES ('DD0094CYY','Barry','Gibbson','1961-10-30','Main Street','BN29AB','2','Hove','knee,lungs,brain',50000,'DD0094CYY');
INSERT INTO doctor VALUES ('AB2323CYA','Peter','Falk','1966-01-30','London Street','LA102W','88','Liverpool','brain',70000.50, NULL);
INSERT INTO doctor VALUES ('DD1199XYZ','John','Smith', NULL, NULL, NULL, NULL,'London','lungs,kidneys',54000,'DD234CD1X');
#mysql 질문 #mysql 면접 질문
