Specify the following queries in SQL on the following database schema (keys are

Question

Specify the following queries in SQL on the following database schema (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)

CoursesOffer(cNo: String, cTitle: String, deptName: String)

Majors(deptName: String, stuID: String, degreeProgram: String, attendYear: Year, attendSemester: String)

Sections(cNo: String, semester: String, sectNo: String, profID: String)

Enrolls(stuID: String, cNo: String, sectNo: String, semester: String, grade: String)

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.

-- Q1. Print the name(s) of student(s) who major in B.S. in Information Technology in the Information Technology department, attending the program before Spring 2013. Note that M.S. in Information Technology, Computer Technology, Spring 2013 are a degree program, a department name, and an attending term, respectively.

-- Q2. Print the age difference between the oldest and youngest student(s). Hints: some MySQL’s built-in functions for calculations on dates could be used to compute a student’s age from his/her date of birth.

-- Q3. Print the name(s) and sid(s) of the student(s) enrolled in Database Management Systems and Knowledge Discovery in Databases. Note that both Database Management Systems and Knowledge Discovery in Databases are course titles.

-- Q4. Print the name of student in each department who has the highest GPA.

-- Q5. Add a column called status to the Major table. Sample values for this column are active, inactive, graduated, and probation. And then, write a command to undo this change.

Explanation / Answer

1.

SELECT S.stuName FROM Students S, Majors M

WHERE S. stuID=M. stuID

AND degreeProgram=”B.S”

AND M. deptName=” Information Technology”

AND Year<2013;

2.

SELECT DATEDIFF(MAX(birthdate),MIN(birthdate))/12

FROM STUDENTS;

3.

SELECT stuID, stuName FROM Students S, CoursesOffer C, Enrolls E

WHERE S. stuID=E. stuID

AND E. cNo=C. cNo

AND C. TITLE=” Database Management Systems”

AND C. TITLE=” Knowledge Discovery in Databases”

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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