ex03.sql

-- initialisation: creates tables
use omnibox;
drop table if exists gallery;
drop table if exists museum;
create table if not exists gallery
    (artwork integer(3),
     dated integer(4),
     atmuseum varchar(25),
    primary key (artwork, dated));
create table if not exists museum
    (code varchar(25) primary key,
     nation varchar(20));

-- populates tables
insert into gallery values
    (1, 1990, 'Louvre'),(1, 1991, 'Louvre'),(1, 1992, 'Louvre'),(1, 1993, 'Uffizi'),(1, 1994, 'Uffizi'),(1, 1995, 'Louvre'),
    (2, 1990, 'Uffizi'),(2, 1991, 'Vatican'),(2, 1992, 'Guggenheim'),(2, 1993, 'Guggenheim'),(2, 1994, 'Guggenheim'),(2, 1995, 'Guggenheim'),
    (3, 1990, 'Vatican'),(3, 1991, 'Guggenheim'),(3, 1992, 'Vatican'),(3, 1993, 'Orsay'),(3, 1994, 'Orsay'),(3, 1995, 'Orsay'),
    (4, 1990, 'Guggenheim'),(4, 1991, 'Guggenheim'),(4, 1992, 'Orsay'),(4, 1993, 'Orsay'),(4, 1994, 'Louvre'),(4, 1995, 'Louvre'),
-- Louvre exclusives
    (5, 1990, 'Louvre'),(5, 1991, 'Louvre'),(5, 1992, 'Louvre'),(5, 1993, 'Louvre'),(5, 1994, 'Louvre'),(5, 1995, 'Louvre'),
    (6, 1990, 'Louvre'),(6, 1991, 'Louvre'),(6, 1992, 'Louvre'),(6, 1993, 'Louvre'),(6, 1994, 'Louvre'),(6, 1995, 'Louvre'),
    (7, 1990, 'Louvre'),(7, 1991, 'Louvre'),(7, 1992, 'Louvre'),(7, 1993, 'Louvre'),(7, 1994, 'Louvre'),(7, 1995, 'Louvre'),
    (8, 1990, 'Louvre'),(8, 1991, 'Louvre'),(8, 1992, 'Louvre'),(8, 1993, 'Louvre'),(8, 1994, 'Louvre'),(8, 1995, 'Louvre'),
    (9, 1990, 'Louvre'),(9, 1991, 'Louvre'),(9, 1992, 'Louvre'),(9, 1993, 'Louvre'),(9, 1994, 'Louvre'),(9, 1995, 'Louvre'),
    (10, 1990, 'Louvre'),(10, 1991, 'Louvre'),(10, 1992, 'Louvre'),(10, 1993, 'Louvre'),(10, 1994, 'Louvre'),(10, 1995, 'Louvre'),
    (11, 1990, 'Louvre'),(11, 1991, 'Louvre'),(11, 1992, 'Louvre'),(11, 1993, 'Louvre'),(11, 1994, 'Louvre'),(11, 1995, 'Louvre'),
    (12, 1990, 'Louvre'),(12, 1991, 'Louvre'),(12, 1992, 'Louvre'),(12, 1993, 'Louvre'),(12, 1994, 'Louvre'),(12, 1995, 'Louvre'),
    (13, 1990, 'Louvre'),(13, 1991, 'Louvre'),(13, 1992, 'Louvre'),(13, 1993, 'Louvre'),(13, 1994, 'Louvre'),(13, 1995, 'Louvre'),
