|
/*
mysql> Drop table Employees;
Query OK, 0 rows affected (0.12 sec)
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.11 sec)
mysql> INSERT INTO Employees (EmpFN, EmpMN, EmpLN)
-> VALUES ('John', 'P.', 'Smith'),
-> ('Robert', NULL, 'Schroader'),
-> ('Mary', 'Marie', 'Michaels'),
-> ('John', NULL, 'Laguci'),
-> ('Rita', 'C.', 'Carter'),
-> ('George', NULL, 'Brooks');
Query OK, 6 rows affected (0.07 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT EmpID, UPPER(CONCAT_WS(' ', EmpFN, EmpMN, EmpLN)) AS Name,
-> CONCAT(LOWER(SUBSTRING(EmpFN, 2, 2)),
-> LOWER(SUBSTRING(EmpLN, 2, 3)), EmpID) AS RegID
-> FROM Employees
-> ORDER BY EmpID;
+-------+---------------------+--------+
| EmpID | Name | RegID |
+-------+---------------------+--------+
| 1 | JOHN P. SMITH | ohmit1 |
| 2 | ROBERT SCHROADER | obchr2 |
| 3 | MARY MARIE MICHAELS | arich3 |
| 4 | JOHN LAGUCI | ohagu4 |
| 5 | RITA C. CARTER | itart5 |
| 6 | GEORGE BROOKS | eoroo6 |
+-------+---------------------+--------+
6 rows in set (0.04 sec)
*/
Drop table Employees;
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;
INSERT INTO Employees (EmpFN, EmpMN, EmpLN)
VALUES ('John', 'P.', 'Smith'),
('Robert', NULL, 'Schroader'),
('Mary', 'Marie', 'Michaels'),
('John', NULL, 'Laguci'),
('Rita', 'C.', 'Carter'),
('George', NULL, 'Brooks');
SELECT EmpID, UPPER(CONCAT_WS(' ', EmpFN, EmpMN, EmpLN)) AS Name,
CONCAT(LOWER(SUBSTRING(EmpFN, 2, 2)),
LOWER(SUBSTRING(EmpLN, 2, 3)), EmpID) AS RegID
FROM Employees
ORDER BY EmpID;
|
