SOes389918

View All Public Scripts Login to Run Script

Script Name SOes389918
Visibility Unlisted - anyone with the share link can access
Description Solución a ejercicio de StackOverflow en español
Contributor jachguate
Created Wednesday September 16, 2020

Statement 1

with 
ContratosPorDiaSemana as ( 
select   to_char(emp.hire_date, 'D')  DiaSemana 
       , count(1) Contrataciones 
       , row_number() over (order by count(1) desc, to_char(emp.hire_date, 'D')) NoFila 
  from hr.employees emp 
 group by to_char(emp.hire_date, 'D') 
) 
select emp.*, cdia.DiaSemana 
  from hr.employees emp 
       inner join ContratosPorDiaSemana cdia on cdia.DiaSemana = to_char(emp.hire_date, 'D')  
 where cdia.NoFila = 1


EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID DIASEMANA
109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 9000  - 108 100 6
111 Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 FI_ACCOUNT 7700  - 108 100 6
113 Luis Popp LPOPP 515.124.4567 07-DEC-07 FI_ACCOUNT 6900  - 108 100 6
119 Karen Colmenares KCOLMENA 515.127.4566 10-AUG-07 PU_CLERK 2500  - 114 30 6
124 Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-07 ST_MAN 5800  - 100 50 6
141 Trenna Rajs TRAJS 650.121.8009 17-OCT-03 ST_CLERK 3500  - 124 50 6
145 John Russell JRUSSEL 011.44.1344.429268 01-OCT-04 SA_MAN 14000 .4 100 80 6
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-NOV-07 SA_REP 7000 .15 145 80 6
156 Janette King JKING 011.44.1345.429268 30-JAN-04 SA_REP 10000 .35 146 80 6
161 Sarath Sewall SSEWALL 011.44.1345.529268 03-NOV-06 SA_REP 7000 .25 146 80 6
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-NOV-05 SA_REP 10500 .25 147 80 6
168 Lisa Ozer LOZER 011.44.1343.929268 11-MAR-05 SA_REP 11500 .25 148 80 6
171 William Smith WSMITH 011.44.1343.629268 23-FEB-07 SA_REP 7400 .15 148 80 6
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-MAR-06 SA_REP 8600 .2 149 80 6
179 Charles Johnson CJOHNSON 011.44.1644.429262 04-JAN-08 SA_REP 6200 .1 149 80 6
203 Susan Mavris SMAVRIS 515.123.7777 07-JUN-02 HR_REP 6500  - 101 40 6
204 Hermann Baer HBAER 515.123.8888 07-JUN-02 PR_REP 10000  - 101 70 6
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-02 AC_MGR 12008  - 101 110 6
206 William Gietz WGIETZ 515.123.8181 07-JUN-02 AC_ACCOUNT 8300  - 205 110 6

19 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID	DIASEMANA
109	Daniel	Faviet	DFAVIET	515.124.4169	16-AUG-02	FI_ACCOUNT	9000	-	108	100	6
111	Ismael	Sciarra	ISCIARRA	515.124.4369	30-SEP-05	FI_ACCOUNT	7700	-	108	100	6
113	Luis	Popp	LPOPP	515.124.4567	07-DEC-07	FI_ACCOUNT	6900	-	108	100	6
119	Karen	Colmenares	KCOLMENA	515.127.4566	10-AUG-07	PU_CLERK	2500	-	114	30	6
124	Kevin	Mourgos	KMOURGOS	650.123.5234	16-NOV-07	ST_MAN	5800	-	100	50	6
141	Trenna	Rajs	TRAJS	650.121.8009	17-OCT-03	ST_CLERK	3500	-	124	50	6
145	John	Russell	JRUSSEL	011.44.1344.429268	01-OCT-04	SA_MAN	14000	.4	100	80	6
155	Oliver	Tuvault	OTUVAULT	011.44.1344.486508	23-NOV-07	SA_REP	7000	.15	145	80	6
156	Janette	King	JKING	011.44.1345.429268	30-JAN-04	SA_REP	10000	.35	146	80	6
161	Sarath	Sewall	SSEWALL	011.44.1345.529268	03-NOV-06	SA_REP	7000	.25	146	80	6
162	Clara	Vishney	CVISHNEY	011.44.1346.129268	11-NOV-05	SA_REP	10500	.25	147	80	6
168	Lisa	Ozer	LOZER	011.44.1343.929268	11-MAR-05	SA_REP	11500	.25	148	80	6
171	William	Smith	WSMITH	011.44.1343.629268	23-FEB-07	SA_REP	7400	.15	148	80	6
176	Jonathon	Taylor	JTAYLOR	011.44.1644.429265	24-MAR-06	SA_REP	8600	.2	149	80	6
179	Charles	Johnson	CJOHNSON	011.44.1644.429262	04-JAN-08	SA_REP	6200	.1	149	80	6
203	Susan	Mavris	SMAVRIS	515.123.7777	07-JUN-02	HR_REP	6500	-	101	40	6
204	Hermann	Baer	HBAER	515.123.8888	07-JUN-02	PR_REP	10000	-	101	70	6
205	Shelley	Higgins	SHIGGINS	515.123.8080	07-JUN-02	AC_MGR	12008	-	101	110	6
206	William	Gietz	WGIETZ	515.123.8181	07-JUN-02	AC_ACCOUNT	8300	-	205	110	6

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database