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 name and price for all products that have been purchased in order 1001. Use IN to implement the query.

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 p.product_name,p.product_price from TABLE misy330ea.product p WHERE p.product_id IN (SELECT p.product_id from TABLE misy330ea.orderline o WHERE o.order_id='1001' ) ;

since product_id is a foreign key referenced in orderline table so product_id from orderline table is selected such that order_id is '1001' and product name and price are displayed

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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