haider haider - 1 month ago 6
SQL Question

sql multiple queries

[I have tried sub queries and join queries but i did not get right answer i need to retrieve data from different tables. can someone help me please. I need answer of this question

Show the department Name, Street Address and
City of the department location and the Country Name
where the department is located for an employee
with the name Den Raphaely

I have tables like this.

COUNTRIES TABLE


  • country_id

  • country_name

  • region_id



DEPARTMENT TABLE


  • department_id

  • department_name

  • manager_id

  • location_id



EMPLOYEES TABLE


  • first_name

  • last_name

  • email

  • phone

  • salary

  • commission

  • department_id

  • job_id

  • hire_date



JOBS_HISTORY TABLE


  • employee_id

  • start_date

  • end_date

  • job_id

  • department_id



JOB TABLE


  • Job_id

  • Min_salary

  • Max_Salary



LOCATIONS TABLE


  • Location_id

  • Street_address

  • Postal_code

  • State

  • Country_id



REGION TABLE


  • region_id

  • region_name][1]



This is the attempted query

SELECT Employees.FIRST_NAME, Employees.LAST_NAME,
Departments.DEPARTMENT_NAME, Locations.Street_Address,
Locations.City, Countries.Country_Name FROM
Countries INNER JOIN Locations ON
Countries.Country_ID=Locations.Country_ID
INNER JOIN Departments ON
Locations.Location_id=Departments.LOCATION_ID
INNER JOIN Employees ON
Departments.DEPARTMENT_ID = (SELECT Employees.DEPARTMENT_ID FROM Employees
WHERE FIRST_NAME LIKE 'Den' AND LAST_NAME LIKE 'Raphaely')

Answer
SELECT e.first_name, e.last_name, d.department_name, l.street_address, l.city, c.country_name
FROM
employees e
INNER JOIN
department d
ON e.department_id = d.department_id
INNER JOIN
location l
ON d.location_id = l.location_id
INNER JOIN country c
ON c.country_id = l.country_id
WHERE
e.first_name = 'Den'
AND
e.last_name = 'Raphaely'
Comments