Mega Code Archive
Manipulate retrieved data from the Employees table
<![CDATA[
mysql>
mysql> CREATE TABLE Employees
-> (
-> EmpID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> EmpFN VARCHAR(20) NOT NULL,
-> EmpMN VARCHAR(20) NULL,
-> EmpLN VARCHAR(20) NOT NULL
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> # insert 7 records into the "employees" table
mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Arthur", "Smith","A");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Peter", "Jones","A");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Ann", "Smith","A");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Sandra", "Williams","A");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Andrew", "Smith","A");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Paul", "Jones","A");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Sally", "Williams","A");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> SELECT EmpID, UPPER(CONCAT_WS(' ', EmpFN, EmpMN, EmpLN)) AS Name,
-> CONCAT(LOWER(LEFT(EmpFN, 2)), LOWER(LEFT(EmpLN, 3)), EmpID) AS RegID
-> FROM Employees
-> ORDER BY EmpID;
+-------+-------------------+--------+
| EmpID | Name | RegID |
+-------+-------------------+--------+
| 1 | ARTHUR A SMITH | arsmi1 |
| 2 | PETER A JONES | pejon2 |
| 3 | ANN A SMITH | ansmi3 |
| 4 | SANDRA A WILLIAMS | sawil4 |
| 5 | ANDREW A SMITH | ansmi5 |
| 6 | PAUL A JONES | pajon6 |
| 7 | SALLY A WILLIAMS | sawil7 |
+-------+-------------------+--------+
7 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop table Employees;
Query OK, 0 rows affected (0.00 sec)
]]>