By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 |
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | 0 |
create table workon (empid number, pid number);
create table employee (empid number, name varchar2(100));
begin
insert into workon (empid, pid) values (30, 1);
insert into workon (empid, pid) values (30, 2);
insert into workon (empid, pid) values (10, 1);
insert into workon (empid, pid) values (20, 1);
insert into workon (empid, pid) values (20, 2);
insert into workon (empid, pid) values (40, 1);
insert into workon (empid, pid) values (40, 2);
insert into workon (empid, pid) values (40, 3);
end;
/
1 rows affected
begin
insert into employee (empid, name) values (10, 'Anne');
insert into employee (empid, name) values (20, 'Tim');
insert into employee (empid, name) values (30, 'Grace');
insert into employee (empid, name) values (40, 'Harold');
end;
/
1 rows affected
SELECT
employee."NAME",
T1."# OF PROJECTS",
(
SELECT COUNT(pid)
FROM workon
WHERE empid = 30
) "Grace's Project"
FROM employee,
(
SELECT empid, COUNT(pid) AS "# OF PROJECTS"
FROM workon
GROUP BY empid
ORDER BY empid
) T1
WHERE T1."# OF PROJECTS" > (SELECT COUNT(pid) FROM workon WHERE empid = 30)
AND t1.empid = employee.EMPID;
| NAME | # OF PROJECTS | Grace's Project |
|---|---|---|
| Harold | 3 | 2 |
WITH emp AS
(
SELECT empid, e.name, COUNT(*) AS projects
FROM workon w
JOIN employee e USING(empid)
GROUP BY empid, e.name
ORDER BY empid
)
, grace AS
(
SELECT * FROM emp WHERE name = 'Grace'
)
SELECT
emp.name,
emp.projects as "# OF PROJECTS",
grace.projects as "Grace's Projects"
FROM emp
CROSS JOIN grace
WHERE emp.projects > grace.projects
ORDER BY emp.projects DESC, emp.name;
| NAME | # OF PROJECTS | Grace's Projects |
|---|---|---|
| Harold | 3 | 2 |