SQL

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

This is the database used

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');