Get all employees data who are older than ‘Jones’ in company. (Hint: Older employees are those whom hire date is less than others) Get sum of salaries of all ‘Boston’ employees. Get all employees who take grade 1 or grade 3 salary.
After setting up all relations, you need to apply following queries to get data from given relations: (Write queries and also paste screenshots in answer file)
Get all employees data who are older than ‘Jones’ in company. (Hint: Older employees are those whom hire date is less than others)
Get sum of salaries of all ‘Boston’ employees.
Get all employees who take grade 1 or grade 3 salary.
Using oracle APEX we are creating a table now -
CREATE TABLE DEPT(
DEPTNO NUMBER(10) NOT NULL,
DNAME VARCHAR(50) NOT NULL,
LOC VARCHAR2(30),
CONSTRAINT DEPT_PK PRIMARY KEY
(DEPTNO)
);
We use NOT NULL constraint to make the cell occupied with something related to column line
for example; we can see a lot of applications where we see (field is mandatory)
NOW EMP TABLE-
CREATE TABLE EMP(
EMPNO NUMBER(10)NOT NULL,
ENAME VARCHAR2(50)NOT NULL,
JOB VARCHAR2(50),
MGR VARCHAR2(50),
HIREDATE DATE,
SAL NUMBER(6),
COMM VARCHAR2(50)
DEPTNO NUMBER(5)NOT NULL,
CONSTRAINT EMP_FK FOREIGN KEY(
DEPTNO) REFERENCES DEPT (DEPTNO), (maintains a connection to EMP table DEPTNO)
CONSTRAINT EMP_PK PRIMARY KEY
(EMPNO)
);
- FOREIGN KEY IS used to establish the connection between any two tables
- PRIMARY KEY is used to uniquely identify records from the table
CREATE TABLE SAL GRADE (
GRADE NUMBER(10) NOT NULL,
LOSAL NUMBER(10),
HISAL NUMBER(10),
CONSTRAINT SALGRADE_PK PRIMARY KEY
(GRADE)
);
Step by step
Solved in 4 steps with 4 images
