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

# after where or having:

# scalar quantum query (single-line subquery)

# column subquery (multi-row subquery)

# Row subquery (multiple rows and multiple columns)

Features: subqueries are placed in parentheses, generally on the right side of the condition, and scalar quantum queries are generally equipped with single-line operators.

One-line operator: > =

Column subqueries: generally used with multi-line operators

Multiline operators: in, any, some, all

# scalar quantum query

# case: employee information whose salary is higher than that of ABEL

SELECT * FROM employeesWHERE salary > (SELECT salary FROM employeesWHERE last_name='Abel')

# case: return the name, job_id and salary of the employee whose job_id is the same as employee 141and whose salary is more than employee 143s

SELECT last_name,job_id,salaryFROM employeesWHERE job_id= (SELECT job_id FROM employeesWHERE employee_id=141) AND salary > (SELECT salaryFROM employeesWHERE employee_id=143)

# case: returns the last_name, job_id and salary of the employee with the lowest salary

SELECT last_name,job_id,salaryFROM employeesWHERE salary= (SELECT MIN (salary) FROM employees)

# case: query the id and its minimum wage of departments whose minimum wage is greater than Department 50

SELECT department_id,MIN (salary) FROM employeesGROUP BY department_idHAVING MIN (salary) > (SELECT MIN (salary) FROM employees WHERE department_id=50)

# illegal use of scalar quantum query

SELECT department_id,MIN (salary) FROM employeesGROUP BY department_idHAVING MIN (salary) > (SELECT salary # one-line operator can only be used with scalar quantum queries, and this is column subquery FROM employees WHERE department_id=50)

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.