SQL for Data Analytics · Queries from diagrams
Queries from diagrams
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.
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.
max_salary is in jobs and the names are in employees, so join the two on job_id. Then filter with WHERE.
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.
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.
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.
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.
Both dependents and employees have first_name and last_name columns. Put dependents. in front of the ones you want.