A_7

CREATE TABLE emp_details(EMP_ID NUMBER(6) NOT NULL CONSTRAINT pk PRIMARY KEY,F_NAME varchar2(20),L_NAME varchar2(25) NOT NULL,HIRE_DATE date NOT NULL,JOB_ID varchar2(10) NOT NULL,SALARY number(8,2),COMISSION_PCt number(2,2),MANAGER_ID number(6),DEPT_ID number(4));

INSERT INTO emp_details VALUES(100,'Steven','King','06-17-1987','AD_PRES',24000,NULL,NULL,90);
INSERT INTO emp_details VALUES(101,'Neena','Kochhar','09-21-1989','AD_VP',17000,NULL,100,90);
INSERT INTO emp_details VALUES(102,'Lex','DE Haan','01-13-1993','AD_VP',17000,NULL,100,90);
INSERT INTO emp_details VALUES(103,'Alexander','Hunold','01-03-1990','IT_PROG',9000,NULL,102,60);
INSERT INTO emp_details VALUES(104,'Bruce','Ernst','05-21-1991','IT_PROG',6000,NULL,103,60);
INSERT INTO emp_details VALUES(107,'Diana','Lorentz','02-07-1999','IT_PROG',4200,NULL,103,60);
INSERT INTO emp_details VALUES(124,'Kevin','Mourgos','11-16-1999','ST_MAN',5800,NULL,100,50);
INSERT INTO emp_details VALUES(141,'Trenna','Rajs','10-17-1995','ST_CLERK',3500,NULL,124,50);
INSERT INTO emp_details VALUES(142,'Curtis','Davies','01-29-1997','ST_CLERK',3100,NULL,124,50);
INSERT INTO emp_details VALUES(143,'Randall','Matos','03-15-1998','ST_CLERK',2600,NULL,124,50);
INSERT INTO emp_details VALUES(144,'Peter','Vargas','07-09-1998','ST_CLERK',2500,NULL,124,50);
INSERT INTO emp_details VALUES(149,'Eleni','Zlotkey','01-29-2000','SA_MAN',10500,0.2,100,80);
INSERT INTO emp_details VALUES(174,'Ellen','Abel','05-11-1996','SA_REP',11000,0.3,149,80);
INSERT INTO emp_details VALUES(176,'Jonathan','Taylor','03-24-1998','SA_REP',8600,0.2,149,80);
INSERT INTO emp_details VALUES(178,'Kimberley','Grant','05-24-1999','SA_REP',7000,0.15,149,NULL);
INSERT INTO emp_details VALUES(200,'Jennifer','Whalen','09-17-1987','AD_ASST',4400,NULL,101,10);
INSERT INTO emp_details VALUES(201,'Michael','Hartstein','02-17-1996','MK_MAN',13000,NULL,100,20);
INSERT INTO emp_details VALUES(202,'Pat','Fay','08-17-1997','MK_REP',6000,NULL,201,20);
INSERT INTO emp_details VALUES(205,'Shelly','Higgins','06-07-1994','AC_MGR',12000,NULL,101,110);
INSERT INTO emp_details VALUES(206,'William','Gietz','06-07-1994','AC_ACCOUNT',8300,NULL,205,110);

SELECT LOWER(F_NAME) as F_name_L_case,DEPT_ID FROM emp_details where DEPT_ID in (20,50,80);

SELECT UPPER(L_NAME) as L_name_U_case,DEPT_ID FROM emp_details where DEPT_ID in (10,110,60);

SELECT INITCAP(JOB_ID) as Job_ID FROM emp_details;

SELECT CONCAT(F_NAME,L_NAME) as NAME from emp_details;

SELECT LENGTH(L_NAME) as LEN FROM emp_details where COMISSION_PCt IS NOT NULL;

SELECT INSTR(F_NAME,'a') FROM emp_details;

SELECT CONCAT(F_NAME,L_NAME) as NAME,LENGTH(L_NAME),INSTR(L_NAME,'a') as POS FROM emp_details where JOB_ID LIKE '%CLERK%';

SELECT RPAD(SALARY,10,'$') from emp_details;

SELECT TRIM('e' FROM F_NAME) as TRIMMED FROM emp_details;

SELECT DEPT_ID,SALARY,HIRE_DATE FROM emp_details where LENGTH(F_NAME) = 6;

SELECT JOB_ID,INSTR(F_NAME,'e') FROM emp_details where F_NAME LIKE '%e%';

SELECT L_NAME FROM emp_details WHERE INSTR(JOB_ID,'REP')=4;

SELECT MOD(SALARY,3000) as SALARY_DIVIDED from emp_details where JOB_ID like '%REP';

SELECT TO_CHAR(SALARY,'$999,999.00') as NSalary FROM emp_details;

SELECT CONCAT('$',TO_CHAR(SALARY)) FROM emp_details where DEPT_ID in (90,110,60);

SELECT L_NAME, CASE WHEN COMISSION_PCT IS NULL THEN 0 ELSE COMISSION_PCT END AS COMM from emp_details

SELECT ROUND(TRUNC(SYSDATE)-HIRE_DATE) as Days_Elapsed FROM emp_details;

SELECT TRUNC((SYSDATE-HIRE_DATE)/7) as Weeks_Elapsed FROM emp_details;

SELECT TRUNC(months_between(SYSDATE,HIRE_DATE)/12) as Years_Elapsed FROM emp_details;

SELECT CONCAT(F_NAME,CONCAT(' ',L_NAME)) as Name,TRUNC(months_between(SYSDATE,HIRE_DATE)) as Months_Worked FROM emp_details;

SELECT CONCAT(F_NAME,concat(' ',L_NAME)) as Name,last_day(HIRE_DATE) AS Last_Day_Of_month FROM emp_details;

SELECT CONCAT(F_NAME,CONCAT(' ',L_NAME)) as Name,TO_CHAR(HIRE_DATE,'YEAR') as Year_in_words FROM emp_details;

SELECT ADD_MONTHS(SYSDATE,5) AS Future_Date FROM dual;

SELECT EMP_ID,HIRE_DATE,TO_CHAR(HIRE_DATE,'Month') AS Start_Month FROM emp_details WHERE EXTRACT(YEAR FROM HIRE_DATE)=1994;

SELECT CONCAT(F_NAME,CONCAT(' ',L_NAME)) AS Name,TO_CHAR(HIRE_DATE,'Day') AS Day_OF_joining,TO_CHAR(HIRE_DATE,'DD') AS Date_OF_joining FROM emp_details;

SELECT (SALARY*12)+NVL(COMISSION_PCT,0) AS annual_salary,ROUND((SALARY*12)+NVL(COMISSION_PCT,0),2) AS rounded_annual_salary FROM emp_details;

SELECT JOB_ID,MAX(SALARY) AS Maximum_Salary FROM emp_details GROUP BY JOB_ID;

SELECT MANAGER_ID,COUNT(*) AS Number_Of_Employees FROM emp_details GROUP BY MANAGER_ID;

ALTER TABLE emp_details MODIFY SALARY NOT NULL;

DROP TABLE emp_details;