New Years Resolution–test cases

If the following email came across your desk

“I have two tables EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) and DEPT(DEPTNO,DNAME,LOCATION).  When I join them I’m getting multiple rows for the same employee”

my question would be – how would you proceed ? What is the first thing you need to do to even begin to assist ?

You need data.  And before you can have data, you need to have tables in which to store that data.

But there’s a problem:

SQL> EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
SP2-0734: unknown command beginning "EMP(EMPNO,..." - rest of line ignored.

Nope…that doesn’t work. Let’s try CREATE TABLE in front of it

SQL> create table EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
  2  /
create table EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
                 *
ERROR at line 1:
ORA-02263: need to specify the datatype for this column

Nope…that doesn’t work either. In fact, its pretty obvious that there isn’t anything useful that we can use here.

So someone needs to convert the information above, into something that is actually usable, ie,

CREATE TABLE DEPT
 (    DEPTNO NUMBER(2,0),
      DNAME VARCHAR2(14),
      LOC VARCHAR2(13),
       CONSTRAINT DEPT_PK PRIMARY KEY (DEPTNO)
) 
  
CREATE TABLE EMP
 (    EMPNO NUMBER(4,0) NOT NULL ENABLE,
      ENAME VARCHAR2(10),
      JOB VARCHAR2(9),
      MGR NUMBER(4,0),
      HIREDATE DATE,
      SAL NUMBER(7,2),
      COMM NUMBER(7,2),
      DEPTNO NUMBER(2,0),
       CONSTRAINT EMP_PK PRIMARY KEY (EMPNO),
       CONSTRAINT EMP_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO)
 ) 

Now then – who do you think is best equipped to do that ? The person who asked the question, ie, the person who actually HAS the tables at their disposal, or someone who has never seen the tables, has no idea of what the datatypes  might be, and no idea of the data inside them ?  I think to answer to that is self-evident.

So please… if you’re asking anyone for assistance, whether it be via email, or blog, or forum or even verbally, please keep in mind that a casual mention of table names and column names is pretty much worthless.  Runnable DDL matters, and meaningful test data matters.   It takes only a few seconds to run DBMS_METADATA.GET_DDL or any modern GUI to get the DDL and/or test data insertion statements.