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 NATURAL JOIN 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

Hi,

To get the details of order 1001 along with product specifications we only need order line table and product table.

Here is the structure of both tables

orderline: order_id product_id quantity
product: product_id product_name product_price.

Now, to get all products in order 1001 we can do,

select * from orderline where order_id=1001;

which will return

+----------+------------+----------+
| order_id | product_id | quantity |
+----------+------------+----------+
| 1001 | 101 | 2 |
| 1001 | 102 | 2 |
| 1001 | 104 | 1 |
+----------+------------+----------+

Now, to get product detals from above product id's we will need to join the product table like

select * from orderline natural join product where order_id=1001; which returns

+------------+----------+----------+-------------------+---------------+
| product_id | order_id | quantity | product_name | product_price |
+------------+----------+----------+-------------------+---------------+
| 101 | 1001 | 2 | Dell E5300 Laptop | 489.98 |
| 102 | 1001 | 2 | Apple Laptop | 988.72 |
| 104 | 1001 | 1 | Desk | 85.98 |
+------------+----------+----------+-------------------+---------------+

By writing NATURAL JOIN, you automatically tell SQL to match the common columsn between tables like product_id in the example above which is common between ordeline and product.

Now if you want only few columns in the result you can select them like

select product_name,product_price,order_id from orderline natural join product where order_id=1001; whch will return.

+-------------------+---------------+----------+
| product_name | product_price | order_id |
+-------------------+---------------+----------+
| Dell E5300 Laptop | 489.98 | 1001 |
| Apple Laptop | 988.72 | 1001 |
| Desk | 85.98 | 1001 |
+-------------------+---------------+----------+

Final solution:

select product_name,product_price from orderline natural join product where order_id=1001;

+-------------------+---------------+
| product_name | product_price |
+-------------------+---------------+
| Dell E5300 Laptop | 489.98 |
| Apple Laptop | 988.72 |
| Desk | 85.98 |
+-------------------+---------------+

Let me know if you have any doubts.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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