Advanced Transact-SQL query (I)

TransAct --- SQL advanced query (I)
1: Multi-Table query and flute Product
2: table alias usage
3: use SQL server statistical functions
4: Use the group by clause to query groups

A: Multi-Table query and deyr Product
So far, we have used a single table query, but in more cases, we need to query multiple tables at the same time, in this case, you can write all the names of multiple tables in the from clause.
For example, you can query the name, education level, and department name of each employee. because the results we need come from two tables, we must use multiple tables to query the Select name, Education, Department name, owner from work, Department [analyze why it is wrong]
Cause: the problem lies in the restrictions on table connection conditions. in the preceding statement, the table connection condition is not restricted. Therefore, the SQL statement extracts a record from the work table and combines it with all records in the department table, assume that the work table has m records, and the department table has n records, then the result is M * n records, which is the deyr product, therefore, most of the results returned by the deliron product are redundant and useless. Therefore, the generation of the deliron product should be avoided.
Solution: in fact, due to the fact that there are no restrictions on the join conditions of the two tables, we only need to restrict the join conditions of the two tables, this avoids the generation of delierji. you can use a where clause to connect the public fields of two tables.
Therefore, change the preceding statement to select name, education level, Department name, owner from work, department where work. Department ID = Department. Department ID

B: use the table alias.
A: When multiple tables are used for query, if two tables have the same columns, you should specify the columns in the selected table.
For example, in the work table, retrieve the employee ID, name, education level, and basic salary of all employees in the address table.
Select employee ID, name, education, basic salary from work, address where work. employee ID = address. employee ID
The preceding statement is incorrect because the table "work" and "Address" have employee ID and name columns. Therefore, you must specify the employee ID and name of the table.
Changed to: select work. employee ID, work. Name, education level, basic salary from work, address where work. employee ID = address. employee ID
Or: Select address. employee ID, address. Name, education level, basic salary from work, address where work. employee ID = address. employee ID
Think about it: for education qualifications, the basic salary does not specify the table name: Work. education, work. Basic Salary [only one table has these columns]
B: You can use aliases to access tables.
Format: 1: Table name as Alias
2: Table Name alias
For example, the preceding statement can be changed:
Select W. Employee No., W. Name, education level, basic salary from work as W, address as a where W. Employee No. = A. Employee No.
In the preceding statement, two tables are referenced in the from statement, and the alias W is specified for the table work and the alias A is specified for the table address. Therefore, W can be used to represent the work table, A is used to represent the address table. or, if the value of AS is omitted, change it to select W. employee ID, W. name, education, basic salary from work W, address a where W. employee ID =. employee ID
C: If an alias is used, aliases must be used in all subsequent query statements.
For example: select work. employee ID, work. Name, education level, basic salary from work W, address a where W. employee ID = A. employee ID [incorrect]

C: use statistical functions:
SQL provides the following statistical functions:
Sum: returns the sum of a number column.
AVG: calculates the average value for a numeric column.
Min: calculates the minimum value for a numeric column.
MAX: returns the maximum value for a numeric column.
Count: returns the number of records that meet the conditions specified in the SELECT statement.
Example: 1: Calculate the sum of the basic salary of all male employees in the work table.
Select sum (basic salary) as gender: basic salary of male from work where gender = \ 'male \'
2: Find the maximum salary and minimum wage of the manager for all titles in the work table. Average salary
Select max (basic salary) as highest salary, min (basic salary) as minimum wage, AVG (average salary) as average salary from work
3: Use the distinct keyword with the statistical function [usually only used with the count function]
Example: 1: retrieve the number of academic qualifications in the work table.
Select count (education level) from work
2: retrieve the number of types of academic qualifications in the work table.
Select count (distinct degree) from work
Try: Select distinct count (education level) from work is feasible?
3: There are work and department tables to search for the number of employees working in the sales department
Select \ 'sales department count \ '= count (employee ID) from work A, Department B
Where a. Department No. = B. Department No. and B. Department name = \ 'sales Department \'
4: Find the number of employees whose basic salary is smaller than the average salary in the work table.
Select count (employee number) as number of people from work
Where basic salary <(select AVG (basic salary) from work)
5: There are subject tables and tuition tables, and the number of students on the Learning Web page is retrieved from the tuition table.
Select count (student ID) as number of people designed on the webpage from tuition A, Subject B
Where a. Major code = B. Course number and B. Course name = \ 'webpage design \'

D: Use the group by clause to classify the results [only used for statistical functions]
Column name: 1: The number of people with different job titles in the work table.
Select title, count (title) as Title number of people from work group by title
2: retrieve the average salary of each degree.
Select degree, AVG (basic salary) from work group by degree
3: the number of students in each subject must be calculated based on the subject table and tuition table.
Select major code, count (Major Code) as number of students into # ABC from tuition group by professional code
Select Course name, number of students from # ABC, subject where major code = course number
4: There are employee tables and commodity sales tables, and the employee number, name, and total sales amount of each employee must be retrieved.
Select employee number, sum (sales volume) as sales volume into # ABCD from commodity sales group by employee number
Select employee. employee ID, name, total sales volume from employee, # ABCD where # ABCD. employee ID = employee. employee ID
5: Find the highest basic salary for each department, and display the Department name and the highest basic salary.
Select Department name, max (basic salary) from work group by department name
Note: 1: aliases allocated to column names are not supported in group.
Select degree as employee degree, count (degree) from work group by employee degree [incorrect]
Changed to: select education, as education, count (education) from work group by education
2: The data of each column after select must appear in the group by clause except for the columns in the statistical function.
For example: Select degree, gender, sum (basic salary) from work group by degree [incorrect]
Changed to: select education, gender, sum (basic salary) from work group by education, gender
Meaning: The sum of the basic salaries of different education backgrounds and genders