Database-WK04

SELECT 'The job id for '||UPPER(LAST_NAME)||' is '||LOWER(JOB_ID) "EMPLOYEE DETAILS" FROM EMPLOYEES;

SELECT employee_id,last_name,department_id FROM employees WHERE LOWER(last_name) = 'higgins';

SELECT employee_id,CONCAT(first_name,last_name) name,job_id,LENGTH(last_name),INSTR(last_name,'a') "Contains 'a'?"
FROM EMPLOYEES WHERE SUBSTR(job_ID,LENGTH(job_ID)-2)= 'REP';

SELECT employee_id,CONCAT(first_name,last_name) name,job_id,LENGTH(last_name),INSTR(last_name,'a') "Contains 'a'?"
FROM EMPLOYEES WHERE SUBSTR(last_name,-2,1)= 'n';

SELECT first_name,RPAD(first_name,20,' ') "FIRST_NAME1",LPAD(first_name,20,'*') "FIRST_NAME2" FROM EMPLOYEES;

SELECT ROUND(67830.4557,3),ROUND(67830.4557,2),ROUND(67830.4557,-3) FROM DUAL;
SELECT TRUNC(67830.4557,3),TRUNC(67830.4557,2),TRUNC(67830.4557,-3) FROM DUAL;

SELECT last_name,salary,MOD(salary,5000)
FROM employees
WHERE job_id = 'SA_REP';

SELECT last_name,hire_date FROM EMPLOYEES WHERE HIRE_DATE < '01-FEB-88';

SELECT SYSDATE FROM dual;

SELECT LAST_NAME,round(MONTHS_BETWEEN(SYSDATE,HIRE_DATE),0) months_worked FROM EMPLOYEES;

SELECT employee_id,hire_date,round(hire_date,'MONTH') roundmonth, trunc(hire_date,'MONTH') truncmonth
FROM EMPLOYEES WHERE HIRE_DATE LIKE '%97';

SELECT EMPLOYEE_ID,HIRE_DATE,ADD_MONTHS(HIRE_DATE,6) REVIEW,
NEXT_DAY(HIRE_DATE,'FRIDAY') "Next Hiredate",LAST_DAY(HIRE_DATE) "Last Hiredate",
MONTHS_BETWEEN(SYSDATE,HIRE_DATE) TENURE
FROM EMPLOYEES
WHERE MONTHS_BETWEEN(SYSDATE,HIRE_DATE) > 250;

SELECT INITCAP(last_name) "Name",LENGTH(last_name) "Length"
FROM EMPLOYEES
WHERE SUBSTR(LAST_NAME,1,1)IN('A','J','M');

SELECT EMPLOYEE_ID,LAST_NAME,SALARY,ROUND(SALARY*1.155) "New Salary"
FROM EMPLOYEES;