EMP: NUMBER (4) EMPNO VARCHAR2 (10) ΕΝΑΜΕ VARCHAR2 (9) JOB NUMBER (4) MGR HIREDATE DATE NUMBER (7, 2) SAL NUMBER (7, 2) СOMM NUMBER (2) DE PTNΟ DEPT: NUMBER (2) DE PTNΟ VARCHAR2 (14) DNAME VARCHAR2 (13) LOC
Let's say we have the attached table :
3. Find the years that company hired more than 5 employees
NOTE: - THE NEW SCHEMA IS ASSUMED WITH FOURTEEN RECORDS AS IT WAS NOT PROVIDED.
SELECT is used to display the records from the table.
The function YEAR() returns an integer value representing the specified date year.
The GROUP BY statement groups rows in overview rows with the same values.
The HAVING Clause allows to define conditions that will filter the outcomes of the group. In a query, the HAVING clause shall follow the GROUP BY clause.
THE COLUMNS AND VALUES ARE ASSSUMED AS THEY WEREN’T GIVEN IN THE QUESTION.
CREATE TABLE emp (
empno NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);
INSERT INTO emp VALUES (69,'NEIL','CLERK',02,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (99,'COOPER','SALESMAN',98,'20-FEB-81',1600,300,30);
INSERT INTO emp VALUES (21,'LIDOS','SALESMAN',98,'22-FEB-81',1250,500,30);
INSERT INTO emp VALUES (66,'JONAS','MANAGER',39,'02-APR-80',2975,NULL,20);
INSERT INTO emp VALUES (54,'MARTIS','SALESMAN',98,'28-SEP-80',1250,1400,30);
INSERT INTO emp VALUES (98,'BLUKE','MANAGER',39,'01-MAY-81',2850,NULL,30);
INSERT INTO emp VALUES (82,'CLAKS','MANAGER',39,'09-JUN-81',2450,NULL,10);
INSERT INTO emp VALUES (88,'STUDS','ANALYST',66,'19-APR-80',3000,NULL,20);
INSERT INTO emp VALUES (39,'KISHU','PRESIDENT',NULL,'17-NOV-80',5000,NULL,10);
INSERT INTO emp VALUES (44,'BARNER','SALESMAN',98,'08-SEP-81',1500,0,30);
INSERT INTO emp VALUES (76,'ADERNER','CLERK',88,'23-MAY-80',1100,NULL,20);
INSERT INTO emp VALUES (01,'JONES','CLERK',98,'03-DEC-81',950,NULL,30);
INSERT INTO emp VALUES (02,'FILLER','ANALYST',66,'03-DEC-81',3000,NULL,20);
INSERT INTO emp VALUES (34,'MITHUN','CLERK',82,'23-JAN-82',1300,NULL,10);
Step by step
Solved in 3 steps with 2 images
