Some common questions in SQL Server

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.

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

-- Implementation code:

Select s #, Sn from S

Where [s #] In (

Select [s #] from C, SC

Where C. [C #] = SC. [C #]

And Cn = n' tax basis ')

2. Use the standard SQL nested statement to query the elective course number as 'C 2' Student name and organization

-- 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 non-elective course Numbers as 'C 5' Student name and organization

-- 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 ([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)

Question 2

Problem description:

Known link mode:

S (SNO, sname) Student Relationship. Sno is the student ID, and sname is the name

C (CNO, cname, cteacher) Course relationship. CNO is the course number, cname is the course name, And cteacher is the course teacher

SC (SNO, CNO, scgrade) Course Selection relationship. Scgrade is the score

1. Find out the names of all students who have not taken the course taught by Mr. Li Ming.

-- Implementation code:

Select sname from S

Where not exists (

Select * from SC, c

Where SC. CNO = C. CNO

And cname = 'lilim'

And SC. Sno = S. SnO)

2. List the names and average scores of students whose two or more courses fail.

-- Implementation code:

Select S. Sno, S. sname, avg_scgrade = AVG (SC. scgrade)

From S, SC ,(

Select SnO

From SC

Where scgrade <60

Group by SnO

Having count (distinct CNO)> = 2

) A where S. Sno = A. SnO and SC. Sno = A. SnO

Group by S. Sno, S. sname

3. List the names of all students who have learned course 1 and course 2

-- Implementation code:

Select S. Sno, S. sname

From S ,(

Select SC. SnO

From SC, c

Where SC. CNO = C. CNO

And C. cname in ('1', '2 ')

Group by SnO

Having count (distinct CNO) = 2

) SC where S. Sno = SC. SnO

4. List the student IDs of all students whose scores are higher than those of students whose scores are equal to those of the course No.

-- Implementation code:

Select S. Sno, S. sname

From S ,(

Select sc1.sno

From SC SC1, C C1, SC SC2, C C2

Where sc1.cno = c1.cno and c1.name = '1'

And sc2.cno = c2.cno and c2.name = '2'

And sc1.scgrade> sc2.scgrade

) SC where S. Sno = SC. SnO

5. List the student IDs of all students whose scores are higher than those of Lesson 2 and their scores of Lesson 1 and Lesson 2

-- Implementation code:

Select S. Sno, S. sname, SC. [score of Lesson 1], SC. [score of Lesson 2]

From S ,(

Select sc1.sno, [score for Lesson 1] = sc1.scgrade, [score for Lesson 2] = sc2.scgrade

From SC SC1, C C1, SC SC2, C C2

Where sc1.cno = c1.cno and c1.name = '1'

And sc2.cno = c2.cno and c2.name = '2'

And sc1.scgrade> sc2.scgrade

)
SC where S. Sno = SC. SnO