Using MySQL, Print the age of the oldest and youngest student(s) per department.

Question

Using MySQL, Print the age of the oldest and youngest student(s) per department. Note that MySQL provides several functions for performing calculations on DATE. The function “TIMESTAMDIFF()” can be used to convert the date of birth to age. Several examples are available at https://dev.mysql.com/doc/refman/5.7/en/date-calculations.html

CREATE TABLE Students(
stuID CHAR(10),
stuName varchar(30),
gender char(1),
birthdate DATE,
enterYear Year(4),
gpa float(4,3),
PRIMARY KEY(stuID));

CREATE TABLE Majors(
deptName varchar(25),
stuID CHAR(10),
degreeProgram VARCHAR(40),
attendSemester VARCHAR(15),
attendYear YEAR(4),
PRIMARY KEY(deptName,stuID),
FOREIGN KEY(deptName) REFERENCES Departments(deptName),
FOREIGN KEY(stuID) REFERENCES Students(stuID));

This is what I have so far:

SELECT Distinct age, deptName
TIMESTAMPDIFF (YEAR, birthdate, CURDATE()) AS age
FROM Student, Majors
Where

Can you help me finish it?

Explanation / Answer

Query for Printing Minimum Age

SELECT min(TIMESTAMPDIFF(YEAR,Students.birthdate,CURDATE())) AS Minimum_age,departments.deptName
FROM Students, Majors,departments
Where Students.stuid=Majors.stuid and Majors.deptname= departments.deptname
group by departments.deptName

Query for Printing Maximum age -

SELECT max(TIMESTAMPDIFF(YEAR,Students.birthdate,CURDATE())) AS Maximum_age,departments.deptName
FROM Students, Majors,departments
Where Students.stuid=Majors.stuid and Majors.deptname= departments.deptname
group by departments.deptName

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.