1. 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.

2. Construct an ID for full-time faculty by concatenating the following:

1. The first letter of the first name
2. The last letter of the last name
3. Characters 2-3 of the department
4. Characters 1 and 4 of the campus

6. The Dean wants starting dates for next years spring semester A, B, and C terms. Each term in 2019 will start a day later than the spring 2019 term. Write a statement to display the starting dates of A, B, and C terms in 2020. Be sure to use at least one date function. Assume the current year is 2018 and changes are for 2019

USE master; GO

IF DB_ID('Assignment9') IS NOT NULL DROP DATABASE Assignment9; GO

CREATE DATABASE Assignment9; GO

USE Assignment9;

CREATE TABLE Faculty (Faculty_ID VARCHAR(2), LastName VARCHAR(20), FirstName VARCHAR(20), Department VARCHAR(20), Campus VARCHAR(10));

INSERT INTO Faculty VALUES ('1', 'Bloomberg', 'Mike', 'Business', 'Kent'); INSERT INTO Faculty VALUES ('2', 'Smith', 'Adam', 'Economics', 'Kent'); INSERT INTO Faculty VALUES ('3', 'Shakespeare', 'Bill', 'English', 'South'); INSERT INTO Faculty VALUES ('4', 'Euler', 'Lynn', 'Math', 'Deerwood'); INSERT INTO Faculty VALUES ('5', 'Einstein', 'Al', 'Science', 'South');

CREATE TABLE Course (Course_ID CHAR(2), Ref_Number CHAR(5), Faculty_ID VARCHAR(2), Term Char(1), BegDate SMALLDATETIME, Enrollment INTEGER, TotRev FLOAT );

INSERT INTO Course VALUES ('1', '12345', 'a', 'A', '2018-01-08 00:00:00', 24, 12345.32 ); INSERT INTO Course VALUES ('2', '54321', '3', 'B', '2018-02-04 00:00:00', 18, 21435.51 ); INSERT INTO Course VALUES ('3', '13524', '1', 'B', '2018-02-04 00:00:00', 7, 1256.67 ); INSERT INTO Course VALUES ('4', '24653', '1', 'C', '2018-03-04 00:00:00', 29, 54421.11 ); INSERT INTO Course VALUES ('5', '98765', '5', 'A', '2018-01-08 00:00:00', 35, 246753.23); INSERT INTO Course VALUES ('6', '14862', '2', 'B', '2018-02-04 00:00:00', 14, 9876.87); INSERT INTO Course VALUES ('7', '96032', '1', 'C', '2018-03-04 00:00:00', 8, 863159.31); INSERT INTO Course VALUES ('8', '81256', '5', 'A', '2018-01-08 00:00:00', 5, 98762.14); INSERT INTO Course VALUES ('9', '64321', '2', 'C', '2018-03-04 00:00:00', 23, 2965.98); INSERT INTO Course VALUES ('10','90908', 'a', 'A', '2018-01-08 00:00:00', 45, 91724.02); INSERT INTO Course VALUES ('11','90908', '3', 'A', '2018-01-08 00:00:00', 23, 73725.77); INSERT INTO Course VALUES ('12','90908', '3', 'A', '2018-01-08 00:00:00', 17, 84224.29); INSERT INTO Course VALUES ('13','90908', 'b', 'A', '2018-01-08 00:00:00', 13, 42719.82);

CREATE TABLE Adjuncts (Faculty_ID Char(2), LastName VARCHAR(20), FirstName VARCHAR(20), Department VARCHAR(10), Campus VARCHAR(10));

INSERT INTO Adjuncts VALUES ('a', 'Rogers', 'Aaron', 'Business', 'Kent'); INSERT INTO Adjuncts VALUES ('b', 'Manning', 'Peyton', 'Economics', 'North'); INSERT INTO Adjuncts VALUES ('c', 'Drew', 'Maurice', 'English', 'Cecil'); INSERT INTO Adjuncts VALUES ('d', 'Griffin', 'Robert', 'Music', 'Deerwood'); INSERT INTO Adjuncts VALUES ('e', 'Goodell', 'Roger', 'Economics', 'South'); INSERT INTO Adjuncts VALUES ('f', 'Vilma', 'Jonathan', 'Business', 'Kent');