· 9 years ago · Dec 01, 2016, 11:22 AM
1-- Stoll, Danny, Blatt05, Gruppe 3
2--------------------------------------------------------------------------------
3--------------------------- Excercise 1-----------------------------------------
4----a)--------------------------------------------------------------------------
5CREATE OR REPLACE VIEW cityLocation AS
6 (SELECT ci1.name AS city,
7 co1.name AS country
8 FROM city ci1
9 JOIN country co1
10 ON ci1.country = co1.code
11 );
12--------------------------------------------------------------------------------
13--------------------------- Excercise 2-----------------------------------------
14--------------------------------------------------------------------------------
15-- Symmetrical view on borders.
16CREATE OR REPLACE VIEW symBorders AS
17 (SELECT country1, country2 FROM borders
18 )
19UNION
20 (SELECT country1, country2 FROM borders
21 );
22-- exactly 1 border Crossing all continents
23CREATE OR REPLACE VIEW oneCrossing AS
24SELECT sb.country1 AS von, sb.country2 nach, 1 AS anzahl FROM symBorders sb;
25-- exactly 2 border Crossings all continents
26CREATE OR REPLACE VIEW twoCrossings AS
27SELECT c1.von AS von,
28 sb.country2 AS nach,
29 2 AS anzahl
30FROM oneCrossing c1,
31 symBorders sb
32WHERE c1.nach = sb.country1
33AND NOT EXISTS -- pair not in onecrossing
34 (SELECT *
35 FROM oneCrossing c2
36 WHERE c2.von = c1.von
37 AND c2.nach = sb.country2
38 );
39-- exactly 3 border crossings all continents
40CREATE OR REPLACE VIEW threeCrossings AS
41SELECT c1.von AS von,
42 sb.country2 AS nach,
43 3 AS anzahl
44FROM twoCrossings c1,
45 symBorders sb
46WHERE c1.nach = sb.country1
47AND NOT EXISTS -- pair not in onecrossing
48 (SELECT *
49 FROM oneCrossing c2
50 WHERE c2.von = c1.von
51 AND c2.nach = sb.country2
52 )
53AND NOT EXISTS -- pair not in twocrossing
54 (SELECT *
55 FROM twoCrossings c3
56 WHERE c3.von = c1.von
57 AND c3.nach = sb.country2
58 );
59-- Build union, order, select and enforce european continent.
60CREATE OR REPLACE VIEW Grenzuebergang AS
61SELECT *
62FROM (
63 (SELECT * FROM oneCrossing
64 )
65UNION
66 (SELECT * FROM twoCrossings
67 )
68UNION
69 (SELECT * FROM threeCrossings
70 )) cr WHERE cr.von IN
71 (SELECT country FROM encompasses en WHERE en.continent = 'Europe'
72 )
73AND cr.nach IN
74 (SELECT country FROM encompasses en WHERE en.continent = 'Europe'
75 )
76ORDER BY cr.von, cr.anzahl, cr.nach;
77--------------------------------------------------------------------------------
78--------------------------- Excercise 4-----------------------------------------
79--------------------------------------------------------------------------------
80-- Result Table
81CREATE TABLE spokenLang
82 (country CHAR(2), percentage NUMBER
83 );
84-- Procedure
85CREATE OR REPLACE PROCEDURE SpokenLanguage
86 (
87 lang CHAR
88 )
89AS
90BEGIN
91 DELETE FROM spokenLang;
92 INSERT INTO spokenLang
93 SELECT l1.country, l1.percentage FROM language l1 WHERE l1.name = lang ;
94END;
95/
96-- Test cases
97EXEC SpokenLanguage('English');
98EXEC SpokenLanguage('Albanian');
99--------------------------------------------------------------------------------
100--------------------------- Excercise 5-----------------------------------------
101----a)--------------------------------------------------------------------------
102CREATE OR REPLACE FUNCTION numberOfBiggerCities(
103 lcode CHAR,
104 minPop NUMBER)
105 RETURN NUMBER
106AS
107 numCities NUMBER;
108BEGIN
109 SELECT COUNT(*)
110 INTO numCities
111 FROM city
112 WHERE country = lcode
113 AND population > minPop;
114 RETURN numCities;
115END;
116/
117-- Test cases
118SELECT numberOfBiggerCities('de', 100000) FROM Dual;
119----b)--------------------------------------------------------------------------
120CREATE OR REPLACE FUNCTION bigCapital(
121 lcode CHAR)
122 RETURN VARCHAR2
123AS
124 cap VARCHAR2(40);
125 capPop NUMBER;
126 biggerCities NUMBER;
127BEGIN
128 -- Compute capital population
129 SELECT ci.population
130 INTO capPop
131 FROM city ci,
132 country co
133 WHERE co.code = lcode
134 AND ci.country = lcode
135 AND ci.name = co.capital;
136 -- Compute number of bigger cities
137 SELECT COUNT(*)
138 INTO biggerCities
139 FROM city
140 WHERE country = lcode
141 AND population > capPop;
142 -- Compute output
143 IF biggerCities = 0 THEN
144 SELECT capital INTO cap FROM country WHERE code = lcode;
145 RETURN cap;
146 ELSE
147 RETURN NULL;
148 END IF;
149END;
150/
151-- Test cases
152SELECT bigCapital('de') FROM Dual;
153SELECT bigCapital('au') FROM Dual;