human
Schema:
Inst (instID, name, country)
Authors (authID, lname, fname)
Papers (paperID, title)
Authorship (authID, instID, paperID, authOrder)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the titles of papers whose first author is affiliated with an institution in the country 'Japan' and has last name 'Ohori'?
assistant
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'
human
Find the first names of all the authors who have written a paper with title containing the word 'Functional'.
assistant
SELECT t1.fname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid = t2.authid JOIN papers AS t3 ON t2.paperid = t3.paperid WHERE t3.title LIKE '%Functional%'
human
Find the name of the organization that has published the largest number of papers.
assistant
SELECT t1.name FROM inst AS t1 JOIN authorship AS t2 ON t1.instid = t2.instid JOIN papers AS t3 ON t2.paperid = t3.paperid GROUP BY t1.name ORDER BY count(*) DESC LIMIT 1
human
Retrieve the title of the paper that has the largest number of authors.
assistant
SELECT t2.title FROM authorship AS t1 JOIN papers AS t2 ON t1.paperid = t2.paperid WHERE t1.authorder = (SELECT max(authorder) FROM authorship)
human
Find the titles of the papers that contain the word 'ML'.
assistant
SELECT title FROM papers WHERE title LIKE '%ML%'
human
Which papers have the substring 'Database' in their titles? Show the titles of the papers.
assistant
SELECT title FROM papers WHERE title LIKE "%Database%" ;