Execute the following queries against tables in the Faculty database. For each exercise, copy/paste both your SQL command and a screen shot of the results into a Word document. Please use one page per item. Please use the most recent form of the Faculty database attached to the assignment.

Write a SELECT statement that returns four columns based on the TotRev column of the Course table:

Use the CAST function to return the first column as data type decimal with 2 digits to the right of the decimal point.

Use CAST to return the second column as a varchar.

Use the CONVERT function to return the third column as the same data type as the first column.

Use CONVERT to return the fourth column as a varchar, using style 1.

Construct a ID for full-time faculty by concatenating the following:

The first letter of the first name

The last letter of the last name

Characters 2-3 of the department

Characters 1 and 4 of the campus

Create two columns from the TotRev field. One field will consist of the dollar part of the number. The other field will consist of the cents part of the number. Hint: Totrev is defined as FLOAT, where the precise number of decimal places is not defined. Use nested functions to first convert FLOAT to DECIMAL with 2 decimal places (inner function) and then extract dollars from the left of the decimal in one case (outer function) and cents from the right of the decimal in the other case.

Write a SELECT statement that returns the Reference Number, Term, and Beginning Date for all B and C term courses. You should a date function for each condition in your query to select the term by date. Hint: Dates in the course table apply to A, B, and C spring terms that begin in January, February, and March respectively.

Create two columns. One will display the total revenue rounded up. The other will display the total revenue rounded down to the nearest dollar.