Consider the below schema of the university database (keys are in bold and under

Question

Consider the below schema of the university database (keys are in bold and underline): • Students(stuID: String, stuName: String, gender: String, birthdate: Date, enterYear: Year, gpa: Float) • Departments(deptName: String, numPhDs: Integer) • ProfessorWorks(profID: String, profName: String, deptName: String) • CourseOffers(cNo: String, cTitle: String, deptName: String) • Majors(deptName: String, stuID: String, degreeProgram: String, attendSemester: String, attendYear: String) • Sections(cNo: String, academicYear: String, semester: String, sectNo: String, profID: String, meetsAt: String, room: String) • Enrolls(stuID: String, cNo: String, academicYear: String, semester: String, sectNo: String, grade: String) These tables have been created and the data have been populated using MySQL server. Download DBHW4s17.sql to your computer and run it on MySQL. Execute your SQL statements based on these tables. Turn in your SQL queries statements and the results of these queries to the assignment link. Write the following queries in SQL statements. The query answers must not contain duplicates, but you should use the SQL keyword DISTINCT only when necessary. When your SQL query statement is not accepted by MySQL, it will display error messages. You need to read that message and fix all problems accordingly. Note that MySQL does not support “INTERSECT” and “EXCEPT” currently. When you need to find the intersection and difference of two result sets, you can use “IN” and “NOT IN” instead. In addition, for this assignment, creation of temporary tables is not allowed, i.e., for each question you have to write exactly one SQL statement.

1. Print the name(s) of professor(s) who teaches class(es) for the Information Technology department. Note that Information Technology is a department name.

2. Print the names of departments that have one or more students whose GPA is above 3.50.

3. 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

. 4. Print the name(s) and sid(s) of the student(s) enrolled in at least three classes in Fall 2016. 5. Find the names of students who have enrolled in both ITT 226 and Math 461 in Fall 2016. Note that ITT 226 and Math 461 are course numbers (cNo).

Explanation / Answer

Please give thumbs up, If it is helpful for you. Thankyou!!

#1.
select profName
from ProfessorWorks
where deptName='Information Technology';

#2.
select deptName
from Majors as M join Students as S
on M.stuID = S.stuID
where S.gpa > 3.50;

#3.
select deptName, min(year(curdate()-year(birthdate)) min_age, max(year(curdate()-year(birthdate)) max_age
from Majors as M join Students as S
on M.stuID = S.stuID
group by deptName;

#4.
select stuName, stuID
from student
where stuID in (select stuID from
(select stuID, count(cNo)
from Enrolls group by stuID
               where academicYear='2016' and semester='Fall'
               having count(cNo) >=3) as T);

#5.
select stuName
from student
where stuID in (select distinct stuID from Enrolls
               where academicYear='2016' and semester='Fall'
               and cNo in ('ITT 226','Math 461');

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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