SQL-server data retrieval (Advanced query)

1. Multi-table join
ANSI Syntax:
Select *
From table 1 Join table 2
On Table 1. Column = TABLE 2. Column SQL Server Syntax:
Select *
From table 1, Table 2 ......
Where table 1. Column = TABLE 2. Column and ...... Select *
From student join SC
On student. Sno = SC. SnO
Where sname = 'zhaopeng' Select student .*
From SC join student
On SC. Sno = student. SnO
Where CNO = 'c101' Select student *
From SC, student
Where SC. Sno * = student. SnO and CNO = 'c101' 1) Internal Connection
[Inner] Join
2) left Outer Join
Left [outer] Join
3) Right Outer Join
Right [outer] Join
4) full connection (displayed both left and right)
Full [outer] Join
5) cross join
Cross join
Select *
From SC cross join student
Note: 1. The column name must be clear. Duplicate column names must be indicated.
2. If the alias is used instead, it indicates that the alias must be used during connection.
3. Default internal connection
4. Table name. * indicates all columns in a table. A. query the student ID, name, and date of birth for the c101 course.
Select SC. Sno, sname, sbrithday
Select student. *, grade
From student join SC
On student. Sno = SC. SnO
Where CNO = 'c101' B. query all exam scores of 001 students
Select *
From SC
Where SnO = '001' C. query all the exam information of Liu Peng
Select *
From student join SC
On student. Sno = SC. SnO
Where sname = 'Liu peng' D. query the student ID and name of the student whose course name is database.
Select SC. Sno, sname
From student join SC
On student. Sno = SC. SnO
Join Course
On course. CNO = SC. CNO
Where cname = 'database principles and Applications' E. query the student ID, name, course name and score of each electives.
Select *
From student join SC
On student. Sno = SC. SnO
Join Course
On course. CNO = SC. CNO
F. query the student ID, name, elective course number, and score for each student.
G. query the Course selections of each student
Select *
From student left join SC
On student. Sno = SC. SnO
Right join Course
On course. CNO = SC. CNO Exercise
1. query the customer ID with orderid 10248, customer name, salesman Id, and salesman name
Select orders. customerid, companyName,
Orders. saleid, salename From orders join MERs
On orders. customerid = Customer. MERs. customerid
Join salers
On orders. saleid = salers. saleid
Where orderid = '123' 2. display the information of all customers and the order ID, salesperson ID and order date of their products.
Select MERs. *, orderid, saleid, orderdate
From MERs left join orders
On customers. custormerid = orders. customerid Select *
From MERs 2. Grouping and summary
1) aggregation (aggregate function)
Select function name (column) Select AVG (grade)
From SC
Select max (grade)
From SC
Select min (grade)
From SC If an aggregate function is used after select, Columns cannot appear.
Null does not count Select count (*)
From student Select min (price)
From Products Select top 1 *
From Products
Order by price Select sum (price * stocks) '', max (price * stocks), min (price * stocks)
From Products Select distinct SnO
From SC Select count (distinct SnO)
From SC
Where Grade <60 or grade is null Count (distinct | all column) Select AVG (grade)
From SC
Where CNO = 'c101' 2) Group Summary
Note:
You can specify columns after select with the aggregate function or group.
Keyword: group by column name 1, column name 2... Having condition -- filter the grouped Summary Results Where condition -- filter source table results
Where> group by> having Select AVG (grade), CNO
From SC
Group by CNO Select AVG (grade) 'average', sum (grade), count (SNO), SnO
From SC
Group by SnO
Having AVG (grade)> 80 The average score for each passing score is greater than 80.
Select AVG (grade), SnO
From SC
Where grade> 60
Group by SnO
Having AVG (grade)> 80 In the sales database, the average price and total rate of these products are listed based on the price entries in price 5 and average price 15 of all types.
Select AVG (price), sum (stocks), categoryid
From Products
Where price> 5
Group by categoryid
Having AVG (price)> 15 Multiple groups
Select *
From Products
Where categoryid = '1' Select sum (stocks), categoryid, price
From Products
Group by categoryid, price 3) Detailed summary (optional)
Compute Aggregate functions Order by Column
Compute by Aggregate functions
By Column
Note: The number and sequence of Columns after 'by' must be consistent with that of 'ORDER '. Select *
From SC
Order by SnO
Compute AVG (grade)
By SnO Calculate the total score and average score of each course, including the course score and detailed information.
Select *
From SC
Order by CNO
Compute AVG (grade), sum (grade)
By CNO Select *
From SC
Compute AVG (grade) 4) nested Query
A. Single-value Query
If it is an equal sign, it must be followed by a single value
Select *
From student
Where sdept = (
Select sdept
From student
Where sname = 'dugang'
) Select *
From student
Where ssex = (
Select ssex
From student
Where sname = 'wang mei ')   Query the information of people in the same department as du gang.
Select *
From student
Where sname = 'dugang' Select *
From student
Where sdept = 'department of Computer Science and Technology' Customer information whose order number is 10249
Select *
From MERs join orders
On customers. customerid = orders. customerid
Where orderid = '123' Select *
From MERs
Where customerid = (
Select customerid
From orders
Where orderid = '123'
) C101 score> average student ID
Select AVG (grade)
From SC
Where CNO = 'c101' Select SnO
From SC
Where CNO = 'c101 'and grade> (
Select AVG (grade)
From SC
Where CNO = 'c101'
) B. Single Column multi-value
Any, all, in > Any (greater than any value in it)
<> Any (not equal to any ,)
= Any > All (compare with no value in it)
<> All In = any (just one of them)
Not in = <> All Query the product information of order 10249.
Select productid
From orderdetails
Where orderid = '123' Select *
From Products
Where productid = any (
Select productid
From orderdetails
Where orderid = '123'
) Select stocks
From Products
Where stocks> All ( ) 5). Create a new table
Select column
Into new table
From source table Select *
Into girls
From student
Where ssex = 'female' Query the names, student IDs, and age of Family Planning students.
Select sname, SnO, year (getdate ()-year (sbrithday) 'age'
Into T1
From student
Where sdept = 'department of Computer Science and Technology' Select *
From T1   Summary:
Select [All | distinct] [Top N percent] column
[Into new table]
From table
[Where filter condition]
[Group by group column]
[Having filtering and aggregation conditions]
[Order by sequence [ASC | DESC]