DEV Community

Mohammed Nadeem Shareef
Mohammed Nadeem Shareef

Posted on • Updated on

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
);

Enter fullscreen mode Exit fullscreen mode

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)
);

Enter fullscreen mode Exit fullscreen mode

Inserting values into department table


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

Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

First Question
DBMS Student Table

Closing here 👋👋👋

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

Discussion (0)