CUSTOMERS \begin{tabular}{|l|l|l|l|l|l|l|l|l|l|l|} \hline Customerld & FirstName & LastName & Address & City & Region & Country & PostalCode & Phone & Email \\ \hline \end{tabular} INVOICES \begin{tabular}{|l|l|l|l|l|l|l|l|l|l|} \hlineInvoiceld & Customerld & InvoiceDate & BillingAddress & BillingCity & BillityState & BillingPostalCode & BillingCountry & Total \\ \hline \end{tabular} INVOICE_ITEMS ALBUMS a) Retrieve the names of artists which contain the sequence of characters 'ce'. Sort the result-set by a descending order of Artistid. [3 marks] Query: Retrieve only the track ids for tracks that have a track name which ends with the character "o". Hint: Oracle uses single quotes for string literals comparison (in case you encounter "invalid identifier" error when running SQL statement. [2 marks] Query: Retrieve only the track ids for tracks that have a track name which ends with the character "o". Hint: Oracle uses single quotes for string literals comparison (in case you encounter "invalid identifier" error when running SQL statement. [2 marks] Query: Retrieve the total number of items purchased (i.e. items that are part of an invoice) for the invoice with Invocield of 157. Name the returned column in the result-set as "Total Items (Invoiceld 157)". Hint: Oracle uses the double quotes for the renaming of column names. Consider using the AS clause for aliasing column names. [4 marks] Tracks table contains a column called Milliseconds which indicates the duration of a song in each track. We would like to perform a quick analysis by identifying the average duration for all tracks, the lowest and highest duration among all tracks in the TRACKS table. Use the names "Average Duration", "Longest Duration" and "Shortest Duration", as the column names of the result-set, respectively. The average must be rounded to 2 decimal points. To round results of aggregate functions, you can use the ROUND function. Hint: Oracle uses the double quotes for the renaming of column names. Consider using the AS clause for aliasing column names. [5 marks] As a musicStore administrator, you would like to identify artists that have produced three or more albums. Write a SQL statement that will determine artists that have three or more albums produced. As part of the result-set, include the name of the artist and the total number of albums produced. Consider using aliasing to avoid possible column ambiguity. Use renaming to make the column names in the result-set more meaningful. Hint: Oracle does not require AS clause for aliasing column names (e.g. CUSTOMERS C and not CUSTOMERS AS C). [4 marks] As a musicStore administrator, you are trying to identify the bestselling albums. That is, you would like to determine the total sales for each album. Hence, write a SQL statement that will compute the total sales for each album. As part of the result-set, include the identifier of the album, the title of the album and the name of the artist. Sort the results by a descending order of the sale values from highest to lowest. Hint: Consider using GROUP BY (see Oracle documentation for details). Use a proper column aliasing for cumulative total (e.g. "Total Sales"). [5 marks] As musicStore administrator, you wish to identify the number of tracks for each genre. Write a SQL statement that will return a result-set containing the genre (name) and the total number of tracks for each genre. Sort the results in an ascending order of genre (name). Use a proper column aliasing for cumulative total (e.g. "Number of Tracks"). [2 marks] Assume as a musicStore owner, you wish to reward customers who have invoices with a total amount that is more than the average invoice amount across all customers with a 10% discount. To this extent, write a SQL statement that will return a list of the email addresses of all customers who have an invoice whose total is more than the average of the total amount of all invoices of all customers. Hint: Nesting is a great way for finding information needed and then use this information across multiple SQL statements. [2 marks] Querv: Write a SQL statement that returns the artist id and artist name that is associated with the tack having the identifier (i.e. Trackld) of 1601. Consider using aliasing to avoid possible column ambiguity. [3 marks] Query