代做COMP7104、代写SQL设计、SQL编程设计调试、代写Database Systems

日期：2019-04-10 10:05

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