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
My other Blogs
Top comments (0)