Description

Exercise Level 1 - Solution

Collection(Collection_Name, Painting_Id, Museum, City, State, Country)

Painting(Painting_Id, Painting_Name, Painter, Century, Year, Style

Algebra

1. 1. List the collections (collection names) of the Guggenheim museum in New York

П collection_name (σ museum = “Guggenheim” ^  state = “New York”  (Collection))

2.  List the paintings (Painting Id, Painting Name, Painter, Year, style) of the Guggenheim museum in New York.

П painting_id, painting_name, painter, year, style (Painter     painting_id=painting_id ((σ museum = “Guggenheim” ^  state = “New York” (Collection))

3. Which museums have some impressionist (style) paintings?

П museum (Collection     painting_id=painting_id (σ style = “impressionist” (Painting))

4. Which museums have paintings of both Vang Gogh and Gauguin?

П museum (Collection     painting_id=painting_id (σ  painter = “Van Gogh” Painting)) Ç П museum (Collection      painting_id=painting_id (σ painter = “Gauguin” (Painting))

5. List all the paintings (Painting Name, Painter) of Vang Gogh or Gauguin in the Guggenheim museum in New York.

П painting_name, painter (σ painter = “Van Gogh”  V  painting_name = “Gauguin” (Painter)     painting_id=painting_id (σ museum = “Guggenheim” ^ state = “New York” (Collection)))

SQL

1 – List the collections (collection names) of the Guggenheim museum in New York

Select Collection_Name

FROM Collection C

WHERE C.museum= ‘Guggenheim’ 

AND C.city= ‘New York’;

2– List the paintings (Painting_Id, Painting_Name, Painter,Year, style) of the Guggenheim museum in New York.

SELECT P.Painting_ID, P.Painting_Name, P.Painter, P.year, P.style

FROM Painting P, Collection C

WHERE P.Painting_ID = C.Painting_ID 

AND C.museum= ‘Guggenheim’ 

AND C.city= ‘New York’;

3 – Which museums have some impressionist (style) paintings?

SELECT C.Museum

FROM Collection C, Painting P

WHERE C.Painting_ID = P.Painting_ID AND P.Style = 'impressionist';

4 - Which museums have paintings of both Vang Gogh and Gauguin?

SELECT C.Museum

FROM Collection C, Painting P

WHERE C.Painting_Id = P.Painting_Id

AND P.Painter = 'Vang Gogh'

AND C.Museum in

(SELECT C1.Museum

FROM Collection C1, Painting P1

WHERE C1.Painting_Id = P1.Painting_Id

AND  P1.Painter =Gauguin)

5 – List all the paintings (Painting Name, Painter) of Vang Gogh or Gauguin in the Guggenheim museum in New York.

SELECT C.Museum

FROM Collection C, Painting P

WHERE C.Painting_Id = P.Painting_Id

AND (P.Painter = 'Vang Gogh' OR .P.Painter =Gauguin)

AND C.museum= ‘Guggenheim’ 

AND C.city= ‘New York’;