DISTINCT has long been criticized for being inefficient. But the answers to those SQL interview questions on the Internet are useful. COUNT(DISTINCT, I didn’t use it very often before, so here’s a note.
Establish 3 tables for business training information of management positions:
S (S#,SN,SD,SA) S#,SN,SD,SA respectively represent student number, student’s name, employer and student’s age
C (C#,CN) C# and CN stand for course number and course name respectively
SC (S#,C#,G) SC (S#,C#,G
\
1. Use standard SQL nested statement to query student ID and name of elective course whose name is’ tax basis’
— Implementation code:
Select * FROM S Where SC =SC (Select * FROM C Where SC =SC.[C#] =SC.
\
Select * from C2 where id = ‘C2’; select * from C2 where id = ‘C2’
— Implementation code:
Select S.SN,S.SD FROM S,SC Where S.[S#]=SC.[S#] AND SC.[C#]=’C2′
\
Select * from student whose name is’ C5 ‘and whose name is’ C5′
— Implementation code:
Select SN,SD FROM S Where [S#] NOT IN( Select [S#] FROM SC Where [C#]=’C5′)
\
4. Use standard SQL nested statement to query names and units of students who select all courses
— Implementation code:
Select SN,SD FROM S Where [S#] IN( Select [S#] FROM SC RIGHT JOIN C ON SC.[C#]=C.[C#] GROUP BY [S#] HAVING COUNT(*)=COUNT([S#]))
\
5. Select * from course where number of students enrolled
— Implementation code:
Select COUNT(DISTINCT [S#]) FROM SC
\
6. Query the student ids and organizations of students who take more than five elective courses
— Implementation code:
Select SN,SD FROM S Where [S#] IN( Select [S#] FROM SC GROUP BY [S#] HAVING COUNT(DISTINCT [C#])>5)
\
\