Our store is now online and customer accounts are being created. Each user needs a login, and the default login will be a combination of their name and city. Write a SQL statement that returns customer id (cust_id), customer name (cust_name) and user_login which is all upper case and comprised of the first two characters of the customer contact (cust_contact) and the first three characters of the customer city (cust_city). So, for example, my login (Ben Forta living in Oak Park) would be BEOAK. Hint, for this one you’ll use functions, concatenation, and an alias.


-- DB2, PostgreSQL
SELECT cust_id, cust_name,
        UPPER(LEFT(cust_contact, 2)) || UPPER(LEFT(cust_city, 3)) AS user_login
FROM customers;
-- Oracle, SQLite
SELECT cust_id, cust_name,
        UPPER(SUBSTR(cust_contact, 1, 2)) || UPPER(SUBSTR(cust_city, 1, 3)) AS user_login
 FROM customers;
-- MySQL
SELECT cust_id, cust_name,
        CONCAT(UPPER(LEFT(cust_contact, 2)), UPPER(LEFT(cust_city, 3))) AS user_login
FROM customers;
-- SQL Server
SELECT cust_id, cust_name,
        UPPER(LEFT(cust_contact, 2)) + UPPER(LEFT(cust_city, 3)) AS user_login
FROM customers;

Comment on this FAQ