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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
What is the best way to paginate results in SQL Server?
SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, * FROM Orders WHERE OrderDate >= '1980-01-01' ) AS RowConstrainedResult WHERE RowNum >= 1 AND RowNum < 20 ORDER BY RowNum
Database: SQL Server
Ref: https://bit.ly/3MGrNlk
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook