Query at least one course and student id as "s001 the students learned the same classmate stude-CodePudding

Veteran, please help me with the new bird, organize their thinking to the to the problem

Behind and to the topic is
Student id query at least learned to "s001" classmate all the other students of a course student number and name;

CodePudding user response:

1,
find S001 lessons2, find and S001 the courses have the same students IN
3, student number and name

Select su. Sno, su. Sname from student su where su. Sno in (
The select sc. Sno from sc where sc. Cno in (select s.c no from sc s where s.s no='s001')
And sc. Sno<> 's001')

CodePudding user response:

Student id query at least learned to "s001" classmate all the other students of a course student number and name;

First you have to consider "s001" students learning courses, query, then consider, just use the exists to filter

Select other classmate student id, name from where students list a not the exists (select 1 from table b students, grades table where a. f student id=b. student id and b. student id=f student id and not the exists (select 1 from the student table c, record table d where c. student id=b. student id and f. student id=d. student id and c. student id=d. student id and c. student id='s001'));

CodePudding user response:

 
AS WITH T (
SELECT 'S001 the AS SNO,' C001 AS CNO FROM DUAL 
UNION ALL 
SELECT 'S002 AS SNO,' C001 AS CNO FROM DUAL 
UNION ALL 
SELECT 'S003 AS SNO,' C001 AS CNO FROM DUAL 
UNION ALL 
SELECT 'S004 AS SNO,' C001 AS CNO FROM DUAL 
UNION ALL 
SELECT 'S001 the AS SNO,' C002 AS CNO FROM DUAL 
UNION ALL 
SELECT 'S002 AS SNO,' C002 AS CNO FROM DUAL 
UNION ALL 
SELECT 'S003 AS SNO,' C002 AS CNO FROM DUAL 
UNION ALL 
SELECT 'S001 the AS SNO,' C003 AS CNO FROM DUAL 
) 
SELECT DISTINCT SNO FROM T 
WHERE SNO!='S001' - (2) find out the other students course 
AND EXISTS - 3. The other students of course whether there is the same as the S001 the classmate, have the selected X 
(SELECT 1 FROM 
(SELECT * FROM T WHERE SNO='S001') T1-1. The courses that students find S001 
WHERE tc NO=T1. CNO) - (3) other students in the course of whether there is the same as the S001 classmates 
- results of a 4. Table joins with the student again