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: Find the first and last names of all artists who were born after 1850.
SQL: SELECT lname ,  fname FROM artists WHERE birthYear  >  1850

Question: What is the painting count of the artist with the longest life ?
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 average height and width of paintings that are oil medium in the place of gallery 241?
SQL: SELECT avg(height_mm) ,  avg(width_mm) FROM paintings WHERE medium  =  "oil" AND LOCATION  =  "Gallery 241"

Question: Find the death year of the artist who made the least number of sculptures?
SQL: SELECT T1.deathYear FROM artists AS T1 JOIN sculptures AS T2 ON T1.artistID  =  T2.sculptorID GROUP BY T2.sculptorID ORDER BY count(*) LIMIT 1

Question: List the names of all distinct paintings in alphabetical order.
SQL: SELECT DISTINCT title FROM paintings ORDER BY title

Question: Find the birth years of all distinct artists who made sculptures after 1920?
SQL: SELECT DISTINCT T1.birthYear FROM artists AS T1 JOIN sculptures AS T2 ON T1.artistID  =  T2.sculptorID WHERE T2.year  >  1920

Question: What are the first and last names of the artists who did not sculpt but could paint.
SQL: SELECT T1.lname ,  T1.fname FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID EXCEPT SELECT T3.lname ,  T3.fname FROM artists AS T3 JOIN sculptures AS T4 ON T3.artistID  =  T4.sculptorID

Question: What are the names of all sculptures in gallery 226?
SQL: SELECT title FROM sculptures WHERE LOCATION  =  "Gallery 226"

Question: What is the height and id of the tallest painting created before 1900?
SQL: SELECT max(height_mm) ,  paintingID FROM paintings WHERE YEAR  <  1900

Question: Where do you have paintings that were created before 1885 that are not on canvas?
SQL: SELECT DISTINCT LOCATION FROM paintings WHERE YEAR  <  1885 AND mediumOn != "canvas"

Question: What are the locations that have paintings in the mediums of on panels and on canvas?
SQL: SELECT DISTINCT LOCATION FROM paintings WHERE mediumOn  =  "panel" INTERSECT SELECT DISTINCT LOCATION FROM paintings WHERE mediumOn  =  "canvas"

Question: What are the locations that have works painted before 1885 and after 1930?
SQL: SELECT DISTINCT LOCATION FROM paintings WHERE YEAR  <  1885 INTERSECT SELECT DISTINCT LOCATION FROM paintings WHERE YEAR  >  1930

Question: How many paintings were created before 1900 in different locations?
SQL: SELECT count(*) ,  LOCATION FROM paintings WHERE YEAR  <  1900 GROUP BY LOCATION

Question: What is the birth year of the artist who created a painting in 1884 that is on canvas?
SQL: SELECT T1.birthYear FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T2.year  =  1884 AND mediumOn  =  "canvas"

Question: Find the locations that have paintings before 1885 and no work with medium on canvas?
SQL: SELECT DISTINCT LOCATION FROM paintings WHERE YEAR  <  1885 AND mediumOn != "canvas"

Question: What are the widths of the paintings that were created by the artist who was born before 1850?
SQL: SELECT T2.width_mm FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T1.birthYear  <  1850

Question: In what year was the artist who created a painting in 1884 born?
SQL: SELECT T1.birthYear FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T2.year  =  1884 AND mediumOn  =  "canvas"

Question: Give the names of all oil paintings created after 1910.
SQL: SELECT title FROM paintings WHERE YEAR  >  1910 AND medium  =  "oil"

Question: What are first names of the artists with oil paintings in gallery 241?
SQL: SELECT DISTINCT T1.fname FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T2.medium  =  "oil" AND LOCATION  =  "Gallery 241"

Question: Which locations have paintings created before 1885 or after 1930?
SQL: SELECT DISTINCT LOCATION FROM paintings WHERE YEAR  < 1885 OR YEAR  >  1930

Question: Find the top 3 artists who have the biggest number of painting works whose medium is oil?
SQL: SELECT T1.lname ,  T1.fname FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T2.medium  =  "oil" GROUP BY T2.painterID ORDER BY count(*) DESC LIMIT 3

Question: List the year, location and title of paintings whose height is longer than 1000 ordered by title.
SQL: SELECT title ,  LOCATION ,  YEAR FROM paintings WHERE height_mm  >  1000 ORDER BY title

Question: What are the numbers of works for different medium type?
SQL: SELECT count(*) ,  medium FROM paintings GROUP BY medium

Question: Which artists have the most paintings in oil?
SQL: SELECT T1.lname ,  T1.fname FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T2.medium  =  "oil" GROUP BY T2.painterID ORDER BY count(*) DESC LIMIT 3

Question: How wide were the paintings by the artist who was born prior to 1850?
SQL: SELECT T2.width_mm FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T1.birthYear  <  1850

Question: What mediums were used for the painting with id 80 ?
SQL: select medium from paintings where paintingid  =  80

Question: Find 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: What are the numbers of paintings created before 1900 in different places?
SQL: SELECT count(*) ,  LOCATION FROM paintings WHERE YEAR  <  1900 GROUP BY LOCATION

Question: Find the first and last name of artists who have painting but no sculpture work.
SQL: SELECT T1.lname ,  T1.fname FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID EXCEPT SELECT T3.lname ,  T3.fname FROM artists AS T3 JOIN sculptures AS T4 ON T3.artistID  =  T4.sculptorID

Question: List the names of all distinct paintings ordered by length.
SQL: SELECT DISTINCT title FROM paintings ORDER BY height_mm

Question: What is the full name of the artist with a sculpture whose title includes the word "female"?
SQL: SELECT T1.lname ,  T1.fname FROM artists AS T1 JOIN sculptures AS T2 ON T1.artistID  =  T2.sculptorID WHERE T2.title LIKE "%female%"

Question: What are the first and last names of the artist who lived the longest?
SQL: SELECT lname ,  fname FROM artists ORDER BY deathYear - birthYear DESC LIMIT 1

Question: How old is the artist who lived the shortest life?
SQL: SELECT deathYear - birthYear FROM artists ORDER BY deathYear - birthYear LIMIT 1

Question: What are the full names of artists born after 1850?
SQL: SELECT lname ,  fname FROM artists WHERE birthYear  >  1850

Question: What are the names of paintings whose width is less than 600 or height is larger than 800?
SQL: SELECT title FROM paintings WHERE width_mm  <  600 OR height_mm  >  800

Question: What are the first and last names of all distinct artists who made sculptures before 1900?
SQL: SELECT DISTINCT T1.lname ,  T1.fname FROM artists AS T1 JOIN sculptures AS T2 ON T1.artistID  =  T2.sculptorID WHERE T2.year  <  1900

Question: Give the full name of the artist who lived the longest.
SQL: SELECT lname ,  fname FROM artists ORDER BY deathYear - birthYear DESC LIMIT 1