Queries Mysql database

Asked

Viewed 129 times

-2

Currently I have studied SQL Server and My SQL in college, but having a lot of difficulty in making queries in the database. The bank has 3 tebelas (aluno, discuplina and matricula)

inserir a descrição da imagem aqui

  1. Show the total amount of tuition paid by students per course (only for students who have enrolled in any discipline).
  2. Show how many approvals each course has (per grade and missing).
  3. Show how many students had already failed by default, but would pass by grade.
SELECT aluno.nome 
FROM aluno, matricula 
WHERE aluno.ra = matricula.ra
  AND faltas > faltas - (faltas * 0.9)
  AND (nota1 + nota2) / 2 > 7; (15)

Repeat the previous query, showing the data separated by stroke.

  1. Show the number of registrations per year and the general average of the averages obtained in these registrations per year.
mysql sql sql-server

  • I don’t know if I got it right but do you want answer for the 4 items? in case, four whole queries?

  • I wanted to understand how to do this type of query that are more specific.

  • https://www.w3schools.com/sql/sql_count_avg_sum.asp this page will help you.. has sum, media and counter functions, you will need them ;)

1 answer

1

Not knowing exactly the fields of each table I have difficulty helping you more. However, I leave below an explanation of the functions that in my view can help you solve the issues.

Question 1

Show the total amount of tuition paid by students per course (only for students who have enrolled in any discipline).

Answer

  • In this issue you should join the tables so that the information is available in a single "notary". That is, the information of the student, which disciplines he is linked and enrollment information were all in the same line. For this use INNER JOIN, using field that allow "connect" one table to another. For example id_matricula on the student table with id_matricula on the discipline table.

  • Assuming that the tuition fee is in the discipline table, you can use the SUM function to sum the field values valor_mensalidade

  • To group course data and students just use GROUP BY

The code to join the tables would look like this

SELECT aluno.nome, disciplina.curso, sum(matricula.valor_mensalidade) FROM aluno
INNER JOIN matricula ON aluno.id_matricula = matricula.id_matricula
INNER JOIN disciplina ON disciplina.id_matricula = aluno.id_matricula
GROUP BY aluno.nome, disciplina.curso

Together

Question 02

Show how many approvals each course has (per grade and missing).

Answer

  • Assuming that in the table aluno if the grades and fouls are placed, you need to link the table students to the table discipline for something equal to the one made in question 01.
  • After checking add the criteria, using the clause WHERE so that only students who have passed will be returned, by the example that you yourself cited the criteria for failure to pass is faltas > faltas - (faltas * 0.9) and approval by note has the criterion (nota1 + nota2) / 2 > 7 PS: I did not understand the expression (Nota1 + nota2) / 2 > 7;(15)
  • You will also need to count the amount of records that the SELECT will return, you can use the function COUNT.
  • You will also need to use the GROUP BY to group data by course

the code would look like this

SELECT disciplina.curso, count(*) FROM alunos
INNER JOIN disciplina ON alunos.id_matricula = disciplina.id_matricula
WHERE disciplina.faltas > .disciplinafaltas - (disciplina.faltas * 0.9)
GROUP BY disciplina.curso

PS2: In my view, there is no table that relates the student to various disciplines.

Question 03

Show how many students had already failed by default, but would pass by grade.

If it makes any sense, I’ll come back and fill it.

Question 04

Show the number of registrations per year and the general average of the averages obtained in these registrations per year.

If it makes any sense, I’ll come back and fill it.

Browser other questions tagged mysql sql sql-server

You are not signed in. Login or sign up in order to post.