SQL Server Exercise 1

Question 1

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 Sn, SD from S
Where [s #] In (
Select [s #] from C, SC
Where C. [C #] = SC. [C #]
And Cn = n' tax basis ')

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, SC
Where 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 S
Where [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 S
Where [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 S
Where [s #] In (
Select [s #] from SC
Group by [s #]
Having count (distinct [C #])> 5)