DB lab 2

SELECT * FROM spj
INNER JOIN p ON p.n_det = spj.n_det
WHERE spj.n_post = 'S3'
AND p.town = 'Рим'





SELECT DISTINCT s.n_post, s.name, s.reiting, s.town FROM spj
INNER JOIN p ON p.n_det  = spj.n_det
INNER JOIN s ON s.n_post = spj.n_post
INNER JOIN j ON j.n_izd  = spj.n_izd
WHERE p.ves >= 17
  AND j.town = 'Рим'





SELECT DISTINCT spj.n_det
FROM spj
WhERE spj.n_post in
    (SELECT DISTINCT spj.n_post
     FROM spj
     WHERE spj.n_izd = 'J7')





SELECT towns.town, COALESCE(pt.count, 0)
FROM (SELECT j.town FROM j
     UNION
     SELECT p.town FROM p
     UNION
     SELECT s.town FROM s
     ORDER BY town) AS towns
LEFT JOIN (SELECT town, SUM(spj.kol) AS count
      FROM spj
      JOIN j ON j.n_izd = spj.n_izd
      GROUP BY town) pt ON pt.town = towns.town
ORDER BY town





SELECT sj_towns.town
FROM (SELECT DISTINCT p.town FROM p) AS p_towns
RIGHT JOIN (SELECT s.town FROM s
            UNION
            SELECT j.town FROM j) AS sj_towns
            ON p_towns.town = sj_towns.town
WHERE p_towns.town IS NULL
================================================
SELECT sj_towns.town
FROM p AS p_towns
RIGHT JOIN (SELECT s.town FROM s
            UNION
            SELECT j.town FROM j) AS sj_towns
            ON p_towns.town = sj_towns.town
WHERE p_towns.town IS NULL





UPDATE j SET new_town = (SELECT s.town
    from spj
    join s on s.n_post = spj.n_post
    where spj.n_izd = j.n_izd
    order by s.town, s.name
    limit 1 )
WHERE LENGTH(j.name) > 10