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   Step 2 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   Step 3 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) i. Display the employee id, name, salary, commissioned salary (incremented salary + commission) with label "Commissioned_Salary". ii. Write a query that displays the sum of all salaries of each job. iii. Get the average of clerks salaries.

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)
i. Display the employee id, name, salary, commissioned salary (incremented salary +
commission) with label "Commissioned_Salary".
ii. Write a query that displays the sum of all salaries of each job.
iii. Get the average of clerks salaries.