Home » SQL & PL/SQL » SQL & PL/SQL » QUERY TO DISPLAY EDUCATION (11g )
QUERY TO DISPLAY EDUCATION [message #684701] Wed, 28 July 2021 03:04 Go to next message
glmjoy
Messages: 182
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68053
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 #684703 is a reply to message #684702] Wed, 28 July 2021 04:08 Go to previous messageGo to next message
glmjoy
Messages: 182
Registered: September 2011
Location: KR
Senior Member
THANKS BUT *Plus internal error state 2091, context 0:0:0 Unsafe to proceed
Re: QUERY TO DISPLAY EDUCATION [message #684704 is a reply to message #684703] Wed, 28 July 2021 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68053
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sure you didn't execute EXACTLY what I did.
Copy and paste your SQL*Plus session, don't forget to post the result of V$VERSION AND to format your post.

Re: QUERY TO DISPLAY EDUCATION [message #684705 is a reply to message #684704] Wed, 28 July 2021 06:03 Go to previous messageGo to next message
glmjoy
Messages: 182
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 #684706 is a reply to message #684705] Wed, 28 July 2021 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68053
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post what I asked in the format I asked.

Re: QUERY TO DISPLAY EDUCATION [message #684707 is a reply to message #684706] Wed, 28 July 2021 06:38 Go to previous messageGo to next message
glmjoy
Messages: 182
Registered: September 2011
Location: KR
Senior Member
Thanks for the reply
SQL> CREATE TABLE HR_EDUCATION
2 (
3 ED_EMP_CODE VARCHAR2(4),
4 ED_EDUCATION VARCHAR2(4),
5 ACADEMIC_YEAR VARCHAR2(6)
6 )
7 /

Table created.
Re: QUERY TO DISPLAY EDUCATION [message #684708 is a reply to message #684707] Wed, 28 July 2021 06:39 Go to previous messageGo to next message
glmjoy
Messages: 182
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3106
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 #684710 is a reply to message #684709] Wed, 28 July 2021 08:03 Go to previous messageGo to next message
glmjoy
Messages: 182
Registered: September 2011
Location: KR
Senior Member
Ok Thanks it works
Re: QUERY TO DISPLAY EDUCATION [message #684711 is a reply to message #684701] Wed, 28 July 2021 09:14 Go to previous message
EdStevens
Messages: 1351
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.
Previous Topic: Execution Plan of query
Next Topic: Question about databses
Goto Forum:
  


Current Time: Tue Dec 07 11:37:59 CST 2021