(a)	List all the artwork/photos by a certain artist/photographer

SELECT picture_id, title
FROM author a, picture p
WHERE a.author_id = p.author_id AND a.name = 'MASTER OF BUDAPEST';


(c)	Show the artist's/photographer's name with the largest number of entries in the database and also list them. 
SELECT name, title
FROM author a, picture p
WHERE a.author_id = p.author_id AND a.author_id IN (
   SELECT author_id
   FROM picture
   GROUP BY author_id
   HAVING COUNT(*) >= ALL (
      SELECT COUNT(*)
      FROM picture
      GROUP BY author_id
   )
);

(d)	What is the most popular genre of artwork/photos (such as abstracts, stills, portraits, landscapes etc.) 
in the database and find all the artist/photographers that have produced something in that genre.

SELECT DISTINCT type_id, name
FROM author a, picture p
WHERE a.author_id = p.author_id AND type_id IN (
   SELECT type_id
   FROM picture
   GROUP BY type_id
   HAVING COUNT(*) >= ALL (
      SELECT COUNT(*)
      FROM picture
      GROUP BY type_id)
);

(e)	List all the artwork/photos that came out the same decade as a given image.

SELECT name, title, date
FROM author a, picture p
WHERE a.author_id = p.author_id AND p.date >= 1700 AND p.date < 1710;

(f)	What is the longest period someone has been producing photos/art for? 
(Difference in dates between their first piece of art or photo and their last one in the database.)

SELECT DISTINCT p.author_id, span
FROM picture p, (
   SELECT author_id, (MAX(date) - MIN(date)) AS span
   FROM picture
   GROUP BY author_id) x
WHERE p.author_id = x.author_id AND span >= ALL (
   SELECT MAX(date) - MIN(date)
   FROM (
      SELECT *
      FROM picture
      WHERE date IS NOT NULL) y
   GROUP BY author_id
);

(g)	List the artists/photographs that have more comments posted to their work than the 
average number of comments posted to an artist/photographer

SELECT name
FROM author a, picture p
WHERE a.author_id = p.author_id AND p.picture_id IN (
   SELECT picture_id
   FROM comments
   GROUP BY picture_id
   HAVING COUNT(*) > ALL (
      SELECT COUNT(comment_id)/COUNT(a1.author_id)
      FROM author a1, comments
   )
);

(h)	Generate a statistical report of all the images in the database. List the average 
rating of all the artwork

SELECT name, title, (r.total_ratings / r.num_ratings) AS avg_rating
FROM picture p, ratings r, author a
WHERE p.picture_id = r.ratings_id AND a.author_id = p.author_id;

(i)	Find the name of the person who has been posting comments the most. Also find out what 
kind of photos/artwork is that person posting the most about, thus letting us know what the 
person is most into or not into. This is sort of like user profiling so that we can suit their 
interests better.

SELECT DISTINCT name, title, comments, x.by
FROM author a, picture p, comments c, (
   SELECT DISTINCT picture_id, by
   FROM comments
   WHERE by IN (
      SELECT by
      FROM comments
      GROUP BY by
      HAVING COUNT(*) >= all (
         SELECT COUNT(*)
         FROM comments
         GROUP BY by
      )
   )
) x
WHERE a.author_id = p.author_id AND p.picture_id = c.picture_id AND p.picture_id = x.picture_id AND c.by = x.by;

(j)	List all the artwork/photographs that were produced/submitted on a given date(ie 1540).
SELECT name, title
FROM author a, picture p
WHERE p.author_id = a.author_id AND p.date = 1540;