0

I have any problem with homework, could you help me? :)

  1. Create table CONTEST that will store data about chess contest for the employees in the company. Columns of the table:

Empno digits, up to 6 digits (employee numer) firstname text, up to 20 characters surname text, up to 30 characters startdate date (the date of a game during contest) department digits, up to 3 digits score digits, the highest possible score is 500.

I have tried following codes: score number(3) <= 500 score number(3) between 0 and 500

create table contest1
(empno number(6),
firstname varchar2(20),
surname varchar2(30),
startdate date,
department number(3),
score number(3) between 0 and 500)
jarlh
  • 42,561
  • 8
  • 45
  • 63
matklim
  • 3
  • 1
  • Strange design. I'd have a foreign key to the table employees, where first name and last name should be stored. – jarlh Jun 14 '19 at 09:40

3 Answers3

2

In Oracle, we can use CHECK constraint. You can give a constraint name and condition. For your reference, I have given a simple example.

Try this:

create table contest1
(empno number(6),
firstname varchar2(20),
surname varchar2(30),
startdate date,
department number(3),
score number(3) CHECK (score between 0 and 500))

Demo

Cheers!!

EDIT

Demo with insert and errors: link

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
Popeye
  • 35,427
  • 4
  • 10
  • 31
1

You need to add a contraint

create table contest1
(empno number(6),
firstname varchar2(20),
surname varchar2(30),
startdate date,
department number(3),
score number(3) CONSTRAINT score CHECK (score BETWEEN 0 AND 500))
David Weber
  • 86
  • 5
1

Your size limits are correct. The 500 as maximum value can be resolved via a check constraint, as David Weber explained. You can also create a trigger before insert or update and do the checking there, if you want something more complex, like a log record to be created, for example.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175