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. This is the DAtabase
USE master;
GO
IF DB_ID('Assignment8') IS NOT NULL
DROP DATABASE Assignment8;
GO
CREATE DATABASE Assignment8;
GO
USE Assignment8;
CREATE TABLE Faculty
(Faculty_ID VARCHAR(2),
LastName VARCHAR(20),
FirstName VARCHAR(20),
Department VARCHAR(10),
Campus VARCHAR(10));
INSERT INTO Faculty VALUES ('1', 'Brown', 'Joe', 'Business', 'Kent');
INSERT INTO Faculty VALUES ('2', 'Smith', 'John', 'Economics', 'Kent');
INSERT INTO Faculty VALUES ('3', 'Jones', 'Sally', 'English', 'South');
INSERT INTO Faculty VALUES ('4', 'Black', 'Bill', 'Economics', 'Deerwood');
INSERT INTO Faculty VALUES ('5', 'Green', 'Gene', 'Business', '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');