w3resource

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:

Query visualization of Find all the details of 1970 winners by the ordered to subject and winner name; but the list contain the subject Economics and Chemistry at last - Duration

Rows:

Query visualization of Find all the details of 1970 winners by the ordered to subject and winner name; but the list contain the subject Economics and Chemistry at last - Rows

Cost:

Query visualization of Find all the details of 1970 winners by the ordered to subject and winner name; but the list contain the subject Economics and Chemistry at last - 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.



Follow us on Facebook and Twitter for latest update.

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

 





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