SQL for Data Analytics · Queries from diagrams

Queries from diagrams

Querying from Entity Relationship Diagrams4 min

Querying from Entity Relationship Diagrams4 min

A note on this video

Near the end of the video, I say the practice won't have you run any code. That was true for an older version of this course. On this site, every query in this unit's practice runs, and it's checked like in the other units.

From the question to the tables

In the video, I wanted the employees whose job can pay more than 10,000. The HR diagram shows that max_salary is in the jobs table. A line connects jobs to employees, through the job_id column in each. So the query starts from employees and joins jobs on job_id.

Pick two tables in the menus under this diagram. The diagram lights up the line or lines between them and writes a JOIN for each line. The menus start on employees and jobs.

Loading…
Write the query from the video, but show the first name, last name, and salary of every employee whose job has a maximum salary over 10,000, with the job's title and maximum salary.
⌘/Ctrl + Enter

max_salary is in jobs and the names are in employees, so join the two on job_id. Then filter with WHERE.

Following a chain of lines

Next in the video, I asked which department is in which region. No line connects departments to regions. The diagram shows a path through locations and countries instead. Departments has a location_id, locations has a country_id, and countries has a region_id.

Each line along the path is one JOIN. Each JOIN's ON uses the two columns its line connects. Trace the path in the diagram before you use the menus.

Loading…
Write a query that shows each department's name with the name of the region it's in.
⌘/Ctrl + Enter

Start from departments and add one JOIN for each line on the way to regions: locations, then countries, then regions. Run the query after each one.

Suppose you want each employee's region. Which tables does the query go through? Why can't it go straight from employees to locations?

How I'd answer it

It goes through employees, departments, locations, countries, and regions. No line connects employees to locations. An employee has a department_id, and the department has the location_id, so the only way from an employee to a location is through their department.

Try it

These are for your own practice. They aren't graded or turned in. Find the tables and the lines in the diagram before you write anything.

Loading…
Write a query that shows how many employees work in each region. Show the region's name and the count.
⌘/Ctrl + Enter

Pick employees and regions in the diagram's menus to see the joins. The path goes through departments, locations, and countries. Then group by the region's name.

Write a query that shows the first and last name of every dependent, with the name of the department their employee works in.
⌘/Ctrl + Enter

Both dependents and employees have first_name and last_name columns. Put dependents. in front of the ones you want.