Exemples en vrac

070423

===== EXEMPLE =============
 
SELECT S#
FROM SPJ
WHERE NOT EXISTS (
  SELECT *
  FROM SPJ
  WHERE 
)
 
REM ===== NO 11 ======================================
 
SELECT *
FROM P
WHERE EXISTS (
  SELECT *
  FROM SPJ
  WHERE SPJ.P#=P.P# 
  AND EXISTS (
    SELECT *
    FROM S
    WHERE CITY='London'
    AND SPJ.S#=S.S#
  )
);
 
REM ===== NO 11 =======SOUS=REQUETE===================
 
SELECT *
FROM P
WHERE P# IN (
  SELECT P#
  FROM SPJ
  WHERE S# IN (
    SELECT S#
    FROM S
    WHERE CITY='London'
  )
);
 
REM ===== NO 11 =======JOINTURE=======================
 
SELECT DISTINCT P.*
FROM S, SPJ,P
WHERE SPJ.S#=S.S# AND SPJ.P#=P.P# AND S.City='London';  
 
REM ===== NO 12 =======EXISTS=========================
 
SELECT *
FROM P
WHERE EXISTS (
  SELECT *
  FROM SPJ
  WHERE SPJ.P#=P.P#
  AND EXISTS (
    SELECT *
    FROM S
    WHERE CITY='London'
    AND SPJ.S#=S.S#
  )
  AND EXISTS (
    SELECT *
    FROM J
    WHERE CITY='London'
    AND SPJ.J#=J.J#
  )
); 
 
REM ===== NO 14 =======EXISTS=========================
 
SELECT JNAME
FROM J
WHERE EXISTS (
  SELECT *
  FROM SPJ
  WHERE J.J#=SPJ.J# AND SPJ.S#='S2'
);
 
SELECT JNAME
FROM J
WHERE J# IN (
  SELECT J#
  FROM SPJ
  WHERE S#='S2'
);
 
SELECT DISTINCT JNAME
FROM J, SPJ
WHERE J.J#=SPJ.J# AND SPJ.S#='S2';
 
REM ===== NO 18 =======EXISTS=========================
 
SELECT DISTINCT P#
FROM SPJ
WHERE EXISTS (
  SELECT *
  FROM J
  WHERE CITY='London'
  AND J.J#=SPJ.J#
);
 
SELECT DISTINCT P#
FROM SPJ
WHERE J# IN (
  SELECT J#
  FROM J
  WHERE CITY='London'
);
 
SELECT DISTINCT P#
FROM P,J,SPJ
 
REM ===== NO 19 =======EXISTS=========================
 
SELECT DISTINCT J#
FROM SPJ
WHERE EXISTS (
  SELECT *
  FROM SPJ SPJ1
  WHERE S#='S4'
  AND SPJ.P#=SPJ1.P#
);
 
SELECT J#
FROM SPJ
WHERE P# IN (
  SELECT P#
  FROM SPJ
  WHERE S#='S4'
);
 
REM ===== NO 20 =======EXISTS=========================
 
REM ===== NO 21 =======EXISTS=========================
 
SELECT SName
FROM S
WHERE EXISTS (
  SELECT *
  FROM S S1
  WHERE S1.S#='S1'
  AND S.Status < S1.Status
);
 
SELECT SNAME
FROM S
WHERE STATUS < (
  SELECT STATUS
  FROM S
  WHERE s#='S1'
);
 
REM ===== NO 32 =======EXISTS=========================
 
SELECT JNAME
FROM J
WHERE NOT EXISTS (
  SELECT *
  FROM SPJ
  WHERE J.J#=SPJ.J#
  AND EXISTS (
    SELECT *
    FROM P
    WHERE Color='Red'
    AND P.P#=SPJ.P#
  )
)
AND NOT EXISTS (
  SELECT *
  FROM SPJ
  WHERE J.J#=SPJ.J#
  AND EXISTS (
    SELECT *
    FROM S
    WHERE City='London'
    AND S.S#=SPJ.S#
  )
);
 
 
REM ==== 32 === DEVRAIT DONNER TOUS LES PROJETS
 
