HW1-SQL-2020

COMP7104-DASC7104 2020-2021 — Advance Database Systems

Homework 1 – SQL (over holiday villages in France)

1) What is the result of the following query, and what is its meaning (in simple and succinct English, i.e., not reading the query back to us) ?

select min (WeekPrice)

from Pricing P, Season S

where P.CodeSeason=S.CodeSeason

and NameSeason=’Full Season’

and typeCabin=6 ;

2) What is the result of the following query, and what is its meaning (in simple and succinct English) ?

select P.CodeVillage, NameVillage

from Pricing P, Season S, Village V

where P.CODESEASON = S.CODESEASON

and V.CODEVILLAGE = P.CODEVILLAGE

and NameSeason=’Full Season’ and typeCabin=6

and WeekPrice= (select min (WeekPrice)

from Pricing P, SEASON S

where P.CodeSeason=S.CodeSeason

and NameSeason=’Full Season’

and typeCabin=6);

3) What is the result of the following query, and what is its meaning (in simple and succinct English) ?

SELECT C.CodeVillage, NameVillage

FROM CompoVillage C, Cabin Ca, Village V

WHERE C.TypeCabin = Ca.TypeCabin

AND V.CodeVillage=C.CodeVillage AND Capacity = 6

GROUP BY C.CodeVillage , NameVillage

HAVING COUNT(*) = ( SELECT COUNT(*)

FROM Cabin

WHERE Capacity = 6);

4) Write in SQL the following queries:

a) For each village (name) find the cabins they have in their composition (as indicated in table CompoVillage) with a count of at least 4 cabins.

b) Find the capacity of the most spacious cabin in the Prémanon village.

c) Find all villages (names) offering an activity that is more expensive than all those offered by Prémanon.

d) Find the villages (names) offering more activities than the Vendes village.

e) Find the activities (names) offered by at least two mountain villages.

f) Find the cabins offered by no mountain villages.

g) For each village, find the number of activities of type Culture they offer (including 0 for those without such activities)

This study source was downloaded by 100000898231818 from CourseHero.com on 04-17-2025 22:32:33 GMT -05:00

Holiday villages have in their composition various cabins, by which they provide accommodation. Holiday villages also provide various activities. Below is the schema and the database.

Primary keys are underlined, arrows represent foreign keys.

NUMACTlVlTY	NAMEACTlVlTY	TYPEACTlVlTY
1	Swimming	Sport
2	Hiking	Culture
3	Trekking	Nature
4	Horse riding	Sport
5	Bike	Nature
6	Museum	Culture
7	Boat	Nature
8	Kayak	Sport
9	Tennis	Sport
10	Climbing	Sport

ACTIVITY

CODEVlLLAGE	NAMEVlLLAGE	LOCATlON
1	Pré manon	NULL
2	L élex	Mountain
3	Saint-Lary	Mountain
4	Le Grand Lioran	Mountain
5	Obernai	Plaine
6	Vendes	NULL
7	Sainte Suzanne	NULL
8	Port-Bail	Sea
9	Ars-en-Ré	Sea
10	Veules-les-Roses	Sea
11	Argol	NULL
12	Sarzeau	Sea

TYPECABlN	NAME	CAPAClTY
1	Evasion	2
2	Eden	4
3	Azur	4
4	Rêve	4
5	Palace	6
6	Détente	6

CABIN

CODESEASON	NAMESEASON
LS	Low Season
FS	Full Season

VILLAGE

CODEVlLLAGE	TYPECABlN	CODESEASON	WEEKPRlCE
1	3	LS	250
1	3	FS	550
2	4	LS	350
2	4	FS	550
3	6	LS	500
3	6	FS	680
4	1	LS	200
4	1	FS	370
4	6	LS	280

PRICING

SEASON

This study source was downloaded by 100000898231818 from CourseHero.com on 04-17-2025 22:32:33 GMT -05:00

CODEVlLLAGE	TYPECABlN	CABlNCOUNT
1	3	6
1	5	2
2	4	20
3	6	5
4	1	5
4	4	20
4	6	10
5	5	15
5	6	20
6	2	10
6	6	10
7	2	5
7	3	5
7	4	10
8	1	5
8	4	10
8	5	10
8	6	10
9	4	10
9	5	10
9	6	10
10	4	6
11	5	2
11	6	3
12	2	5
12	4	5
12	6	5

COMPOVILLAGE

CODEVlLLAGE	NUMACTlVlTY	PRlCEACTlVlTY
1	1	0
1	3	2
1	5	5
1	10	8
2	2	10
2	4	8
2	5	4
2	10	6
3	1	0
3	2	12
3	4	6
3	5	3
3	9	2
3	10	8
4	2	10
4	5	4
4	10	9
5	1	0
5	6	2
6	1	0
6	5	3
6	6	8
7	2	6
7	3	3
8	1	2
8	7	12
8	8	3
8	9	4
9	1	1
9	5	3
9	7	8
10	1	2
10	2	12
10	8	5
11	2	4
11	10	8
12	1	0
12	7	10
12	8	6
12	9	2