Display order number, quantity, item name, vendor and total order value of order number 2805 : Sort Order « Select Clause « SQL / MySQL

1. SQL / MySQL
2. Select Clause
3. Sort Order

Display order number, quantity, item name, vendor and total order value of order number 2805

       
mysql>
mysql> CREATE TABLE IF NOT EXISTS items
    -> (
    ->   id             INT             PRIMARY KEY,
    ->   vendor INT             NOT NULL,
    ->   name           CHAR(20)        NOT NULL,
    ->   price          DECIMAL(6,2)    NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO items (id, vendor, name, price)   VALUES (601, 2, "Elephants", 147.50);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO items (id, vendor, name, price)   VALUES (602, 2, "Reindeers",  123.00);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO items (id, vendor, name, price)   VALUES (603, 1, "Alligators", 185.00);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS vendors
    -> (
    ->   id             INT     PRIMARY KEY,
    ->   name           CHAR(20)        NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO vendors (id, name) VALUES (1, "Alpha Inc");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO vendors (id, name) VALUES (2, "Zeta Inc");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS orders
    -> (
    ->   num    INT     PRIMARY KEY,
    ->   item   INT     NOT NULL,
    ->   qty    INT     NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO orders (num, item, qty) VALUES (2805, 603, 10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO orders (num, item, qty) VALUES (2806, 603, 5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO orders (num, item, qty) VALUES (2807, 601, 10);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> SELECT   orders.num                      AS Number,
    ->  orders.qty                      AS Qty,
    ->  items.name                      AS Toy,
    ->  vendors.name                    AS Vendor,
    ->  items.price * orders.qty        AS Total
    -> FROM     items, vendors, orders
    -> WHERE    vendors.id = items.vendor
    -> AND      items.id = orders.item
    -> AND      orders.num = 2805;
+--------+-----+------------+-----------+---------+
| Number | Qty | Toy        | Vendor    | Total   |
+--------+-----+------------+-----------+---------+
|   2805 |  10 | Alligators | Alpha Inc | 1850.00 |
+--------+-----+------------+-----------+---------+
1 row in set (0.00 sec)

mysql>
mysql> # delete these sample tables
mysql> DROP TABLE IF EXISTS items;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS vendors;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS orders;
Query OK, 0 rows affected (0.00 sec)

   
    
    
    
    
    
    
  

Related examples in the same category