ITSE 1303 Richland College Chinook Database MySQL Program

Apps is MYSQL Workbench .database chinookdatabase

— Date and Time Conditional Functions.

— 1. Use now and curtime functions to display these values on your system */

— 2. Count the number of invoices grouped by the day of the week using the Invoice date.

— 3. Display all columns and rows from the invoice table where the invoicedate is in the second quarter of 2012. Hint you will need to use two functions in the WHERE clause, YEAR and QUARTER*/

— 4. Use the extract function to return the InvoiceId, InvoiceDate and Total for all invoices that were placed on the first day of any month or year.

— 5. Use a case function to display the following information from the track table. The TrackId, name, the UnitPrice and the word “MPEG”, if the the MediaTypeId is 1 or 3, and “AAC” for all others. Name this column ‘Media Type’

— 6. Display the InvoiceId and InvoiceDate for all invoices. Use the date format function to dformate the date as MM/DD/YY. Use a column alias of your choice for the formatted date

— 7. Use an IF statement to display the InvoiceId, BillingCity, BillingState, BillingCountry and the word “Yes” if the BillingCountry is “USA”, “Mexico” or “Canada” or the word “No” for all other BillingCountries. The title for the added column should be ‘North America’