Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 1/
- SELECT CONCAT(UPPER(NOM),CONCAT(' ',PRENOM)) as"nom et prenom"
- FROM MEMBRES;
- OU BIEN :)
- SELECT UPPER(NOM)||' '||PRENOM as "nom et prenom"
- FROM MEMBRES;
- 2/
- SELECT PRIX_I,EXTRACT(YEAR FROM DATE_D)as "year",TO_CHAR(DATE_D,'q')as "trimestre",
- case(TO_CHAR(DATE_D,'q') )
- WHEN '1' THEN 1
- WHEN '2' THEN 1
- ELSE 2
- END as"semestre"
- FROM DEALS;
- 3/
- SELECT TO_CHAR(NUM_ADRESSE)||' '||RUE_ADRESSE||' '||ville||' '||CP as "adresse"
- FROM PRESTATAIRES_SERVICES;
- OU BIEN :)
- SELECT CONCAT( TO_CHAR(NUM_ADRESSE), CONCAT(' ',CONCAT(RUE_ADRESSE,CONCAT(' ',CONCAT( ville, CONCAT(' ',CP) ) ) ) ) ) as "adresse"
- FROM PRESTATAIRES_SERVICES;
- 4/
- SELECT MAX(NOTED)as"maximum note",MIN(NOTED)as"minimum note",AVG(NOTED)as"moyenne" FROM DEALS;
- 5/
- SELECT COUNT(*)as"nombre de deals" FROM DEALS
- WHERE UPPER(EXPIRE) ='NON';
- 6/
- SELECT VILLE, COUNT(NOM_PREST) as "nombre de prestataires"
- FROM PRESTATAIRES_SERVICES
- GROUP BY VILLE;
- ///////// option order by///
- SELECT VILLE, COUNT(NOM_PREST) as "nombre de prestataires"
- FROM PRESTATAIRES_SERVICES
- GROUP BY VILLE
- ORDER BY "nombre de prestataires" DESC;
- 7/
- SELECT INTITULE, SUM(NBCOUPON) as "somme des coupons"
- FROM ACHATS
- GROUP BY INTITULE;
- 8/
- SELECT ACHATS.INTITULE, MEMBRES.NOM,MEMBRES.PRENOM
- FROM ACHATS
- INNER JOIN MEMBRES
- ON (MEMBRES.LOGIN= ACHATS.LOGIN) ;
- 9/
- SELECT ACHATS.INTITULE, MEMBRES.NOM,MEMBRES.PRENOM,DEALS.PRIX_I
- FROM ACHATS
- INNER JOIN MEMBRES
- ON (MEMBRES.LOGIN= ACHATS.LOGIN)
- INNER JOIN DEALS
- ON (DEALS.INTITULE= ACHATS.INTITULE) ;
- 10/
- SELECT DEALS.INTITULE, PRESTATAIRES_SERVICES.NOM_PREST,PRESTATAIRES_SERVICES.EMAIL
- FROM PRESTATAIRES_SERVICES
- INNER JOIN DEALS
- ON (PRESTATAIRES_SERVICES.NOM_PREST= DEALS.NOM_PRESTATAIRE) ;
- 11/
- SELECT C.NOM_CATEGORIE,C.DESCRIPTIONC, COUNT(*) as "nombre de deals associés"
- FROM CATEGORIES C
- INNER JOIN DEALS D
- ON (C.NOM_CATEGORIE=D.NOM_CATG)
- GROUP BY ( NOM_CATEGORIE,DESCRIPTIONC );
- ////////////////// COURS //////////////////////
- FONCTIONS MONOLIGNES
- 1/NOMBRE
- ROUND,TRUNC,CEIL,FLOOR
- SELECT ROUND(3.77,1)
- FROM DUAL;
- 2/CARACTERE
- CONCAT,SUBSTR,LENGTH,UPPER,LOWER,INITCAP(premiere lettre en maj)
- SELECT SUBSTR('test',2,3) //2 :position a partir de laquelle on commence ,3:nbr de caractere
- FROM DUAL;
- 3/DATE
- EXTRACT (day/month/year/hour/minute/seconde from date),MONTHS_BETWEEN,LAST_DAY
- 4/CONVERTION
- TO_DATE : Convertir une Chaine en format Date
- TO_NUMBER : Convertir une Chaine en format Numérique
- TO_CHAR : Convertir une Date /un nombre en une Chaine
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement