You need to write a few SQL queries to the diagram. Request condition, chart, and also my variant of requests here:

Diagram

Condition

My version:

  1. SELECT Specialty Name FROM WHERE Specialties Specialty Code IN (SELECT Specialty Code FROM Groups WHERE Group Code IN (SELECT Group Code FROM Students WHERE Student Code IN (SELECT Student Code FROM Grades WHERE Grade IN (SELECT MAX (AVG) Grade) FROM Grades) )));

  2. SELECT Group Name FROM Groups WHERE Group Code IN (SELECT Group Code FROM Students WHERE Student Code IN (SELECT Student Code FROM Grades WHERE Grade> 5));

  3. SELECT Teacher's Last Name FROM Teacher WHERE Teacher's Code IN (SELECT Teacher's Code FROM Grades WHERE Grade <6);

  4. SELECT Name, Fellowship Size FROM Students WHERE Student Code IN (SELECT Student Code FROM Grades WHERE COUNT (Grades = 4)> 2);

Closed due to the fact that the essence of the issue is not clear to the participants of Kromster , Akina , 0xdb , Denis , aleksandr barakin 14 May at 9:35 .

Try to write more detailed questions. To get an answer, explain what exactly you see the problem, how to reproduce it, what you want to get as a result, etc. Give an example that clearly demonstrates the problem. If the question can be reformulated according to the rules set out in the certificate , edit it .

  • No to all 4 replies. - Akina
  • How then to fix that was correct? - Corvus
  • Nobody will do anything for you here, do not hope. - Akina
  • Well, just for someone it will not be a burden to give a more detailed answer than just "No." - Corvus
  • The form in which you asked a question is extremely annoying members of the forum who can help you (including me). First, it makes sense to split your initial question into four questions; Secondly, read this. Stackoverflow.com/help/how-to-ask and this is ru.stackoverflow.com/help/on-topic (especially in terms of learning tasks). For each of the four separate questions, remove the extra tables. Avoid applying task text as a picture. Your attempt to solve the problem and the course of your reasoning should be clearly visible. Nothing will be done for you, but only they will tell you where to look. - Alexander Muksimov

1 answer 1

  1. max (avg ()) cannot be, it is impossible to nest aggregate functions. the average is needed in the context of the specialty, so there should be a group of by specialties It would be better to calculate the average score in the context of specialties (collecting all the join'ami, no IN). After sort by it and take the first entry.

  2. It is required to receive groups where ALL students received at least 5. Your request will select groups where at least one student received such a score. It is required again join followed by group by, which will collect the minimum estimates in the group and in having having filtered where the minimum score is not lower than 6.

  3. (which is actually 4). As in the first case, get teachers who have at least one grade less than 6, i.e. completely opposite result. You need either not exists / not in, which will check the absence of grades above 6. or again group by, by teacher with a check in having, that the maximum grade is lower than 6.

  4. (which is 3). I would take the word "Reduce" as the need to change the contents of the table, and not just output the result. COUNT(Оценки = 4) will give the number of records where the Rating is not null, no checking for = 4 will occur, because count considers the number of non-null values ​​(and the = operator always returns non-null and then only in mysql, in other databases you cannot write this at all ). In addition, count () in the where clause cannot be, because where is applied before grouping. We need to get students with the required grades. Again, by student group by, eliminating only the necessary estimates in where and checking count (*)> 2 in having. And then do not forget to still count 80% of the scholarship and either withdraw or use the update (depending on how you understand the word "Reduce")

PS It is practically useless to study sql purely on paper; create a database (you can use it on some online service), fill it with test data. And check requests for performance and the desired result.

  • @Corvus, IMHO, it makes sense to take an answer from Mike (there you have a "accept" button with a green check mark) - according to how you have formulated the question, you are unlikely to wait for a more complete and adequate analysis of your problems. - Alexander Muksimov