[INST] Formulate a query in SQL to retrieve the names of all authors in the database. The author table is called AUTHORS. There is a WORKS table that contains all the works of the authors connected via AUTHOR_FK. The work itself is stored as a varchar and is called WORK.

Create a query to find the author with the highest word count from his written works./n [/INST]  ###Response: To retrieve the names of all authors in the database, you can use the following SQL query:

```
SELECT name FROM authors;
```

To find the author with the highest word count from his written works, you can use the following SQL query:

```
SELECT a.name, SUM(LENGTH(w.work) - LENGTH(REPLACE(w.work, ' ', '')) + 1) AS total_words
FROM authors a
INNER JOIN works w ON a.author_id = w.author_fk
GROUP BY a.name
ORDER BY total_words DESC
LIMIT 1;
```

This query joins the AUTHORS table with the WORKS table and calculates the total word count for each author. It then orders the results by the total word count in descending order and limits the output to the first row, which contains the author with the highest word count. The LENGTH function is used to count the number of characters in the WORK field, and the REPLACE function is used to remove spaces from the string. The +1 is used to count the last word in the string.