[OliveOyl3471]

Yes, this is homework. If I was doing this just for fun it would be C++, not SQL. lol.

Here's my problem:
Display the employee’s name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Sunday, the Seventh of September, 1981.”

And here's what I have so far. It works, but I don't know how to get it to display the next Monday, or how to format it properly.

	SELECT ename, hiredate, ADD_MONTHS(hiredate, 6) as “REVIEW” 
	FROM emp;

I actually have two problems that I can't figure out. Here's the part of the second one that I'm having trouble with:
6. For each employee display the employee name and calculate the number of months between today and the date the employee was hired. Label the column MONTHS_WORKED.

Here's what I have so far, but I don't know how to get it to calculate the months between the two dates. Or maybe it is calculating it correctly, but it does not display correctly:

SELECT ename, SYSDATE - hiredate  as “MONTHS_WORKED” FROM emp;
 

In case you need it, here's the setup file I'm using:

SET TERMOUT OFF

drop user cs443;
create user CS443 identified by CS443;

grant connect, resource to cs443;

CONNECT cs443/CS443

DROP TABLE DEPT CASCADE CONSTRAINTS;
CREATE TABLE DEPT
	   (DEPTNO NUMBER(2) constraint dept_deptno_pk primary key,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) );

DROP TABLE EMP;
CREATE TABLE EMP
   (EMPNO NUMBER(4) constraint emp_empno_pk primary key,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	GENDER VARCHAR2(1),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2) );


INSERT INTO DEPT VALUES
	(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
	(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
	(40,'OPERATIONS','BOSTON');


INSERT INTO EMP VALUES
(7369,'SMITH','CLERK','M',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN','M',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN','M',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER','M',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN','F',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER','M',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER','M',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST','M',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT','M',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,NULL);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN','M',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK','F',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK','M',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST','F',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK','M',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

DROP TABLE BONUS;
CREATE TABLE BONUS
	(
	EMPNO NUMBER(4)	constraint bonus_empno_pk primary key,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9)  ,
	SAL NUMBER,
	COMM NUMBER
	);

DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
	  ( GRADE NUMBER,
	LOSAL NUMBER,
	HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;

insert into bonus select empno, ename, job, sal, comm from emp where comm >= 500;
commit;

create index emp_dept_idx on emp (deptno);
create index emp_gender_deptno_idx  on emp (gender, deptno);

exec dbms_stats.gather_table_stats(user,'emp',method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE,estimate_percent=>100);
exec dbms_stats.gather_table_stats(user,'dept',method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE,estimate_percent=>100);
exec dbms_stats.gather_table_stats(user,'bonus',method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE,estimate_percent=>100);
exec dbms_stats.gather_table_stats(user,'salgrade',method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE,estimate_percent=>100);


set TERMOUT ON

This post has been edited by OliveOyl3471: 24 June 2009 - 06:39 PM

[OliveOyl3471]

I found some answers here:
http://www.psoug.org.../date_func.html

I figured out how to do both of these. Although I'm sure there are other ways of doing it, some maybe better than this, at least this works.

Display the employee’s name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Sunday, the Seventh of September, 1981.”

	SELECT ename, hiredate,
	TO_CHAR(NEXT_DAY(ADD_MONTHS(hiredate, 6), 'MON'), ‘fm
	“Monday, the“ ddspth  “of“  MONTH“,” YYYY’) as “REVIEW” 
	FROM emp;

This works, too:

	SELECT ename, hiredate,
	TO_CHAR(NEXT_DAY(ADD_MONTHS(hiredate, 6), 'MON'), ‘fmDay“,
	the“ ddspth  “of“  Month“,” YYYY’) as “REVIEW” 
	FROM emp;

For each employee display the employee name and calculate the number of months between today and the date the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed. Round the number of months up to the closest whole number.

SELECT ename, ROUND(MONTHS_BETWEEN(SYSDATE, hiredate)) as “MONTHS_WORKED” FROM emp
ORDER BY MONTHS_WORKED;

This post has been edited by OliveOyl3471: 26 June 2009 - 01:49 AM