Informatics Practices

Fill in the blanks:

2 Likes

Answered By

1 Like

Examine the structure of the EMPL and DEPT tables:

Table : EMPL

Column name	Data type	Remarks
EMPLOYEE_ID	NUMBER	NOT NULL, Primary Key
EMP_NAME	VARCHAR(30)
JOB_ID	VARCHAR(20)
SALARY	NUMBER
MGR_ID	NUMBER	References EMPLOYEE_ID COLUMN
DEPARTMENT ID	NUMBER	Foreign key to DEPARTMENT ID column of the DEPT table

Table : DEPT

Column name	Data type	Remarks
DEPARTMENT_ID	NUMBER	NOT NULL, Primary Key
DEPARTMENT_NAME	VARCHAR(30)
MGR_ID	NUMBER	References MGR_ID column of the EMPL table

Evaluate this SQL statement :

SELECT employee_id, e.department_id, department_ name, salary 
FROM EMPL e, DEPT d
WHERE e.department_id = d.department_id;

Which SQL statement is equivalent to the above SQL statement ?

(a)

SELECT employee_id, department_id, department_name, salary 
FROM EMPL
WHERE department_id MATCHES department_id of DEPT;

(b)

SELECT employee_id, department_id, department_name,salary 
FROM EMPL
NATURAL JOIN DEPT;

(c)

SELECT employee_id, d.department_id, department_name,salary
FROM EMPL e 
JOIN DEPT d 
ON e.department_id = d.department_id;

(d)

SELECT employee_id, department_id, department_name,salary
FROM EMPL 
JOIN DEPT 
USING (e. department _id, d.department_id);