Rupam created a MySQL table to store the details of Nobel prize winners. Help her to write the following MySQL queries :
TABLE : NOBEL
| Winner_Id | YEAR | SUBJECT | WINNER | COUNTRY | CATEGORY |
|---|---|---|---|---|---|
| 1001 | 1970 | Physics | Hannes Alfven | Sweden | Scientist |
| 1002 | 1970 | Physiology | Bernard Katz | NULL | Scientist |
| 1003 | 1970 | Literature | Aleksandr Solzhenitsyn | Russia | Linguist |
| 1004 | 1971 | Chemistry | Gerhard Herzberg | Germany | Scientist |
| 1005 | 1978 | Peace | Menachem Begin | Israel | Prime Minister |
| 1006 | 1987 | Economics | Robert Solow | USA | Economist |
| 1007 | 1994 | Literature | Kenzaburo Oe | Japan | Linguist |
- Display the names of Nobel Prize winner in 'Literature' for the year 1970.
- Display the subject and category of winners whose country is not known.
- Display the details of all Nobel Prize winners who were Scientists.
- Count total number of winners whose subject is Literature.
You're viewing a preview — the full solution, concept, methods & PYQ mapping are locked.
Start your 14-day free trial to unlock the full solution →These MySQL queries retrieve specific Nobel Prize winner details, filter by criteria like subject, year, country, and category, and count entries based on conditions.
Rupam, when working with databases like MySQL, the ability to retrieve specific pieces of information from your tables is incredibly powerful. This is where SQL (Structured Query Language) comes in. Think of your NOBEL table as a meticulously organized ledger; SQL queries are the precise instructions you give to the database to find, filter, and present the data exactly as you need it. Each query starts with the SELECT command, which tells the database what columns you want to see, and often includes a WHERE clause to specify the conditions that rows must meet to be included in your result.
Let's break down how to write the queries you need for your NOBEL table.
(i) Display the names of Nobel Prize winner in 'Literature' for the year 1970.
To get the names of winners, we need to select the WINNER column. Then, we apply two conditions simultaneously: the SUBJECT must be 'Literature' AND the YEAR must be 1970. We link these conditions using the AND operator.
SELECT WINNER
FROM NOBEL
WHERE SUBJECT = 'Literature' AND YEAR = 1970;
SQL is case-insensitive for keywords like SELECT, FROM, WHERE, AND, but string literals (like 'Literature' or '1970') are usually case-sensitive depending on the database's collation settings. It's best practice to match the case as it appears in your data.
(ii) Display the subject and category of winners whose country is not known.
Sometimes, data might be missing or unknown, represented by NULL in a database. To find entries where the COUNTRY is not specified, we use the IS NULL operator. We want to see the SUBJECT and CATEGORY for these specific winners.
SELECT SUBJECT, CATEGORY
FROM NOBEL
WHERE COUNTRY IS NULL;
When checking for missing values, always use IS NULL instead of = NULL. The = operator is used for comparing actual values, but NULL represents an absence of value, so it requires a special comparison.
(iii) Display the details of all Nobel Prize winners who were Scientists.
If you want to see all the information available for certain rows, you can use the asterisk (*) in your SELECT statement. This tells the database to return every column for the rows that match your criteria. Here, the criterion is that the CATEGORY must be 'Scientist'.
SELECT *
FROM NOBEL …
Unlock everything free for 14 days
- Full step-by-step solutions
- Concept-first explanations
- Methods, shortcuts & mistakes
- PYQ mapping + timed mock tests
Full access for 14 days. No credit card required.