Home » Other » Training & Certification » Question for this problem
Question for this problem [message #138230] Tue, 20 September 2005 16:20
liuyanhong1
Messages: 3
Registered: September 2005
Junior Member
Hi,

Can some SQL experts solve the following problem:

DEPARTMENTS
DEPARTMENT_ID NUMBER NOT NULL, Primary Key
DEPARTMENT_NAME VARCHAR2
(30)
MGR_ID NUMBER References NGR_ID column of the

EMPLOYEES table
LOCATION_ID NUMBER Foreign key to LOCATION_ID column of the
LOCATIONS table

LOCATIONS
LOCATION_ID NUMBER NOT NULL, Primary Key
CITY VARCHAR2 |30)

Which two SQL statements produce the name, department name, and the city of all the
employees who earn more then 10000? (Choose two)

A. SELECT emp_name, department_name, city
FROM employees e
JOIN departments d
USING (department_id)
JOIN locations 1
USING (location_id)
WHERE salary > 10000;
B. SELECT emp_name, department_name, city
FROM employees e, departments d, locations 1
JOIN ON (e.department_id = d.department id)
AND (d.location_id =1.location_id)
AND salary > 10000;
C. SELECT emp_name, department_name, city
FROM employees e, departments d, locations 1
WHERE salary > 10000;
D. SELECT emp_name, department_name, city
FROM employees e, departments d, locations 1
WHERE e.department_id = d.department_id
AND d.location_id = 1.location_id
AND salary > 10000;
E. SELECT emp_name, department_name, city
FROM employees e
NATURAL JOIN departments, locations
WHERE salary > 10000;
Answer given: B, D

But I think B could be wrong. How can one join used in more than 2 tables. I choose A, D.

Please tell me which one is right. If B is right, could you tell me which site can find the proof. Thanks in advance!

Previous Topic: Advice on best way for oca/ocp developer
Next Topic: ORACLE 11i Apps DBA Trtaining
Goto Forum:
  


Current Time: Mon Apr 06 00:40:40 CDT 2020