Database schema:
CREATE TABLE Artists (
  artistID NUMBER PRIMARY KEY,
  lname TEXT,
  fname TEXT,
  birthYear NUMBER,
  deathYear NUMBER
);
CREATE TABLE Paintings (
  paintingID NUMBER PRIMARY KEY,
  title TEXT,
  year NUMBER,
  height_mm NUMBER,
  width_mm NUMBER,
  medium TEXT,
  mediumOn TEXT,
  location TEXT,
  painterID NUMBER,
  FOREIGN KEY (painterID) REFERENCES Artists(artistID)
);
CREATE TABLE Sculptures (
  sculptureID NUMBER PRIMARY KEY,
  title TEXT,
  year NUMBER,
  medium TEXT,
  location TEXT,
  sculptorID NUMBER,
  FOREIGN KEY (sculptorID) REFERENCES Artists(artistID)
);

Question: What is the id of the oldest painting?
SQL: SELECT paintingID FROM paintings ORDER BY YEAR LIMIT 1

Question: Find the unique id of the painters who had medium oil paintings exhibited at gallery 240?
SQL: SELECT DISTINCT painterID FROM paintings WHERE medium  =  "oil" AND LOCATION  =  "Gallery 240"

Question: What are the average height and width of paintings grouped by painters and ordered by name?
SQL: SELECT avg(height_mm) ,  avg(width_mm) ,  painterID FROM paintings GROUP BY painterID ORDER BY title

Question: Find the distinct ids of all paintings that are older than some painting at location gallery 240.
SQL: SELECT paintingID FROM paintings WHERE YEAR  <  (SELECT max(YEAR) FROM paintings WHERE LOCATION  =  "Gallery 240")

Question: Find the titles of paintings and sculpture works made by the artist whose id is 222?
SQL: SELECT T2.title FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T1.artistID  =  222 UNION SELECT T4.title FROM artists AS T3 JOIN sculptures AS T4 ON T3.artistID  =  T4.sculptorID WHERE T3.artistID  =  222

Question: What is the age of the artist who had the shortest life?
SQL: SELECT deathYear - birthYear FROM artists ORDER BY deathYear - birthYear LIMIT 1

Question: What are the ids of paintings that are taller than 500 and shorter than 2000?
SQL: SELECT paintingID FROM paintings WHERE height_mm  >  500 AND height_mm  <  2000

Question: What is the name and year of each painting created by the artist whose first name is Mary?
SQL: SELECT T2.title ,   T2.year FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T1.fname  =  "Mary"

Question: What are the distinct titles of every painting that has a greater height than some painting on canvas?
SQL: SELECT DISTINCT title FROM paintings WHERE height_mm  >  (SELECT min(height_mm) FROM paintings WHERE mediumOn  =  "canvas")

Question: How many paintings did the artist with the longest life make ?
SQL: select count(*) from artists as t1 join paintings as t2 on t1.artistid  =  t2.painterid group by t2.painterid order by t1.deathyear - t1.birthyear desc limit 1

Question: What are the first and last names of the artists who have not only medium oil paintings but also paintings with the lithographic medium?
SQL: SELECT T1.lname ,  T1.fname FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T2.medium  =  "oil" INTERSECT SELECT T3.lname ,  T3.fname FROM artists AS T3 JOIN paintings AS T4 ON T3.artistID  =  T4.painterID WHERE T4.medium  =  "lithograph"

Question: What are the location and medium type of paintings that are created by the artist whose first name is Pablo?
SQL: SELECT T2.location ,   T2.medium FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T1.fname  =  "Pablo"

Question: What is the name of every distinct painting in alphabetical order?
SQL: SELECT DISTINCT title FROM paintings ORDER BY title

Question: What is the id of the artist with the most paintings before 1900?
SQL: SELECT T1.artistID FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T2.year  <  1900 GROUP BY T1.artistID ORDER BY count(*) DESC LIMIT 1

Question: When did each artist who created less than 4 paintings die ?
SQL: select t1.deathyear from artists as t1 join paintings as t2 on t1.artistid  =  t2.painterid group by t2.painterid having count(*)  <  4

Question: List the ids of all paintings that are taller than the longest painting created after 1900.
SQL: SELECT paintingID FROM paintings WHERE height_mm  >   (SELECT max(height_mm) FROM paintings WHERE YEAR  >  1900)

Question: List the title and location of all paintings.
SQL: SELECT title ,  LOCATION FROM paintings

Question: What is the title and location of the oldest painting ?
SQL: select title ,  location from paintings order by year limit 1

Question: Find the first names and number of works of all artists who have at least two paintings?
SQL: SELECT T1.fname ,  count(*) FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID GROUP BY T2.painterID HAVING count(*)  >=  2

Question: What are the ids of the paintings created before all of the paintings in gallery 240?
SQL: SELECT paintingID FROM paintings WHERE YEAR  <   (SELECT min(YEAR) FROM paintings WHERE LOCATION  =  'Gallery 240')

Question: List the painting id, location and title of the medium oil paintings ordered by year.
SQL: SELECT paintingID ,  title ,  LOCATION FROM paintings WHERE medium  =  "oil" ORDER BY YEAR

Question: Find the names of all sculptures located in gallery 226.
SQL: SELECT title FROM sculptures WHERE LOCATION  =  "Gallery 226"

Question: Tell me the height and id number of the widest painting in gallery 240.
SQL: SELECT paintingID ,  height_mm FROM paintings WHERE LOCATION  =  'Gallery 240' ORDER BY width_mm DESC LIMIT 1

Question: Find the first and last names of the artists who have both works of paintings and sculptures?
SQL: SELECT T1.lname ,  T1.fname FROM artists AS T1 JOIN sculptures AS T2 ON T1.artistID  =  T2.sculptorID INTERSECT SELECT T3.lname ,  T3.fname FROM artists AS T3 JOIN paintings AS T4 ON T3.artistID  =  T4.painterID

Question: What are the titles of paintings that have a width less than 600 or a height taller taller than 800?
SQL: SELECT title FROM paintings WHERE width_mm  <  600 OR height_mm  >  800

Question: What are the average height of paintings for different medium types?
SQL: SELECT avg(height_mm) ,  medium FROM paintings GROUP BY medium

Question: What are the first and last names of artists who have painted using both oil and lithographic mediums?
SQL: SELECT T1.lname ,  T1.fname FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T2.medium  =  "oil" INTERSECT SELECT T3.lname ,  T3.fname FROM artists AS T3 JOIN paintings AS T4 ON T3.artistID  =  T4.painterID WHERE T4.medium  =  "lithograph"