Mega Code Archive
Display the all members of staff in the same department as Tony West
<![CDATA[
mysql>
mysql>
mysql> CREATE TABLE IF NOT EXISTS staff
-> (
-> dept CHAR(20) NOT NULL,
-> name CHAR(20) PRIMARY KEY
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> # insert 6 records into the "staff" table
mysql> INSERT INTO staff (dept, name) VALUES ("Sales", "Tom");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO staff (dept, name) VALUES ("Legal", "Max");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO staff (dept, name) VALUES ("Works", "Ed");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO staff (dept, name) VALUES ("Sales", "Sue");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO staff (dept, name) VALUES ("Works", "Al");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO staff (dept, name) VALUES ("Sales", "Tony");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> # display all data in the "staff" table
mysql> SELECT * FROM staff;
+-------+------+
| dept | name |
+-------+------+
| Sales | Tom |
| Legal | Max |
| Works | Ed |
| Sales | Sue |
| Works | Al |
| Sales | Tony |
+-------+------+
6 rows in set (0.00 sec)
mysql>
mysql> # display the all members of staff in the same department as Tony West
mysql> SELECT s1.dept AS Department, s1.name AS Name
-> FROM staff AS s1, staff AS s2
-> WHERE s1.dept = s2.dept AND s2.name = "Tony West";
Empty set (0.00 sec)
mysql>
mysql> # delete this sample table
mysql> DROP TABLE IF EXISTS staff;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
]]>