Untitled

 avatar
shinta0x01
plain_text
19 days ago
2.3 kB
4
Indexable
Never
USE LOREGAS_DB;
CREATE TABLE DEPARTMENT(
    DEPTNO CHAR(3) NOT NULL PRIMARY KEY,
    DEPTNAME VARCHAR(40) NOT NULL,
    MGRNO CHAR(6),
    ADMRDEPT CHAR(3) NOT NULL,
    LOCATION CHAR(5)
);

ALTER TABLE DEPARTMENT
ADD CONSTRAINT fk_dept_mgrno FOREIGN KEY (MGRNO) REFERENCES EMPLOYEE(EMPNO);

CREATE TABLE EMPLOYEE(
    EMPNO CHAR(6) NOT NULL PRIMARY KEY,
    FIRSTNAME VARCHAR(20) NOT NULL,
    MIDINT CHAR(1),
    LASTNAME VARCHAR(15) NOT NULL,
    WORKDEPT CHAR(3),
    PHONENO CHAR(11),
    HIREDATE DATE,
    JOB CHAR(10),
    EDLEVEL SMALLINT CHECK (EDLEVEL >= 10 AND EDLEVEL <= 20),
    GENDER CHAR(1) CHECK(GENDER = 'F' OR GENDER = 'M'),
    BIRTHDATE DATE,
    SALARY DECIMAL(9,2) CHECK (SALARY > 0.00),
    BONUS DECIMAL(9,2) CHECK (BONUS > 0.00),
    COMM DECIMAL(9,2) CHECK (COMM > 0.00),
    UNIQUE(PHONENO)
);
ALTER TABLE EMPLOYEE
ADD CONSTRAINT fk_emp_workdept FOREIGN KEY (WORKDEPT) REFERENCES DEPARTMENT(DEPTNO);

CREATE TABLE EMP_ACT(
    EMPNO CHAR(6) NOT NULL,
    PROJNO CHAR(6) NOT NULL,
    ACTNO SMALLINT NOT NULL,
    EMPTIME DECIMAL(5,2),
    EMSTDATE DATE,
    EMENDATE DATE,
    PRIMARY KEY (EMPNO, PROJNO, ACTNO)
);
ALTER TABLE EMP_ACT
ADD CONSTRAINT fk_empact_empno FOREIGN KEY (EMPNO) REFERENCES EMPLOYEE(EMPNO),
ADD CONSTRAINT fk_empact_projno FOREIGN KEY (PROJNO) REFERENCES PROJECT(PROJNO);

CREATE TABLE PROJECT(
    PROJNO CHAR(6) NOT NULL PRIMARY KEY,
    PROJNAME VARCHAR(30) NOT NULL,
    DEPTNO CHAR(3) NOT NULL,
    RESPEMP CHAR(6) NOT NULL,
    PRSTAFF DECIMAL(5,2),
    PRSTDATE DATE,
    PRENDATE DATE,
    MAJPROJ CHAR(6)
);
ALTER TABLE PROJECT
ADD CONSTRAINT fk_project_deptno FOREIGN KEY (DEPTNO) REFERENCES DEPARTMENT(DEPTNO),
ADD CONSTRAINT fk_project_responsible_emp FOREIGN KEY (RESPEMP) REFERENCES EMPLOYEE(EMPNO);

-- DEPARTMENT--------

INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION)
VALUES ('B01', 'PLANNING', '000020', 'A00', '');

SELECT * FROM DEPARTMENT;

-- EMPLOYEE--------

INSERT INTO EMPLOYEE (EMPNO, FIRSTNAME, MIDINT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, GENDER, BIRTHDATE, SALARY, BONUS, COMM)
SELECT '000010', 'CHRISTINE', 'I', 'HAAS', DEPTNO, '3978', '1965-01-01', 'PRES', 18, 'F', '1933-08-14', 52750.00, 1000.00, 4220.00
FROM DEPARTMENT
WHERE DEPTNO = 'A00';

SELECT * FROM EMPLOYEE;
Leave a Comment