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

pause-café

Pause-café #51

SQL par l'exemple TP SQL

SQL par l'exemple

TP SQL