Using the Chinook database and SQL Server Management Studio (SSMS), write SQL queries for the following questions. Submit your answers in a single SQL file to the online portal. Use SQLs commenting syntax to include your name at the top of the file. Also include the question number as a comment before each answer.

1. Find the genres that have an ampersand "&" in the name. (3 rows)

Use the Genre table. Display 2 columns, Name and NewName. NewName is derived from Name, but it will have some of the ampersands removed. If the ampersand has a space before and after it then Replace the ampersand with the word "and", otherwise the name stays the same. Only display those records with an ampersand in the Name column.

2. Display Employee names and Birthdates. (8 rows)

Use the Employee table. Concatenate the first and last name into new column called FullName. Display the Birthdate in 3 different columns called Day, Month and Year. The Month column value should be the full name of the month.

3. Run a query with the following modifications to the Album Title column: (347 rows)

Display Title with all the spaces removed. Name it TitleNoSpaces. Display Title in all upper-case letters. Name it TitleUpperCase. Display Title in reverse order. Name it TitleReverse. Display the character length of the Title column Name it TitleLength. Display the starting position of the first space in the Title column. Name it SpaceLocation.

4. Display the current age in years of Employees. (8 rows)

Display FirstName, LastName, BirthDate, and Age. Age is a column you will have to build from birthdate and the current date. Note: This question is tougher than it looks. I will accept a close answer.

5. Display Title and ShortTitle for Employees. (8 rows)

Use the Employee table. Short title is derived from the Title column but has the first word in the title removed. (e.g. "General Manager" becomes "Manager".) Remove any leading spaces.

6. Display Customer names and initials. (59 rows)

Display FirstName, LastName, Initials. Initials is the customer's initials from his or her first and last name. Order the records by Initials.

7. Display the Name, Phone and Fax numbers for USA customers. (13 Rows)

Use the FirstName, LastName, Phone and Fax columns. For Phone and Fax remove the international code "+1" and replace the dash "-" with a space. If the Fax number is NULL replace it with the value "Unknown". Order by LastName

8. Display customer names and their company. (35 rows)

Use the Customer table. Create a new column called CustomerName that has LastName followed by a comma then FirstName. CustomerName must be all upper case. If the company value is NULL, replace it with "N/A". Only display customers whose last name starts with A through M.

9. Display the fiscal year an invoice record was recorded in the Invoice table. (412 rows)

Your result set will have InvoiceId, CustomerId, Total, InvoiceDate and FiscalYear columns. The FiscalYear is 6 months ahead of the calendar year (e.g. fiscal year 2010 contains dates from July 2009 through June 2010) Display the FiscalYear in the following format (FY2009, FY2010, FY2011, etc...) Change the InvoiceDate column datatype from datetime to date (i.e. don't display the time which is all zeros anyway.) Order by InvoiceDate in descending order.

10. Group the customers into Customer Type buckets. (59 rows)

Use the Customer table. Display the following columns: CustomerType, FirstName, LastName and Country CustomerType is a derived column. If the customer is from "USA" or "Canada" then display "Domestic" otherwise display "International". Order by CustomerType then LastName in ascending order.