w3resource
Run SQL Query

SQL: Find Nobel Prize winners for the subject that does not begin with the letter 'P'

SQL Basic Select Statement: Exercise-23 with Solution.

From the following table, write a SQL query to find Nobel Prize winners for the subject that does not begin with the letter 'P'. Return year, subject, winner, country, and category. Order the result by year, descending and winner in ascending.

Sample table : nobel_win

 

Sample Solution :

SELECT * 
 FROM nobel_win 
  WHERE subject NOT LIKE 'P%' 
   ORDER BY year DESC, winner;

Output of the Query:

year	subject	   winner			country	   	category
1994	Literature Kenzaburo Oe	      		Japan	  	Linguist
1994	Economics  Reinhard Selten		Germany	  	Economist
1987	Chemistry  Donald J. Cram		USA		Scientist
1987	Chemistry  Jean-Marie Lehn		France	  	Scientist
1987	Literature Joseph Brodsky		Russia	  	Linguist
1987	Economics  Robert Solow			USA		Economist
1971	Chemistry  Gerhard Herzberg    		Germany 	Scientist
1971	Literature Pablo Neruda			Chile	  	Linguist
1971	Economics  Simon Kuznets		Russia	  	Economist
1970	Literature Aleksandr Solzhenitsyn 	Russia 		Linguist
1970	Chemistry  Luis Federico Leloir 	 France		Scientist
1970	Economics  Paul Samuelson		USA		Economist

Relational Algebra Expression:

Relational Algebra Expression: Find all the details of the nobel winners for the subject not started with the letter 'P' and arranged the list as the  most recent comes first, then by name in order.

Relational Algebra Tree:

Relational Algebra Tree: Find all the details of the nobel winners for the subject not started with the letter 'P' and arranged the list as the  most recent comes first, then by name in order.

Practice Online


Query Visualization:

Duration:

Query visualization of Find all the details of the nobel winners for the subject not started with the letter 'P' and arranged the list as the most recent comes first, then by name in order - Duration

Rows:

Query visualization of Find all the details of the nobel winners for the subject not started with the letter 'P' and arranged the list as the most recent comes first, then by name in order - Rows

Cost:

Query visualization of Find all the details of the nobel winners for the subject not started with the letter 'P' and arranged the list as the most recent comes first, then by name in order - Cost

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

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

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Can a foreign key be NULL and/or duplicate?

First remember a Foreign key simply requires that the value in that field must exist first in a different table (the parent table). That is all an Foreign key is by definition. Null by definition is not a value. Null means that we do not yet know what the value is.

Ref: https://bit.ly/3uVu3OS