TBD Práctica 3

1  - SELECT * FROM J;
2  - SELECT * FROM S WHERE CIUDAD = "Londres";
3  - SELECT DISTINCT S# FROM SPJ WHERE J# = "J1" ORDER BY S#;
4  - SELECT * FROM SPJ WHERE CANT >= 300 AND CANT <= 750;
5  - SELECT DISTINCT COLOR, CIUDAD FROM P;

6  - SELECT S#, P#, J# FROM S,P,J WHERE S.CIUDAD = P.CIUDAD AND P.CIUDAD = J.CIUDAD;
7  - SELECT S#, P#, J# FROM S,P,J WHERE S.CIUDAD <> P.CIUDAD OR S.CIUDAD <> J.CIUDAD;
8  - SELECT S#, P#, J# FROM S,P,J WHERE S.CIUDAD <> P.CIUDAD AND S.CIUDAD <> J.CIUDAD AND P.CIUDAD <> J.CIUDAD;
9  - SELECT DISTINCT P# FROM SPJ,S WHERE SPJ.S# = S.S# AND S.CIUDAD = "Londres";
10 - SELECT DISTINCT P# FROM SPJ, S, J WHERE SPJ.S# = S.S# AND SPJ.J# = J.J# AND S.CIUDAD = "Londres" AND J.CIUDAD = "Londres";
11 - SELECT DISTINCT S.CIUDAD, J.CIUDAD FROM SPJ, S, J WHERE SPJ.S# = S.S# AND SPJ.J# = J.J#;
12 - SELECT DISTINCT P# FROM SPJ, S, J WHERE SPJ.S# = S.S# AND SPJ.J# = J.J# AND S.CIUDAD = J.CIUDAD;
13 - SELECT DISTINCT J# FROM SPJ, S, J WHERE SPJ.S# = S.S# AND SPJ.J# = J.J# AND S.CIUDAD <> J.CIUDAD;
14 - SELECT DISTINCT T1.P# T2.P# FROM SPJ T1, SPJ T2 WHERE T1.S# = T2.S# AND T1.P# <> T2.P#;

15 - SELECT COUNT(DISTINCT J#) FROM SPJ WHERE S# = "S1";
16 - SELECT SUM(CANT) FROM SPJ WHERE S# = "S1" AND P# = "P1";
17 - SELECT P#, J#, SUM(CANT) FROM SPJ GROUP BY P#, J#;
18 - SELECT P# FROM SPJ GROUP BY P# HAVING AVG(CANT) > 320;

19 - SELECT * FROM SPJ WHERE CANT IS NOT NULL;
20 - SELECT J#, CIUDAD FROM J WHERE CIUDAD LIKE "_o%";

21 - SELECT JNOMBRE FROM J WHERE J# IN (SELECT DISTINCT J# FROM SPJ WHERE S# = "S1");
22 - SELECT COLOR FROM P WHERE P# IN (SELECT DISTINCT P# FROM SPJ WHERE S# = "S1");
23 - (Se podría resolver directamente con la subconsulta)
     SELECT P# FROM P WHERE P# IN (SELECT DISTINCT P# FROM SPJ, J WHERE SPJ.J# = J.J# AND J.CIUDAD = "Londres");
24 - SELECT DISTINCT J# FROM SPJ WHERE P# IN (SELECT DISTINCT P# FROM SPJ WHERE S# = "S1");
25 - SELECT DISTINCT S# FROM SPJ WHERE P# IN
        (SELECT DISTINCT P# FROM SPJ WHERE S# IN
            (SELECT DISTINCT S# FROM SPJ, P WHERE SPJ.P# = P.P# AND P.COLOR = "Rojo"));
26 - SELECT S# FROM S WHERE SITUACION < (SELECT SITUACION FROM S WHERE S# = "S1");
27 - SELECT J# FROM J WHERE CIUDAD = (SELECT MIN(CIUDAD) FROM J);

28 - SELECT P# FROM P WHERE EXISTS
         (SELECT * FROM J WHERE J.CIUDAD = "Londres" AND EXISTS
              (SELECT * FROM SPJ WHERE SPJ.J# = J.J# AND SPJ.P# = P.P#));
29 - SELECT J# FROM J WHERE EXISTS
         (SELECT * FROM SPJ WHERE SPJ.J# = J.J# AND EXISTS
             (SELECT P# FROM P WHERE SPJ.P# = P.P# EXISTS
                  (SELECT * FROM SPJ SPJ2 WHERE SPJ2.P# = P# AND SPJ2.S# = "S1");
30 - SELECT J# FROM J WHERE NOT EXISTS
         (SELECT * FROM P WHERE P.COLOR = "Rojo" AND EXISTS
              (SELECT * FROM S WHERE S.CIUDAD = "Londres" AND EXISTS
                  (SELECT * FROM SPJ WHERE SPJ.S# = S.S# AND SPJ.R# = R.R# AND SPJ.J# = J.J#)));
31 - SELECT J# FROM J WHERE NOT EXISTS
         (SELECT * FROM S WHERE S# <> "S1" AND EXISTS
             (SELECT * FROM SPJ WHERE SPJ.S# = S.S# AND SPJ.J# = J.J#))
     AND EXISTS (SELECT * FROM SPJ WHERE S# = "S1" AND SPJ.J# = J.J#);

32 - SELECT DISTINCT CIUDAD FROM
     ((SELECT DISTINCT CIUDAD FROM S) UNION
      (SELECT DISTINCT CIUDAD FROM P) UNION
      (SELECT DISTINCT CIUDAD FROM J))
     ORDER BY CIUDAD;

33 - UPDATE P SET COLOR = "Gris" WHERE COLOR = "Rojo";
34 - DELETE FROM J WHERE J# NOT IN (SELECT DISTINCT J# FROM SPJ);
35 - INSERT INTO S (S#, NOMBRE, CIUDAD) VALUES ("S10", "Salazar", "Nueva York");