﻿

# Untitled

a guest
Dec 5th, 2019
126
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