"A smuggled SQL test question" Playyuer proposition, Playyuer, CCAT written
I believe that there are many netizens have recently seen a post called "A SQL Test question". This test should still have a lot of places worth thinking. Interested readers can try to do it before reading this article, if you can do it well, at least, you are an experienced SQL user.
Let's take a look at the topic: 2D tables T (F1, F2, F3, F4, F5, F6, F7) indicate the following relationship:
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ────────────────────────────────────────────────── Course ID │ Course Name │ Grade │ Teacher ID │ Teacher Name │├ ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── Wang Wu ────┼──────────┼──────────┤│ S3 │ │ K4 │ │ 53 │ T4 │ political Zhao │├───── ─ ─ ─────────────────────────────────────────────────── ─────────────────── ─ │ S1 │ 张三 │ K1 │ Mathematics │ 61 │ T1 │ Zhang Teacher │├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ────────────── ─ ┤│S2 │ Li 4 │ K3 │ English │ 88 │ T3 │ Li Teacher │├──────────────────────────────────────────────────────────────────────────────────────── ─────────────────────────────────────────────────────────── ┼─────── ┤│S1 │ 张 │ │ K4 │ Politics │ 77 │ T4 │ Zhao Teacher │├───────────────────────────── ┼ - ────────────────────────────────────────────────── ──── ─ ┤│S2 │ Li 4 │ K4 │ Politics │ 67 │ T5 │ Zhou Teacher │├───────────────────────────────── ─────────────────────────────────────────────────────── ─ ┤│S3 │ 王 五 │ K2 │ Language │ 90 │ T2 │ 王 老 │├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ┼───────────────────────────────────── ─ ─ ─ │ ┤ │ K1 │ Mathematics │ 55 │ T1 │ Zhang Teacher │├ - ──────────────────────────────────────────────────── ────────────────────────── ─ ─ ┤│S1 │ 张三 │ K2 │ │ 81 │ T2 │ 王 老 │├────────────── ────────────────────────────────────────────────── ─ ┼──────────────────────────────────────────────────────────────────────────────────────────────────yle >> │ 59 │ T1 │ 王 王 │├──────────────────────────────────────────────────────────────────────────────────────────── ─ ┼──────────────── ────────────────────────────────── ───────────────── ─ ┤│S1 │ 张三 │ K3 │ English │ 37 │ T3 │ Li Teacher │├────────────────────────────── ───────────────────────────────────────────────────────── ─ ─ ──────────────────────────────────────────────────────tes
1 │ T1 │ Zhang Teacher │├ --──────────────────────────────────────────── ┼──────────────────────────────────────── │ │ │ │ │ ├─── ────────────────────────────────────────────────── ─ ─ ──────────────────── ─ │ │ │ │ │ │ │ └───────────── ───────────────────────────────────────────────────────── ─ ─ ┴───────── ┘ ┘ 于 大 大 理 理 表 表 表 表 表 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整 整Type (unique, no repetition), and Table contains except for the F field, delete other fields completely the excess dirty recording data:
This question is a problem with the "logical repetition" record. Of course, this situation can be used to eliminate the primary key constraint! However, the real situation is often the original data in "washing", and the logic master key is too late. The constraint will bring inconvenience to the collected raw data, for example: reading attendance records from the swipe machine. When the application data is applied, the dirty data will be swept away! The reason why you want to keep an automatic identity column, because it is indeed the premise necessary for the following answers:
DELETE L from "L Join" transcript "R on L." student ID "= r." student ID "and L." course ID "= r." Course ID "and L.f> R.f
This is one of the most exciting ways to think about and mostly effective. Use not to wait, you can leave the same set of repeated data in the F field value, and select other deletion. If there is only one, it will not be selected. Here is to emphasize that everyone must clearly clear the basic table of the operation is also the two-dimensional table data set by the Basic Table after the delete keyword and the filter condition is connected by the basic table, which is all of the FROM clause. . At least one individual name is taken to reference the Basic table. The use of alias is very convenient when writing a large number of SQLs, and facilitates the constructive SQL. If necessary, it is necessary to continue to use the WHERE clause. If the above example is not intuitive, the following mimics an inequality of self-contained, there is a set of numbers (1, 2, 3), make a non-equal contact, so that the left subset is greater than the right subset, is: 2 1 3 1 3 2 If the left subset is now selected, it is 2 and 3. 1 No smaller data in the right can match it, so it is filtered. If the data is repeated, the efficiency will be unconventional. Fortunately, it is not SELECT, but DELETE does not need to return the result set, which affects nature.
Delete t from transcripts T where f not in (SELECT MIN (f) from scores I group by I. Student ID, i. Course ID HAVING Count (*)> 1) And f not in (SELECT MIN (f) from Press Table I Group by I. Student ID, I. Course ID HAVING Count (*) = 1)
This method is simple, just like translation natural language, it is very accurately described with the characteristics of eligible records, and even the second condition is indeed. At least one> = number should be used to merge these two conditions or only retain any of the conditions, and improve efficiency. Delete t from transcript table T where f> (SELECT MIN (f) from transcripts as I where I. Student ID = T. Student ID and I. Course ID = T. Course ID Group By I. Student ID, I. Course ID)
This method is basically a correlation subquery version of the method, and the readers of the Cartesi can be well understood, and the statistical function is used, so the efficiency is not too high. The careful readers will find that the Group By clause in the query is not necessary, and it should be improved.
About debugging of the Delete statement, experienced programmers will temporarily replace the dangerous delete with a harmless Select. E.g:
SELECT L. * - Delect L Temporarily annotated from "L Join" transcript "R on L." student ID "= r." Student ID "and L." course ID "= r." course ID "And LF> RF
In this way, it greatly reduces the possibility of online data being unintentional, and of course, the data is also important in advance. Similarly UPDATE and INSERT write should also act according to this. From the perspective of the relationship between database principles, these write operations are typical "Selection" operations, Update and INSERT and "Projection" operations, they are all these relationships. "Write" application's performance form. In fact, the purpose of the query is also nothing more than browsing, deleting, updating or inserting. Usually written is more than the read operation, if the index is too large, it will only reduce efficiency.
Choosing "Sub Query" or "Connection" is different in efficiency, but the most critical difference is to express the read and write of the result set of queries, the developer is writing a "read-only" query record set, The respective efficiency of "subquery" and "connect" should be first considered, but if you want to implement the query result set of "writable" applications, whether it is related or non-related sub-queries, it is difficult to avoid in complex applications. .
In the above solution, it should be said that the first method, simple and effective, and is very creative, it is a recommended method. Of course, the easiest way to write should be:
Delete T from T, T T1 WHERE T. Student ID = T1. Student ID and T. Course ID = T. Course ID and T.f In fact, this is the "standard" of the method (but it is not "ANSI / ISO SQL" standard) connection writing method, the following answers are generally not to be understood, this is also the "ANSI / ISO SQL" standard. The encouragement, Join is indeed more easily expressed the relationship between the tables, and interested readers can override themselves. If you use "* =" to implement two tables, it is important to note that the AND condition of the WHERE clause is in order, and the "ANSI / ISO SQL" standard does not allow the order of WHERE conditions affect the results of the query, but The order of each table connection of the FROM clause can affect the results of the query. 2. Related records of the highest score of each department: (is the highest, minimum score of each course) Course ID, course name, highest score, student ID, student name, teacher ID, teacher name, Minimum points, student ID, student name, teacher ID, teacher name If this question, if you just find the highest score or minimum score, it is a very simple question: SELECT L. Course ID, Max (L. Course Name), Max (L. Grade) AS The highest score, min (l. Grade) AS lowest FROM transcript LGROUP BY L. Course ID However, the topic of the drill is to Lists relevant records related to the highest and minimum achievements of various subjects, which is often true. Since you have elected the highest and minimum points of each department, then the rest is to incorporate the information of students and teachers into this result set. If you write it, it is very troublesome, because the fields to add too much, soon make the code difficult to manage. Still change your ideas: SELECT L. Courses ID, L. Course name, l. [Grade] as the highest score, l. [Student id], l. [Student name], l. [Teacher ID], l. [Teacher Name], r. [Grade] The lowest score, r. [Student ID], r. [Student name], r. [Teacher ID], r. [Teacher name] from transcripts L Join transcript AS R ON L [Course ID] = R. [Course ID] where l. [grade] = (SELECT MAX (IL. [Grade]) from transcript AS [il] WHERE L. [Course ID] = IL. [Course ID] GROUP BY IL. [Course ID]) And R. [Result] = (Select Min (IR. [Results]) from transcripts AS [IR] WHERE R. [Course ID] = IR. [Course ID] group by IR [Course ID]) At first glance, it seems to be complicated. In fact, if you have mastered the basic methods of constructing a cross-facing table and the knowledge of the associated subquery, the problem is solved. Since the lowest and highest points are for course information, the answer cleverly merges the course information to the highest score, of course, can also be merged into the minimum. The code is in the rules, the style is very good, readability is also good. 3. With average score from high to low order, print all students' four (mathematics, language, English, political) course scores: (就 每 成 单 成) Student ID, Student Name, Mathematics , Language, English, politics, effective courses, effective average (Note: Effective courses are recorded in the T table, if you don't understand how you can print "effective courses" and "effective average points") It is to be noted that the title clearly proposes "four-door (mathematics, language, English, political) courses" is reasonable, because of the realization, it is indeed possible to avoid the value of the data on the line in the original table. This is another typical "row variable" related sub-query: SELECT Student ID, Max (Student Name) AS Student Name, (SELECT Score from Redissibility WHERE Student ID = T. Student ID AND Course ID = 'K1') AS Mathematics, (SELECT Score from Pied WHERE Student ID = T. Student ID and course ID = 'k2') AS language, (SELECT score from transcripts WHERE students id = T. Student ID and course id = 'k3') AS English, (SELECT score from transcript WHERE students ID = T. Student ID AND Course ID = 'K4') AS Politics, Count (*) AS Effective Course Number, AVG (T. Results) AS Average Results From Transcripts AS TGROUP BY Students Idorder BY Average Results This can be said to be a very rule of solution, in this application, the child query is much more readable than the coupling code. If the database engine is recognized as a link to the join, it will be went, in fact, the associated subqueries must have a connection. Here, add another table Ranks (Rank, Minvalue, MaxValue): ┌──────────────────────────────────────────────────────────────────── ────── ┐ │ Rank │ Minvalue │ MaxValue │ ├────────────────────────────────────────────── A │ 90 │ 100 │ ├───────────────────────────────────────────────────── SELECT Student ID, Max (Student Name) AS Student Name, (SELECT Score from T WHERE Student ID = T0. Student ID AND Course ID = 'K1') AS Mathematics, (SELECT MAX (RANK) from Ranks, t where t. Grade> = ranks.minvalue and t. Grade <= ranks.maxvalue and t. Student ID = T0. Student ID and T. Course ID = 'K1') AS Mathematics Level, (SELECT Score from T where student ID = T0. Student ID and course ID = 'k2') AS language, (Select Min (Rank) from ranks, t where t. Grade> = ranks.minvalue and t. Grade <= ranks.maxvalue and t. Student ID = T0. Student ID and T. Course ID = 'k2') AS language level, (SELECT score from T where student ID = t0. Student ID and course id = 'k3') AS English, (Select Max (Rank) from ranks, t wherere T. Grade> = ranks.minvalue and t. grade <= ranks.maxvalue and t. Student ID = T0. Student ID and T. Course ID = 'K3') AS English Level, (SELECT Score from T Where Student ID = T0. Student ID AND Course ID = 'K4') AS Politics, (Select Min (Rank) from Ranks, T where t. Grade> = ranks.minvalue And t. grade <= ranks.maxvalue and t. Student ID = T0. Student ID and T. Course ID = 'K4') AS Political Level, Count (*), AVG (T0. grade), (SELECT MAX (Rank ) From ranks where avg (t0. Grade)> = ranks.minvalue and avg (t0. Grade) <= ranks.maxvalue) AS average level from t as t0group by Student ID This surface is used on the surface, and then think carefully Thought, the interval of each record in the Ranks table is not intended, in fact, it can also be considered equivalent connection, such surface design undoubtedly there is a good scalability, if the title is only required Print (student ID, student name, number of effective courses, effective average, average class): SELECT Student ID, Max (Student Name) AS Student Name, Count (*), AVG (T0. Grade), (SELECT MAX "(Rank) from ranks where avg (t0. grade)> = Ranks.minvalue and AVG (T0. Results) <= ranks.maxvalue) AS Average Level From Ti AS T0GROUP BY Student ID This solution is more comprehensive. Going back to the original question, introduce a relatively good way, only one simple grouping query can solve the problem, experienced readers may have already thought, that is, Case: SELECT Student ID, Min (Student Name), SUM (CASE Course ID WHEN 'K1' THEN SECTY ELSE 0 END) AS Mathematics, SUM (Case Course ID WHEN 'K2' THEN SEARE ELSE 0 End) AS Language, SUM (CASE Course ID WHEN 'K3' THEN Review ELSE 0 END) AS English, SUM (Case Course ID WHEN 'K4' THEN Review ELSE 0 End) AS Politics, Count (*) AS Effective Course, AVG (T. Score) AS Average Results From transcripts as Tgroup by student idorder by average grade DESC Although it may be a bit strange to see the answer, it is very good to understand, readability is not low, the efficiency is also high. But it can't be like the previous answer, and the student is out of the class in the middle of the score, and it is really zero. This solution fully utilizes the role of the CASE statement for data classification: CASE will score the results in four categories, and SUM is used to eliminate excess 0. Select [T]. [Student ID], Max ([T]. [Student Name]) AS Student Name, Max ([T1]. [Results]) AS Math, Max ([T2]. [Grade]) AS language , Max ([T3]. [Grade]) AS English, Max ([T4]. [Grade]) AS politics, count ([t]. [Lesson ID]) AS effective course, (Isnull ([T1 ]. [Grade]), 0) ISNULL (Max ([T2]), 0) ISNULL (Max ([T3]. [Grade]), 0) ISNULL (Max ([T4]. [Grade]), 0) / count ([t]. [Lesson ID]) AS effective average from FROM scores T Left Join transcripts AS [T1] ON [T]. [Student ID] = [T1]. [Student ID] and [t1]. [T2] = 'K1' Left Join Transcript, AS [T2] ON [T]. [Student ID] = [T2]. [Student ID] and [T2]. ID] = 'K2' Left Join Transcript AS [T3] ON [T]. [Student ID] = [T3]. [Student ID] and [T3]. [T3]. [Course ID] = 'K3' Left Join Transcript AS [T4] ON [T] = [T4]. [Student ID] and [T4]. [T4]. [Course ID] = 'K4' Group by [T]. [Student ID] ORDER BY effective average DESC This method is a fairly orthodox coupled solution, although it is troublesome, but it is not difficult to understand. Again from a practical point of view, true demand is often not like this topic to clearly propose a relatively static demand such as printing four-door (mathematics, language, English, political) courses. "It is time when dynamic SQL is big, it is obviously One way is undoubtedly the best choice for constructing dynamic SQL, of course, the other two SQL laws are still very obvious, the same is not difficult to construct. Taking the case version as an example: first use a cursor traversal, take out all the courses to join: SUM (Case 'course ID' courseway name 'TENGEE ELSE 0 END) AS course name form, replenish SELECT and FROM, WHERE If necessary, a SQL that generates dynamic transcripts is born, as long as it is executed by the relevant program call, it can be a more complete solution. In fact, the most similar typical application is the summary of the fine table in the primary, thin relationship of the main table projection, such as two tables: Master (f, f1, f2 ...) a pair of Details (f, F3, f4 ...) SELECT *, (Select Count (*) from Details Where Master.f = Details.f), (Select Sum (F3) from Details Where Master.f = Details.f) from master 4. According to the percentage of each subject, the percentage of low to high and post rate is from high to low order, and the average of the average grades and non-rate rates (indicated "N lines" is represented by "N row": (is the analysis of which Course is difficult) Course ID, course name, average score, and percentage SELECT course ID, MAX (course name) AS course name, avg (grade) AS average score, 100 * SUM (Case WHEN score> = 60 Then 1 else 0 end) / count (*) AS and grid population from transcripts TGROUP BY Course IDORDER BY and EDRY DESC This question should be said to be simple, just use "line" to provide performance form. As long as you want to understand what is packet you want to group, take the statistical aggregation function, you will be good. 5. The average score rate of the printed four courses (with "1 line 4 column"): (is the problem of analyzing which course is difficult) Mathematical average division, mathematical and grid population, Chinese average division, Chinese and Geometers, English Average score, English and genus score, political average, political and parallel This question is actually the "column" expression version of the previous question, which is static, because this question is similar to the third question, and the extension must be used to achieve additional Program construction dynamic SQL: Select SUM (Case WHEN Course ID = 'K1' THEN) ELSE 0 end) / sum (Case course ID when 'k1' Then 1 ELSE 0 END) AS mathematical average score, 100 * SUM (Case WHEN course ID = 'K1' AND score> = 60 THEN 1 ELSE 0 END) / SUM (Case When Course ID = 'K1' TEN 1 ELSE 0 END) AS Mathematics and Component Points, SUM (Case WHEN Course ID = 'K2' Then Reradiction ELSE 0 End) / SUM (CASE course ID when 'k2' Then 1 ELSE 0 END) AS language average, 100 * SUM (Case When course ID = 'K2' AND)> = 60 Then 1 else 0 End) / SUM (Case WHEN Course ID) = 'K2' THEN 1 ELSE 0 END) AS Language and Piece Points, SUM (Case WHEN Course ID = 'K3' THEN Review ELSE 0 End) / SUM (Case Course ID WHEN 'THEN 1 ELSE 0 End) AS English average Point, 100 * SUM (Case WHEN Course ID = 'K3' AND)> = 60 THEN 1 ELSE 0 End) / SUM (Case WHEN Course ID = 'K3' Then 1 ELSE 0 End) AS English and Grid Points, SUM (CASE WHEN Course ID = 'K4' TENGET ELSE 0 End) / SUM (Case Course ID WHEN 'K4' THEN 1 ELSE 0 End) AS Politics Average, 100 * Sum (Case WHEN Courses ID = 'K4' AND Score> = 60 THEN 1 ELSE 0 END / SUM (Case WHEN Course ID = 'K4' TEN 1 ELSE 0 End) AS Politics and Ge - Bumbe FROM Decoration Table THewei Looks, but actually is the most classic CASE application, very Practical data analysis technology. First, a series of results in the original table is 2 consecutive projections for four different courses, and use the value domain of Case and data ['K1', 'K2', 'K3', 'K4'] to divide the data, and then use SUM () [1 ... 1] implements the function of seems to be the counter that should be used with counters, don't talk about joint and subqueys, even the traces of Group By group can't be found! Read hard, you can only keep one field first, relatively understand, and make up one by one. This question can also be considered a "row" cross-perspective example! In addition, "line" is dynamic, "line" is relatively unlimited, "column" is relatively limited, "line" The increase is the application level, but "free" to delete, "column", "column", is managed, don't change it easily! 6. According to the different courses of different teachers, the average division is from high to low: (is what the teacher's level is high) Teacher ID, teacher name, course ID, course name, average SELECT teacher ID, MAX (teacher name) AS teacher name, course ID, MAX (course name) AS course name, AVG (grade) AS average grade FROM transcript TGROUP BY course ID, teacher idorder by avg (grade) DESC this If you don't say it, even if you turn your eyes, you don't move your hands, your answer should be out! If the average points take the highest points and one minimum, it is not difficult to write: SELECT teacher ID, MAX (teacher name), course ID, max (course name) AS course name -, AVG (grade) AS average results, (Sum (Sum) - (SELECT MAX) from transcripts WHERE course ID = T1. Course ID AND teacher ID = T1. Teacher ID) - (SELECT MIN) - (SELECT MIN) WHERE course ID = T1. Course ID AND teacher ID = T1. Teacher ID) / cast ((SELECT COUNT) ) -2 from transcript WHERE course ID = T1. Course ID AND teacher ID = T1. Teacher ID) AS FLOAT AS Average FROM Reviews AS T1WHERE (Select Count (*) -2 from transcript WHERE course ID = T1 Teacher ID = T1. Teacher ID)> 0Group BY Course ID, Teacher Idorder By Average Desc 7. Collection of mathematics scores 10th to 15th student transcript or print average score 10 to 15th students' transcripts [Student ID], [Student Name], Mathematics, Chinese, Politics ,average score If you only consider a course, such as: Mathematical results, very simple: SELECT TOP 5 * from T where course ID = 'K1' AND grade NOT IN (SELECT TOP 15 results from T Order BY grade DESC) Order by grade DESC Select * from t where course id = 'K1' AND grade NOT IN (SELECT TOP 10 results from T ORDER BY grade DESC) And grades in (SELECT TOP 15 results from T Order BY grade DESC) Order BY grade DESC Logically, the 10th to 15th is from the original top 15, "again" picked out the top 5 do not, retaining the remaining 5. The second way is to pick out the previous 10 records from 15 in front. If the two data sets are poor, there is a relatively low efficiency, which if necessary "ANSI / ISO SQL "Except keyword is ideal. This technique is often used in the application of data "paging", as long as follows the following principles: Select Top @Pagesize * from T where sortfield not in (select top @Pagesize * @Pagei sortfield from t order by sortfield) Order by Sortfield to this, the main purpose of the question is reached. As for the printed transcript: [Student ID], [Student Name], mathematics, language, English, politics, and average results have similar topics, doing it, so only provide reference answers, will not have explained: Select Distinct Top 5 [Selected], [Reviews]. [Student Name] AS Student Name, [T1]. [Result] AS Mathematics, [T2]. [Result] AS language, [T3] "AS English," [T4]. [Result] AS Politics, ISNULL ([T1]. [Grade], 0) ISNULL ([T2]. [Grade], 0) ISNULL ([T3]. Grade], 0) isnull ([T4]. [Grade], 0) AS total from FROM [transcript] LEFT JOIN [transcript] AS [T1] ON [transcript]. [Student ID] = [T1] [Student id] and [t1]. [Lesson ID] = 'K1' Left Join [transcript] AS [T2] ON [transcript]. [Student ID] = [T2]. [Student ID] and [T2] ]. [Lesson ID] = 'K2' Left Join [transcript] AS [T3] on [transcript]. [Student id] = [T3]. [Student id] and [t3]. [Lesson ID] = ' K3 'LEFT JOIN [transcript] AS [T4] ON [transcript]. [student id] = [T4]. [student id] and [t4]. [T4] =' K4 'WHERE ISNULL ([T1] [Grade], 0) isnull ([T2]. [Grade], 0) isnull ([T3]. [Grade], 0) isnull ([T4]. [Grade], 0) Not in (SELECT Distinct top 15 with Ties isnull ([T1]. [Grade], 0) ISNULL ([T2]. [Grade], 0) isnull ([T3]. [Grade], 0) ISNULL ([T4]. [Grade], 0) f ROM [transcript] Left join [transcript] AS [T1] ON [transcript]. [Student id] = [T1]. [Student id] and [t1]. [T1]. [T1]. [课] = 'K1' Left Join [ Serague] AS [T2] ON [transcript]. [Student ID] = [T2]. [Student id] and [t2]. [T2]. [Trial ID] = 'K2' Left Join [transcript] AS [T3] ON [Student ID] = [T3]. [Student ID] and [T3]. [Trial ID] = 'K3' Left Join [transcript] AS [T4] ON [transcript]. [Student ID]. ] = [T4]. [Student id] and [t4]. [Course id] = 'k4' Order by isnull ([T1]. [Grade], 0) isnull ([T2]. [Grade], 0) Isnull ([T3]. [Grade], 0) ISNULL ([T4]. [Grade], 0) DESC) Finally, I have to say more: General TOP keywords are true for true meaning. 8. Statistical printing various scores, each score number: courses, courses, [100-85], [85-70], [70-60], [<60] Although the surface is not so easy, it can be easily achieved with CASE: SELECT Course ID, Course Name, Sum (Case WHEN Results Between 85 and 100 Ten 1 Else 0 End) AS [100 - 85], SUM (Case When Results Between 70 and 85 Then 1 Else 0 End) AS [85 - 70] , SUM (Case WHEN 1 ELSE 0 End) AS [70 - 60], SUM (Case WHEN 1 ELSE 0 End) AS [60 -] from transcripts group by course ID, course name Note that Between, although the field name is from high, can Between or from low to high, if you accidentally, it will make a hard-finding logic error: in mathematics, when A> B, [a, b] is an empty set. 9. Printing student average grades and their name Select Count (Distinct BF) AS Name, a. Student ID, Max (a. Student Name), Max (AF) from (SELECT DISTINCT T. Student ID, T. Student Name, (Select Avg) from T T T1 Where T1. Student ID = T. Student ID) AS F from T) AS A, (Select Distinct T. Student ID, T. Student Name, (SELECT AVG) from T T1 WHERE T1. Student ID = T. Student ID ) as f from t) as b where Af <= B.fgroup by a. Student IDORDER BY Count (BF) There are many places worth mentioning, first using two exactly the same autocorcogen queries to generate two derived tables as the basic table for less than or equal connection, so that they can be smaller than or equal to each value. Other values of count (Distinct) count aggregation functions come. Select 1 (Select Count (Distinct [Average Results]) from (SELECT [Student ID], Max ([Student Name]) AS Student Name, AVG ([Results]) AS [Average Results] From T GROUP BY [Student ID ]) AS T1 WHERE [average grade]> T2. [Average grade]) AS number, [student ID], [student name], [average grade] from (SELECT [Student ID], Max ([Student Name]) AS Student name, avg ([grade]) AS [average score] from t group by [student id]) as t2order by t2. [Average grade] DESC Method II also uses two exactly the same self-related sub-queries to generate two derived tables as the basic table, and then use the correlation of the associated subquery between them, the counting aggregation function of COUNT (DISTINCT) 1 is also updated. Show. From the application perspective, this question is quite reasonable, and the number of situations are also the same. However, if you want to implement a line number similar to the automatic sequence, the limitations of the solution are highlighted, and it is necessary to deal with equal equality. Not equal to (t1.f1 ... t1.fn <= t2.f1 ... t2.fn). Continue to display only even or odd lines by judging if count (distinct)% 2 is 0. Having Count (Distinct BF)% 2 = 1 or: WHERE 1 (Select Count (Distinct [Average Results]) from (SELECT [Student ID], Max ([Student Name]) AS Student Name, AVG ([Results]) AS [average score] from t group by [student ID]) AS T1 WHERE [average grade]> T2. [Average score])% 2 = 1 Briefly, Having and WHER are distinguished in the query containing the Group By group, and the "" where the "" WHER is first filtering in the data packet, and having should generally be in the same way with the aggregation function. meaning. The two methods once again reflected that the subquery and connection can be made a wonderful, the second seed query method is worth recommending, which is easy to add this function to the original query without this feature. This question is just to show a relatively novel solution idea, avoiding the problem of efficiency. 10. Report of the top three records of each subject: (do not consider the results]) Student ID, student name, course ID, course name, grade, teacher ID, teacher name If you only consider the top three people, use the knowledge of related sites: SELECT * FROM score table T1 WHERE achievement in (SELECT TOP 3 results from transcript WHERE T1. Course ID = Course ID ORDER BY grade DESC) Order by T1. Course ID The results of such queries should be more than equal to three, because there may be strips, if less than three natural is that the course is not so many exams! If you don't consider the situation, strictly control each department only Three records are printed, and the "Student ID" constructor is used to construct the associated subqueries. SELECT * FROM score table T1 WHERE Student ID in (SELECT TOP 2 Student ID FROM Picture WHERE T1. Course ID = Course ID ORDER BY Score DESC) Order By T1. Course ID This application can also be achieved if the idea of using the 10th question can also be implemented. 11. The problem of standardized standardization can be said to be benevolent, and the wise is witnessed. And don't do it, but you can't do it, it's not necessarily a good thing. First analyze the correspondence relationship of information, there are four information in this table. Students, courses, teachers, grades. The top three can exist independently, and the last one can be seen based on the top three exists. Then, we build four tables according to these four categories: about students, there are two: students ID, name; teachers will have teachers ID, name, course ID, this is why I want students and teachers Will be the reasons for two tables; the course has a course ID, the course name; the last grades, a part of the joint, here, it must have a student ID, teacher ID, course ID, grade Four items, the opposite and other forms should be applied, in addition to the field, other tables that are referenced. In this way, the scripts of several tables are probably: Create Table "Student Information" ("ID" Char (4), "Name" char (16), Primary Key ("ID")) CREATE TABLE "course information "(" ID "char (4)," Name "char (16), Primary Key (" ID "),) Create Table "Teacher Information" ("ID" Char (4), "Name" Char (16), "Course ID" Char (4), Primary Key ("ID"), Foreign Key ("Course ID") References Course Information ("ID")))) Create Table "Results" ("Student ID" Char (4), "Teacher ID" Char (4), "Course ID" Char (4), Grade Numeric (5, 2), Primary Key ("Student ID", "Teacher ID", "Course ID"), Foreign Key ("Student ID") References "Student Information" ("Id"), Foreign Key ("Teacher ID") References "Teacher Information" ("ID"), Foreign Key ("Course ID") References "Course Information" ("ID")) This is clearly based on the classification of information as much as possible. It is the benefit of various information, but the problem is also obvious, for example, a teacher cannot bring two different classes at the same time (of course, this may be required by the business rules), and this is too delicate . If you don't need to conduct personnel management for teachers, you can complete the teacher information and course information as a table. That is to say, the same name course with different teachers is visual as different courses. In this way, there is of course its application background, many teachers, especially higher education and famous teachers, often have their own style, completely visualize two courses, I believe that the same professor C , Lippman and Stroustrup have different students. . To say the problem, that is, if you want to limit students can't repeat a group, you have to use triggers, there is no good way, but this problem, the first design is not solved, even for teachers And the relationship between the courses is not necessarily possible, and the problem is complicated. Now list the scripts of the second design: Create Table "Student Information" ("ID" Char (4), "Name" char (16), Primary Key ("ID")) Create Table "Curriculum Information" ("ID" Char (4), "Course Classification" Char (4), "Name" Char (16), "Teacher ID" Char (4), "Teacher Name" Char (16), PRIMARY Key ("ID")))) Create Table "Result Information" ("Student ID" Char (4), "Course ID" Char (4), Grade Numeric (5, 2), Primary Key ("Student ID", "Course ID"), Foreign Key ( "Student ID") References "Student Information" ("ID"), Foreign Key ("Course ID") References "Course Information" ("ID") -) This is not a bit better, and if there is no teacher teaches different courses, and we want to simplify management, or even "course classification" and "teacher ID" fields can be used. Of course, depending on the needs of the business, if you want to classify the course to restrict students learn, you don't want to bring additional performance overhead, use the first design, or put the course classification field in a grade information table, is a more Good way. Regarding the design and management of the database, there are several experiences, come out here to communicate: Standardize the data, it is best to comply with its application background. This is easy to understand and manage; the normalization of the data is not necessarily, the better, the particle size is appropriate, and the next programming is generally easier; although it is not fine, it is almost necessary. I have a problem; it is important: Don't abuse automatic identity columns! In particular, don't abuse automatic identity column as a single constraint condition in a table, usually, that is nothing different! Regarding these questions, our views are here, I hope that friends can come up with more and better opinions, let's discuss it together. Original title: Create Table [T] ([Id] [INT] Identity (1, 1) Not Null, [Student ID] [VARCHAR] (50) NULL, [Student Name] [VARCHAR] (50) NULL, [Courses ID] [VARCHAR] (50) NULL, [Courses Name] [varcha] (50) NULL, [Result] [REAL] NULL, [Teacher ID] [VARCHAR] (50) NULL, [Teacher Name] [VARCHAR ] (50) NULL, ConsTRAINT [PK_T] Primary Key Clustered ([ID]) ON [PRIMARY]) ON [PRIMARY] GO INSERT INTO T ([Student ID], [Student Name], [Course ID], [Course Name], [Result], [Teacher ID], [Teacher Name]) VALUES ('S3', 'King ",' K2 ',' Language ', 81,' T2 ',' Wang ') INSERT INTO T ([Student ID], [Student Name], [Course ID], [Course Name], [Result], [Teacher ID] , [Teacher name]) Values ('s3', 'king five', 'k4', 'politics', 53, 't4', 'Zhao') INSERT INTO T ([Student ID], [Student Name], [Course ID], [Course Name], [Result], [Teacher ID], [Teacher Name]) VALUES ('S4', 'Zhao Sul',' K1 ',' Mathematics ', 99,' T1 ',' Teacher Zhang ') Insert Into T ([Student ID], [Student Name], [Course ID], [Course Name], [Result], [Teacher ID], [Teacher Name]) VALUES (' S4 ',' Zhao Six ',' K2 ',' Language ', 33,' T2 ',' Wang ') INSERT INTO T ([Student ID], [Student Name], [Course ID], [Courses Name], [Result], [Teacher ID], [Teacher Name]) Values ('S4', 'Zhao Sul',' K4 ',' Politics', 59, 'T4', 'Zhao') INSERT INTO T ([Student ID], [ Student Name], [Course ID], [Courses Name], [Result], [Teacher ID], [Teacher Name]) Values ('S1', 'Zhang San', 'K4', 'Political', 79, ' T4 ',' Zhao Teacher ') Insert Into T ([Student ID], [Student Name], [Course ID], [Course Name], [Result], [Teacher ID], [Teacher Name]) VALUES (' S1 ',' Zhang San ',' K1 ',' Mathematics', 98, 'T1', 'Zhang Teacher') INSERT INTO T ([Student ID], [Student Name], [Course ID], [Course Name], [Results], [Teacher ID], [Teacher Name]) Values ('S1', 'Zhang San', 'K3', 'English ", 69,' T3 ',' Li Teacher ') Insert Into T ([Student ID], [Student Name], [Course ID], [Program Name], [Result], [Teacher ID], [Teacher Name]) VALUES ('S7', 'Peter', 'K1', 'Mathematics ", 64, ' T1 ',' Zhang Teacher ') Insert Into T ([Student ID], [Student Name], [Course ID], [Course Name], [Result], [Teacher ID], [Teacher Name]) VALUES (' S7 ',' Peter ',' K2 ',' 王 老 ') INSERT INTO T ([Student ID], [Student Name], [Course ID], [Course Name], [ Grade], [Teacher ID], [Teacher Name]) Values ('S7', 'Peter', 'K4', 'Politics ", 53,' T4 ',' Zhao Teacher ') Insert Into T ([Student ID], [Student Name], [Course ID], [Course Name], [Result], [Teacher ID], [Teacher Name]) VALUES (' S2 ',' mike ',' K1 ',' Mathematics', 64, 'T1', 'Zhang') INSERT INTO T ([Student ID], [Student Name], [Course ID], [Course Name], [ Grade], [Teacher ID], [Teacher Name]) Values ('S2', 'Mike', 'K2', 'Language ", 81,' T2 ',' Wang Teacher ') Insert Into T ([Student ID] [Student Name], [Course ID], [Course Name], [Result], [Teacher ID], [Teacher Name]) Values ('S2', 'Mike', 'K4', 'Political', 53, The two-dimensional table T (F1, F2, F3, F4, F5, F6, F7) indicate the following relationship: ┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ───────────────────────────────────────────────── Course ID │ Course Name │ Grade │ Teacher ID │ Teacher Name │ ├ ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ─ ─ ────────────────────────────────────────────────────────────────────────yle;:::::::::::::::::::: ─ ─ ─────────────────────────────────────────────────── ─────────────────── │ S1 │ 张三 │ K1 │ Mathematics │ 61 │ T1 │ Zhang Teacher │ ├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ────────────── ─ │ S2 │ Li 4 │ K3 │ English │ 88 │ T3 │ Li Teacher │ ├──────────────── ─────────────────────────────────────────────────────────── ┼─────── ─ ┤ │ S1 │ 张三 │ K4 │ Politics │ 77 │ T4 │ Zhao Teacher │ ├───────────────────────── ┼ - ────────────────────────────────────────────────── ─ ─ ─ ┤ │ S2 │ Li 4 │ K4 │ Politics │ 67 │ T5 │ Zhou Teacher │ ├──────────────────────────────────────────────── ─────────────────────────────────────────────────────── ─ ┤ │ S3 │ Wang 5 │ K2 │ │ 90 │ T2 │ 王 王 │ ├──────────────────────────────────── ─ ─ ─────────────────────────────────────────────────────────────────tes 05 │ K1 │ Mathematics │ 55 │ T1 │ Zhang Teacher │ ├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ─────────────────────────── ─ │ S1 │ 张 三 │ K2 │ Language │ 81 │ T2 │ Wang Teacher │ ├──────────────────────────────────────────────────── ─ ─ ────────────────────────────────────────────────────tes ─────────────────────────────────────────────────────────── ┼────────────────────────────────────────────────────yle::::::::::::::: ::::: ┼ - ────────────────────────────────────────────────── ──────────────── ┤ │ S2 │ Li 4 │ K1 │ mathematics │ 81 │ T1 │ Zhang Teacher │ ├────────────────────────────────────── ─ ─ ───────────────────────────────── │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ├────────────────────────────────────────── ──────────────────────── │ │ │ │ │ │ │ │ └────── ──┴──────────┴──────────┴──────────┴──────────┴─── ─────────────────── ─ ┘ ┘ ┘ 表 T (F1, F2, F3, F4, F5, F6, F7) is represented below: -------- -------------------------------------------------- -------------------- │ Student ID │ Student Name │ Course ID │ Course Name │ Grade │ Teacher ID │ Teacher Name │ │ S3 │ Wang 5 │ K4 │ Politics │ 53 │ │ │ │ │ 61 │ T1 │ 数 老 │ │ │ 张 老 │ K3 │ English │ 88 │ T3 │ Li Teacher │ │ S1 │ 张三 │ K4 │ Politics │ 77 │ T4 │ Zhao Teacher │ │ S2 │ 李 四 │ K4 │ Politics │ 67 │ T5 │ Zhou Teacher │ │ S3 │ Wang 5 │ K2 │ │ │ │ │ S3 │ 王 五 │ K1 │ Mathematics │ │ │ 张 张 │ │ │ 张 张 │ K2 │ Language │ 81 │ T2 │ 王 老 │ │ │ │ Zhao 6 │ K2 │ │ │ │ │ │ 张三 │ K3 │ │ │ │ │ 老 │ │ S2 │ 李 四 │ K1 │ Mathematics │ 81 │ T1 │ Zhang Teacher │ │ ... . │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------1. Standardization Please answer in T-SQL (MS SQL Server) or Jet SQL (MS Access)! 2. If the T table has a field F0 data type is automatic incremental integer (unique, no repetition), and the T table contains In addition to the F0 field, delete the excess of excess dirty recording data (to retain one): Delete t from T, T1 WHERE T. Student ID = T1. Student ID and T. Course ID = T. Course ID and T.f0 Delete from t where [f0] not in (SELECT MAX ([f0]) from [T] group by t.f1, t.f2, t.f3 haVing count (*)> 1) And f0 not in (SELECT MAX [F0]) from [t] group by t.f1, t.f2, t.f3 having count (*) = 1) Delete from T where [f0] <(Select Max ([F0]) from [t] as t1 where t1 .f1 = t .f1 and t1.f2 = t .f2 and t1.f3 = t .f3 group by t1. F1, T1.F2, T1.F3) 3. Print the highest score of each department: (is the highest, minimum of students and teachers of each course) course ID, course name, highest score, student ID, student name, teacher ID, teacher name, minimum Division, student ID, student name, teacher ID, teacher name SELECT T. Courses ID, T. Course Name, T. [Result] AS Mode, T. [Student ID], T. [Student Name], T. [Teacher ID], T. [Teacher Name], T1. [Grade] The least score, T1. [Student ID], T1. [Student Name], T1. [Teacher ID], T1. [Teacher Name] From T Left Join T AS T1 on T. [Course ID] = T1 [Course ID] WHERE T. [grade] = (SELECT MAX (T2. [Grade]) from t as [t2] where t. [course id] = T2. [Course ID] group by t2. [Courses ID] ) And t1. [Grade] = (SELECT MIN (T3. [Grade]) from t as [t3] where t1. [Lesson ID] = T3. [Courses ID] group by t3. [Course id]) 4. Press from high to low order, print all students (mathematics, language, English, politics) courses: (就 成 的) Student ID, Student Name, Mathematics, Language , English, political, effective courses, effective average (Note: Effective courses have the student's performance record in T table, if not understand "effective course number" and "effective average points") SELECT Student ID, Max (Student Name) AS Student Name, (SELECT Score from T where Student ID = T0. Student ID AND Course ID = 'K1') AS Mathematics, (SELECT Score from T WHERE Student ID = T0. Student ID And course id = 'k2') AS language, (SELECT score from T where student id = t0. Student ID and course id = 'k3') AS English, (SELECT score from T where student ID = t0. Student ID and course ID = 'K4') AS Politics, Count (*), AVG (T0. Review) from T AS T0 Group by Student ID SELECT Student ID, Max (Student Name) AS Student Name, (SELECT Score from T where Student ID = T0. Student ID AND Course ID = 'K1') AS Mathematics, (Select Max (Class) from Classes, t where t. Results> = Classes.Minv and T. grades <= classes.maxv and t. Student ID = T0. Student ID and T. Course ID = 'K1') AS Mathematics Level, (SELECT Score from T where Student ID = T0. Student ID AND Course ID = 'K2') AS language, (Select Min (Class) from classes, t where t. Grade> = classes.minv and t. Grade <= classes.maxv and t. Student ID = T0. Student ID and T. Course ID = 'k2') AS language level, (SELECT score from T where student id = t0. Student ID and course id = 'k3') AS English, (Select Max (Class) from Classes, t wherere T. Grade> = Classes.Minv and T. grade <= classes.maxv and t. Student ID = T0. Student ID and T. Course ID = 'K3') AS English Level, (SELECT Score from T Where Student ID = T0. Student ID AND Course ID = 'K4') AS Politics, (Select Min (Class) from Classes, T Where T. Grade> = Classes.minv And t. grade <= classes.maxv and t. Student ID = T0. Student ID and T. Course ID = 'K4') AS Policy Level, count (*), AVG (T0. grade), (SELECT MAX "From classes where avg (t0. Grade)> = Classes.minv and Avg (t0. Grade) <= classes.maxv) AS average level from t as t0 group by student ID Select [T]. [Student ID], Max ([T]. [Student Name]) AS Student Name, Max ([T1]. [Results]) AS Math, Max ([T2]. [Grade]) AS language , Max ([T3]. [Grade]) AS English, Max ([T4]. [Grade]) AS politics, count ([t]. [Lesson ID]) AS effective course, (Isnull ([T1 ]. [Grade]), 0) ISNULL (Max ([T2]), 0) ISNULL (Max ([T3]. [Grade]), 0) ISNULL (Max ([T4]. [Grade]), 0) / count ([t]. [Lesson ID]) AS effective average from FROM [T] Left join [t] as [t1] on [t]. [Student ID] = [T1] [Student id] and [t1]. [T2] = 'K1' Left Join [T] AS [T2] ON [T]. [Student ID] = [T2]. [Student ID] and [T2]. [Course ID] = 'K2' Left Join [T] AS [T3] ON [T]. [Student ID] = [T3]. [Student ID] and [T3]. [Timi ID] = 'K3' LEFT JOIN [T] AS [T4] ON [T]. [Student ID] = [T4]. [Student ID] and [T4]. [T4] = 'K4' Group by [T]. [Student ID] Order by (ISNULL ([T1]. [Grade]), 0) ISNULL (Max ([T2]), 0) ISNULL (Max ([T3]. [Grade]), 0) ISNULL (Max ([T4]. [Grade]), 0) / count ([t]. [T]. [T]. "The 10th to 15th" Student transcripts or print average grades " 10 students' transcripts [Student ID], [Student Name], Mathematics, Language, English, Politics, Average Select Distinct [T]. [Student ID], [T]. [Student Name] AS Student Name, [T1]. [Result] AS Mathematics, [T2]. [Grade] AS language, [T3]. [Grade] AS English, [T4]. [Results] AS Politics, ISNULL ([T1]. [Grade], 0) isnull ([T2]. [Grade], 0) ISNULL ([T3]. [Grade], 0 ) Isnull ([T4]. [Grade], 0) AS total from FROM [T] LEFT JOIN [T] AS [T1] ON [T]. [Student ID] = [T1]. [Student ID] and [ T1] = 'K1' Left Join [T] AS [T2] ON [T]. [Student ID] = [T2]. [Student ID] and [T2]. [T2]. [Lesson ID] = 'K2 'Left Join [T]. [Student ID] = [T3]. [Student ID] and [T3]. [T3] =' K3 'Left Join [T] as [T4] ON [T]. [T4]. [Student ID] and [T4]. [T4]. [T1] = 'K4' WHERE ISNULL ([T1]. [Grade], 0) ISNULL ([T2] [Grade], 0) isnull ([T3]. [Grade], 0) isnull ([T4]. [Grade], 0) Not in (Select Distinct Top 3 with TIES ISNULL ([T1]. [Grade ], 0) ISNULL ([T2]. [Grade], 0) ISNULL ([T3]. [Grade], 0) isnull ([T4]. [Grade], 0) from [t] left JOIN [ T] as [t1] On [T]. [T1]. [Student ID] and [T1]. [T1]. [T2] = 'K1' Left Join [T] AS [T2] ON [T]. [Student id] = [T2]. [T2]. [T2] = 'K2' Left Join [T] AS [T3] ON [T]. [Student ID] = [T3]. [Student ID] and [ T3]. [Lesson ID] = 'K3' Left Join [T] AS [T4] ON [T]. [Student ID] = [T4]. [Student ID] and [t4]. [T4]. [Lesson ID] = 'K4 'Order by isnull ([T1]. [Grade], 0) ISNULL ([T2]. [Grade], 0) isnull ([T3]. [Grade], 0) ISNULL ([T4]. ], 0) DESNULL ([T1]. [Grade], 0) isnull ([T2]. [Grade], 0) ISNULL ([T3]. [Grade], 0) ISNULL ([T4]. [Grade], 0) in (Select Distinct Top 4 with TIES ISNULL ([T1]. [Grade], 0) ISNULL ([T2 ]. [Grade], 0) ISNULL ([T3]. [Grade], 0) isnull ([T4]. [Grade], 0) from [t] left join [t] as [t1] on [t] ]. [Student ID] = [T1]. [Student ID] And [T1]. [T2] = 'K1' Left Join [T] AS [T2] ON [T]. [Student ID] = [T2] [Student ID] and [t2]. [T2] = 'K2' Left Join [T] AS [T3] ON [T]. [Student ID] = [T3]. [Student ID] and [T3]. [Course ID] = 'K3' Left Join [T] AS [T4] ON [T]. [Student ID] = [T4]. [Student ID] and [T4]. [Triarse ID] = 'K4' Order By ISNULL ([T1]. [Grade], 0) ISNULL ([T2]. [Grade], 0) ISNULL ([T3]. [Grade], 0) isnull ([T4]. [Grade], 0 ) DESC) Order by isnull ([T1]. [Grade], 0) isnull ([T2]. [Grade], 0) isnull ([T3]. [Grade], 0) isnull ([T4]. [Grades], 0) DESC6. According to the percentage of each subject, the percentage from low to high and average results from high to low order, statistics, the average of the average scores and the noble rate of each subject (represented by "N)) : (Is the problem of analyzing which course) course ID, course name, average grade, and 百 分 SELECT course ID, MAX (course name) AS course name, AVG (grade) AS average score, 100 * SUM (Case WHEN score> = 60 THEN 1 else 0 end) / count (*) AS and Geparase from T Group BY Course ID ORDER BY and Bai Pieces DESC 7. The average score rate and the average rate of the printing of the printing rate (using "1 line 4 column"): (is the problem of analyzing which course is difficult) Mathematical average division, mathematics and grid population, Chinese average division, language and Geometers, English Average score, English and genus score, political average, political and parallel Select SUM (Case WHEN Course ID = 'K1' TENGEE ELSE 0 End) / (Select Count (*) from T Where Courses ID = 'K1') AS Mathematics Average, 100 * Sum (Case WHEN Course ID = 'K1 'And score> = 60 THEN 1 ELSE 0 END) / SUM (Case WHEN Course ID =' K1 'TEN 1 ELSE 0 End) AS Mathematics and Geparase, SUM (Case WHEN Course ID =' K2 'THEN) ELSE 0 END) / (Select Count (*) from T where course ID = 'k2') AS language average, 100 * SUM (Case WHEN course ID = 'K2' AND score> = 60 Then 1 else 0 end) / sum (Case When) Course ID = 'K2' THEN 1 ELSE 0 END) AS Language and ELS BACT SUM (Case WHEN Course ID = 'K3' TENGEL ELSE 0 End) / (Select Count (*) from T Where Courses ID = 'K3') AS English average, 100 * Sum (Case WHEN Course ID = 'K3' AND)> = 60 THEN 1 ELSE 0 END) / SUM (Case WHEN Course ID = 'K3' Then 1 ELSE 0 End) AS English and Grid Points, SUM (Case WHEN Course ID = 'K4' THEN Review ELSE 0 End) / (Select Count (*) from T Where Course ID = 'K4') AS Politics Average, 100 * SUM (Case When Course ID = 'K4' AND score> = 60 THEN 1 ELSE 0 END) / SUM (Case WHEN Course ID = 'K4' TEN 1 ELSE 0 END) AS Politics and Gepass FROM T8. Press different teachers to teach different courses from high to low list printing : (Is it to analyze which teacher's level of the course level) Teacher ID, teacher name, course ID, course name Called, average score (average points) and one highest points and one minimum SELECT teacher ID, MAX (teacher name), course ID, max (course name) AS course name, avg (grade) AS average grade from t group by course ID, teacher ID ORDER BY AVG (grade) The average points are not difficult to write by pressing a maximum division and a minimum division, it is not difficult to write: SELECT teacher ID, MAX (teacher name), course ID, max (course name) AS course name -, AVG (grade) AS average results, (Sum (Sum) - (SELECT MAX) from transcripts WHERE course ID = T1. Course ID AND teacher ID = T1. Teacher ID) - (SELECT MIN) - (SELECT MIN) WHERE course ID = T1. Course ID AND teacher ID = T1. Teacher ID) / cast ((SELECT COUNT) ) -2 from transcript WHERE course ID = T1. Course ID AND teacher ID = T1. Teacher ID) AS FLOAT AS Average FROM transcripts AS T1 WHERE (SELECT Count (*) -2 from transcript WHERE course ID = T1. Course ID AND teacher ID = T1. Teacher ID)> 0 group by course ID, teacher ID ORDER BY Average DESC9. Statistics Print Each section, each score number: Course ID, course name, [100-85 ], [85-70], [70-60], [<60] SELECT Course ID, Course Name, Sum (Case WHEN Results Between 85 and 100 Ten 1 Else 0 End) AS [100 - 85], SUM (Case When Results Between 70 and 85 Then 1 Else 0 End) AS [85 - 70] , SUM (Case WHEN 1 ELSE 0 END) AS [70 - 60], SUM (Case WHEN 1 ELSE 0 END) AS [60 -] from t group by course ID, course name 11. Printing student average score and its name Select Count (Distinct BF), a. Student ID, Max (a. Student Name), Max (AF) from (SELECT DISTINCT T. Student ID, T. Student Name, "(SELECT AVG (grade) from T T T1 WHERE T1. Student ID = T. Student ID) AS F from T) AS A, (Select Distinct T. Student ID, T. Student Name, (Select Avg) from T T1 WHERE T1 . Student ID = T. Student ID) AS F from T) as b WHERE AF <= bf group by a. Student ID ORDER BY Count (BF)
