Hi Everyone,

This is my problem;

PROBLEM 1:  I need to create a table called INSTRUCTOR_NEW with all of the information from INSTRUCTOR table.  Add a column to the instructor table called SALARY which should be a number with 2 decimal places.  Add a constraint to the INSTRUCTOR_NEW table called
 inst_max_sal that stipulates that no instructor can have a salary greater than $30
00(three thousand dollars)  (this is a weekly salary).

code:

CREATE TABLE instructor_new AS
      SELECT *
        FROM instructor;

 ALTER TABLE instructor_new
         ADD (salary    NUMBER(4,2)
  CONSTRAINT inst_max_sal
       CHECK (salary >= 0
         AND salary < 3000));
/

I used the code above however the problem I'm having is identifying the salary as 
a weekly salary.   How do I write that the MAX salary is $3000.00 per week i
n SQL?
   

In addition to doing your homework, do we also have to pay portion of
your tuition fees?

Daniel

[ Post a follow-up to this message ]

Well "Dan";

I am asking for help understanding the problem...isn't that what I asked for
?  "Help" doesn't always mean giving an answer, I would be happy with a hint
 or real advise.  However, your giving me sarchasm which is far from "help".

Thank You

PS: If you don't know, don't give sarchasm in return 

__________________
Hakim Z. Singhji
DBA Trainee
hakim.singhji@earthlink.net

Last edited by singhjih on 04-06-04 at 04:16 PM

[ Post a follow-up to this message ]

singhjih <singhjih.14a3a5@mail.webservertalk.com> wrote in message news:<sin
ghjih.14a3a5@mail.webservertalk.com>...

You do not specify what the INSTRUCTOR table looks like, so it is
difficult to help you. Why don't you include all the details?

Based on what you are saying, I am assuming you need an aggregate
function to enforce your constraint. If so, I think you will need to
use a before insert trigger on the table rather than a simple check
constraint, and inside the trigger, run the aggregate query to
determine whether or not the constraint is violated or not.

Dave

> Hi Everyone,
>
> This is my problem;
>
> *PROBLEM 1:*  I need to create a table called *INSTRUCTOR_NEW* with all
> of the information from *INSTRUCTOR* table.  Add a column to the
> instructor table called *SALARY* which should be a number with 2
> decimal places.  Add a constraint to the *INSTRUCTOR_NEW* table called
> *inst_max_sal* that stipulates that no instructor can have a salary
> greater than $3000(three thousand dollars)  (this is a weekly salary).
>
>
> Code:
> --------------------
>
>   CREATE TABLE instructor_new AS
>   SELECT *
>   FROM instructor;
>
>   ALTER TABLE instructor_new
>   ADD (salary    NUMBER(4,2)
>   CONSTRAINT inst_max_sal
>   CHECK (salary >= 0
>   AND salary < 3000));
>   /
>
> --------------------
>
> I used the code above however the problem I'm having is identifying the
> salary as a weekly salary.   *How do I write that the MAX salary is
> $3000.00 per week in SQL?
> *

[ Post a follow-up to this message ]

The table looks like the following:

code:

INSTRUCTOR_ID(PK)    NUMBER(8,0)     NOT NULL
SALUTATION                VARCHAR2(5)    NULL
FIRST_NAME                VARCHAR2(25)  NULL
LAST_NAME                 VARCHAR2(25)  NULL
STREET_ADDRESS       VARCHAR2(50)   NULL
ZIP(FK)                        VARCHAR2(5)    NOT NULL
PHONE                        VARCHAR2(15)
CREATED_BY               VARCHAR(3)
CREATED_DATE           DATE                NOT NULL


I'm not fully capable to write PL/SQL I just started last week however I am 
a very compitent student.  If I need to write a trigger could you lead me in
 the right direction.

singhjih wrote:

>
> The table looks like the following:
>
>
> Code:
> --------------------
>
>   INSTRUCTOR_ID(PK)    NUMBER(8,0)     NOT NULL
>   SALUTATION     VARCHAR2(5)    NULL
>   FIRST_NAME     VARCHAR2(25)  NULL
>   LAST_NAME     VARCHAR2(25)  NULL
>   STREET_ADDRESS     VARCHAR2(50)   NULL
>   ZIP(FK)     VARCHAR2(5)    NOT NULL
>   PHONE      VARCHAR2(15)
>   CREATED_BY      VARCHAR(3)
>   CREATED_DATE      DATE                NOT NULL
> --------------------
>
>
> I'm not fully capable to write PL/SQL I just started last week however
> I am a very compitent student.  If I need to write a trigger could you
> lead me in the right direction.
>
>

Oracle has a lot of good documentation on their site at
http://docs.oracle.com.  You should get familiar with the one called 'SQL
Reference' for language and syntax, and the one called PLSQL Reference (I
think) for general PLSQL.

CREATE TRIGGER and PLSQL Anonymous block are some keywords.

/Hans

[ Post a follow-up to this message ]

Hans Forbrich <forbrich@yahoo.net> wrote in message news:<enBcc.7559$Sh4.5308@edtnps84>...[
color=darkred]
> singhjih wrote:
>[/color]

 
>
> Oracle has a lot of good documentation on their site at
> http://docs.oracle.com.  You should get familiar with the one called 'SQL
> Reference' for language and syntax, and the one called PLSQL Reference (I
> think) for general PLSQL.
>
> CREATE TRIGGER and PLSQL Anonymous block are some keywords.
>
> /Hans

Hans gives good advice. Check to doc, its quite complete. The key to
the trigger is that you can reference the old and new values with
syntax :new.<col_name> and :old.<col_name> . You can write PL/SQL code
in the trigger that references the old and new value and thus create
complex constraints.

Suggestion. Instead of trying to solve your problem straight out. Try
to create a very simple trigger to get familiar with the concept and
to see it work. Then tackle your problem.

Dave

[ Post a follow-up to this message ]