SELECT JName
FROM J
WHERE J# IN (
  SELECT J#
  FROM SPJ
  WHERE P# NOT IN (
    SELECT P#
    FROM SPJ
    WHERE S# IN (
      SELECT S#
      FROM S
      WHERE CITY='London'
    )
    AND P# IN (
      SELECT P#
      FROM P
 
      WHERE COLOR='Red'
    )
  )  
);
 
SELECT JName
FROM J
WHERE NOT EXISTS (
  SELECT *
  FROM SPJ, S, P
  WHERE SPJ.J#=J.J#
  AND SPJ.S#=S.S#
  AND SPJ.P#=P.P#
  AND S.CITY='London'
  AND COLOR='Red'
);
 
SELECT JName
FROM J
WHERE NOT EXISTS (
  SELECT *
  FROM SPJ
  WHERE SPJ.J#=J.J# 
  AND S# IN (
    SELECT S#
    FROM S
    WHERE CITY='London'
  )
  AND P# IN (
    SELECT P#
    FROM P
    WHERE COLOR='Red'
  )
);
 
SELECT JName
FROM J
WHERE NOT EXISTS (
  SELECT *
  FROM SPJ 
  WHERE SPJ.J#=J.J# 
  AND EXISTS (
    SELECT *
    FROM S
    WHERE S.S#=SPJ.S#
    AND CITY='London'
  )
  AND EXISTS (
    SELECT *
    FROM P
    WHERE P.P#=SPJ.P#
    AND COLOR='Red'
  )
);
 
REM ==== 33 === DEVRAIT DONNER TOUS LES PROJETS
 
SELECT DISTINCT PName
FROM P, SPJ
WHERE P.P#=SPJ.P#
AND NOT EXISTS (
  SELECT *
  FROM SPJ
  WHERE S#!='S5'
  AND P.P#=SPJ.P#
);
 
REM Optimal
SELECT DISTINCT PName
FROM P
WHERE P# IN (
  SELECT P#
  FROM SPJ
)
AND NOT EXISTS (
  SELECT *
  FROM SPJ
  WHERE S#!='S5'
  AND P.P#=SPJ.P#
);
 
REM ==== 34 ===
 
SELECT PName
FROM P
WHERE NOT EXISTS (
  SELECT *
  FROM J
  WHERE CITY='London'
  AND NOT EXISTS (
    SELECT *
    FROM SPJ
    WHERE P.P#=SPJ.P#
    AND J.J#=SPJ.J#
  )
);

Lab7

REM ======= Lab 7 ==================
 
DELETE FROM ClientsImage;
DELETE FROM ContratsImage;
DELETE FROM DepensesImage;
DELETE FROM FacturesImage;
 
COLUMN Numéro Format 99
COLUMN Client FORMAT A10
COLUMN Adresse FORMAT A50
COLUMN Téléphone FORMAT A15
 
COLUMN NUMCONTRAT FORMAT 999
COLUMN NOMCONTRAT FORMAT A10
COLUMN VALEURCONTRAT FORMAT 9999999
COLUMN BUDGETDEPENSESCONTRAT FORMAT 9999999
 
REM ==== Requete 1 ==================================
 
SELECT NumeroClient AS "Numéro",
       NomClient AS "Client",
       RueClient||' '||VilleClient||' '||ProvinceClient||' '||CodePostalClient||' '||PaysClient AS "Adresse",
       TelephoneClient AS "Téléphone"
FROM ClientsImage
ORDER BY NomClient;
 
REM ==== Requete 2 ==================================
 
COLUMN ContratsImage FORMAT A8
 
SELECT NumContrat AS "Numéro",
       NomContrat AS "Contrat",
       To_Char(ValeurContrat*0.8,'$9999,990.00') AS "Valeur US",
       To_Char(BudgetDepensesContrat*0.8,'$9999,990.00') AS "Budget dépenses"
FROM ContratsImage
ORDER BY NomContrat, NumContrat DESC;
 
REM ==== Requete 3 ==================================
 
