SHARE
TWEET

Untitled

a guest Dec 5th, 2019 85 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. 1/
  3. SELECT CONCAT(UPPER(NOM),CONCAT(' ',PRENOM)) as"nom et prenom"
  4. FROM MEMBRES;
  5. OU BIEN :)
  6.  
  7. SELECT UPPER(NOM)||' '||PRENOM as "nom et prenom"
  8. FROM MEMBRES;
  9.  
  10. 2/
  11. SELECT PRIX_I,EXTRACT(YEAR FROM DATE_D)as "year",TO_CHAR(DATE_D,'q')as "trimestre",
  12. case(TO_CHAR(DATE_D,'q') )
  13. WHEN '1' THEN 1
  14. WHEN '2' THEN 1
  15. ELSE  2
  16. END as"semestre"
  17. FROM DEALS;
  18. 3/
  19. SELECT TO_CHAR(NUM_ADRESSE)||' '||RUE_ADRESSE||' '||ville||' '||CP as "adresse"
  20. FROM PRESTATAIRES_SERVICES;
  21.  OU BIEN :)
  22. SELECT CONCAT( TO_CHAR(NUM_ADRESSE), CONCAT(' ',CONCAT(RUE_ADRESSE,CONCAT(' ',CONCAT( ville, CONCAT(' ',CP) ) ) ) ) )  as "adresse"
  23. FROM PRESTATAIRES_SERVICES;
  24.  
  25. 4/
  26. SELECT MAX(NOTED)as"maximum note",MIN(NOTED)as"minimum note",AVG(NOTED)as"moyenne" FROM DEALS;
  27. 5/
  28. SELECT COUNT(*)as"nombre de deals" FROM DEALS
  29. WHERE UPPER(EXPIRE) ='NON';
  30. 6/
  31. SELECT VILLE, COUNT(NOM_PREST) as "nombre de prestataires"
  32. FROM PRESTATAIRES_SERVICES
  33. GROUP BY VILLE;
  34. /////////  option order by///
  35. SELECT VILLE, COUNT(NOM_PREST) as "nombre de prestataires"
  36. FROM PRESTATAIRES_SERVICES
  37. GROUP BY VILLE
  38. ORDER BY "nombre de prestataires" DESC;
  39.  
  40.  
  41. 7/
  42. SELECT INTITULE, SUM(NBCOUPON) as "somme des coupons"
  43. FROM ACHATS
  44. GROUP BY INTITULE;
  45. 8/
  46. SELECT ACHATS.INTITULE, MEMBRES.NOM,MEMBRES.PRENOM
  47. FROM ACHATS
  48. INNER JOIN MEMBRES
  49. ON (MEMBRES.LOGIN= ACHATS.LOGIN) ;
  50.  
  51. 9/
  52.  
  53. SELECT ACHATS.INTITULE, MEMBRES.NOM,MEMBRES.PRENOM,DEALS.PRIX_I
  54. FROM ACHATS
  55. INNER JOIN MEMBRES
  56. ON (MEMBRES.LOGIN= ACHATS.LOGIN)
  57. INNER JOIN DEALS
  58. ON (DEALS.INTITULE= ACHATS.INTITULE) ;
  59. 10/
  60. SELECT DEALS.INTITULE, PRESTATAIRES_SERVICES.NOM_PREST,PRESTATAIRES_SERVICES.EMAIL
  61. FROM PRESTATAIRES_SERVICES
  62. INNER JOIN DEALS
  63. ON (PRESTATAIRES_SERVICES.NOM_PREST= DEALS.NOM_PRESTATAIRE) ;
  64. 11/
  65.  
  66. SELECT C.NOM_CATEGORIE,C.DESCRIPTIONC, COUNT(*) as "nombre de deals associés"  
  67. FROM CATEGORIES C
  68. INNER JOIN DEALS D
  69. ON (C.NOM_CATEGORIE=D.NOM_CATG)
  70. GROUP BY ( NOM_CATEGORIE,DESCRIPTIONC );
  71.  
  72. ////////////////// COURS //////////////////////
  73. FONCTIONS MONOLIGNES
  74.  
  75. 1/NOMBRE
  76. ROUND,TRUNC,CEIL,FLOOR
  77. SELECT ROUND(3.77,1)
  78. FROM DUAL;
  79.  
  80. 2/CARACTERE
  81. CONCAT,SUBSTR,LENGTH,UPPER,LOWER,INITCAP(premiere lettre en maj)
  82. SELECT SUBSTR('test',2,3) //2 :position a partir de laquelle on commence ,3:nbr de caractere
  83. FROM DUAL;
  84. 3/DATE
  85. EXTRACT (day/month/year/hour/minute/seconde from date),MONTHS_BETWEEN,LAST_DAY
  86. 4/CONVERTION
  87.  TO_DATE : Convertir une Chaine en format Date
  88.  TO_NUMBER : Convertir une Chaine en format Numérique
  89.  TO_CHAR : Convertir une Date /un nombre en une Chaine
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top