To understand this question, the user needs to know how to define foreign key constraints in SQL. A foreign key is a column or combination of columns that is used to establish a link between the data in two tables. The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
Let's go through each option and explain why it is right or wrong:
A. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);
This option is incorrect because it has a syntax error. The FOREIGN KEY constraint is not properly defined. It is missing parentheses around the column name, and the table name should be followed by the column name in parentheses.
B. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
This option is correct. It defines a FOREIGN KEY constraint called emp_deptno_fk on the deptno column of the EMP table. The constraint references the DEPTNO column of the DEPT table.
C. CRETE TABLE EM (empno NUMBER(4), ename VARCHAR2(35) deptno NUMBER (7,2) NOT NULL, CONSTRAINT em_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));
This option is incorrect because it has a syntax error. The table name is misspelled, and the FOREIGN KEY constraint is not properly defined. It should be defined before the REFERENCES keyword.
D. CREATE TABLE EMP (empno NUMBER (4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp deptno fk REFERENCES dept (deptno));
This option is incorrect because it has a syntax error. The FOREIGN KEY constraint is not properly defined. It is missing parentheses around the column name, and the table name should be followed by the column name in parentheses.
Therefore, the correct answer is:
The Answer is: B