Calculate the average value the maximum value and the minimu

Calculate the average value, the maximum value, and the minimum value for one of the columns in the JustLee Books Database using the AVG, MAX and MIN group functions.

Use the COUNT function to count non-NULL values first and then to count the total number of records in one of the tables in JustLee Books Database.

The JustLee Books Database is below

CREATE TABLE Orders
(Order# NUMBER(4),
Customer# NUMBER(4),
OrderDate DATE NOT NULL,
ShipDate DATE,
ShipStreet VARCHAR2(18),
ShipCity VARCHAR2(15),
ShipState VARCHAR2(2),
ShipZip VARCHAR2(5),
ShipCost NUMBER(4,2),
CONSTRAINT orders_order#_pk PRIMARY KEY(order#),
CONSTRAINT orders_customer#_fk FOREIGN KEY (customer#)
REFERENCES customers(customer#));

INSERT INTO ORDERS
VALUES (1000,1005,TO_DATE(\'31-MAR-09\',\'DD-MON-YY\'),TO_DATE(\'02-APR-09\',\'DD-MON-YY\'),\'1201 ORANGE AVE\', \'SEATTLE\', \'WA\', \'98114\' , 2.00);
INSERT INTO ORDERS
VALUES (1001,1010,TO_DATE(\'31-MAR-09\',\'DD-MON-YY\'),TO_DATE(\'01-APR-09\',\'DD-MON-YY\'), \'114 EAST SAVANNAH\', \'ATLANTA\', \'GA\', \'30314\', 3.00);
INSERT INTO ORDERS
VALUES (1002,1011,TO_DATE(\'31-MAR-09\',\'DD-MON-YY\'),TO_DATE(\'01-APR-09\',\'DD-MON-YY\'),\'58 TILA CIRCLE\', \'CHICAGO\', \'IL\', \'60605\', 3.00);
INSERT INTO ORDERS
VALUES (1003,1001,TO_DATE(\'01-APR-09\',\'DD-MON-YY\'),TO_DATE(\'01-APR-09\',\'DD-MON-YY\'),\'958 MAGNOLIA LANE\', \'EASTPOINT\', \'FL\', \'32328\', 4.00);
INSERT INTO ORDERS
VALUES (1004,1020,TO_DATE(\'01-APR-09\',\'DD-MON-YY\'),TO_DATE(\'05-APR-09\',\'DD-MON-YY\'),\'561 ROUNDABOUT WAY\', \'TRENTON\', \'NJ\', \'08601\', NULL);
INSERT INTO ORDERS
VALUES (1005,1018,TO_DATE(\'01-APR-09\',\'DD-MON-YY\'),TO_DATE(\'02-APR-09\',\'DD-MON-YY\'), \'1008 GRAND AVENUE\', \'MACON\', \'GA\', \'31206\', 2.00);
INSERT INTO ORDERS
VALUES (1006,1003,TO_DATE(\'01-APR-09\',\'DD-MON-YY\'),TO_DATE(\'02-APR-09\',\'DD-MON-YY\'),\'558A CAPITOL HWY.\', \'TALLAHASSEE\', \'FL\', \'32307\', 2.00);
INSERT INTO ORDERS
VALUES (1007,1007,TO_DATE(\'02-APR-09\',\'DD-MON-YY\'),TO_DATE(\'04-APR-09\',\'DD-MON-YY\'), \'9153 MAIN STREET\', \'AUSTIN\', \'TX\', \'78710\', 7.00);
INSERT INTO ORDERS
VALUES (1008,1004,TO_DATE(\'02-APR-09\',\'DD-MON-YY\'),TO_DATE(\'03-APR-09\',\'DD-MON-YY\'), \'69821 SOUTH AVENUE\', \'BOISE\', \'ID\', \'83707\', 3.00);
INSERT INTO ORDERS
VALUES (1009,1005,TO_DATE(\'03-APR-09\',\'DD-MON-YY\'),TO_DATE(\'05-APR-09\',\'DD-MON-YY\'),\'9 LIGHTENING RD.\', \'SEATTLE\', \'WA\', \'98110\', NULL);
INSERT INTO ORDERS
VALUES (1010,1019,TO_DATE(\'03-APR-09\',\'DD-MON-YY\'),TO_DATE(\'04-APR-09\',\'DD-MON-YY\'),\'384 WRONG WAY HOME\', \'MORRISTOWN\', \'NJ\', \'07960\', 2.00);
INSERT INTO ORDERS
VALUES (1011,1010,TO_DATE(\'03-APR-09\',\'DD-MON-YY\'),TO_DATE(\'05-APR-09\',\'DD-MON-YY\'), \'102 WEST LAFAYETTE\', \'ATLANTA\', \'GA\', \'30311\', 2.00);
INSERT INTO ORDERS
VALUES (1012,1017,TO_DATE(\'03-APR-09\',\'DD-MON-YY\'),NULL,\'1295 WINDY AVENUE\', \'KALMAZOO\', \'MI\', \'49002\', 6.00);
INSERT INTO ORDERS
VALUES (1013,1014,TO_DATE(\'03-APR-09\',\'DD-MON-YY\'),TO_DATE(\'04-APR-09\',\'DD-MON-YY\'),\'7618 MOUNTAIN RD.\', \'CODY\', \'WY\', \'82414\', 2.00);
INSERT INTO ORDERS
VALUES (1014,1007,TO_DATE(\'04-APR-09\',\'DD-MON-YY\'),TO_DATE(\'05-APR-09\',\'DD-MON-YY\'), \'9153 MAIN STREET\', \'AUSTIN\', \'TX\', \'78710\', 3.00);
INSERT INTO ORDERS
VALUES (1015,1020,TO_DATE(\'04-APR-09\',\'DD-MON-YY\'),NULL,\'557 GLITTER ST.\', \'TRENTON\', \'NJ\', \'08606\', 2.00);
INSERT INTO ORDERS
VALUES (1016,1003,TO_DATE(\'04-APR-09\',\'DD-MON-YY\'),NULL,\'9901 SEMINOLE WAY\', \'TALLAHASSEE\', \'FL\', \'32307\', 2.00);
INSERT INTO ORDERS
VALUES (1017,1015,TO_DATE(\'04-APR-09\',\'DD-MON-YY\'),TO_DATE(\'05-APR-09\',\'DD-MON-YY\'),\'887 HOT ASPHALT ST\', \'MIAMI\', \'FL\', \'33112\', 3.00);
INSERT INTO ORDERS
VALUES (1018,1001,TO_DATE(\'05-APR-09\',\'DD-MON-YY\'),NULL,\'95812 HIGHWAY 98\', \'EASTPOINT\', \'FL\', \'32328\', NULL);
INSERT INTO ORDERS
VALUES (1019,1018,TO_DATE(\'05-APR-09\',\'DD-MON-YY\'),NULL, \'1008 GRAND AVENUE\', \'MACON\', \'GA\', \'31206\', 2.00);
INSERT INTO ORDERS
VALUES (1020,1008,TO_DATE(\'05-APR-09\',\'DD-MON-YY\'),NULL,\'195 JAMISON LANE\', \'CHEYENNE\', \'WY\', \'82003\', 2.00);

CREATE TABLE Publisher
(PubID NUMBER(2),
Name VARCHAR2(23),
Contact VARCHAR2(15),
Phone VARCHAR2(12),
CONSTRAINT publisher_pubid_pk PRIMARY KEY(pubid));

INSERT INTO PUBLISHER
VALUES(1,\'PRINTING IS US\',\'TOMMIE SEYMOUR\',\'000-714-8321\');
INSERT INTO PUBLISHER
VALUES(2,\'PUBLISH OUR WAY\',\'JANE TOMLIN\',\'010-410-0010\');
INSERT INTO PUBLISHER
VALUES(3,\'AMERICAN PUBLISHING\',\'DAVID DAVIDSON\',\'800-555-1211\');
INSERT INTO PUBLISHER
VALUES(4,\'READING MATERIALS INC.\',\'RENEE SMITH\',\'800-555-9743\');
INSERT INTO PUBLISHER
VALUES(5,\'REED-N-RITE\',\'SEBASTIAN JONES\',\'800-555-8284\')

Solution

Expalnation :

First query returns min value in column Shipcost,max value in column Shipcost,avg value in column Shipcost ,Number of Rows in order table without counting null values in Shipcost cloumn and total number of records in table order.

Sample output (not acurate from the given table):

MIN ShipCost MAX ShipCost   AVG ShipCost   Rows Count WIthout Null values   Rows Count In Table Order
10 100 40.5 6 8