-- create a table
CREATE table employees(
    emp_id INT  PRIMARY KEY AUTO_INCREMENT,
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    desig VARCHAR(50) NOT NULL DEFAULT 'Probation',
    dept VARCHAR(50) NOT NULL
);
INSERT into employees (emp_id,fname,lname,desig,dept) VALUES (101,'Raju','DWIVEDI','Manager','Loan');
INSERT into employees (fname,lname,desig,dept) VALUES ('Sham','SUNDAR','Cashier','Cash');
INSERT into employees (fname,lname,desig,dept) VALUES ('Paul','TIWARI','Asscociate','Loan');
INSERT into employees (fname,lname,desig,dept) VALUES ('Alex','MISHRA','Accountant','Loan');
INSERT into employees (fname,lname,desig,dept) VALUES ('Victor','TRIPATHI','Asscociate','Deposit');
SELECT * FROm employees;
#TASK 1 101:Raju:Manager:Loan
SELECT CONCAT_WS(':',emp_id,fname,desig,dept) from employees where emp_id = 101;
#TASK 2 101:Raju:DWIVEDI: Manager:Loan
SELECT CONCAT_WS(':',emp_id,fname,lname,desig,dept) from employees where emp_id = 101;

#TASK 3 101:Raju:MANAGER:Loan
SELECT CONCAT_WS(':',fname,UPPER(desig),dept) from employees where emp_id = 101;

#TASK4
#L101 Raju
#C102 Sham
SELECT CONCAT(dept,emp_id),fname from employees; #step1
#step2
SELECT CONCAT(LEFT(dept,1),emp_id),fname from employees WHERE emp_id in(101,102);