|
/*
mysql> Drop table Employees;
ERROR 1051 (42S02): Unknown table 'employees'
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.13 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.04 sec)
Records: 6 Duplicates: 0 Warnings: 0
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 | JOHN P. SMITH | josmi1 |
| 2 | ROBERT SCHROADER | rosch2 |
| 3 | MARY MARIE MICHAELS | mamic3 |
| 4 | JOHN LAGUCI | jolag4 |
| 5 | RITA C. CARTER | ricar5 |
| 6 | GEORGE BROOKS | gebro6 |
+-------+---------------------+--------+
6 rows in set (0.02 sec)
mysql>
*/
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(LEFT(EmpFN, 2)), LOWER(LEFT(EmpLN, 3)), EmpID) AS RegID
FROM Employees
ORDER BY EmpID;
|