-- Orsay exclusives
    (14, 1990, 'Orsay'),(14, 1991, 'Orsay'),(14, 1992, 'Orsay'),(14, 1993, 'Orsay'),(14, 1994, 'Orsay'),(14, 1995, 'Orsay'),
    (15, 1990, 'Orsay'),(15, 1991, 'Orsay'),(15, 1992, 'Orsay'),(15, 1993, 'Orsay'),(15, 1994, 'Orsay'),(15, 1995, 'Orsay'),
    (16, 1990, 'Orsay'),(16, 1991, 'Orsay'),(16, 1992, 'Orsay'),(16, 1993, 'Orsay'),(16, 1994, 'Orsay'),(16, 1995, 'Orsay'),
    (17, 1990, 'Orsay'),(17, 1991, 'Orsay'),(17, 1992, 'Orsay'),(17, 1993, 'Orsay'),(17, 1994, 'Orsay'),(17, 1995, 'Orsay'),
    (18, 1990, 'Orsay'),(18, 1991, 'Orsay'),(18, 1992, 'Orsay'),(18, 1993, 'Orsay'),(18, 1994, 'Orsay'),(18, 1995, 'Orsay'),
    (19, 1990, 'Orsay'),(19, 1991, 'Orsay'),(19, 1992, 'Orsay'),(19, 1993, 'Orsay'),(19, 1994, 'Orsay'),(19, 1995, 'Orsay'),
    (20, 1990, 'Orsay'),(20, 1991, 'Orsay'),(20, 1992, 'Orsay'),(20, 1993, 'Orsay'),(20, 1994, 'Orsay'),(20, 1995, 'Orsay'),
    (21, 1990, 'Orsay'),(21, 1991, 'Orsay'),(21, 1992, 'Orsay'),(21, 1993, 'Orsay'),(21, 1994, 'Orsay'),(21, 1995, 'Orsay'),
    (22, 1990, 'Orsay'),(22, 1991, 'Orsay'),(22, 1992, 'Orsay'),(22, 1993, 'Orsay'),(22, 1994, 'Orsay'),(22, 1995, 'Orsay'),
    (23, 1990, 'Orsay'),(23, 1991, 'Orsay'),(23, 1992, 'Orsay'),(23, 1993, 'Orsay'),(23, 1994, 'Orsay'),(23, 1995, 'Orsay'),
-- Uffizi exclusives
    (24, 1990, 'Uffizi'),(24, 1991, 'Uffizi'),(24, 1992, 'Uffizi'),(24, 1993, 'Uffizi'),(24, 1994, 'Uffizi'),(24, 1995, 'Uffizi'),
    (25, 1990, 'Uffizi'),(25, 1991, 'Uffizi'),(25, 1992, 'Uffizi'),(25, 1993, 'Uffizi'),(25, 1994, 'Uffizi'),(25, 1995, 'Uffizi');
-- museums
insert into museum values
    ('Prado', 'Spain'),
    ('Louvre', 'France'),
    ('Orsay', 'France'),
    ('Uffizi', 'Italy'),
    ('Vatican', 'Italy'),
    ('Guggenheim', 'United States');

-- EXERCISE 3.1
-- shows the artworks stored at least two years in a row in Italian museums
create view template
    as select g.artwork, g.dated, g.atmuseum
            from gallery g, museum m
            where g.atmuseum = m.code
                and m.nation = 'Italy'
            order by artwork;
select distinct t1.artwork
    from template t1, template t2
    where t1.artwork = t2.artwork
          and t1.dated + 1 = t2.dated;
drop view if exists template;

-- EXERCISE 3.2
-- shows the number of artworks stored in any French museum in any given year, but only if it's greater than 10
select g.atmuseum, g.dated, count(distinct g.artwork) as artworks
        from gallery g, museum m
        where g.atmuseum = m.code
              and m.nation = 'France'
        group by g.atmuseum, g.dated
        having count(distinct g.artwork) > 10;

-- EXERCISE 3.3
-- shows the artworks that have never been stored in Italian museums
select distinct artwork
    from gallery
    where artwork <> all
        (select distinct g.artwork
         from gallery g, museum m
         where g.atmuseum = m.code
               and m.nation = 'Italy');

-- removes data
drop view if exists template;
drop table if exists gallery;
drop table if exists museum;