SQL Exercises: Economics, Chemistry listed at end of 1970 as winners

SQL Basic Select Statement: Exercise-24 with Solution.

From the following table, write a SQL query to find the details of 1970 Nobel Prize winners. Order the results by subject, ascending except for 'Chemistry' and ‘Economics’ which will come at the end of the result set. Return year, subject, winner, country, and category.

Sample table: nobel_win

Sample Solution :

SELECT *
FROM nobel_win
WHERE year=1970 
ORDER BY
 CASE
    WHEN subject IN ('Economics','Chemistry') THEN 1
    ELSE 0
 END ASC,
 subject,
 winner;

Output of the Query:

year	subject		winner				country		category
1970	Literature	Aleksandr Solzhenitsyn		Russia		Linguist
1970	Physics		Hannes Alfven			Sweden		Scientist
1970	Physics		Louis Neel			France		Scientist
1970	Physiology	Bernard Katz			Germany		Scientist
1970	Physiology	Julius Axelrod			USA		Scientist
1970	Physiology	Ulf von Euler			Sweden		Scientist
1970	Chemistry	Luis Federico Leloir		France		Scientist
1970	Economics	Paul Samuelson			USA		Economist

Code Explanation:

The given SQL query retrieves all columns from the 'nobel_win' table where the year is 1970. The query will return all rows that match this criteria. The output will be sorted based on the following order:
First, it will sort the rows where the subject is either 'Economics' or 'Chemistry' and put them in the first group and sort them by ascending order.
Next, it will sort the remaining rows by subject in ascending order.
Lastly, it will sort the remaining rows alphabetically based on the name of the winner in ascending order.
The ORDER BY clause uses a CASE statement to evaluate the subject column, if the subject is 'Economics' or 'Chemistry' it will return 1 and for all other subjects it will return 0. This will sort the rows with 'Economics' or 'Chemistry' as the subject in the first group and all other subjects in the second group.
In the end, the output will be a list of all Nobel Prizes awarded in 1970, with the rows where the subject or the winner's name is either 'Economics' or 'Chemistry' listed first, followed by all other rows sorted by their subject and name.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous Python Exercise: Subjects not start with P, sorted by most recent, name.
Next Python Exercise: Find all products between Rs.200 and Rs.600.