"The Met", SQL: Intermediate

I am just wondering the following,

Project:
https://www.codecademy.com/paths/data-science/tracks/sql-intermediate/modules/dspath-sql-aggregates-projects-and-applications/projects/sql-the-met

Question: 7

When I write the following code:

SELECT CASE
WHEN medium LIKE ‘%gold%’ THEN ‘Gold’
WHEN medium LIKE ‘%silver%’ THEN ‘Silver’
ELSE NULL
END AS ‘Bling’,
COUNT(*)
FROM met
WHERE Bling IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;


What happens if I have a piece of art that has silver and gold, does this code double counts that piece? if so, how would you write a code that excludes silver in gold and gold in silver, I guess you would have to also add a new CASE counting pieces with both, silver and gold

1 Like

Greetings. I thought about your question and I experimented with the command “NOT LIKE”.
Try this for example:

SELECT * FROM met
LIMIT 20;

Getting the following results;

Imagine now that we want to query for titles including the word ‘Dollar’ but not the word 'Two".

SELECT * FROM met
WHERE title LIKE ‘%dollar%’ AND title NOT LIKE ‘%two%’
LIMIT 20;

I believe this statement would do the trick.

SELECT CASE
WHEN (medium LIKE ‘%gold%’ AND medium NOT LIKE ‘%silver%’) THEN ‘Gold’
WHEN (medium LIKE ‘%silver%’ AND medium NOT LIKE ‘%gold%’) THEN ‘Silver’
ELSE NULL
END AS ‘Bling’, COUNT(*)
FROM met

Of course there is no way to test this on the table ‘met’ provided. At least when I run this, I receive the same results as with the Solution Code provided in the exercise.

Hope this helps. If you can test it somewhere else please let me know.