Given the following data to be inputted into SQL, give the code for the followin

Question

Given the following data to be inputted into SQL, give the code for the following:

List total dollar amount ordered for each order, where for each product ordered its amount equals to quantity times price.

The data is as follows:

CREATE database misy330ea;
CREATE   TABLE   misy330ea.customer
( cust_id           decimal(11,0) not null,
    cust_name     varchar(25) not null,
    street             varchar(30),
    city                varchar(20),
    state               varchar(2),
    zipcode          varchar(5),
    PRIMARY KEY (cust_id) );
CREATE   TABLE   misy330ea.ordertable
( order_id           decimal(11,0) not null,
    order_date       date,
    cust_id           decimal(11,0),
    PRIMARY KEY (order_id),
FOREIGN KEY (cust_id) REFERENCES misy330ea.customer (cust_id));
CREATE   TABLE misy330ea.product
( product_id           decimal(11,0) not null,
    product_name       varchar(50),
    product_price         decimal(6,2),
    PRIMARY KEY (product_id));
CREATE   TABLE   misy330ea.orderline
(   order_id           decimal(11,0) not null,
    product_id        decimal(11,0) not null,
    quantity             decimal(11,0),
   PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES misy330ea.ordertable (order_id),
FOREIGN KEY (product_id) REFERENCES misy330ea.product (product_id));
    insert into misy330ea.product values (101, 'Dell E5300 Laptop', 489.98);
    insert into misy330ea.product values (102, 'Apple Laptop', 988.72);
    insert into misy330ea.product values (103,'Printer',59);
    insert into misy330ea.product values (104, 'Desk', 85.98);
    insert into misy330ea.product values (105, 'Office Chair', 55.99);
    insert into misy330ea.product values (106, 'Stapler', 15.88);
    insert into misy330ea.product values (107, 'Index Divider', 5.99);
    insert into misy330ea.product values (108, 'Shredder', 74.99);
    insert into misy330ea.customer values (1, 'John Doe', '200 Maple', 'SLC', 'UT', '84102');
    insert into misy330ea.customer values (2, 'Bill Doll', '300 West', 'SLC', 'UT', '84107');
    insert into misy330ea.customer values (3, 'Josh Turburn', '250 North', 'SLC', 'UT', '84108');
    insert into misy330ea.customer values (4, 'Mary Lee', '200 South', 'SLC', 'UT', '84102');
    insert into misy330ea.customer values (5, 'Jane Smith', '120 University', 'SLC', 'UT', '84102');
    insert into misy330ea.customer values (6, 'Luis Smith', '200 Maple', 'SLC', 'UT', '84102');
    insert into misy330ea.customer values (7, 'Ben Brown', '300 West', 'SLC', 'UT', '84107');
    insert into misy330ea.customer values (8, 'Carl Smith', '250 North', 'SLC', 'UT', '84108');
    insert into misy330ea.customer values (9, 'John Doll', '200 South', 'SLC', 'UT', '84102');
    insert into misy330ea.customer values (10, 'Jennet Chris', '120 University', 'SLC', 'UT', '84102');
    insert into misy330ea.customer values (11, 'Joyce French', '200 Main', 'Tucson', 'AZ', '45102');
    insert into misy330ea.customer values (12, 'Jennifer English', '250 State', 'Tucson', 'AZ', '45112');
    insert into misy330ea.customer values (13, 'Tom Borg', '3000 Sunset', 'LA', 'CA', '12112');
    insert into misy330ea.customer values (14, 'Helen Thomas', '1200 Hollywood', 'LA', 'CA', '12117');
    insert into misy330ea.customer values (15, 'Brian Borg', '100 College', 'SF', 'CA', '17118');
    insert into misy330ea.ordertable values (1001, '2008-10-24', 1);
    insert into misy330ea.ordertable values (1002, '2008-10-21', 8);
    insert into misy330ea.ordertable values (1003, '2008-10-22', 15);
    insert into misy330ea.ordertable values (1004, '2008-10-22', 5);
    insert into misy330ea.ordertable values (1005, '2008-10-24', 3);
    insert into misy330ea.ordertable values (1006, '2008-10-24', 2);
    insert into misy330ea.ordertable values (1007, '2008-10-27', 11);
    insert into misy330ea.ordertable values (1008, '2008-10-30', 12);
    insert into misy330ea.ordertable values (1009, '2008-11-01', 4);
    insert into misy330ea.ordertable values (1010, '2008-11-05', 1);
insert into misy330ea.orderline values (1001, 101, 2);
insert into misy330ea.orderline values (1001, 102, 2);
insert into misy330ea.orderline values (1001, 104, 1);
insert into misy330ea.orderline values (1002, 103, 5);
insert into misy330ea.orderline values (1003, 103, 3);
insert into misy330ea.orderline values (1004, 106, 2);
insert into misy330ea.orderline values (1004, 108, 2);
insert into misy330ea.orderline values (1005, 104, 4);
insert into misy330ea.orderline values (1006, 104, 1);
insert into misy330ea.orderline values (1006, 105, 2);
insert into misy330ea.orderline values (1006, 107, 2);
insert into misy330ea.orderline values (1007, 102, 2);
insert into misy330ea.orderline values (1008, 103, 3);
insert into misy330ea.orderline values (1008, 108, 3);
insert into misy330ea.orderline values (1009, 104, 2);
insert into misy330ea.orderline values (1009, 107, 3);
insert into misy330ea.orderline values (1010, 108, 10);

Explanation / Answer

SELECT oi.order_id,SUM(oi.quantity * p.product_price) AS grand_total FROM misy330ea.orderline oi, misy330ea.product p WHERE oi.product_id =p.product_id GROUP BY oi.order_id;

we performed join between orderline and product to get sum of product price and quantity , then we are grouping by orderid to get the sum value for each unique order

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.