Poster un commentaire ou une réponse
TP Les fonctions monolignes et analytiques (LID)
Zaineb :
nb posts:320
nb discussions:33
inscrit le :23-01-2015
Zaineb :
nb posts:320
nb discussions:33
inscrit le :23-01-2015
Correction:
si substr('nommmmm',3) compte a partitr de le 3eme carac
nextday....... date du 1er mercredi (exple) apres cette date
last day ................dernier jour du mois
order by 3 ordonner par la colonne 3 mais ne fonctionne pas sur les fctions
null*+-/ egal a null
TP4
1
select employee_id,(concat(last_name,first_name)) nom,salary,round((salary*0.155)+salary) "new salary" from employees
2
select employee_id,(concat(last_name,first_name)) nom,salary,((salary*0.155)+salary) "new salary",(((salary*0.155)+salary)- salary) increase from employees
3
select initcap(concat(last_name,first_name)),length(last_name) from employees where (substr(last_name,1,1)) in ('A','J','M') order by last_name
OU----- where last_name like "J%" or last_name like "M%" or last_name like "A%"
4
select lastname,hired,round(monthsbetwen(sysdate,hiredate)) "mounths work"from employ order by monthsbetween...
5
select last_name || ' earns' || tocahr(salary,'$99,999.0))' ||... from employees
6
select last_name,lpad(salary,15,'$') salary from employees
cour:
select employee_id, decode(department_id,90,'xx',20,'xxxx',salary) from employees^// est modifié juste dans laffichage
select decode('ali','salah','fff','default') from dual si pa pareil retourne default
/select decode('ali','salah','fff') from dual si ali==salah affiche fff
utilisation des alias:
select c.* from (select last_name nom, first_name from employees) c where nom='King'
suite TP
7
select last_name,hire_date, to_char((next_day((add_months(hire_date,6)),1)),'Day," the ", Ddspth ," of ", Month YYYY') from employees
8
select last_name,(NVL(to_char(commission_pct),'rrr')) from employees
9
select employee_id,last_name,first_name,hire_date,
case
when to_char(hire_date,'MM')<6 then '1'
else '2' end as semestre ,
to_char(hire_date,'q') trim,
to_char(hire_date,'MM') mois ,to_char(hire_date,'w') as "semaine du mois",
to_char(hire_date,'ww') as "semaine dans lannée",to_char(hire_date,'DD') as "jour du mois",
to_char(hire_date,'Day') as "jour de la semaine ",
to_char(hire_date,' Day "le :" DD month yyyy ') as "date dembauche"
from employees
10
select row_number() over(order by hire_date,last_name,first_name) NO,employee_id matricule,last_name||' '||first_name,hire_date from employees
ordonne suivant date, si meme date ordonne suivant nom, sinon prenom
11
select row_number() over(partition by (to_char(hire_date,'yyyy')) order by (to_char(hire_date,'yyyy')) desc) no,
employee_id matricule,last_name||' '||first_name,hire_date,to_char(hire_date,'yyyy'),
case
when to_char(hire_date,'MM')<6 then '1'
else '2' end sem,to_char(hire_date,'q')
from employees order by to_char(hire_date,'yyyy') desc
OUUUUUUUUUUUUUU
select row_number() over(partition by (to_char(hire_date,'yyyy')) order by (to_char(hire_date,'yyyy')) desc,
(case
when to_char(hire_date,'MM')<6 then '1'
else '2' end )) NO,
employee_id matricule,last_name||' '||first_name,hire_date,to_char(hire_date,'yyyy'),
case
when to_char(hire_date,'MM')<6 then '1'
else '2' end sem,to_char(hire_date,'q')
from employees order by to_char(hire_date,'yyyy') desc
12
+ utiliser lalias compliké
select row_number() over(order by matricule), C.* from(select employee_id matricule, first_name nom,hire_date date_embauche,to_char(hire_date,'yyyy') annee_embauche,
case
when to_char(hire_date,'MM')<=6 then '1'
else '2' end sem ,
to_char(hire_date,'Day ,"le : ",DD month yyy') "date embauche" from employees)C
where sem<=6
13
select c.* from (select count(to_char(hire_date,'yyyy')) nbr,to_char(hire_date,'yyyy') annee from employees
group by (to_char(hire_date,'yyyy'))) c where nbr between 10 and 20 order by annee
