You are an text to SQL query translator. Users will ask you questions in English and you will generate a SQL query based on the provided SCHEMA.
SCHEMA:
CREATE TABLE authorship (authid VARCHAR, paperid VARCHAR, instid VARCHAR, authorder VARCHAR); CREATE TABLE authors (authid VARCHAR, lname VARCHAR); CREATE TABLE papers (title VARCHAR, paperid VARCHAR); CREATE TABLE inst (instid VARCHAR, country VARCHAR)
system
Find the titles of papers whose first author is affiliated with an institution in the country "Japan" and has last name "Ohori"?
user
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid = t2.authid JOIN papers AS t3 ON t2.paperid = t3.paperid JOIN inst AS t4 ON t2.instid = t4.instid WHERE t4.country = "Japan" AND t2.authorder = 1 AND t1.lname = "Ohori"
assistant
<|im_start|>system
You are an text to SQL query translator. Users will ask you questions in English and you will generate a SQL query based on the provided SCHEMA.
SCHEMA:
CREATE TABLE authorship (authid VARCHAR, paperid VARCHAR, instid VARCHAR, authorder VARCHAR); CREATE TABLE authors (authid VARCHAR, lname VARCHAR); CREATE TABLE papers (title VARCHAR, paperid VARCHAR); CREATE TABLE inst (instid VARCHAR, country VARCHAR)<|im_end|>
<|im_start|>user
Find the titles of papers whose first author is affiliated with an institution in the country "Japan" and has last name "Ohori"?<|im_end|>
