s1/c# Language and Database Technology Foundation/11-Connection Query and group query

Group queries

The T-SQL statements that take a group query are as follows.

SELECT Courseid,avg (Score) As course average results

From score

GROUP by CourseID

(1) What is the number of students in each gender?

SELECT COUNT (*) as number of SSex from Students

GROUP by SSex

(2) Check the total number of people in each grade

SELECT COUNT (*) as grade number, Sgrade from Students

GROUP by Sgrade

(3) The average score of each account is queried and displayed in order from highest to lowest

SELECT Courseid,avg (Score) As course average score from score

GROUP by CourseID

ORDER by AVG (score) DESC

Multi-column Group query

SELECT COUNT (*) as number, Sgrade as grade, SSex as sex from Students

GROUP by Sgrade,ssex

ORDER by Sgrade

When you use the group by keyword, the columns that you can specify in the select list are limited and allow only the following items.

1. Columns to be grouped

2. An expression that returns a value for each grouping, such as the column computed by the aggregate function.

Grouping filtering using the HAVING clause

(1) Grades with a total population of more than 15

SELECT COUNT (*) as number of Sgrade as grade from Students

GROUP by Sgrade

But there is one condition: more than 15 of the grade. At this time, the conditional limit after grouping statistics is involved, the limit is count (*) >15. Using the WHERE clause at this time does not satisfy the query requirement, because the WHERE clause can only filter data that is not grouped before statistics. The filter for the grouped condition must use the HAVING clause, which simply says that the HAVING clause is used to filter the grouped data, and the "group" as a "column" to qualify the condition.

SELECT COUNT (*) as number of Sgrade as grade from Students

GROUP by Sgrade

Having COUNT (*) >15

(2) The average grade of the inquiry to achieve the passing of the course information

SELECT CourseID As course number, AVG (score) as course average score

From score

GROUP by CourseID

Having AVG (score) >=60

The HAVING and where clauses can be used together in the same SELECT statement, using the order of

Where→group by→having

Tip: In the SELECT statement, the Where, GROUP by, have clauses, and aggregate functions are executed in the following order: The WHERE clause removes data from the data source that does not match the search criteria; The GROUP BY clause collects data rows into groups, and statistical functions calculate statistical values for each group The HAVING clause strips out each group of data rows that do not conform to their group search criteria.

(3) Check the total number of passing students and the average score of each course

SELECT COUNT (*) as number, AVG (score) as average, CourseID as course from score

WHERE score>=60

GROUP by CourseID

(4) Track the total number of passes per course and pass average of 80 minutes

SELECT COUNT (*) as number, AVG (score) as average, CourseID as course from score

WHERE score>=60

GROUP by CourseID

Having AVG (score) >=80

(5) In the employee table according to the department, check the "department number with two and above employees with a salary of not less than 2000"

SELECT department number, COUNT (*) From Employee Information table

WHERE wage >=2000

Group BY department number

Having COUNT (*) >1

Classification of Multi-table join queries

A multi-table join query is actually querying data through the association of common columns between tables, which is the most important feature of relational database queries.

1, Internal connection query

Inner JOIN query is the most typical and most commonly used connection query, and he matches it according to the common columns in the table. In particular, an inner join query is typically used when there are primary foreign key relationships between two tables.

A. Specify the join condition in the WHERE clause

For example, the T-SQL to query student names and grades is as follows.

SELECT Students.sname,score.courseid,score.score

From Students,score

WHERE Students.scode=score.studentid

This form of query above is equivalent to the two table names immediately following the From, then the column is distinguished by "table name. Column Name" In the field list, which is then judged in the WHERE condition clause, requiring the student number information to be equal.

B. Use inner JOIN in the FROM clause ... On

The above query can also be done through the following join ... ON clause to implement.

SELECT S.sname,c.courseid,c.score

From Students as S

INNER JOIN score as C on (S.scode = C.studentid)

In the above internal connection query:

• INNER join to connect two tables
• Inner can be omitted
• On to set conditions
• As specifies an "alias" for the table. If the column name of the query is not duplicated in the two or more tables used, the reference to the column does not have to be qualified with the table name.

Look again at the T-SQL statement

SELECT S.sname,c.courseid,c.score

From Students as S

INNER JOIN score as C on (S.scode = C.studentid)

WHERE c.score>=60 and C.courseid=1

In-connection queries are usually not only connected to two tables, but sometimes involve three or more tables. For example, a curriculum is available in addition to student information sheets and student scores. The above query not only shows the student's name, score, but also the course number to display the name of the corresponding course in the Course Name table, you can use the following three tables to connect the query T-SQL statement to implement.

SELECT S.sname as student name, CS. Coursename as course name, C.score as test results

From Students as S

INNER JOIN score as C on (S.scode=c.studentid)

INNER JOIN Course as CS on (Cs.courseid=c.courseid)

The result of an inner join query is to pick the data that meets the join criteria from the combination of two or more tables, ignoring the data if it cannot meet the join criteria. In an internal connection query, the table participating in the join is equal in status.

The opposite of an inner join query is called an outer join query. The tables participating in the outer JOIN query have master-slave points, matching the data columns from the table with each row of the primary table, returning the data that meets the join criteria directly to the result set, and then returning to the result set by filling in the null value (null value) for those columns that do not meet the join criteria.

2, External connection query

An outer join query returns at least all records from one table, and optionally returns records for another table based on the matching criteria. External links can be left outer joins, right outer joins.

A, LEFT outer connection query

The result set of the left OUTER join query includes all rows of the left table specified in the right-join clause, not just the rows that match the join column. If a row in the left table does not have a matching row in the right table, all the selection columns of the right table in the associated result set row are null values.

The left OUTER join query uses ... On or left OUTER JOIN ... The ON keyword to associate the tables. For example, to count all students ' exams, ask to show each test score for each test student, and students who do not have a arguments exam will be shown. At this time, the Student information table is the main table (sometimes called left table), the Student score table for the left outer connection from the table to query the following T-SQL statement is shown.

SELECT S.sname,c.courseid,c.score

From Students as S

Left OUTER joins score as C on S.scode=c.studentid

In the Student information table, each record is matched with the record of the score table (the matching condition is S.scode=c.studentid). If the match succeeds, it returns to the recordset (the value of S.sname,c.courseid,c.score), and if no matching record is found, a null value is populated to populate the recordset.

Some of the students did not take any course exams, so there is no relevant Test record in the score table, the corresponding account number and scores are filled with null (null value).

B, right outer connection query

A right outer join query is similar to a left OUTER join query except that all matching rows in the right table are included. If there are items in the right table that do not have corresponding entries in the left table, they are populated with null values.

The right outer join query is to use the starboard join ... On or right OUTER JOIN ... The ON keyword to associate the tables. For example, in a database, the right outer join between the Book table titles and the Publisher table publishers will include all publishers, and publishers that do not have a title in the titles table (which has not been published for a long time) will also be listed.

SELECT Titles.title_id,titles.title,publishers.pub_name

From titles

Right OUTER JOIN Publishers on titles.pub_id = publishers.pub_id

s1/c# Language and Database Technology Foundation/11-Connection Query and group query