Common SQL Server test questions (1) Question 1 Problem description: create three tables for managing job business training information: S (s #, Sn, SD, SA) s #, Sn, SD, SA stands for student ID, Student name, organization, student age C (C #, CN) C #, CN stands for course number, course name SC (s #, CN respectively #, C #, g) s #, C #, and g respectively represent the student ID, the number of the elective course, and the score 1. use the standard SQL nested statement 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 Foundation ') 2. use the standard SQL nested statement 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 the standard SQL nested statement to query the name and unit of the student who does not take the course number '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 ([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 unit of more than 5 electives-implementation code: Select Sn, SD from s where [s #] In (select [s #] from SC group by [s #] Having count (distinct [C #])> 5) common SQL Server test questions (2) Question 2 Problem description: known Relationship Mode: S (SNO, sname) Student Relationship. Sno is the student ID, and sname is the class relationship of C (CNO, cname, cteacher. CNO is the course number, cname is the course name, And cteacher is the course selection relationship of the course instructor SC (SNO, CNO, scgrade. Scgrade is 1. find 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. lists the names and average scores of students with more than two failed courses. 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 =. snO and SC. sno =. sno group by S. sno, S. sname 3. lists the names of all students who have studied 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 score is higher than that of the Course whose score is "1". 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. lists 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 for 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
---
2005-9-22
