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

    39

    This website collects cookies to deliver better user experience

    DBMS Employee and Department