SQL Server multi-table query

Author: GW

------------------------Data Query----------------------------

--(Select Sno, AVG (grade) as Avg_grade from SC Group by Sno) as St2

--select Student.sno from Student-outer join SC on (STUDENT.SNO=SC.CNO);

--select Student.sno,sname,ssex,sage,sdept,sc.cno,sc.grade from Student-outer join SC on (STUDENT.SNO=SC.SNO);

--select Student.sno,sname,ssex,sage,sdept,sc.cno,sc.grade from student, SC where student.sno=sc.sno;

--select Student.sno, Sname,cno,grade from student, SC where Student.sno=sc.sno and sc.cno= ' 2 ' and sc.grade>80

--Query Each student's school number, name, elective course name and grade
--select Student.sno, Sname, Course.cname, Sc.grade from Sc,student,course where Student.sno=sc.sno and sc.cno= Course.cno

--select sno,sname,sdept from student where sdept on (select sdept from student where sname= ' David ');

--The course name of the course that queries each student for more than its average score 1
--select distinct Sno, CNO from SC x where grade >= (select AVG (grade) from SC y where Y.sno=x.sno)

--Use the result of the query as a new set
--The course name of the course that queries each student for more than its average score 2
--select St1.sno,st1.cno from
--SC St1, (select Sno, AVG (grade) as Avg_grade from SC Group by Sno) as St2
--where St1.sno=st2.sno and st1.grade>=st2.avg_grade;

--Query the average score of each student
--select Sno,avg (grade) as Avg_grade from SC x GROUP by Sno;

--The number of students who query average scores greater than 80
--select Sno,avg (grade) as Avg_grade from SC x GROUP by SNO have avg (grade) >80;

--select distinct Sno, CNO from SC x where x.grade>= (select AVG (y.grade) from sc y where y.cno = X.cno);

--select Cno,avg (grade) as M_grade from SC Group by CNO;

--query students who are larger than the average of the course number, grade, course number
--select distinct SNO,GRADE,CNO from SC x where x.grade>= (select AVG (y.grade) from SC y where x.cno=y.cno);

--select sname, sage,sdept from student where Sage<any (select sage from student where sdept= ' CS ') and sdept!= ' CS ';

--select sname, sage,sdept from student where Sage>any (select sage from student where sdept= ' CS ') and sdept!= ' CS ';

--use top to return only the first three rows
--select Top 3 * from student;

--select B.sname, A.cno, A.sno from
--Student B, (select Cno,sno from SC) as a
-Where B.sno=a.sno;

--Create a database of departments
--create Table Dept (dno Int, dname varchar, dmoster varchar), primary key (DNO));

--drop Table Dept;

--insert into Table dept (dno,dmoster) VALUES (10299, ' 113 ');

--select * from student;

--sname,cno from STUDENT,SC
--where NOT EXISTS (SELECT * from SC where sno=student.sno and cno= ' 1 ') and Sc.sno=student.sno

--Bloated query statements
--select distinct sname from STUDENT,SC
--where exists (SELECT * from SC b where Sno=student.sno and b.cno= ' 1 ') and Sc.sno=student.sno

--select sname from Student
--where exists (SELECT * from SC b where Sno=student.sno and b.cno= ' 1 ')

--The following sentence is done directly by a Cartesian product
--select Sname,cno from STUDENT,SC

--Query and high-fade away in a department of other students 1
--select sno,sname,sdept from student S1 where exists
--(SELECT * from student S2 where s2.sdept=s1.sdept and s2.sname= ' High Fade Away ') and s1.sname!= ' High Fade Away '

--select* from student;

--Query and high-fade away in a department of other students 2
--select sno,sname,sdept from student where sdept in (select sdept from student S2 where s2.sname= ' high-fade ') and sname! = ' high-fade '

--Query and high-fade away in a department of other students 3
--select sno,sname,sdept from student where sdept= (select sdept from student S2 where s2.sname= ' high-fade ') and sname! = ' high-fade '

--select sname from Student
--Where NOT EXISTS (SELECT * FROM Course
--Where NOT EXISTS (SELECT * FROM SC
-where Sno=student.sno and Cno=course.cno))

--
--
--
--

--have not understood exists this statement below is from Baidu know on the search out
--exists or not EXISTS is the condition in which the field of the main query is passed to the back of the query, and the return value is true or false.
--exists TRUE, then the query condition is set, and the results are displayed.
--not EXISTS TRUE, or FALSE, the query connection condition is not valid.
--select * From course where isn't exists (SELECT * from grade where grade. Course Code =course. Course Code)
--This statement is the data that is not present in the grade of the course code in the Query course table.
--Look at the grade table, the course number 01 to 06, and the course table, 01 to 07, then 07 in the grade table is not present, then, is eligible.
--the same select * from course where exists (SELECT * from grade where grade. Course Code =course. Course Code)
--The record condition for the query course is numbered in grade. Then it is clear that the result is K01 to K06 data.
In addition, the function of exists and not exists can be implemented in or not, but the exists efficiency is high.
--Because the result returned by exists and not exists is true or FALSE, the first qualifying result is encountered in a subquery.
--the query exits without a full table search.
--instead of in or in, you can query all the select words in the query.

