SQL subquery and group by Query

1. subquery in SQL:

When we are doing SQL questions, we are most afraid of complex query statements, because most of the time we need two or three subqueries for implementation,

Most of the students are afraid of subqueries. I was confused by these questions,

However, after doing a lot of such questions, we found that there are also some rules. We can sum up a set of methods suitable for ourselves to deal with them,

The following is my solution to this type of question:

A shopping table is as follows:

Question: query the information of a shopper who has more than one type of shopping item.

 

I split the question as follows:

 

Two query statements can be obtained:

 

<1> query the shopping information of a shopper.

 

Select * from shopping info table where shopper in ......

 

<2> query shoppers with more than one type of purchased item.

 

Select shopper from shopping info table group by shopper having count (*)> = 2

 

After the statement <2> is added to the statement <1>, a correct answer is obtained.

 

Analyze the statements from the external, take out simple query statements, and then combine them.

Of course, my question here is that my temporary thinking may be relatively simple. I have seen such a statement.

Query ...... ...... ...... ...... ....... It can be said that there may be two or three or more subqueries, and a good method is required.

2. Grouping query in SQL (usually used together with Aggregate functions ):

Student Information table:

 

Question: 1. query the average score of each grade and class;

The average score of the query in article 2 is greater than 580.

1 solution: Select grade, AVG (total score) as "average score"

From student information table

Group by grade

Illustration:

 

 

 

Select AVG (total score) as "average score" from student info table

 

 

Select AVG (total score) as "average score" from student info table

Solution: Select grade, class, AVG (total score) as "average score"

From student information table

Group by grade, Class

Select AVG (total score) as "average score" from student info table

 

Select AVG (total score) as "average score" from student info table

 

Select AVG (total score) as "average score" from student info table

 

Select AVG (total score) as "average score" from student info table

2 solution:

 

Select grade, class, AVG (total score) as "average score"

From student information table

Group by grade, Class

Having average score> 580

 

Having is equivalent to where, having is used for the condition after group by, and where is used for the condition before group.