SQL class notes-multi-table query

2017.11.14

Second: The most basic connection query----multi-table Query
1. Using an internal connection
Internal connections are also called natural connections, the most common form of connection
Grammar:
Select list from table name 1, table name 2 where table name 1. column name 1= table name 2. Column Name 2
Or select select list from table name 1 join table name 2 on table name 1. column name 1= table name 2. Column Name 2

Example 1: Query each student and their elective courses:
Select student.*,sc.* from STUDENT,SC where Student.sno=sc.sno
Select student from STUDENT,SC where Studnet.sno=sc.sno
Select Student.sno,sname,cno from STUDENT,SC where STUDNET.SNO=SC.SNO (unique column can not write source)

Example 2: Query all student's number, name and score:
Select X.sno,x.sname,y.grade from student as X, SC as y where X.sno=y.sno
or select X.sno,sname,grade

Self-connection (a table and its own connection):

Example 3: Query the names and ages of all students older than ' Liu Chen ':
Select X.sname,x.sage from student as x,student as Y where X.sage>y.sage and Y.sname= ' Liu Chen '

Three: Compound conditional join (WHERE clause contains multiple join conditions)

Example 4: Study number of all students who have enrolled in course 2nd and have achieved more than 90 points, name:
Select Student.sno,student.sname from STUDENT,SC where Student.sno=sc.sno and sc.cno= ' 2 ' and sc.grade>90

Example 5: The names of the top 3 students of the 2nd course are queried:
Select Top 3 sname as top 3 students from STUDENT,SC where Student.sno=sc.sno and sc.cno=2 order BY sc.grade Desc

Example 6: Query each student's school number, name, elective course name and results:
Select Student.sno,sname,cname,grade from Student,sc,course where Student.sno=sc.sno and Sc.cno=course.cno

V: The connection specified with the Join keyword:
Merging data from multiple tables and involving connections between multiple tables, connections between multiple tables can be divided into inner joins, outer joins, and cross-joins. Connected tables can come from the same or different databases

1. Internal connection:
Select column name from < data source > Join < data source > on even table condition
Note: (1) The inner connection merges two tables according to the join conditions specified on, returning rows that meet the criteria
(2) function equivalence using a WHERE clause to query a table

1. In the student database, inquiry number, name, score
Select Student.sno,sname,grade from student join SC on STUDENT.SNO=SC.SNO
Select X.sno,sname,grade from
Multiple table connections:
From (Table 1 join table 2 on table 1. Field number = superscript. field number join table 3 on table 1. Field number = Table 3. Field number)
Example 6: Query each student's school number, name, elective course name and results:
Select Student.sno,sname,cname,grade from Student,sc,course where Student.sno=sc.sno and Sc.cno=course.cno
Select Studnet.sno,sname,cname,grade from (student join SC in STUDENT.SNO=SC.SNO) Join course on SC.CNO=COURSE.CNO

2. External connection:
3 Different types:
1. Left OUTER JOIN-out joins returns all matching rows and returns from the table to the left of the key value join
2. Right outer connection
3. Full external connection
Grammar:
Select column name from < data source > [Left][right][full]oueter Join < data source > on hyphen table condition

1. Show all student's number, name, test result (using left link):
Select X.sno,sname,grade from Student x left outer joins SC y on X.sno=y.sno

2. Show all student's number, name, test result (using right connection):
Select Y.sno,sname,grade from SC x right outer join studnet y on X.sno=y.sno

3. Show all student's number, name, test result (using full connection):
Select X.sno,sname,grade from Student x full outer joins SC y on X.sno=y.sno

3. Cross-connect (unrestricted connection, combining two tables without any restrictions, cross-joins without a WHERE clause will produce a Cartesian product of the table involved in the connection):
Select column name from < data source > Cross Join < data source >

Find out what all students are likely to choose:
Select Sno,sname,cno,cname from student Cross join course
or select Sno,sname,cno,cname from Student,course

(generally non-conditional, non-additive words with where) add condition: Select Sno,sname,cno,cname from student Cross join course where sdept= ' computer ' and STUDENT.SNO=SC, Sno

4. Federated queries (combining multiple SELECT statements to return result sets to a result set):
SELECT statement 1 Union [ALL] SELECT statement 2

Attention:
The 1.union operator joins a query that must have a similar data type, the same number of fields, and the same field order in the field list
2. By default, SQL server2008 automatically deletes duplicate records in the result set. If you want to return all the data, you can add the all option after the union
3. The field name of the first SELECT statement determines the field name of the entire result set, if you want to change the result set to show
4.
Find information about all teachers and students in the school:
Select Sno Number, sname name from studnet Union select Tno,tname from teacher

Practice:

Use students
Go
--1 and name of all students who have enrolled in the 2nd course and have scored above 90 points.
Select Student.sno,student.sname from STUDENT,SC where Student.sno=sc.sno and sc.cno= ' 2 ' and sc.grade>90

--2, check all student's school number, name, elective course name and result.
Select Student.sno,sname,cname,grade from Student,sc,course where Student.sno=sc.sno and Sc.cno=course.cno

--、 use its own connection to query the names and ages of all students who are smaller than "Zhang Li".
Select X.sname,x.sage from student as X, student as Y where X.sage<y.sage and Y.sname= ' Zhang Li '

--4, the name of the first 2 students of the 1th-course grade.
Select top 2 sname as top 2 students from STUDENT,SC where Student.sno=sc.sno and sc.cno=1 order BY sc.grade Desc

--5, check the course selection (including name, course number and grade) of all students majoring in computer science.
Select Student.sname,cname,grade from Student,sc,course where Student.sno=sc.sno and Sc.cno=course.cno and sdept like ' calculation Machine

--6, inquires the student's name which the elective course result is above 80 points.
Select Student.sname from STUDENT,SC where Student.sno=sc.sno and sc.grade>80

--7, the average score and the highest score of the elective courses for male and female students.
Select Ssex,avg (grade) as average, Max (grade) as highest score from Sc,student.sno=sc.sno GROUP by Ssex
Or: Select Ssex,avg (grade) as average, Max (grade) as highest score from student join SC in STUDENT.SNO=SC.SNO GROUP by Ssex

--8, use natural connection to inquire the name of the electronic professional girl, elective course name and result.
Select Sname,sdept,grade from Student a join SC B on A.sno=b.sno joins course C on B.cno =c.cno where a.sdept like ' electronics ' an D a.ssex = ' female '
Or: Select Student.sno,sname,cname,grade from Student,sc,course where Student.sno=sc.sno and Sc.cno=course.cno and ssex= ' Female ' and sdept= ' electrons '
Or: Select Student.sno,sname,cname,grade from (student join SC on STUDENT,SNO=SC.SNO) Join course ...
--9, the use of internal connection to query the electronic professional students of the school number, name, elective course name and results.
Select A.sno,a. Sname,a.sdept,b.grade from Student A joins SC B on A.sno=b.sno joins course C on B.cno=c.cno where a.sdept l Ike ' Electronics ' and a.ssex= ' men '

--10, use the outside connection to inquire about the course selection of all students, including the case of no elective course.
Select X.sno,sname,cno from Student x full outer joins SC y on X.sno=y.sno

--11, use cross-connect to find out the possibility of all students choosing a course.
Select Sno,sname,cno,cname from student Cross join course

--12, use Union union query to query all school students and teachers information. (The teacher table is created in the database and the fields are tno,tname,ssex.) Data type reference student table. "Teacher Table" data free add 5 lines).
Select Sno Number, sname name, ssex Sex from student union select Tno,tname,tsex from teacher

SQL class notes-multi-table query