Create a SQL query that shows the first_name, last_name, hire_date, and the numb
ID: 3755517 • Letter: C
Question
Create a SQL query that shows the first_name, last_name, hire_date, and the number of days that the employee has worked from the EMPLOYEES table. Use the concatenation operator to put the first_name and the last_name together in the same column in the result set with a space between the two names. Use date arithmetic to show only employees that have worked longer than 6 years (365 * 6).
You have been asked to create a report showing the intra-office contact information. Create a SQL query using the EMPLOYEES table that shows the employee_id, first name, last name, email address as email@itresources.com and the last 4 digits of the phone number, usually referred to as the phone line number. Only show employees that don’t have a job ID like AD_ anything.
Use the CONCAT() function, as opposed to the concatenation operator used above to put the email besides ‘@itresources.com’
4. Modify the previous SQL query and add the phone_number to your result set as we are used to seeing it: (xxx)xxx-xxx. The easiest way to do this is to use a combination of the concatenation operator‘||’ with the substr() function to get the digits from the phone_number based on their position and length. Use a column alias to show the new format phone number AS “Usual phone number”
IE EMAIL PHONELINE AHUNOLD@itresources.com BERNST@itresources.com DAUSTIN@itresources.com 4567 4568 4569Explanation / Answer
Below is the query to get the first name, last name,hire_date, and the number of days that the employee has worked:
Select first_name,last_name,hire_date, datediff (date, hire_date, getdate()) as TotalDays from Employee;
Here DATEDIFF(day, startdate,enddate) is a SQL function that returns the total number of days between today's date and hiredate.
Query to get the employees who have worked longer than 6 years with concatenatioon of first name and last name:
Select first_name || ' ' || last_name "name" from Employees where DATEDIFF(year, hire_date,getdate()) > 6
SQL query using the EMPLOYEES table that shows the employee_id, first name, last name, email address as email@itresources.com and the last 4 digits of the phone number:
select employee_id, first_name,last_name, concat(email,"@itresources.com") as Email, substr(phone_number,9) as PhoneLine from employee where job_id not like 'AD_%';
Modify the previous SQL query and add the phone_number to your result set as we are used to seeing it: (xxx)xxx-xxx:
select employee_id, first_name,last_name, concat(email,"@itresources.com") as Email, phone_number, '(' || substr(phone_number,1,3) || ')' || substr(phone_number,5,3) || '-' || substr(phone_number,9)) as Usual PhoneNumber from employee where job_id not like 'AD_%';