SELECT DISTINCT NumeroClient AS "Numéro du client"
FROM ContratsImage;
 
 
REM ==== Requete 4 ==================================
 
SELECT DISTINCT NomResponsableContrat
FROM ContratsImage
WHERE Bureau IN ('Montréal', 'Paris')
AND SECTION IN ('Cinéma','Publicité');
 
REM ==== Requete 5 ==================================
 
SELECT SYSDATE
FROM DUAL;
 
SELECT *
FROM ContratsImage
WHERE to_Char(DateDebutContrat,'YYYY')='2000'
AND to_Char(DateFinContrat,'YYYY')='2000';
 
REM ==== Requete 6 ==================================
 
SELECT *
FROM ContratsImage
WHERE to_Char(DateDebutContrat,'YYYY')<'2001'
AND to_Char(DateFinContrat,'YYYY')>'1999';
 
REM ==== Requete 7 ==================================
 
SELECT *
FROM ContratsImage
WHERE (to_Char(DateDebutContrat,'YYYY')=2000 OR to_Char(DateFinContrat,'YYYY')=2000)
AND (to_Char(DateDebutContrat,'YYYY')<>to_Char(DateFinContrat,'YYYY'));
 
SELECT *
FROM ContratsImage
WHERE NOT (to_Char(DateDebutContrat,'YYYY')=2000
AND to_Char(DateFinContrat,'YYYY')=2000);
 
REM ==== Requete 8 ==================================
 
SELECT *
FROM ContratsImage
WHERE (to_Char(DateDebutContrat,'YYYY')<'2000' AND to_Char(DateFinContrat,'YYYY')<'2000') OR
(to_Char(DateDebutContrat,'YYYY')>'2000' AND to_Char(DateFinContrat,'YYYY')>'2000');
 
REM ==== Requete 9 ==================================
 
SELECT *
FROM ClientsImage
WHERE PaysClient = 'Canada'
AND CodePostalClient LIKE 'K1%'
ORDER BY NomClient;
 
REM ==== Requete 10 ==================================
 
SELECT NumContrat AS "NumCont",
       NomContrat AS "Contrat",
       NomClient AS "Client",
       ValeurContrat AS "Valeur",
       DateDebutContrat AS "Debut",
       DateFinContrat AS "Fin",
       SECTION AS "Section"
FROM ContratsImage, ClientsImage
WHERE ContratsImage.NumeroClient=ClientsImage.NumeroClient
ORDER BY DateDebutContrat;
 
REM ==== Requete 11 ==================================
 
SELECT CodeDepense AS "Code",
       DateDepense AS "Date",
       MontantDepense AS "Montant",
       NumeroContrat AS "Contrat",
       NomContrat AS "Nom de Contrat",
       DateDebutContrat AS "Début",
       DateFinContrat AS "Fin",
       NomClient AS "Client"
FROM DepensesImage, ContratsImage, ClientsImage
WHERE DepensesImage.NumeroContrat=ContratsImage.NumContrat
AND ContratsImage.NumeroClient=ClientsImage.NumeroClient
AND to_Char(DateDepense,'YYYY')=2000
AND TypeDepense='Travail interne';
 
REM ==== Requete 12 ==================================
 
SELECT ClientsImage.NumeroClient AS "#Client", NomClient, VilleClient,
       NumContrat, NomContrat, Bureau
FROM ClientsImage,ContratsImage
WHERE ClientsImage.NumeroClient=ContratsImage.NumeroClient
AND VilleClient=Bureau;
 
REM ==== Requete 13 ==================================
 
SELECT ContratsImage.*
FROM ContratsImage, ClientsImage
WHERE ContratsImage.NumeroClient=ClientsImage.NumeroClient
AND NomClient='Dupont';
 
SELECT *
FROM ContratsImage
WHERE ContratsImage.NumeroClient IN (
  SELECT NumeroClient
  FROM ClientsImage
  WHERE NomClient='Dupont'
);
 
SELECT *
FROM ContratsImage
WHERE EXISTS (
  SELECT *
  FROM ClientsImage
  WHERE NomClient='Dupont'
  AND ContratsImage.NumeroClient=NumeroClient
);
 
 
REM ==== Requete 14 ==================================
 
