retrieve the name of the customer placing order 4
mysql> CREATE TABLE IF NOT EXISTS customers(
-> acc_num INT PRIMARY KEY,
-> name CHAR(20) NOT NULL );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO customers (acc_num, name) VALUES (123, "T.Smith");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customers (acc_num, name) VALUES (124, "P.Jones");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> CREATE TABLE IF NOT EXISTS orders(
-> ord_num INT PRIMARY KEY,
-> acc_num INT NOT NULL );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO orders (ord_num, acc_num) VALUES (3, 123);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO orders (ord_num, acc_num) VALUES (4, 124);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> # display all data in the "customers" and "orders" tables
mysql> SELECT * FROM customers;
+---------+---------+
| acc_num | name |
+---------+---------+
| 123 | T.Smith |
| 124 | P.Jones |
+---------+---------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM orders;
+---------+---------+
| ord_num | acc_num |
+---------+---------+
| 3 | 123 |
| 4 | 124 |
+---------+---------+
2 rows in set (0.00 sec)
mysql>
mysql> # retrieve the name of the customer placing order 4
mysql> SELECT ord_num, customers.acc_num, name
-> FROM customers, orders
-> WHERE customers.acc_num = orders.acc_num
-> AND orders.ord_num = 4;
+---------+---------+---------+
| ord_num | acc_num | name |
+---------+---------+---------+
| 4 | 124 | P.Jones |
+---------+---------+---------+
1 row in set (0.00 sec)
mysql>
mysql> # delete these sample tables
mysql> DROP TABLE IF EXISTS customers;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS orders;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
Related examples in the same category