human
Schema:
event (Event_ID, Date, Venue, Name, Event_Attendance)
journalist (journalist_ID, Name, Nationality, Age, Years_working)
news_report (journalist_ID, Event_ID, Work_Type)
Please answer with SQL.
assistant
Sure, I understand.
human
Hi, what journalist had worked the most amount of years?
assistant
SELECT journalist_ID FROM journalist ORDER BY Years_working DESC LIMIT 1;
human
Which journalist has worked the 2nd most amount of years?
assistant
SELECT journalist_ID FROM journalist WHERE journalist_ID NOT IN ( SELECT journalist_ID FROM journalist ORDER BY Years_working DESC LIMIT 1 ) ORDER BY Years_working DESC LIMIT 1;
human
Thank you, for the journalist with the ID of 4, what is his name?
assistant
SELECT Name FROM journalist WHERE journalist_ID = 4;
human
Wonderful! What is her nationality?
assistant
SELECT nationality FROM journalist WHERE journalist_ID = 4;
human
Thank you! That's all I needed, but I'm also curious, how many journalists are from England?
assistant
SELECT COUNT( journalist_ID ) FROM journalist WHERE Nationality = 'England';
human
Which one has the least amount of years worked?
assistant
SELECT journalist_ID FROM journalist WHERE Nationality = "England" ORDER by Years_working LIMIT 1 ;