Pause-Café Volubis

pause-café

rendez-vous technique
Pause-Café est une réunion technique
destinée aux informaticiens sur plateforme IBM i.
Elle a lieu 3 à 4 fois par an : en Bretagne et sur internet.

Pause-café #51

Mai 2009

SQL par l'exemple

TP SQL

********************************************************************************
* TP sur la base vinicole (BDVINx)                                            **                                                                              
********************************************************************************

nombre de vins entrés cette année dans votre cave (fichier MA_CAVE)

select * from ma_cave where year(cav_entreele) = year(current date)

                      ce trimestre
select * from ma_cave where year(cav_entreele) = year(current date)
and quarter(cav_entreele) = quarter(current date)


                      dans l'année écoulée.
select * from ma_cave where cav_entreele between current date - 1 year + 1 day and current date --ou current date - cav_entreele < 00010001 --ou days(current date) - days(cav_entreele) <= 365


il y a t-il des appellations dans la région Alsace ne contenant pas le mot  "Alsace"
select count(*) from appellations join regions using(region_code) where ucase(region) like '%ALSACE%' and ucase(appellation) not like '%ALSACE%'




liste des vins de la région "Loire" (région contient le mot "Loire")
select * from vins join appellations using(appel_code) join regions using(region_code) where ucase(region) like '%LOIRE%'





augmentez de 10 % ,le prix actuel des vins de plus de 2 ans (MA_CAVE)  cave
update ma_cave set cav_prxactuel = cav_prxactuel *1,1
where cav_entreele < current date - 2 years


dans le fichier producteurs, certains noms de communes ne sont pas cadrés  à gauche, cadrez les à gauche.

update producteurs set pr_commune = ltrim(pr_commune)
where left(pr_commune , 1) = ' '





pour des problèmes de page de code, remplacez "côte" par "cote"

 dans le fichier des vins.

update vins set vin_nom = replace(vin_nom, 'côte' , 'cote')
where vin_nom like '%côte%'




nombre de vins par producteurs
select pr_code, count(*) from vins
group by pr_code

ou mieux
select pr_nom , count(*) from vins join producteurs using(pr_code)
group by pr_code , pr_nom




nombre de vins en moyenne par producteurs (le résultat est 3) with temp as (
select pr_code, count(*) as nombre from vins
group by pr_code)
select avg(nombre) from temp



liste des producteurs produisant un nombre > à la moyenne (classée par nombre de vins produits, en décroissant)
with temp as (
select pr_nom, count(*) as nombre from vins
join producteurs using(pr_code)
group by pr_code, pr_nom)
select * from temp
where nombre > (select avg(nombre) from temp)
order by nombre desc





le TOP 10 des producteurs (en nombre de vins produits) with temp as (
select pr_nom, count(*) as nombre from vins
join producteurs using(pr_code)
group by pr_code, pr_nom)
select * from temp
where nombre > (select avg(nombre) from temp)
order by nombre desc
fetch first 10 rows only

les 10 producteurs suivants
WITH
temp1 as (
select pr_nom, count(*) as nombre from vins
join producteurs using(pr_code)
group by pr_code, pr_nom)
, temp2 as ( select pr_nom, nombre ,
row_number() over (order by nombre desc) as noligne
from temp1 where nombre >
(select avg(nombre) from temp)
)
select * from temp2 where noligne between 11 and 20
il y a-t-il des appellations sans producteur ?    si oui, combien ?
select count(*) from appellations exception join producteurs
using(appel_code)






il y a-t-il des appellations n'ayant ni producteur ni vin, si oui combien ?
select count(*) from appellations A
where not exists
(select * from producteurs where appel_code = A.appel_code)
and not exists
(select * from vins where appel_code = a.appel_code)




par producteur, nombre de vins: blanc , rouge, rosé le fichier des type de vins indique :
TYPEDEVIN TYPE_CODE
Blanc effervescent 1
Blanc 2
Rouge effervescent 3
Rouge 4
Rosé effervescent 5
Rosé 6
Moelleux effervescent 7
Moelleux 8

     producteur | nbr de blanc  |  nbr de rouges |      ---------------------------------------------
select pr_code ,
sum(case when type_code in (1, 2, 7, 8) then 1 else 0 END) blanc ,
sum(case when type_code in (3, 4, 5, 6) then 1 else 0 END) rouge
from vins group by pr_code


 puis demandez le total des vins blanc et des vins rouges
select
sum(case when type_code in (1, 2, 7, 8) then 1 else 0 END) blanc ,
sum(case when type_code in (3, 4, 5, 6) then 1 else 0 END) rouge
from vins


augmentez le prix-actuel des vins ayant plus de 5 ans
          de 10% si le prix est < à 10 €           de 15% si le prix est < à 15 €           de 20% au dela de 15 €
update ma_cave set cav_prxactuel = cav_prxactuel * (
CASE
when cav_prxactuel < 10 then 1,10
when cav_prxactuel < 15 then 1,15
else 1,20
END )
where cav_entreele < current date - 5 years



ajoutez au fichier des vins une zone prix_tarif acceptant NULL

     alter table vins        add column prix_tarif dec(7 , 2)



renseignez cette zone pour les vins de votre cave avec le prix actuel
update vins V set prix_tarif = (select cav_prxactuel from ma_cave
where vin_code = V.vin_code)
Résultat de SELECT dépasse une ligne.
En effet au moins un vin a été acheté deux fois à deux prix différents
==>update vins V set prix_tarif =(select avg(cav_prxactuel) from ma_cave
where vin_code = V.vin_code)


25221 lignes mises à jour dans VINS de BDVIN1.



si la zone n'avait pas accepté la valeur nulle, ou pour des raisons de perf.   comment fallait-il écrire la requête?

update vins V set prix_tarif =(select avg(cav_prxactuel) from ma_cav
where vin_code = V.vin_code)
where exists (select * from ma_cave
where vin_code = V.vin_code)


12 lignes mises à jour dans VINS de BDVIN1.

enlever la zone prix_tarif
           alter table vins drop column prix_tarif

Copyright © 1995,2009 VOLUBIS