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. It is necessary to emphasize: Be sure to select non-repetitive connection conditions, you can use field combination according to actual conditions 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, if the HAVING or WHERE clause is implemented:
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") -)
Is this a little refreshing? In this way, if there is no teacher teaches different courses, and we want to simplify management, but you can not need "course classification" and "teacher ID" fields. 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, 'T4', 'Zhao Teacher')
The two-dimensional table T (F1, F2, F3, F4, F5, F6, F7) are shown below: ┌ - ─ ─ ───────────────────────────────── ─ ┬──────────── ┐ │ Student ID │ Student Name │ Course ID │ Course Name │ Protect │ Teacher ID │ Teacher Name │ ─────────── ─ ─ ─ ────────────────────────────────── │ S3 │ 王 五 │ K4 │ Politics │ 53 │ T4 │ Zhao Teacher │ ├── ─ ┼──────────────────────────────────────────── ─ │ S1 │ 张三 │ K1 │ Mathematics │ 61 │ T1 │ Zhang Teacher │ ├────────────────────────────────────────────────── │ S2 │ 李 四│K3 │ English │ │ ├ ───────────────────────────────────────── ─ ┼───── ┤ │ S1 │ 张三 │ K4 │ Politics │ 77 │ T4 │ Zhao Teacher │ ├───────────────────────────────────────────────────────────────── ─ ┼─────────────────── │ S2 │ 李 四 │ K4 │ Politics │ 67 │ T5 │ Zhou Teacher │ ├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ──────────────────────────────────────────── │ S3 │ Wang 5 │ K2 │ │ 90 │ T2 │ 王 老 │ ├────── ─ ┼──────────────────────────────────────────── ─ │ S3 │ Wang 5 │ K1 │ Mathematics │ 55 │ T1 │ Zhang Teacher │ ├───────────────────────────────────────────────── │ S1 │ 张三 │ K2 │ Language │ 81 │ T2 │ 王 王 │ ├────────────────────────────────────── ─ ┼──── │ S4 │ Zhao Six │ K2 │ Language │ 59 │ T1 │ Wang Teacher │ ├───────────────────────────────────────────────────────────────────────────────────── ┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ─ ─ ─ ─ ─ ─ ─ ─ ─ ───────────────────────────────────────────────────────────────────
-------------------------------------------------- ---------------------------- │ Student ID │ Student Name │ Course ID │ Course Name │ Grade │ Teacher ID │ Teacher Name │ │ │ │ │ │ │ │ S3 │ 王 五 │ K4 │ Politics │ 53 │ T4 │ 老 │ K1 │ 张 三 │ │ │ 数 数 │ │ │ │ │ │ │ │ │ K3 │ English │ 88 │ T3 │ Li Teacher │ │ S1 │ 张三 │ K4 │ Politics │ 77 │ T4 │ 老 │ │ │ │ 李 四 │ │ │ 政 │ │ │ │ │ │ │ │ K2 │ Language │ 90 │ T2 │ Wang Teacher │ │ │ 王 五 │ │ │ 数 老 │ │ │ │ 张三 │ K2 │ │ 81 │ T2 │ 王 老 │ │ S4 │ Zhao Six│K2 │ Language │ 59 │ T1 │ Wang Teacher │ │ │ │ │ │ │ │ │ │ │ │ │ 81 │ T1 │ 张 老 │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ... │ │ │ │ │ │ │ ------------------------------------- ----------------------------------------- 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, remove the excess dirty recording data (one to keep one of the other fields):
Delete t from t, t as 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)
