Basic SQL operations (1)

Create three tables to manage job business training information:

S (SID, Sn, SD, SA) Sid, Sn, SD, and SA represent the student ID, Student name, organization, and student age respectively.

C (CID, CN) CID, CN represents the course number and Course name respectively

SC (SID, CID, g) Sid, CID, and G represent the student ID, course number, and score respectively.

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

----- Student ID and name for the Elective Course "Tax Foundation"
Select Sid, Sn from s where Sid in (select Sid from C, SC where c. cid = SC. CID and Cn = 'tax basis ')

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

--- Name and unit of the elective course number 'c2'
Select Sn, SD from S, SC where S. Sid = SC. Sid and cid = 'c2'

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

----- Name and Department of all elective courses
Select Sn, SD from (select. sid from (select Sid, count (SID) as CC from SC group by SID) as a inner join (select count (*) as CC from C) as B on. cc = B. CC) as C left join S on S. SID = C. sid

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

--- The name and unit of the student who does not take the course number 'c5'
Select Sn, SD from s where sid not in (select Sid from SC where cid = 'c5 ')

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

---- Number of trainees of Elective Courses
Select count (distinct Sid) from SC

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

--- Name and organization of more than 5 trainees of the Elective Course
Select Sn, SD from S
Where sid in (select Sid from SC group by SID having count (distinct CID)> 5)