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
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)
1) President takes maximum salary in our record. Find maximum salary of employees other
than president.
2) Show employee name, job, salary and incremented salary (25% incremented salary) of
employees.
3) Get all Clerk or Salesman salaries that are below 3000 in descending order with respect
to sum of salaries.
Step by step
Solved in 5 steps with 14 images
