DBMS Employee and Department

Solution

Creating department table.

CREATE TABLE DEPARTMENT (
    DEPTNO NUMBER(10)
    CONSTRAINT DEPARTMENT_DEPTNO_PK PRIMARY KEY,
    DEPTNAME VARCHAR2(20)
    CONSTRAINT DEPARTMENT_DEPTNAME_UN UNIQUE,
    LOCATION CHAR(15)
    CONSTRAINT DEPARTMENT_LOCATION_NN NOT NULL
);

Creating employee table.

CREATE TABLE EMPLOYEE (
    EMPNO NUMBER(10)
    CONSTRAINT EMPLOYEE_EMPNO_PK PRIMARY KEY,
    EMPNAME VARCHAR2(20)
    CONSTRAINT EMPLOYEE_EMPNAME_UN UNIQUE,
    DEPTNO NUMBER(10)
    CONSTRAINT EMPLOYEE_DEPTNO_BR REFERENCES DEPARTMENT(DEPTNO),
    MANAGER NUMBER(10)
    CONSTRAINT EMPLOYEE_MANAGER_SR REFERENCES EMPLOYEE(EMPNO)
);

Inserting values into department table

INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, LOCATION)
VALUES (&DEPTNO, &DEPTNAME, &LOCATION);

press "/" for inserting second record and so on.

Inserting values into employee table

INSERT INTO EMPLOYEE(EMPNO, EMPNAME, DEPTNO, MANAGER)
VALUES (&EMPNO, &EMPNAME, &DEPTNO, &MANAGER);

press "/" for inserting second record and so on.

• Program is completed here, we have created both the tables and relationship between them.
• To show the department table sql SELECT * FROM DEPARTMENT
• To show the employee table sql SELECT * FROM EMPLOYEE
• To show both the table

SELECT * FROM DEPARTMENT, EMPLOYEE WHERE DEPARTMENT.DEPTNO = EMPLOYEE.DEPTNO

First Question
DBMS Student Table

Closing here 👋👋👋

This is Shareef.
My recent project yourounotes
My Portfolio
Twitter ShareefBhai99
Linkedin
My other Blogs