SQL interview-Students who are inquiring for elective courses

First of all:

1. EXISTS found by sub-query

A commit not found in a EXISTS subquery

Description: Do not translate to exist and not exist, the head dizzy.

2. Establish the concept of a program loop, which is a dynamic query process. such as for loops.

3. exists executes the process exists first executes the outer query, and then executes the memory query, in the opposite direction. The process is to first remove the outside

The first tuple in the layer, then executes the inner query, substituting the first tuple of the outer table, if the inner query is true, there is the result

When Returns the first tuple in the outer table, and then takes out the second tuple, executing the same algorithm. Until you have finished scanning the outer entire table.

for (int i = 0; i<>eofout;i++)

{

for (int j = 0; j<eofint,j++)

}

Then look at an example: three tables Student (Sno,sname), curriculum Course (cno,cname) Schedule SC

(SNO,CNO)

Ask for the name of the student who has enrolled in all courses

My train of thought:

First, the student's course information is present in the SC table, to know whether a student has enrolled in all the courses, at least we need to know a total

Several courses, this is the first condition. Second, the students take the choice or not, we have to scan SC full table, statistics out of all the courses

Student number, and finally the name of the student number in the student table.

The statement is as follows: (tested)

Select Sname from Student

where Sno in

(Select Sno from SC

GROUP BY Sno//According to the Sno group, each student is enrolled in several courses. If it equals the total number of course, that's what we're looking for Sno

Have count (*) = (select count (*) from course))//Statistics course there are several courses in the CPC

Another way of thinking:

Introduced: Change the title to find the number of 00003 no elective subjects

Idea: We can put the known number into, each of the subjects into (cycle), which will form a 1*count (*) combination.

This composition as a condition, one by one with the SC table match, can not find matching we submit.

Select Cname from Course where

Not exists//cannot find a combination, submit course

(SELECT * from SC where course.cno = cno and sno = ' 00002 ')

Match in SC

Change the title: Find the name of a student who has no elective subjects

Train of thought: unknown number, the subject is unknown, indicating there are two unknown variables. There should be two of exists. We can scan

Student and course are combined in S * C to match each of these combinations with the SC, noting that the S*c combination already contains the

It's possible. If all is found, it means that all courses have been taken. If you can't find it, it means you have no elective courses. And then we'll have no elective.

Submitted to the previous exists loop. If the previous exists does not exist, it is submitted to the outer loop.

Select Sname from Student

Where NOT EXISTS//

(SELECT * FROM Course

Where NOT exists//non-existent commits to course

(SELECT * from SC where

Sno = Student.sno and CNO = course.cno))//substituting two unknown variables

Looking back, we are the first to elicit the topic:

The name of the student who took all the courses

First, there are three concepts in mind:

1. EXISTS found by sub-query

A commit not found in a EXISTS subquery

Description: Do not translate to exist and not exist, the head dizzy.

2. Establish the concept of a program loop, which is a dynamic query process. such as for loops.

3. exists executes the process exists first executes the outer query, and then executes the memory query, in the opposite direction. The process is to first remove the outside

The first tuple in the layer, then executes the inner query, substituting the first tuple of the outer table, if the inner query is true, there is the result

When Returns the first tuple in the outer table, and then takes out the second tuple, executing the same algorithm. Until you have finished scanning the outer entire table.

for (int i =0; i<>eofout;i++)

{

for (int j = 0; j<eofin,j++)

}

Then look at an example: three tables Student (Sno,sname), curriculum Course (cno,cname) Schedule SC

(SNO,CNO)

Ask for the name of the student who has enrolled in all courses

My train of thought:

First, the student's course information is present in the SC table, to know whether a student has enrolled in all the courses, at least we need to know a total

Several courses, this is the first condition. Second, the students take the choice or not, we have to scan SC full table, statistics out of all the courses

Student number, and finally the name of the student number in the student table.

The statement is as follows: (tested)

Select Sname from Student

where Sno in

(Select Sno from SC

GROUP BY Sno//According to the Sno group, each student is enrolled in several courses. If it equals the total number of course, that's what we're looking for Sno

Have count (*) = (select count (*) from course))//Statistics course there are several courses in the CPC

Another way of thinking:

Introduced: Change the title to find the number of 00003 no elective subjects

Idea: We can put the known number into, each of the subjects into (cycle), which will form a 1*count (*) combination.

