Extra challenges (optional)
Last updated on 2023-05-02 | Edit this page
- Are there extra challenges to practice translating plain English queries to SQL queries?
- Extra challenges to practice creating SQL queries.
SQL queries help us ask specific questions which we want to answer about our data. The real skill with SQL is to know how to translate our questions into a sensible SQL queries (and subsequently visualise and interpret our results).
Have a look at the following questions; these questions are written in plain English. Can you translate them to SQL queries and give a suitable answer?
Also, if you would like to learn more SQL concepts and try additional challenges, see the Software Carpentry Databases and SQL lesson.
SELECT First_Author, COUNT( * ) AS n_articles FROM articles GROUP BY First_Author ORDER BY n_articles DESC;
SELECT Author_Count, COUNT( * ) FROM articles GROUP BY Author_Count;
SELECT Language, COUNT( * ) FROM articles JOIN languages ON articles.LanguageId=languages.id WHERE Language != '' GROUP BY Language;
SELECT Licence, AVG( Citation_Count ), COUNT( * ) FROM articles JOIN licences ON articles.LicenceId=licences.id WHERE Licence != '' GROUP BY Licence;
SELECT Title, First_Author, Author_Count, Citation_Count, Month, Year, Journal_Title, Publisher FROM articles JOIN journals ON articles.issns=journals.ISSNs JOIN publishers ON publishers.id=journals.PublisherId;