SELECT VilleClient
FROM ClientsImage
WHERE NumeroClient IN (
  SELECT NumeroClient
  FROM ContratsClient
  WHERE Bureau=
 
SELECT ClientsImage.VilleClient
FROM ClientsImage, (
  SELECT Bureau
  FROM ContratsImage
  WHERE
WHERE NumeroClient IN (
  SELECT NumeroClient
  FROM ContratsClient
  WHERE Bureau=
 
SELECT DISTINCT ClientsImage.VilleClient AS "Ville du client",
       ContratsImage.Bureau AS "Bureau du contrat"
FROM ClientsImage, ContratsImage
WHERE ClientsImage.NumeroClient=ContratsImage.NumeroClient;
 
REM ==== Requete 15 ==================================
 
SELECT NumContrat
FROM ContratsImage
WHERE NumContrat IN (
  SELECT NumContrat
  FROM DepensesImage
  WHERE TypeDepense IN (
    SELECT TypeDepense
    FROM DepensesImage
    WHERE NumContrat=1
  )
);
 
SELECT DISTINCT  NumContrat
FROM ClientsImage, ContratsImage
GROUP BY ClientsImage.NumeroClient
HAVING ClientsImage.NumeroClient=ContratsImage.NumeroClient;
 
WHERE ClientsImage.NumeroClient=ContratsImage.NumeroClient
 
 
SELECT DISTINCT ClientsImage.NumeroClient, NumContrat
FROM ClientsImage, ContratsImage
GROUP BY ClientsImage.NumeroClient, NumContrat
 
SELECT C1.NumContrat, C2.NumContrat
FROM ContratsImage C1, ContratsImage C2
WHERE C1.NumeroClient=C2.NumeroClient
AND C1.NumContrat<C2.NumContrat;
 
 
 
 
REM ==== Requete 16 ==================================
 
SELECT DISTINCT NumeroContrat
FROM DepensesImage
WHERE TypeDepense IN (
  SELECT TypeDepense
  FROM DepensesImage
  WHERE NumeroContrat=1
);
 
REM ==== Requete 17 ==================================
 
SELECT NumContrat, NomContrat
FROM ContratsImage
WHERE ValeurContrat>(
  SELECT ValeurContrat
  FROM ContratsImage
  WHERE NumContrat=7
);
 
REM ==== Requete 18 ==================================
 
SELECT DISTINCT NumContrat, NomContrat
FROM ContratsImage
WHERE ValeurContrat < (
  SELECT MAX(ValeurContrat)
  FROM ContratsImage
  WHERE NumeroClient IN (
    SELECT NumeroClient
    FROM ClientsImage
    WHERE VilleClient='Toronto'
  )
);

Lab 8 : Vues

-- Lab no 8
-- exercice no 5 (vues)
CREATE OR REPLACE VIEW Contrats AS
SELECT NumContrat, NomContrat, NomResponsableContrat, DateDebutContrat, DateFinContrat
FROM ContratsImage;
 
GRANT ALL ON Contrats TO etudiant57, etudiant52;
 
-- exercice no 6
 
CREATE OR REPLACE VIEW Factures AS
SELECT NumFacture, DateFacture, MontantFacture,
       NumContrat, NomContrat, DateDebutContrat, DateFinContrat,
       NomClient
FROM FacturesImage, ContratsImage, ClientsImage
WHERE FacturesImage.NumeroContrat=ContratsImage.NumContrat
AND ContratsImage.NumeroClient=ClientsImage.NumeroClient;
 
GRANT SELECT ON Factures TO etudiant57, etudiant52;
 
-- exercice no 7
 
CREATE OR REPLACE VIEW FacturesContrat AS
SELECT NumContrat, NomContrat, COUNT(NumFacture) Nombre, SUM(MontantFacture) Somme
FROM FacturesImage, ContratsImage
WHERE FacturesImage.NumeroContrat=ContratsImage.NumContrat
GROUP BY NumContrat, NomContrat;
 
GRANT SELECT ON Contrats TO etudiant57, etudiant52;