DB lab 2

PostgreSQL |

1.52 KB

None

0 👍

0 👎

Raw Download Clone Embed Print View

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

Comments

01/01/70 12:00:00 AM UTC
Plain Text |
0 B
|
👍
/
👎
01/01/70 12:00:00 AM UTC
Plain Text |
0 B
|
👍
/
👎
01/01/70 12:00:00 AM UTC
Plain Text |
0 B
|
👍
/
👎

Pasted

DB lab 2

Comments