SQL

In this question, you are required to use the sample database of an online music store (the one we used in the workshop) to answer a few business questions with SQL queries.

Provide SQL queries that can answer the following questions:

1) (4 pts) How many songs are larger than 20MB?

2) (5 pts) Is there any customer from PA (Pennsylvania)?

Hint: there is an implied restriction - what if in some other country (other than the USA), there is also a state named PA?

3) (6 pts) Find the average song length (in minutes) by GenreId.

Hint: the aggregate function is avg().

Note:

a. There might be more than one correct answer.

b. The tables that you need to use (and key columns related questions are underlined) are:

• Track: contains detailed information about each song, with the following columns
  • Name
  • AlbumId
  • MediaTypeId
  • GenreId
  • Composer
  • Milliseconds: the length for each song
  • Bytes: file size for each song
  • UnitPrice
• Customer: contains information about each customer, such as
  • customer name
  • address information: company, address, city, state, country, postalcode
  • contacts: phone, fax, email

View keyboard shortcuts

EditViewInsertFormatToolsTable

12pt

Paragraph