Shulou(Shulou.com)06/01 Report--

Add screening

# case: employee name and department name with bonus

SELECT last_name,department_nameFROM employees AS e,departments AS dWHERE e.department_id=d.department_idAND e.commission_pct IS NOT NULL

# case: query the department name and city name with the second character o in the city name

SELECT department_name,cityFROM departments AS d,locations AS lWHERE d.location_id=l.location_idAND l.city LIKE'_o%'

Add grouping

# case: query the number of departments in each city

SELECT COUNT (*), cityFROM departments AS dJ AS lWHERE d.`location _ id` = l.`location _ id`GROUP BY city

# case: query the department name and leadership number of each department with bonus and the minimum wage of that department

SELECT department_name,d.manager_id,MIN (salary) FROM employees AS e-mai AS dWHERE d.department_id=e.department_idAND commission_pct IS NOT NULLGROUP BY department_name,d.manager_id

Add sort

# case: query the name of each type of work and the number of employees, and descend by the number of employees

SELECT job_title,COUNT (*) FROM employees AS ejol jobs AS jWHERE e.job_id=j.job_idGROUP BY job_titleORDER BY COUNT (*) DESC

Three-meter connection

# case: query employee name, department name, city

SELECT last_name,department_name,cityFROM employees AS eJournal "AS d Magi" AS lWHERE e.departmentformidy = d.`department _ id`and d.`location _ id` = l.location_id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.