Which is the valid DDL statement for creating the ORD_DETAIL table?

You want to create an ORD_DETAIL table to store details for an order placed having the following
business requirement:
1) The order ID will be unique and cannot have null values.
2) The order date cannot have null values and the default should be the current date.
3) The order amount should not be less than 50.
4) The order status will have values either shipped or not shipped.
5) The order payment mode should be cheque, credit card, or cash on delivery (COD).
Which is the valid DDL statement for creating the ORD_DETAIL table?

You want to create an ORD_DETAIL table to store details for an order placed having the following
business requirement:
1) The order ID will be unique and cannot have null values.
2) The order date cannot have null values and the default should be the current date.
3) The order amount should not be less than 50.
4) The order status will have values either shipped or not shipped.
5) The order payment mode should be cheque, credit card, or cash on delivery (COD).
Which is the valid DDL statement for creating the ORD_DETAIL table?

A.
CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN (‘Shipped’, ‘Not Shipped’)),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN (‘Cheque’, ‘Credit Card’,
‘Cash On Delivery’)));

B.
CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN (‘Shipped’, ‘Not Shipped’)),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN (‘Cheque’, ‘Credit Card’,
‘Cash On Delivery’)));

C.
CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN (‘Shipped’, ‘Not Shipped’)),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN (‘Cheque’, ‘Credit Card’,
‘Cash On Delivery’)));

D.
CREATE TABLE ord_details
(ord_id NUMBER(2),
ord_date DATE NOT NULL DEFAULT SYSDATE,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN (‘Shipped’, ‘Not Shipped’)),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN (‘Cheque’, ‘Credit Card’,
‘Cash On Delivery’)));



Leave a Reply 14

Your email address will not be published. Required fields are marked *


Skomi

Skomi

Hi,
ok that C should be answer, but why could not be also and B if question asked for column ORD_ID should be UNIQUE and NOT NULL column and that have same function as PRIMARY KEY

mr_tienvu

mr_tienvu

I think you have smart question. But there should have PRIMARY KEY in a table, and it will be queried or index better than none.

user2

user2

B is wrong due to “ord_amount > 50”

C has “ord_amount >= 50”

Alvin2201

Alvin2201

Yes but B with “ord_amount > 50” satisfies the business requirement that “The order amount should not be less than 50.”
They should reverse the sentence: “The order amount can be equal or more than 50.” otherwise is ambiguous.

know

know

“The order amount should not be less than 50.” -> means can be more or equal 50. So you have C or D to choose. Then D has no constraint on ord_id so it is wrong according to “The order ID will be unique and cannot have null values.” After elimination only C left.

Branden Chu Arnold

Branden Chu Arnold

Why isn’t it A?

I thought there needed to be a group by

mr. binsted

mr. binsted

I like to hump semen

i love to hump myself

i love to hump myself

answer w is wrong

all i like to do is fuck

all i like to do is fuck

696969696

Devan M

Devan M

I have weed in my trunk

sky

sky

ord_date Date Default sysdate — this ans is wrong.
bcz sysdate can not be used as default date.