讲解COMP7104、辅导SQL设计、SQL编程设计调试、辅导Database Systems 讲解Python程序|讲解R语言程序
- 首页 >> OS编程 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
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
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
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