I’m not a fan of SQL. I always found the syntax to be clumsy and tiresome. Compared to modern coding languages it seems arcane with basic instruction and primitive types. I could have spent more time learning it I suppose but I never wanted to, and I think my time was better spend elsewhere. (To be clear, I do have a grasp on designing databases at a high level, but the actual SQL syntax escapes me)
So I was delighted the to notice that DataGrip from JetBrains now has an AI assistant panel. I connected it to a local model and tried to see if it could write some SQL.
The problem background:
- I have a table Work – it has details on a book; stuff like title, author etc
- I have an ISBN table – it links a particular ISBN to a work. Most Works have only 2 ISBNs, but some times they have more (when a new edition comes out for example)
My prompt
I am using Postgres. I would like to run a query where I get all of the Works that have more than 2 ISBNs defined in the ISBN table. For each work I would like the id, title, author and all the isbns.The result
SELECT
w.id,
w.title,
w.author,
STRING_AGG(i.isbn, ', ') AS ISBNs
FROM works w
JOIN isbns i ON w.id = i.work_id
GROUP BY w.id, w.title, w.author
HAVING COUNT(i.isbn) > 2;
running it yields
My take – Its better than I would write. Firstly I never knew the function STRING_AGG existed; it seems to like a ",".join() in python. Second, the rest is very readable and efficient. I know I would have spent a long time trying to put the syntax together, and honestly I’d probably have used subqueries
Takeaway – SQL will become much easier for me to use in the future and with the AI assistant I might have found a syntax teacher.