Need help with SQL Statements This assignment is designed to let you have a hand
ID: 3564803 • Letter: N
Question
Need help with SQL Statements
This assignment is designed to let you have a hands-on experience writing SQL statements. It includes modifying relations, modifying data, and retrieving data from a database. You have to use MySQL to complete this assignment. The lecture notes for the relational data model and SQL provide a good introduction to the basic features of SQL DML. After following the lecture notes you should be able to complete this assignment. You may also wish to refer to Chapters 3, 4 and 5 of the textbook.
Specify the following queries in relational algebra on the following database schema (keys are in bold and underline):
These tables have been created and the data have been populated using MySQL server. Download DBHW4.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
Explanation / Answer
-- Print the name(s) and age(s) of student(s) with 3.895 GPA.
SELECT sName, age
FROM students
WHERE gpa > 3.895;
-- Print the names of departments that have one or more majors who are under 18 years old.
SELECT a.dName
FROM (SELECT m.dName, (max(s.year) - min(s.year)) diff
FROM departments d, major m, students s
WHERE d.dName = m.dName
AND m.sid = s.sid
GROUP BY m.dName) a
WHERE a.diff < 18;
-- Print the age difference between the oldest and youngest major(s) per department.
SELECT m.dName, (max(s.year) - min(s.year)) diff
FROM departments d, major m, students s
WHERE d.dName = m.dName
AND m.sid = s.sid
GROUP BY m.dName;
-- Print the name(s) and sid(s) of the student(s) enrolled in at least three classes.
SELECT s.sid, s.sName
FROM students s, enroll e
WHERE s.sid = e.sid
GROUP BY s.sid, s.sName
HAVING COUNT(*) > 3;
-- Find the names of students who have not enrolled in any course yet Fall 2014