SQL database for a university

gistfile1.txt

CREATE SCHEMA university_schema;

CREATE TABLE students(
   students_id int,
   students_name nvarchar(50),
   students_surname nvarchar(50),
   students_birthdate date
 );
 CREATE TABLE courses(
   courses_id int,
   courses_name nvarchar(50)
 );
 CREATE TABLE professors(
   professors_id int,
   professors_name nvarchar(50),
   professors_email nvarchar(50)
  );
 CREATE TABLE grades(
   grades_id int,
   grades_name nvarchar(50),
   grades_score decimal,
   grades_professors_id int,
   grades_students_id int
 );
 
 USE university_schema;
 
 INSERT INTO students(students_id,students_name, students_surname,students_birthdate)
 VALUES (1,"Sonia","Perez","12-08-1980")
 
 INSERT INTO students(students_id,students_name, students_surname,students_birthdate)
 VALUES (2,"Susana","Garcia","18-08-1988")
 
 INSERT INTO students(students_id,students_name, students_surname,students_birthdate)
 VALUES (3,"Sara","Sanchez","02-07-1981")
 
 INSERT INTO students(students_id,students_name, students_surname,students_birthdate)
 VALUES (4,"Marta","Sanz","02-03-1983")
 
 INSERT INTO students(students_id,students_name, students_surname,students_birthdate)
 VALUES (5,"Vanesa","Fuentes","19-02-1983")
 
 INSERT INTO students(students_id,students_name, students_surname,students_birthdate)
 VALUES (6,"Sonia","Mendia","22-02-1982")
 
 USE university_schema;
 
 INSERT INTO courses(courses_id,courses_name)
 VALUES (1,"SEO")
 
 INSERT INTO courses(courses_id,courses_name)
 VALUES (2,"Marketing")
 
 INSERT INTO courses(courses_id,courses_name)
 VALUES (3,"Coding") 
 
 INSERT INTO courses(courses_id,courses_name)
 VALUES (4,"Maths")
 
 USE university_schema;
 
 INSERT INTO professors(professors_id,professors_name,porfessors_email)
 VALUES (1,"Sonia","sonia@bottega.edu")
 
 INSERT INTO professors(professors_id,professors_name,porfessors_email)
 VALUES (2,"Hector","hector@bottega.edu")
 
 INSERT INTO professors(professors_id,professors_name,porfessors_email)
 VALUES (3,"Giancarlo","giancarlo@bottega.edu")
 
 INSERT INTO professors(professors_id,professors_name,porfessors_email)
 VALUES (4,"Joaquin","joaquin@bottega.edu")
 
 USE university_schema;
 
 INSERT INTO grades(grades_id,grades_name,grades_score,grades_professors_id,grades_students_id)
 VALUES (1,"SEO",7,1,1)
 
 INSERT INTO grades(grades_id,grades_name,grades_score,grades_professors_id,grades_students_id)
 VALUES (2,"SEO",4,1,2)
 
 INSERT INTO grades(grades_id,grades_name,grades_score,grades_professors_id,grades_students_id)
 VALUES (3,"SEO",3,1,3)
 
 INSERT INTO grades(grades_id,grades_name,grades_score,grades_professors_id,grades_students_id)
 VALUES (4,"SEO",9,1,4)
 
 INSERT INTO grades(grades_id,grades_name,grades_score,grades_professors_id,grades_students_id)
 VALUES (5,"Marketing",7,2,1)
 
 INSERT INTO grades(grades_id,grades_name,grades_score,grades_professors_id,grades_students_id)
 VALUES (6,"Marketing",4,2,2)
 
 INSERT INTO grades(grades_id,grades_name,grades_score,grades_professors_id,grades_students_id)
 VALUES (7,"Marketing",6,2,3)
 
 INSERT INTO grades(grades_id,grades_name,grades_score,grades_professors_id,grades_students_id)
 VALUES (8,"Marketing",8,2,1)
 
 INSERT INTO grades(grades_id,grades_name,grades_score,grades_professors_id,grades_students_id)
 VALUES (9,"Coding",9,3,1)
 
 INSERT INTO grades(grades_id,grades_name,grades_score,grades_professors_id,grades_students_id)
 VALUES (10,"Maths",9,4,1)
 
 #the average grade that is given by each professor
 
 SELECT 
 p.professors_name AS "Professors",
 AVG (grades_score) AS "Average"
 FROM professors p
 JOIN grades g
 ON p.professors_courses_id = g.grades_courses_id
 GROUP BY p.professors_name; 
  
 #the top grades for each student
 
 SELECT  
 s.students_name AS "Students Name",
 MAX(grades_score) AS "Best grades"
 FROM grades g
 JOIN students s
 ON s.students_id = g.grades_students_id
 GROUP BY  g.grades_students_id; 
 
 #sort students by the courses that they are enrolled in
 
 SELECT COUNT(grades_courses_id) as "Enrolled",
 c.courses_name as "Grades"
 FROM grades g
 JOIN courses c
 ON g.grades_courses_id = c.courses_id
 GROUP BY  c.courses_id; 
 
 #summary report of courses and their average grades, sorted bu the most challenging course (course with the lowest average grade) to the easiest course
 courses_name AS "Courses",
 AVG (grades_score) AS "Grades Average"
 FROM courses c
 JOIN grades g
 ON c.courses_id = g.grades_courses_id
 GROUP BY courses_name 
 ORDER BY AVG (grades_score) ASC;
 
 #which student an professor have the most courses in common
 SELECT professors_name AS "Professors", COUNT(professors_name) AS "Common courses",
 grades_students_id AS "ID Students"
 FROM professors p
 JOIN grades g
 ON p.professors_courses_id = g.grades_courses_id
 WHERE professors_name= "Sonia"                                          
 GROUP BY grades_students_id
 ORDER BY COUNT(professors_name) DESC;


SELECT professors_name AS "Professors", COUNT(professors_name) AS "Common courses",
grades_students_id AS "ID Students"
FROM professors p
JOIN grades g
ON p.professors_courses_id = g.grades_courses_id
WHERE professors_name= "Hector"                                         
GROUP BY grades_students_id
ORDER BY COUNT(professors_name) DESC;


SELECT professors_name AS "Professors", COUNT(professors_name) AS "Common courses",
grades_students_id AS "ID Students"
FROM professors p
JOIN grades g
ON p.professors_courses_id = g.grades_courses_id
WHERE professors_name= "Giancarlo"                                          
GROUP BY grades_students_id
ORDER BY COUNT(professors_name) DESC;


SELECT professors_name AS "Professors", COUNT(professors_name) AS "Common courses",
grades_students_id AS "ID Students"
FROM professors p
JOIN grades g
ON p.professors_courses_id = g.grades_courses_id
WHERE professors_name= "Joaquin"                                          
GROUP BY grades_students_id
ORDER BY COUNT(professors_name) DESC;