COMP7104 – DASC7104 2018-2019
COMP7104 – DASC7104 2018-2019 – Advance Database Systems
Homework 1 – SQL (over holiday villages in France)
1) What result is given by the following query, and what is its meaning ?
select min (WeekPrice)
from Pricing P, Season S
where P.CodeSeason=S.CodeSeason
and NameSeason='Full Season'
and typeCabin=6 ;
2) What result is given by the following query, and what is its meaning ?
select Pr.CodeVillage, NameVillage
from Pricing P, Season S, Village V
where P.CODESEASON = S.CODESEASON
and V.CODEVILLAGE = Pr.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 result is given by the following query, and what is its meaning ?
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
代做COMP7104作业、代写SQL课程设计作业、SQL编程设计作业调试、代写Database Systems作业
WHERE Capacity = 6);
2) Write in SQL the following queries :
a) Find the villages offering at least one activity at less than 3, along with the name of that activity.
b) Find the price of the most expensive activity offered by the Prémanon village.
c) Find all villages offering an activity that is more expensive than all those offered by Prémanon.
d) Find the name of villages offering more activities than the Vendes village.
e) Find the name of activities offered by at least one mountain village.
f) Find the name of the activities offered by all mountain villages.
g) For each village, the number of activies of type Nature they offer (including 0 for those with no
such activities)
Holiday villages provide accommodation and various activities. Below is the schema and database .
CODEVILLAGE NAMEVILLAGE LOCATION
1 NULL
2 Mountain
3 Mountain
4 Mountain
5 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 Sea
Prémanon
Lélex
Saint-Lary
Le Grand Lioran
Obernai
Sarzeau
TYPECABIN NAME CAPACITY
1 Evasion 2
2 Eden 4
3 Azur 4
4 Rêve 4
5 Palace 6
6 Détente 6
CODESEASON NAMESEASON
LS
FS
Low Season
Full Season
CODEVILLAGE TYPECABIN CODESEASON WEEKPRICE
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
NUMACTIVITY NAMEACTIVITY TYPEACTIVITY
1 Sport
2 Culture
3 Trekking Nature
4 Sport
5 Nature
6 Culture
7 Nature
8 Kayak Sport
9 Tennis Sport
10 Sport
Swimming
Hiking
Horse riding
Bike
Museum
Boat
Climbing
COMPOVILLAGE
ACTIVILLAGE
CODEVILLAGE NUMACTIVITY PRICEACTIVITY
CODEVILLAGE TYPECABIN CABINCOUNT
因为专业，所以值得信赖。如有需要，请加QQ：99515681 或邮箱：99515681@qq.com
微信：codinghelp