This composition as a condition, one by one with the SC table match, can not find matching we submit.

Select Cname from Course where

Not exists//cannot find a combination, submit course

(SELECT * from SC where course.cno = cno and sno = ' 00003 ')

Match in SC

Change the title: Find the name of a student who has no elective subjects

Train of thought: unknown number, the subject is unknown, indicating there are two unknown variables. There should be two of exists. We can scan

Student and course are combined in S * C to match each of these combinations with the SC, noting that the S*c combination already contains the

It's possible. If all is found, it means that all courses have been taken. If you can't find it, it means you have no elective courses. And then we'll have no elective.
Submitted to the previous exists loop. If the previous exists does not exist, it is submitted to the outer loop.



Finally, answer your question in detail: Database SQL statements Query the student's number and name for all courses
Check the name of the student who has enrolled in all 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);

Understand the following: Check the name of the student who took all the courses.
Without a care, the first select must be selected in the Student table sname both:
SELECT Sname
From Student
Plus the condition is: where
What is the condition? The condition is that the---------------------"inquiry took all the courses
Because there are no (any) predicates, they can only be represented by EXISTS or not EXISTS. Do you understand this?
So we have to translate the conditions into-----------------------. There is no class. The student did not take elective
Where the back is nonexistent (not EXISTS) (
A course the student did not take an elective
)

The next step is to take the courses in the course table to find out if there are no elective
How to find it? Because a commit not found in the EXISTS subquery
Also you have to understand----------------------------not EXISTS queries are related queries----------
So just put in the last select
WHERE sno= Student.sno
and cno= Course.cno);
is to connect this classmate through the SC table and the course of Crouse, to find the connection is not on, that is: no elective, so that found a course this student did not take elective, there is no elective class, then the student was pass off,
One at a time to judge a classmate.



If you have learned program programming, you can look at the following content, if not, can be ignored. ----------

The above is an understanding of the internal implementation of the database system, which can be represented by a for loop
for (I=1; i<student.length (total number of students); i++) {
For (I=j;j<crouse.length (total number of course doors); j + +) {
Conditions are:
No class, no elective.
}
}



In the end you have to remember
1. The first select is the student you want to choose.
SELECT Sname
From Student
2. The second select is a course
3. The third select is the form of the relationship between the student and the curriculum------------SC elective form
Let them connect.



Fixed pattern 1 You want the results of the table student
2 table curriculums that meet the criteria
3 form of the relationship table elective
Where to connect them through elective tables.

============================

Query for the existence of quantifier query for select----

exists represents the existence of quantifier ョ, the query result has no value, only the logical value TRUE True and logical false false two values.

ex41: Check the list of all students who have enrolled in 001 courses

Select Sname
From Student A
where exists (
SELECT *
From SC b
where A.sno=b.sno and cno= ' 001 ')

ex42: Check the list of students who have not enrolled in the 001 course

Select Sname
From Student A
Where NOT EXISTS (
SELECT *
From SC b
where A.sno=b.sno and cno= ' 001 ')

ex43: Check the list of students who have enrolled in all the courses.

Because there is no universal quantifier in SQL, you can understand this: query a student like this, without a course he didn't choose

Select Sname
From Student A
Where NOT EXISTS (
SELECT *
From Course b
Where NOT EXISTS (
SELECT *
From SC C
where A.sno=c.sno and C.cno=b.cno))

NOTE: SQL has no implication predicate and can be converted using an equivalent transform

P→q≡┑p∨q

ex43: Check the list of students who have enrolled at least 95002 of all courses.

To transform a query:

Verbs represented by P: 95002 Elective course y

Q represents the verb: Student x Elective course y

The query is converted to: (y) p→q

Further conversions: (y) p→q≡

┑ (ョy (┑ (p→q))) ≡┑ (ョy (┑ (┑p∨q)))

≡┑ョy (p∧q) de-mold root law

It expresses the meaning that there is no such course y,95002 elective y and X is not selected, the SQL statement is as follows:

Select Sname,sno
From Student A
Where Sno <> ' 95002 ' and NOT EXISTS (
SELECT *
From SC b
where sno= ' 95002 ' and NOT EXISTS (
SELECT *
From SC C
where A.sno=c.sno and C.cno=b.cno))

SQL interview-Students who are inquiring for elective courses