134 lines
2.7 KiB
Plaintext
134 lines
2.7 KiB
Plaintext
--
|
|
-- connection of inner, left and right joins. See slide 9
|
|
--
|
|
SELECT cntry.name
|
|
,prv.name
|
|
,gm.mountain
|
|
,brds.country1
|
|
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
|
|
RIGHT OUTER JOIN "IDBS01_Q12025"."borders" brds
|
|
ON brds.country1 = cntry.code
|
|
limit 10;
|
|
|
|
--
|
|
-- Difference UNION and UNION ALL
|
|
--
|
|
SELECT cntry.name
|
|
FROM "IDBS01_Q12025"."Country" cntry
|
|
UNION
|
|
SELECT cntry.name
|
|
FROM "IDBS01_Q12025"."Country" cntry
|
|
ORDER BY 1
|
|
limit 10;
|
|
|
|
--
|
|
-- Transferaufgabe
|
|
--
|
|
|
|
--
|
|
-- Aufgabe 1
|
|
--
|
|
|
|
SELECT city."name" AS Stadtname
|
|
,city.population AS Bevoelkerung
|
|
FROM "IDBS01_Q12025"."Country" cntry
|
|
RIGHT OUTER JOIN "IDBS01_Q12025"."City" city
|
|
ON cntry.code = city.country
|
|
WHERE cntry."name" = 'Austria'
|
|
;
|
|
|
|
--
|
|
-- Aufgabe 2
|
|
--
|
|
|
|
SELECT borderCnrty."name" AS Grenzland
|
|
,borderCnrty.population AS Bevoelkerung
|
|
FROM "IDBS01_Q12025"."Country" cntry
|
|
INNER JOIN "IDBS01_Q12025"."borders" brds1
|
|
ON brds1.country1 = cntry.code
|
|
INNER JOIN "IDBS01_Q12025"."Country" borderCnrty
|
|
ON borderCnrty.code = brds1.country2
|
|
WHERE cntry."name" = 'Germany'
|
|
ORDER BY brds1.length DESC
|
|
;
|
|
|
|
--
|
|
-- Aufgabe 3
|
|
--
|
|
|
|
CREATE VIEW "IDBS01_Q12025"."BorderCountry" AS
|
|
SELECT borderCnrty."name" AS Grenzland
|
|
,borderCnrty.population AS Bevoelkerung
|
|
FROM "IDBS01_Q12025"."Country" cntry
|
|
INNER JOIN "IDBS01_Q12025"."borders" brds1
|
|
ON brds1.country1 = cntry.code
|
|
INNER JOIN "IDBS01_Q12025"."Country" borderCnrty
|
|
ON borderCnrty.code = brds1.country2
|
|
WHERE cntry."name" = 'Germany'
|
|
ORDER BY brds1.length DESC
|
|
;
|
|
|
|
DROP VIEW "IDBS01_Q12025"."BorderCountry";
|
|
|
|
--
|
|
-- Aufgabe 4
|
|
--
|
|
|
|
SELECT *
|
|
FROM "IDBS01_Q12025"."borders"
|
|
WHERE country1 = 'BY'
|
|
;
|
|
|
|
START TRANSACTION;
|
|
INSERT INTO "IDBS01_Q12025"."borders" (Country1, Country2, Length) VALUES ('PL','BY',605);
|
|
INSERT INTO "IDBS01_Q12025"."borders" (Country1, Country2, Length) VALUES ('BY','PL',605);
|
|
COMMIT;
|
|
|
|
START TRANSACTION;
|
|
DELETE FROM "IDBS01_Q12025"."borders"
|
|
WHERE country1 = 'PL'
|
|
AND country2 = 'BY'
|
|
;
|
|
DELETE FROM "IDBS01_Q12025"."borders"
|
|
WHERE country1 = 'BY'
|
|
AND country2 = 'PL'
|
|
;
|
|
COMMIT;
|
|
|
|
--
|
|
-- Aufgabe 5
|
|
--
|
|
|
|
SELECT *
|
|
FROM "IDBS01_Q12025"."Country" cntry
|
|
WHERE cntry.code = 'EAU'
|
|
;
|
|
|
|
|
|
START TRANSACTION;
|
|
UPDATE "IDBS01_Q12025"."Country" SET Population = 44270000 WHERE Code = 'EAU';
|
|
COMMIT;
|
|
|
|
START TRANSACTION;
|
|
UPDATE "IDBS01_Q12025"."Country" SET Population = 20158176 WHERE Code = 'EAU';
|
|
COMMIT;
|
|
|
|
--
|
|
-- Aufgabe 6
|
|
--
|
|
CREATE TABLE "IDBS01_Q12025"."BevoelklerungNeu"
|
|
(ID INTEGER
|
|
,LandCode VARCHAR(4)
|
|
,datum DATE
|
|
,population INTEGER
|
|
,PRIMARY KEY(ID))
|
|
;
|
|
|
|
DROP TABLE "IDBS01_Q12025"."BevoelklerungNeu";
|
|
|