• API
• FAQ
• Tools
• Archive
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/
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
50.
51. 9/
52.
53. SELECT ACHATS.INTITULE, MEMBRES.NOM,MEMBRES.PRENOM,DEALS.PRIX_I
54. FROM ACHATS
55. INNER JOIN MEMBRES
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.
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!

Top