Question

Write an SQL statement that will display the Employee ID, First name (only last 2 letters), phone number (replace . by /) of all employees whose job Id has a word ‘REP’.

Answer 1

Explanation::

• An table named Employee is created and select query is run on the same

CREATE TABLE QUERY::

CREATE TABLE EMPLOYEE (
employeeID INT NOT NULL,
firstName VARCHAR(100),
lastname VARCHAR(100),
phoneNumber VARCHAR(100),
jobID varchar(100),
PRIMARY KEY (employeeID)
);

-------------------------------------------------------------------------------------------------------

INSERT QUERY FOR SOME DATA IN TABLE::

INSERT INTO EMPLOYEE(employeeID,firstName,lastname,phoneNumber,jobID) values(123,'Steve','Berry','123.456.789','REPORTER');
INSERT INTO EMPLOYEE(employeeID,firstName,lastname,phoneNumber,jobID) values(124,'Amanda','Pelkey','127.488.289','CREW');
INSERT INTO EMPLOYEE(employeeID,firstName,lastname,phoneNumber,jobID) values(125,'Harry','Potter','127.488.289','Presenter');
INSERT INTO EMPLOYEE(employeeID,firstName,lastname,phoneNumber,jobID) values(126,'Hermione','Granger','127.488.289','Representative');
INSERT INTO EMPLOYEE(employeeID,firstName,lastname,phoneNumber,jobID) values(127,'Ronald','Weasley','127.488.289','Prep Team');
INSERT INTO EMPLOYEE(employeeID,firstName,lastname,phoneNumber,jobID) values(128,'Tom','Riddle','127.488.289','Fire Team');
INSERT INTO EMPLOYEE(employeeID,firstName,lastname,phoneNumber,jobID) values(129,'Sirius','Black','127.488.289','Reporter');
INSERT INTO EMPLOYEE(employeeID,firstName,lastname,phoneNumber,jobID) values(130,'Nevil','Longbottom','127.488.289','Helper');
INSERT INTO EMPLOYEE(employeeID,firstName,lastname,phoneNumber,jobID) values(131,'Parvati','Patil','127.488.289','Press');

QUERY::

SELECT * FROM EMPLOYEE GIVES::

ANSWER QUERY::

SELECT employeeID,RIGHT(firstname,2),REPLACE(phoneNumber,'.','/') FROM EMPLOYEE WHERE jobID LIKE '%REP%';

OUTPUT::

Please provide the feedback!!

Thank You!!