Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table?

Examine the description of the EMP_DETAILS table given below:
name NULL TYPE
EMP_ID NOT NULL NUMBER
EMP_NAME NOT NULL VARCHAR2 (40)
EMP_IMAGE LONG
Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table?
(Choose two.)

A.
An EMP_IMAGE column can be included in the GROUP BY clause.

B.
An EMP_IMAGE column cannot be included in the ORDER BY clause.

C.
You cannot add a new column to the table with LONG as the data type.

D.
You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.

Show Hint

← Previous question

Next question →

Leave a Reply 14

wugy

Although, there is an error ‘ORA-02272: constrained column cannot be of LONG datatype’, but actually, my test as follow, it seems the ‘D’ is also right.

create table testlong (
EMP_ID NUMBER NOT NULL,
EMP_NAME VARCHAR2 (40) NOT NULL,
EMP_IMAGE LONG);

select constraint_name,column_name from user_cons_columns where table_name=’TESTLONG’;

CONSTRAINT_NAME COLUMN_NAME
—————————— ——————————
SYS_C0026829 EMP_ID
SYS_C0026830 EMP_NAME

alter table testlong modify (emp_image not null);
Table altered.

select constraint_name,column_name from user_cons_columns where table_name=’TESTLONG’;

CONSTRAINT_NAME COLUMN_NAME
—————————— ——————————
SYS_C0026829 EMP_ID
SYS_C0026830 EMP_NAME
SYS_C0026831 EMP_IMAGE

desc testlong
TABLE testlong
Name Null? Type
—————————————– ——– —————————-
EMP_ID NOT NULL NUMBER
EMP_NAME NOT NULL VARCHAR2(40)
EMP_IMAGE NOT NULL LONG

So, is it a bug?

Justyna

Can be a bug. I did not test it. In the SQL Fundamentals I is written:
“No constraints can be defined on a LONG column”.

Alvin2201

create table testlong (
EMP_ID NUMBER NOT NULL,
EMP_NAME VARCHAR2 (40) NOT NULL,
EMP_IMAGE LONG);

> TABLE CREATED

select constraint_name,column_name from user_cons_columns where table_name=’TESTLONG’;

> CONSTRAINT_NAME COLUMN_NAME

Alvin2201

CONSTRAINT_NAME COLUMN_NAME
—————————-
SYS_C0010319 EMP_NAME
SYS_C0010318 EMP_ID

alter table testlong modify (emp_image not null);

table TESTLONG modified

select constraint_name,column_name from user_cons_columns where table_name=’TESTLONG’;

CONSTRAINT_NAME COLUMN_NAME
————————–
SYS_C0010320 EMP_IMAGE
SYS_C0010319 EMP_NAME
SYS_C0010318 EMP_ID

INSERT INTO TESTLONG SELECT 1, ‘SMITH’ FROM DUAL;
ORA-00947. 00000 – “not enough values”

INSERT INTO TESTLONG SELECT 1, ‘SMITH’, ‘MY LONG TEXT’ FROM DUAL;

1 rows inserted;

in 12c option D is correct

Dragana

I think that the only constraint we can define for a long column is “not null” as well as it can appear in where clause in “is(not) null” condition. So, there are three correct statements here.

Raj

Why option C is correct?

Henrique

@Raj , Only one column can be used LONG per table.

singh

NYRIES

Considering option D, ALTER command won`t work if there is data in the table. In order to change constraints(ALTER COMMAND TO WORK) in any table , table must not consist any data in it.

bob

Not true

drop table t1;
Table dropped.

create table t1(id number, text long);
Table created.

insert into t1(id , text)
values(1, ‘Hello’);
1 row created.

select * from t1;
ID TEXT
———- ————————————————–
1 Hello
1 row selected.

select constraint_name,column_name from user_cons_columns where table_name=’T1′;
no rows selected.

alter table t1 modify (text not null);
Table altered.

select constraint_name,column_name from user_cons_columns where table_name=’T1′;

CONSTRAINT_NAME COLUMN_NAME
————— ———–
SYS_C0012145 TEXT

NYRIES

A is false as you cannot perform group by clause on image, B is true as ORDER BY clause cannot be performed on image, C is true becoz there can be only one LONG data in one table, D is false because , NOT NULL cannot be included if there is data in the table.