Home » SQL & PL/SQL » SQL & PL/SQL » QUERY TO DISPLAY EDUCATION (11g )
QUERY TO DISPLAY EDUCATION [message #684701] |
Wed, 28 July 2021 03:04 |
|
glmjoy
Messages: 187 Registered: September 2011 Location: KR
|
Senior Member |
|
|
create table HR_EDUCATION
EMP_CODE VARCHAR2(4),EDUCATION_CODE VARCHAR2(4),
ACADEMIC_YEAR VARCHAR2(6))
(EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR)
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0001','0001','1990');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0001','0002','1992');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0002','0001','1995');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0003','0001','1990');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0003','0002','1992');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0003','0003','1996');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0003','0004','1998');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0004','0001','1998');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0004','0002','2000');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0004','0003','2004');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0005','0001','1998');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0005','0002','2000');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0006','0001','1998');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0006','0002','2000');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0007','0001','1990');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0007','0002','1992');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0007','0003','1996');
insert into HR_EDUCATION (EMP_CODE ,EDUCATION_CODE,ACADEMIC_YEAR) VALUES ('0007','0004','1998');
----------------------------------------------------
QUALIFICATIONS
'0001' HIGH SCHOOL
'0002' COLLEGE
'0003' BACHLORS
'0004' MASTERS
I HAVE 7 EMPLOYEES I WANT COUNTING ACADEMIC RECORDS FOR EXAMPLE EMPLOYEE CODE '0001' HAS QUALIFICATION TILL COLLEGE
EMPLOYEE '0002' HAS ONLY HIGH SCHOOL, EMPLOYEE '0003' HAS MASTERS DEGREE SO ON
I WANT COUNTING
HIGH SCHOOL COLLEGE BACHLOR MASTER
1 3 1 2
|
|
|
Re: QUERY TO DISPLAY EDUCATION [message #684702 is a reply to message #684701] |
Wed, 28 July 2021 03:38 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Please format your post as explained in How to use [code] tags and make your code easier to read, and align the column in result.
And test your test case before posting it:
SQL> create table HR_EDUCATION
2 EMP_CODE VARCHAR2(4),EDUCATION_CODE VARCHAR2(4),
3 ACADEMIC_YEAR VARCHAR2(6));
EMP_CODE VARCHAR2(4),EDUCATION_CODE VARCHAR2(4),
*
ERROR at line 2:
ORA-00922: missing or invalid option
One way to do it:
SQL> with
2 grads as (
3 select emp_code, max(education_code) grad
4 from HR_EDUCATION
5 group by emp_code
6 )
7 select count(decode(grad,'0001',grad)) "HIGH SCHOOL",
8 count(decode(grad,'0002',grad)) "COLLEGE",
9 count(decode(grad,'0003',grad)) "BACHLOR",
10 count(decode(grad,'0004',grad)) "MASTER "
11 from grads
12 /
HIGH SCHOOL COLLEGE BACHLOR MASTER
----------- ---------- ---------- ----------
1 3 1 2
|
|
|
|
|
Re: QUERY TO DISPLAY EDUCATION [message #684705 is a reply to message #684704] |
Wed, 28 July 2021 06:03 |
|
glmjoy
Messages: 187 Registered: September 2011 Location: KR
|
Senior Member |
|
|
I am using 11g
SQL>
1 with
2 grads as (
3 select ED_EMP_CODE, max(ED_EDUCATION) grad
4 from HR_EMP_EDUCATION
5 group by ED_EMP_CODE
6 )
7 select count(decode(grad,'0000',grad)) "HIGH SCHOOL",
8 count(decode(grad,'0001',grad)) "COLLEGE",
9 count(decode(grad,'0002',grad)) "BACHLOR",
10 count(decode(grad,'0003',grad)) "MASTER "
11* from grads
12 /
SQL*Plus internal error state 2091, context 0:0:0
Unsafe to proceed
SQL>
|
|
|
|
|
Re: QUERY TO DISPLAY EDUCATION [message #684708 is a reply to message #684707] |
Wed, 28 July 2021 06:39 |
|
glmjoy
Messages: 187 Registered: September 2011 Location: KR
|
Senior Member |
|
|
SQL>
1 with
2 grads as (
3 select ED_EMP_CODE, max(ED_EDUCATION) grad
4 from HR_EMP_EDUCATION
5 group by ED_EMP_CODE
6 )
7 select count(decode(grad,'0000',grad)) "HIGH SCHOOL",
8 count(decode(grad,'0001',grad)) "COLLEGE",
9 count(decode(grad,'0002',grad)) "BACHLOR",
10 count(decode(grad,'0003',grad)) "MASTER "
11* from grads
12 /
SQL*Plus internal error state 2091, context 0:0:0
Unsafe to proceed
SQL>
|
|
|
Re: QUERY TO DISPLAY EDUCATION [message #684709 is a reply to message #684701] |
Wed, 28 July 2021 07:03 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
SELECT SUM(CASE MAX(EDUCATION_CODE) WHEN '0001' THEN 1 END) "HIGH SCHOOL",
SUM(CASE MAX(EDUCATION_CODE) WHEN '0002' THEN 1 END) "COLLEGE",
SUM(CASE MAX(EDUCATION_CODE) WHEN '0003' THEN 1 END) "BACHLOR",
SUM(CASE MAX(EDUCATION_CODE) WHEN '0004' THEN 1 END) "MASTER"
FROM HR_EDUCATION
GROUP BY EMP_CODE
/
HIGH SCHOOL COLLEGE BACHLOR MASTER
----------- ---------- ---------- ----------
1 3 1 2
SQL>
SY.
|
|
|
|
Re: QUERY TO DISPLAY EDUCATION [message #684711 is a reply to message #684701] |
Wed, 28 July 2021 09:14 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
As a side observation, you have
ACADEMIC_YEAR VARCHAR2(6))
So why are you declarining it to be a max size of 6 when your data obviously should always be exactly 4 characters.
Actually, since this is a date, it really shouldn't be a character string or a number, but a DATE. Yes, the DATE type also includes month, day, hour, minute, and second, which you don't care about, but you can always truncate it to just the year.
But even if I accept the rational for not making it a DATE, I'd expect the defined size to be congruent with the actual data.
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:56:52 CDT 2024
|