61 lines
1.4 KiB
Plaintext
61 lines
1.4 KiB
Plaintext
-- Example 1-to-1 relationship
|
|
-- PK cntry.code is FK ppl.country
|
|
SELECT cntry."name"
|
|
,cntry.capital
|
|
,ppl.population_growth
|
|
FROM "IDBS01_Q12025"."Country" cntry
|
|
INNER JOIN "IDBS01_Q12025"."Population" as ppl
|
|
ON cntry.code = ppl.country
|
|
limit 10;
|
|
|
|
|
|
-- Example 1-to-CN relationship
|
|
-- PK cntry.code is FK brd.country1
|
|
-- without mapping table
|
|
SELECT *
|
|
FROM "IDBS01_Q12025"."Country" cntry
|
|
FULL OUTER JOIN "IDBS01_Q12025".borders brd
|
|
ON cntry.code = brd.country1
|
|
WHERE 1 = 1
|
|
--AND brd.country1 IS NULL
|
|
AND cntry.code IS NULL
|
|
limit 10;
|
|
|
|
-- Example 1-to-CN relationship
|
|
--how to check if it is a 1-to-N relationship
|
|
|
|
SELECT *
|
|
FROM "IDBS01_Q12025"."Country" cntry
|
|
LEFT JOIN "IDBS01_Q12025"."geo_Desert" gdsrt
|
|
ON cntry.code = gdsrt.country
|
|
LEFT JOIN "IDBS01_Q12025"."Desert" dsrt
|
|
ON gdsrt.desert = dsrt."name"
|
|
limit 10;
|
|
|
|
|
|
--Example N-to-CM relationship
|
|
--with mapping table to N created by provicence
|
|
|
|
SELECT gm.mountain
|
|
,count (1) AS Anzahl
|
|
FROM "IDBS01_Q12025"."Country" cntry
|
|
LEFT JOIN "IDBS01_Q12025"."geo_Mountain" gm
|
|
ON cntry.code = gm.country
|
|
group by gm.mountain
|
|
having count(1) > 1
|
|
order by 2 desc
|
|
;
|
|
|
|
SELECT *
|
|
FROM "IDBS01_Q12025"."geo_Mountain"
|
|
WHERE "geo_Mountain".mountain = 'Popocatepetl'
|
|
limit 10;
|
|
|
|
SELECT *
|
|
FROM "IDBS01_Q12025"."Country" cntry
|
|
INNER JOIN "IDBS01_Q12025"."Province" prv
|
|
ON cntry.code = prv.country
|
|
LEFT OUTER JOIN "IDBS01_Q12025"."geo_Mountain" gm
|
|
ON prv.country = gm.country
|
|
AND prv."name" = gm.province
|
|
limit 10; |