USE hanbitDB;
DROP TABLE student;
CREATE TABLE student (

  s_id  CHAR(10) ,
  s_name CHAR(5),
  s_addr VARCHAR(100) ,
  s_phone CHAR(11) ,
  s_dept VARCHAR(10) ,
  s_eYear SMALLINT ,
  s_subj  CHAR(10) , 
  s_sea   CHAR(1) , -- P, S, F, W
  s_score CHAR(2), 
  s_sukcha SMALLINT

);

INSERT INTO student VALUES('2020001', '박서준', '서울시 영등포구 화곡동 123번지',

   '01012341234', '컴퓨터', 2020, '데이터베이스', 'F', 'B+', 7);

INSERT INTO student VALUES('2020001', '박서준', '서울시 영등포구 화곡동 123번지',

   '01012341234', '컴퓨터', 2020, 'JAVA', 'F', 'A+', 1);

INSERT INTO student VALUES('2020001', '박서준', '서울시 영등포구 화곡동 123번지',

   '01012341234', '컴퓨터', 2020, 'PYTHON', 'W', 'C0', 30);

SELECT * FROM student;
DROP TABLE student;
CREATE TABLE student (

  s_id  CHAR(10) PRIMARY KEY ,
  s_name CHAR(5),
  s_addr VARCHAR(100) ,
  s_phone CHAR(11) ,
  s_dept VARCHAR(10) ,
  s_eYear SMALLINT );

DROP TABLE subj;
CREATE TABLE subj(

  s_num  INT  auto_increment primary KEY,

  st_id CHAR(10) ,
  s_subj  CHAR(10) , 
  s_sea   CHAR(1) , -- P, S, F, W
  s_score CHAR(2), 
  s_sukcha SMALLINT ,
  FOREIGN KEY(st_id) REFERENCES student(s_id));

INSERT INTO student VALUES('2020001', '박서준', '서울시 영등포구 화곡동 123번지',

   '01012341234', '컴퓨터', 2020);

INSERT INTO subj VALUES(NULL, '2020001', '데이터베이스', 'F', 'B+', 7);
INSERT INTO subj VALUES(NULL, '2020001','JAVA', 'F', 'A+', 1);
INSERT INTO subj VALUES(NULL, '2020001','PYTHON', 'W', 'C0', 30);
SELECT * FROM student;
SELECT * FROM subj;
SELECT * 
FROM student
JOIN subj 
ON student.s_id = subj.st_id
WHERE student.s_id = '2020001';