A_6

CREATE TABLE e_mp(e_id number PRIMARY KEY,fname varchar(20) NOT NULL,lname varchar(20) NOT NULL,hire_date DATE NOT NULL,job_id varchar2(20) NOT NULL,sale number NOT NULL,dept_id number,CHECK(dept_id>=10));

INSERT INTO e_mp VALUES(198,'Donald','Connell','06-21-1999','SH_CLERK',2600,50);
INSERT INTO e_mp VALUES(199,'Douglas','Grant','01-13-1998','SH_CLERK',3000,50);
INSERT INTO e_mp VALUES(200,'Jennifer','Whalen','09-17-1987','AD_ASSST',4400,10);
INSERT INTO e_mp VALUES(201,'Michael','Hartstein','01-19-1999','IT_PROG',6000,20);
INSERT INTO e_mp VALUES(202,'Pat','Fay','10-25-1989','AC_MGR',6500,20);
INSERT INTO e_mp VALUES(203,'Susan','Marvis','11-26-1976','AD_VP',7500,40);
INSERT INTO e_mp VALUES(204,'Hermann','Baer','08-23-1995','AD_PRES',9500,90);
INSERT INTO e_mp VALUES(205,'Shelly','Higgins','02-24-1998','AC_MGR',2300,60);
INSERT INTO e_mp VALUES(206,'William','Gitz','03-12-2001','IT_PROG',5000,60);
INSERT INTO e_mp VALUES(100,'Steven','King','06-15-2002','AD_ASST',8956,100);
INSERT INTO e_mp VALUES(101,'Neena','Kochar','07-10-2003','SH_CLERK',3400,30);
SELECT * FROM e_mp;

SELECT fname as emp_name_asc from e_mp ORDER BY fname ASC;

SELECT fname as emp_name_dsc from e_mp ORDER BY fname DESC;

SELECT hire_date as emp_hire_date_asc from e_mp ORDER BY hire_date ASC;

SELECT * from e_mp where fname like 'J%' or fname like 'M%'  ORDER BY lname ASC;

SELECT max(sale) as maxxx,min(sale) as minnn,avg(sale) as avggg , sum(sale) as summ from e_mp;

SELECT job_id as JOB_D,COUNT(job_id) as E_nos,max(sale) as maxxx,min(sale) as minnn,avg(sale) as avggg , sum(sale) as summ from e_mp GROUP BY job_id;

SELECT COUNT(job_id)  from e_mp where job_id like '%MGR%';

SELECT max(sale)-min(sale) as DIFF from e_mp;

SELECT max(sale)-avg(sale) as DIFF from e_mp where job_id like '%IT%';

SELECt min(fname) as ROW_Frst , max(fname) as ROW_Last from e_mp;

SELECt min(hire_date) as HIRE_Frst , max(hire_date) as HIRE_Last from e_mp;

SELECT max(sale) as maxx,avg(sale) as avgg from e_mp where job_id LIKE '%CLERK%';

SELECT dept_id,min(sale) as Lowest_salary from e_mp GROUP BY dept_id;

SELECT dept_id,min(sale) as Lowest_salary from e_mp WHERE sale>=3000 GROUP BY dept_id;

SELECT fname||' whose Designation is '||job_id||' gets '||sale||' but wants to earn '||3*sale from e_mp;

SELECT e_id||','||fname||','||lname||','||hire_date||','||job_id||','||sale||','||dept_id from e_mp;

SELECT CURRENT_DATE as TODAY from dual;

SELECT e_id,EXTRACT(DAY FROM hire_date) as day,EXTRACT(YEAR FROM hire_date) as Year from e_mp;

SELECT fname||' '||lname as Emp_name,TO_CHAR(hire_date,'dd-mon-yyyy') as hired_on from e_mp;

SELECT e_id,EXTRACT(MONTH FROM hire_date) as MONTH from e_mp;

SELECT fname||' '||lname as Emp_name,e_id,TO_CHAR(hire_date,'dd-mon-yyyy') as hired_on from e_mp;

SELECT fname||' '||lname as Emp_name,e_id,TO_CHAR(hire_date,'mon-dd-yyyy') as hired_on from e_mp;

SELECT to_char(current_date,'YEAR') as Year_in_words from dual;

SELECT TO_DATE(current_date - 15) as Day_minus_15,current_date as curr,TO_DATE(current_date + 15) as Day_plus_15 from dual;