Writing SQL query for music database

Question

I am not very good at SQL and i dont know if I am writing these queries correctly. My answer for each question is provided below each question. Any tips/corrections greatly appreciated.

For both question, i dont know if i wrote my WHERE clause correctly with the nested SELECT.

Find all tracks of type audio (MediaType.name equal to "audio") which have length longer than the average length of all other tracks with the same type

my answer:

SELECT *

FROM Track INNER JOIN MediaType

ON Track.MediaTypeId = MediaType.MediaTypeId

WHERE Track.Milliseconds > (SELECT AVG(Milliseconds) FROM Track)

AND MediaType.name = 'audio'

Find name(s) of the customer(s) from US that do not belong to a company and who have overall paid the most of any US customers

my answer:

SELECT FirstName, LastName

FROM Customer INNER JOIN Invoice

ON Customer.CustomerId = Invoice.CustomerId

WHERE Customer.Country = 'US' AND Customer.Company = null AND Invoice.Total = (SELECT MAX(total) FROM Invoice)

If your code performs what you want it to, the question is *off-topic* here and should be asked on [https://codereview.stackexchange.com/](https://codereview.stackexchange.com/). If it doesn't perform what you want it to, you have to specify what are the differences between expected results and actual results, as well as any errors your code might output. — tao, May 12 '21 at 13:16

gene · Answer 1 · 2021-05-10T23:00:06.413

1

I would recommend that you use is null instead of = null in the second query if customers who do not belong to a company have customer null in the Customer.Company column.

Check out this thread: SQL is null and = null

edited May 10 '21 at 23:00

answered May 10 '21 at 22:54

gene

41
3

Thank you for that suggestion. Regarding the correctness of my code with respect to the questions and schema, in your opinion does my answers satisfy the question requirements? – Timothy Anderson May 10 '21 at 23:06
Yes, otherwise this solution looks correct. Please upvote. – gene May 10 '21 at 23:12

score 0 · Answer 2 · answered May 11 '21 at 03:46

Your first answer mentioned :

...which have length longer than the average length of all other tracks WITH THE SAME TYPE

So your subquery in where condition should be AVG(Milliseconds) of 'Audio Type'

SELECT *

FROM Track INNER JOIN MediaType

ON Track.MediaTypeId = MediaType.MediaTypeId

WHERE Track.Milliseconds > (SELECT AVG(Milliseconds) FROM Track INNER JOIN MediaType

ON Track.MediaTypeId = MediaType.MediaTypeId WHERE MediaType = 'audio')

AND MediaType.name = 'audio'

Customer.Company = null always returns FALSE. You have to change it to Customer.Company is null

Writing SQL query for music database

2 Answers2