Mastering SQL with SQLZoo: A Comprehensive Guide to Solving SQL Challenges: SELECT from Nobel

Welcome to My in-depth guide on solving SQL challenges with SQLZoo! Whether you’re a budding data enthusiast, a seasoned developer, or just someone keen to sharpen your SQL skills, this blog is tailored for you. SQLZoo is a renowned interactive platform that offers a wide range of exercises designed to test and improve your understanding of SQL.

In this blog, I will walk you through various SQLZoo challenges, providing detailed solutions and explanations for each query. My aim is to help you grasp the nuances of SQL, from basic SELECT statements to more complex JOINs and subqueries. By following along, you will not only learn how to write efficient SQL queries but also gain insights into best practices and common pitfalls to avoid.

Here’s what you can expect:

1. Step-by-Step Solutions: We will break down each problem, explaining the logic behind the query and the reasoning for each step.
2. Comprehensive Explanations: Each solution will be accompanied by thorough explanations to ensure you understand the underlying concepts.
3. Practical Tips: Along the way, we’ll share tips and tricks to help you write cleaner, more efficient SQL code.
4. Varied Difficulty Levels: The challenges range from beginner to advanced, making this guide suitable for learners at any stage.

Join us on this journey to master SQL and become proficient in handling real-world data tasks with confidence. Let’s dive into the world of SQLZoo and start solving some exciting challenges!

Previous Blog Posts

0 SELECT basics Solutions

1 SELECT from WORLD Solutions

2 Select From Nobel

1. Winners from 1950

Change the query shown so that it displays Nobel prizes for 1950.

SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950

Explanation: This query selects the year (yr), subject (subject), and winner (winner) from the nobel table where the year is 1950. It retrieves the Nobel Prize details for that specific year.

2. 1962 Literature

Show who won the 1962 prize for literature.

SELECT winner
FROM nobel
WHERE yr = 1962 AND subject = 'literature'

Explanation: This query selects the winner of the Nobel Prize from the nobel table where the year is 1962 and the subject is 'literature'. It retrieves the Nobel Prize winner in literature for that year.

3. Albert Einstein

Show the year and subject that won ‘Albert Einstein’ his prize.

SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein'

Explanation: This query selects the year and subject from the nobel table where the winner is 'Albert Einstein'. It retrieves the year and subject of the Nobel Prize won by Albert Einstein.

4. Recent Peace Prizes

Give the name of the ‘peace’ winners since the year 2000, including 2000.

SELECT winner
FROM nobel
WHERE subject LIKE '%peace%' AND yr >= 2000

Explanation: This query selects the winners from the nobel table where the subject contains 'peace' and the year is greater than or equal to 2000. It retrieves the winners of the Nobel Peace Prize from the year 2000 onwards.

5. Literature in the 1980's

Show all details (yr, subject, winner) of the literature prize winners for 1980 to 1989 inclusive.

SELECT yr, subject, winner
FROM nobel
WHERE yr BETWEEN 1980 AND 1989 AND subject = 'literature'

Explanation: This query selects the year, subject, and winner from the nobel table where the year is between 1980 and 1989 and the subject is 'literature'. It retrieves the Nobel Prize winners in literature for that decade.

6. Only Presidents

Show all details of the presidential winners:

• Theodore Roosevelt
• Thomas Woodrow Wilson
• Jimmy Carter
• Barack Obama

SELECT *
FROM nobel
WHERE winner IN ('Theodore Roosevelt',
                 'Thomas Woodrow Wilson', 
                 'Jimmy Carter',
                 'Barack Obama')

Explanation: This query selects all columns from the nobel table where the winner is either 'Theodore Roosevelt', 'Thomas Woodrow Wilson', 'Jimmy Carter', or 'Barack Obama'. It retrieves the Nobel Prize details for these specific winners.

7. John

Show the winners with first name John

SELECT winner
FROM nobel
WHERE winner LIKE 'John %'

Explanation: This query selects the winners from the nobel table where the winner's name starts with 'John'. It retrieves all winners whose names start with 'John'.

8.Chemistry and Physics from different years

Show the year, subject, and name of physics winners for 1980 together with the chemistry winners for 1984.

SELECT yr, subject, winner
FROM nobel
WHERE (subject = 'physics' AND yr = 1980) OR 
      (subject = 'chemistry' AND yr = 1984)

Explanation: This query selects the year, subject, and winner from the nobel table where the subject is 'physics' and the year is 1980 or the subject is 'chemistry' and the year is 1984. It retrieves the Nobel Prize details for these specific conditions.

9. Exclude Chemists and Medics

Show the year, subject, and name of winners for 1980 excluding chemistry and medicine

SELECT yr, subject, winner
FROM nobel
WHERE yr = 1980 AND subject NOT IN ('chemistry', 'medicine')

Explanation: This query selects the year, subject, and winner from the nobel table where the year is 1980 and the subject is not 'chemistry' or 'medicine'. It retrieves the Nobel Prize details for the year 1980 excluding those in chemistry and medicine.

10. Early Medicine, Late Literature

Show year, subject, and name of people who won a ‘Medicine’ prize in an early year (before 1910, not including 1910) together with winners of a ‘Literature’ prize in a later year (after 2004, including 2004)

SELECT yr, subject, winner
FROM nobel
WHERE (subject = 'Medicine' AND yr < 1910) OR 
      (subject = 'Literature' AND yr >= 2004)

Explanation: This query selects the year, subject, and winner from the nobel table where the subject is 'Medicine' and the year is before 1910 or the subject is 'Literature' and the year is 2004 or later. It retrieves the Nobel Prize details for these specific conditions.

11. Umlaut

Find all details of the prize won by PETER GRÜNBERG

SELECT *
FROM nobel
WHERE winner = 'PETER GRÜNBERG'

Explanation: This query selects all columns from the nobel table where the winner is 'PETER GRÜNBERG'. It retrieves the Nobel Prize details for Peter Grünberg.

12. Apostrophe

Find all details of the prize won by EUGENE O’NEILL

SELECT *
FROM nobel
WHERE winner = 'EUGENE O\'NEILL'

Explanation: This query selects all columns from the nobel table where the winner is 'EUGENE O'NEILL'. It retrieves the Nobel Prize details for Eugene O'Neill.

13. Knights of the realm

Knights in order

List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.

SELECT winner, yr, subject
FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC, winner ASC

Explanation: This query selects the winner, year, and subject from the nobel table where the winner's name starts with 'Sir'. It orders the results by year in descending order and winner in ascending order. It retrieves and sorts the Nobel Prize winners with titles starting with 'Sir'.

14. Chemistry and Physics last

The expression subject IN (‘chemistry’,’physics’) can be used as a value — it will be 0 or 1.

Show the 1984 winners and subject ordered by subject and winner name; but list chemistry and physics last.

SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY subject IN ('physics', 'chemistry'), subject, winner

Explanation: This query selects the winner and subject from the nobel table where the year is 1984. It orders the results by whether the subject is 'physics' or 'chemistry' first, then by subject, and finally by winner. It prioritizes physics and chemistry subjects for sorting, followed by alphabetical ordering of the subjects and winners.

Thank you for joining me in this SQL adventure! I hope you found the solutions and explanations in SELECT from World insightful and engaging. But we’re not stopping here. There’s so much more to explore and learn!

In SELECT within SELECT , we will delve deeper into more complex SQL challenges on SQLZoo.
Thanks :)