Explain the Oracle SQL grouping query instances and explain the oraclesql instances

Explain the Oracle SQL grouping query instances and explain the oraclesql instances

I. Introduction

There is a user table. The query table structure statement is as follows:

select dbms_metadata.get_ddl('TABLE','USERS') from dual;

The table structure is as follows:

  CREATE TABLE "PMSS"."USERS"    (    "ID" NUMBER(10,0) NOT NULL ENABLE,     "NAME" VARCHAR2(40),     "AGE" NUMBER(10,0),     "DEPT" VARCHAR2(10),     "SALARY" NUMBER(10,0),     "BIRTHDAY" DATE,      PRIMARY KEY ("ID")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "PMSS"  ENABLE   ) SEGMENT CREATION IMMEDIATE   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "PMSS" 

Table

Ii. Query

1. How many people are there in all departments?

SELECT count(1),dept FROM usersWHERE 1=1GROUP BY dept;

Query Result

2. query the records of employees with higher salaries than the average salary of each department

First, check the average salary of each department:

Then, we are looking at the personal salary which is larger than the average salary of our own department:

The SQL statement is as follows:

SELECT u.* FROM USERS u,(SELECT u.DEPT,avg(u.SALARY) as avgSalary FROM USERS uGROUP BY u.DEPT) aWHERE 1=1AND u.DEPT=a.DEPTAND u.SALARY>a.avgSalary;

Iii. Analysis

Let's analyze why it cannot be written like this:

1. First, the sentence in parentheses is called a subquery. By default, the result of a subquery is a result set, which is essentially a table;

2. A table or result set must be followed by the FROM statement. Therefore, the subquery results must be placed behind the FROM statement;

4. The topic of the entire sentence query is:

When the execution of the first line is complete, the salaries of all users are queried. However, the subquery is required. Here we have two understandings:

First, the first line in the red box represents the parallel comma, and two sentences instead of two tables;

Type 2: The table in the red box of the first row represents the parallel comma, and the parallel table is two tables;

Which one is right?

Second:

Because:

Two sentences cannot be found;

5. The result set on both sides is in the form of multiplication. What if there are no restrictions?

A total of 25 records, no restrictions,

5. Restrictions: therefore, when filtering, you must add the department restrictions;