Show accumulated high salary and low salary from SALGRADE table. Display the highest, lowest, sum, average salary of all employees in a single query. Name the column accordingly. Write a query to display the difference between highest and lowest salary. Write a query that displays the sum of all salaries of each job.
CREATING TABLES USING oracle APEX
CREATE TABLE DEPT (
DEPTNO NUMBER(10) NOT NULL,
DNAME VARCHAR2(50) NOT NULL,
LOC VARCHAR2(50),
CONSTRAINT DEPT_PK PRIMARY KEY (DEPTNO)
);
- NOTNULL constraint is used it represents cell should not be empty
- Here NUMBER AND VARCHAR are data types
Now create 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),
DEPTNO NUMBER(10) NOT NULL,
CONSTRAINT EMP_PK PRIMARY KEY
(EMPNO),
CONSTRAINT EMP_FK FOREIGN KEY (
DEPTNO) REFERENCES DEPT (DEPTNO)
);
- oracle consists of standard DATE formats we have to use that
-
PRIMARY KEY is used to uniquely identify records from the table
-
FOREIGN KEY is used to establish the connection between any two tables
- Here DEPT is a resultant table of EMP table
Now SALGRADE TABLE
CREATE TABLE SALGRADE (
GRADE NUMBER (10) NOT NULL,
LOSAL NUMBER(6),
HISAL NUMBER(6),
CONSTRAINT SALGRADE_PK PRIMARY KEY (GRADE)
);
- Combination of unique and not-null constraints we get primary key constraint
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)
Show accumulated high salary and low salary from SALGRADE table.
Display the highest, lowest, sum, average salary of all employees in a single query. Name the column accordingly.
Write a query to display the difference between highest and lowest salary.
Write a query that displays the sum of all salaries of each job.
Step by step
Solved in 5 steps with 4 images
