🔒 Closed Patulong po sa SQL DataBase

[Carl Angelo Lorete]

Paano po pagsamahin yung apat na table? Need ko po yung result na magkakasama po sila at nasa tama po yung information ng bawat employee. Ito
Ito po yung code ko. Wala pong error dyan. Need ko lang po mapagsama yung 4 tables.

create database PDM;



create table Employee(emp_num INT(11) PRIMARY KEY,
emp_name VARCHAR(30) NOT NULL,
job_class VARCHAR(30) NOT NULL,
FOREIGN KEY(job_class) REFERENCES job(job_class));

INSERT INTO Employee(emp_num, emp_name, job_class)
VALUES

(101 , "G. NEWS", "DB. DESIGNER"),
(102 , "D. SENIOR", "S. ANALYST"),
(103 , "E. ARBOUGH", "E. ENGINEER"),
(104 , "A. RAMORAS", "S. ANALYST"),
(105 , "K. JOHNSON", "DB. DESIGNER"),
(106 , "W. SMITHFIELD", "PROGRAMMER"),
(107 , "M. ALONZO", "PROGRAMMER"),
(108 , "R. WASHINGTON", "S. ANALYST"),
(111 , "G. WABASH", "C. SUPPORT"),
(112 , "D. SMITHSON", "DSS ANALYST"),
(113 , "D. JOENBROOD", "A. DESIGNER"),
(114 , "A. JONES", "A. DESIGNER"),
(115 , "T. BAWANGI", "S. ANALYST"),
(118 , "J. FROMMER", "G. SUPPORT")
;




create table Assignment (proj_num INT(11) NOT NULL,
emp_num INT(111),
assign_hours DECIMAL(10,5) NOT NULL,
FOREIGN KEY(emp_num) REFERENCES employee(emp_num),
FOREIGN KEY(proj_num) REFERENCES project(proj_num))
;

INSERT INTO Assignment(proj_num, emp_num, assign_hours)
VALUES
(15 , 103 , 23.8),
(15 , 101 , 19.4),
(15 , 105 , 35.7),
(15 , 106 , 12.6),
(15 , 102 , 23.8),

(18 , 114 , 24.6),
(18 , 118 , 45.3),
(18 , 104 , 32.4),
(18 , 112 , 44.0),

(22 , 105 , 64.7),
(22 , 104 , 48.4),
(22 , 113 , 23.6),
(22 , 111 , 22.0),
(22 , 106 , 12.8),

(25 , 107 , 24.6),
(25 , 115 , 45.8),
(25 , 101 , 56.3),
(25 , 114 , 33.1),
(25 , 108 , 23.6),
(25 , 118 , 30.5),
(25 , 112 , 41.4)
;






create table Job (job_class VARCHAR(25) PRIMARY KEY,
chg_hour FLOAT NOT NULL);


INSERT INTO Job(job_class, chg_hour)
VALUES
("A. DESIGNER", 48.10),
("C. SUPPORT", 26.87),
("DB. DESIGNER", 105.00),
("DSS ANALYST", 45.95),
("E. ENGINEER", 84.50),
("G. SUPPORT", 18.36),
("PROGRAMMER", 35.75),
("S. ANALYST", 96.75)
;


create table Project (proj_num INT(2) PRIMARY KEY,
proj_name VARCHAR(25) NOT NULL);

INSERT INTO Project(proj_num, proj_name)
VALUES
(15 , "EVERGREEN"),
(18 , "AMBERWAVE"),
(22 , "ROLLING TIDE"),
(25 , "STARFLIGHT")
;



Kaso ganito po lumalabas kapag nagSelect * From table1, table2, ..... Tapos sobrang haba pa. Sana maging isang table lang po sya.

 

[dee-u]

Ganito ba?

SELECT
    `employee`.`emp_num`
    , `employee`.`emp_name`
    , `employee`.`job_class`
    , `assignment`.`assign_hours`
    , `project`.`proj_name`
    , `job`.`chg_hour`
FROM
    `employee`
    INNER JOIN `assignment`
        ON (`employee`.`emp_num` = `assignment`.`emp_num`)
    INNER JOIN `project`
        ON (`project`.`proj_num` = `assignment`.`proj_num`)
    INNER JOIN `job`
        ON (`job`.`job_class` = `employee`.`job_class`);