--In a subquery, when the first qualifying result is encountered, the query exits without a full table retrieval.

--select * from SC where Sno in (' 2014003 ');

--select Sno from SC S1 where CNO =all (SELECT * from SC where Sno in (' 2014003 '));

/*
Example 1: Check the names of all students who have enrolled in course 1th.
Solution 1: Using exists
First, a tuple in the student table is taken, and then in the SC table, the Sno of the tuple is sc.sno=, and the corresponding cno= ' 1 ',
If present, then the WHERE clause of the outer query is returned as true, then the tuple in the student table can be output. Then loop through
Student other tuples in the table.
For example, the first record in the SC table is eligible for the tuple with a secondary number equal to 2002151121 in the student table.
The WHERE clause then returns TRUE, so the tuple can be output. Then loop through.
Select Sname
From Student
where exists
(
SELECT *
From SC
where Sno = Student.sno and cno= ' 1 '
);
Solution 2: Using connection queries
Select Sname
From STUDENT,SC
where STUDENT.SNO=SC. Sno and SC. cno= ' 1 ';

Example 2: Check the name of a student who does not have an elective course No. 1th.
Solution:
Select Sname
From Student
Where NOT EXISTS
(
SELECT *
From SC
where Sno=student.sno and cno= ' 1 '
);

Example 3: Check the name of the student who took all the courses.
Select Sname
From Student
Where NOT EXISTS
(
SELECT *
From Course
Where NOT EXISTS
(
SELECT *
From SC
where Sno=student.sno and
Cno=course.cno
) );

--!!! In the exists (NOT EXISTS) clause, if more than one record is judged on each record in turn
--!!! A record satisfies the condition and returns true. The rest is not queried for exists (not exists) in the combined outer layer
--!!! If the outer layer is exists, the record is selected if the outer layer is not exists, and not true if the record is discarded

Lookup semantics: Query Such a student, there is no subject he does not take.
Lookup process:
First, select a tuple in the student table and then, in turn, determine whether each tuple in the course table can be output, as long as there are
A course can be output, then the WHERE clause of the outermost query is returned as false, and when judging whether a course can be output,
To use the third query, using the current number and the current course number, in the SC table query, if present, then the second layer of query
The WHERE clause returns FALSE. At this point, each course can not be output, this number corresponding to the tuple can be output. Said
The student took all the courses.

Example 4: A student number of at least 2014006 elective courses.
--
SELECT DISTINCT Sno
From SC SCX
Where NOT EXISTS
(SELECT *
From SC SCY
where SCY. sno= ' 2014006 ' and
NOT EXISTS
(SELECT *
From SC SCZ
where SCZ. Sno=scx. Sno and
SCZ. Cno=scy. Cno));
*/

/*
SELECT DISTINCT Sno from SC S1
Where NOT EXISTS
(SELECT * from SC S2 where s2.sno= ' 2014006 ' and NOT exists
(SELECT * from SC S3 where S1.sno=s3.sno and S2.cno=s3.cno)
)

*/

/*
Query semantics: There is no such course y, the student 2014006 elective y, and the student x did not choose.
Query process: First select a record in the SCX table, such as the first one, and then look at the Scy table, only the Scy table can not
Output, the WHERE clause of the first-level query returns True, and the first record can be output; so I'm going to check the Scy table once.
In each tuple, the first three, because the study number first does not meet =200215122 so must not output, fourth words,
It depends on what value is returned by the not exists behind and, depending on whether the number is present in the third-level query
Equals Scx.sno and the course number is =scy. The tuple of the CNO, after being viewed, has, then returns false, so the fourth one cannot lose.
Out, the fifth is similar, so the first layer of the query's not exists returns true. So the first record can be output.
*/

/*
--How to determine whether a two set contains a B
--to determine whether each element of B exists in a in turn
No element in--b does not exist in a
SELECT * from K where NOT exists
(SELECT * from B where NOT exists
(SELECT * from A where a.filed=b.filed and K ...) )

*/

--the set and operation can be substituted with or.
--select distinct Sno from SC where cno= ' 4 ' or cno= ' 5 '

--select Sno from SC where cno= ' 4 ' union select Sno from SC where cno= ' 5 '

--the intersection of the set can be replaced with and.
--select * from student where sage>=22 and sdept= ' CS '

--the difference operation of the set can be replaced with not in instead of the set to be dropped
--select * from student where sage>=22 and sdept not in
--(select sdept from student s1 where sdept= ' CS ')

--Query Computer department All students who are older than 21 years old are excluded
--select * FROM student S1 where sdept= ' CS ' and sage not in
--(select sage from student S2
--where s2.sage>=22 and S1.sno=s2.sno)

SQL Server multi-table query