This assignment is designed to let you have a handson experi

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 pro-vide a good introduction to the basic features of SQL DML. After following the lecture notes you should able to complete this assignment. You may also wish to refer to chapters 6, and 7 of the textbook.

Solution

I can\'t able to download your DB file as it is not available. I have created my own DB with the structure given in the table designs. Please change the values in my SQL queries to work on your tables. The following are the queries.

1) select stuName,gpa from Students;
MariaDB [test]> select stuName,gpa from Students;
+---------+------+   
| stuName | gpa |   
+---------+------+   
| Alex | 9.80 |   
| Alex | 8.80 |   
| Stanley | 6.80 |   
| Rong Fu | 8.80 |   
| Emma | 9.10 |   
+---------+------+

2) select stuname,gpa from Students where gpa<\"2.0\";

MariaDB [test]> select stuname,gpa from Students where gpa<\"2.0\";
+---------+------+   
| stuname | gpa |   
+---------+------+   
| Maria | 1.10 |   
| Perry | 1.80 |   
+---------+------+   
2 rows in set (0.00 sec)

3) select stuName,gpa from Students where stuId in (select stuId from Majors where deptName = \"Physics\");
+---------+------+   
| stuName | gpa |   
+---------+------+   
| Alex | 9.80 |   
+---------+------+   
1 row in set (0.00 sec)

4) select cNo from Sections where profID = (select profID from ProfessorWorks where profName=\"Brando\") and academicYear=\"2003\";

5) select cName from CourseWorks where cNo in (select cNo from Sections where profID = (select profID from ProfessorWorks where profName=\"Brando\") and academicYear=\"2003\"; );