Question

Use the HR database for all the questions in this lab. For each item in this lab, paste the SQL you used to complete the task and the number of rows retrieved. Ensure you follow the coding standards listed in the ETSU SQL standards guide found on D2L. Save this document as a PDF and name it your_name_lab7.pdf

1. Display the employees’ first name, last name and phone number of employees with salaries greater than 10,000 dollars.

SELECT first_name, last_name, phone_number, salary

FROM hr.employees

WHERE salary > 10000;

Rows Retrieved: 15

2. Display the department ID, and department name of departments without managers.

3. Display all the employees in department 50 who joined after the year 1997.

4. Display the full name of employees (as a single column) and start date who joined on the 15^th of any month. (Hint: Use TO_CHAR function)

5. Assuming the all emails of employees end with “@abcbank.com”, display the full name and their email address of all employees

6. Assuming each employee pays 5% if their salary as tax, display all the full name, phone number , salary, tax of all employees who’s tax is more than 500 dollars

7. Password of the format “XXyymmdd” is to be generated for all employees.

Where “XX” is the first two letters of the first name, “yy” is the last two letters of the last name, “mm” is the month of hire while “dd” is the day of hire.

Display all employees full name and password.

8. Find the employee(s) who work as an IT programmer or Sales Rep, having an ‘a’ as the second character of the first name and with no ‘n’ in the last name.

Answer 1

2. Display the department ID, and department name of departments without managers.

SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM HR.DEPARTMENTS WHERE MANAGER_ID IS NULL;

3. Display all the employees in department 50 who joined after the year 1997

SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID=50 AND SUBSTR(HIRE_DATE,1,4)<1997;

4. Display the full name of employees (as a single column) and start date who joined on the 15^th of any month. (Hint: Use TO_CHAR function)

SELECT HE.FIRST_NAME||' '||HE.LAST_NAME , START_DATE FROM HR.JOB_HISTORY HJ JOIN HR.EMPLOYEES HE ON HJ.EMPLOYEE_ID=HE.EMPLOYEE_ID WHERE TO_CHAR(START_DATE,'dd') = 15

5. Assuming the all emails of employees end with “@abcbank.com”, display the full name and their email address of all employees

SELECT FIRST_NAME||' '||LAST_NAME ,EMAIL||'@abcbank.com' FROM HR.EMPLOYEES

6. Assuming each employee pays 5% if their salary as tax, display all the full name, phone number , salary, tax of all employees who’s tax is more than 500 dollars

SELECT FIRST_NAME||' '||LAST_NAME ,PHONE_NUMBER,SALARY, (SALARY*0.05) AS TAX FROM HR.EMPLOYEES WHERE (SALARY*0.05)>500;

7. Password of the format “XXyymmdd” is to be generated for all employees.

Where “XX” is the first two letters of the first name, “yy” is the last two letters of the last name, “mm” is the month of hire while “dd” is the day of hire.

Display all employees full name and password.

SELECT FIRST_NAME||' '||LAST_NAME , SUBSTR(FIRST_NAME,1,2)||SUBSTR(LAST_NAME, LENGTH(LAST_NAME)-2,2)||TO_CHAR(START_DATE,'mm')||TO_CHAR(START_DATE,'dd')  FROM HR.EMPLOYEES

8. Find the employee(s) who work as an IT programmer or Sales Rep, having an ‘a’ as the second character of the first name and with no ‘n’ in the last name.

SELECT HE.EMPLOYEE_ID FROM HR.EMPLOYEES HE JOIN HR.JOBS HJ ON HE.JOB_ID=HJ.JOB_ID WHERE HJ.JOB_TILE IN ('IT programmer','Sales Rep') AND FIRST_NAME LIKE '_a%' AND LAST_NAME LIKE '%n%';

note: if u have any doubt, let me know.