SQL statement interview questions (IN, NOT IN, COUNT, GROUP)

Jessonlv-lu guodong Original article, reproduced please indicate the source: http://blog.csdn.net/jessonlv

The interview questions I encountered during the interview mainly focused on the joint query of SQL. google made a mistake, but I fixed the mistake and shared it with you:

Problem description:
Create three tables to manage job business training information:
S (S #, SN, SD, SA) S #, SN, SD, and SA represent the student ID, Student name, organization, and student age respectively.
C (C #, CN) C #, CN represents the course number and Course name respectively.
SC (S #, C #, G) S #, C #, and G represent Student IDs, elective course numbers, and academic scores respectively.

The following five processes are required:
1. Use standard SQL nested statements to query the student ID and name of the elective course named 'tax basics'
2. Use standard SQL nested statements to query the name and unit of the student whose elective course number is 'c2'
3. Use standard SQL nested statements to query the names and units of students who do not select the course number as 'c5'
4. Use standard SQL nested statements to query the names and units of trainees who take all courses
5. query the number of students who have selected the course
6. query the student ID and organization of more than 5 Elective Courses

1. Use standard SQL nested statements to query the student ID and name of the elective course named 'tax basics'
-- Implementation code:

Select s #, sd from swhere [S #] IN (SELECT [S #] from c, SC WHERE C. [C #] = SC. [C #] and cn = n' Tax Foundation ')

2. Use standard SQL nested statements to query the name and unit of the student whose elective course number is 'c2'

-- Implementation code:

SELECT S.SN,S.SD FROM S,SCWHERE S.[S#]=SC.[S#]    AND SC.[C#]='C2'

3. Use standard SQL nested statements to query the names and units of students who do not select the course number as 'c5'

-- Implementation code:

SELECT SN,SD FROM SWHERE [S#] NOT IN(    SELECT [S#] FROM SC     WHERE [C#]='C5')

4. Use standard SQL nested statements to query the names and units of trainees who take all courses

-- Implementation code:

SELECT SN,SD FROM SWHERE [S#] IN(    SELECT [S#] FROM SC         RIGHT JOIN C ON SC.[C#]=C.[C#]    GROUP BY [S#]    HAVING COUNT(*)=COUNT(DISTINCT [S#]))

5. query the number of students who have selected the course

-- Implementation code:

SELECT students = COUNT (DISTINCT [S #]) FROM SC

6. query the student ID and organization of more than 5 Elective Courses

-- Implementation code:

SELECT SN,SD FROM SWHERE [S#] IN(    SELECT [S#] FROM SC     GROUP BY [S#]    HAVING COUNT(DISTINCT [C#])>5)

The above are the interview questions collected during the interview process. In the future, I will collect more interview questions. The key is to collect the questions and summarize them. I hope to help everyone who needs them.