Assignment: Query Your Classmate's Databas
In this sixth module, you will practice getting data back out of your database and manipulating data that are resident there. There are many ways for querying data and many ways to aggregate, group, and join your data. In this module's readings, you learned all about data manipulation language (DML), which is used to pull data out of a database and to manipulate data within the database. For the following discussion, you will get some practice with querying by doing some roleplay of real-world scenarios with your classmates.
Step I
Select an ER diagram that you have worked on during one of the discussions in a prior module. This ER diagram should be in at least third normal form and contain at least three tables and at least two relationships. Post an initial message, in which you do the following:
1. Write descriptions for three data querying requirements that could be answered from the data stored in a database represented by your ER diagram. Your classmates will provide answers based on the requirements. Answers to your three requests should contain at least one JOIN; an aggregation function ; a GROUP By clause; and a WHERE or HAVING clause. Some examples:
i. All sales transactions that occurred in the month of March
ii. All students enrolled in IT371 for the Fall 2018 semester
iii. The total amount spent per month on hair products
2. Write descriptions for two modifications for data already in the database. Some examples:
i. Change Bobby Ray's last name to "Jones"
ii. Change student number 64's birth date to 1/1/2001
3. Write descriptions for two deletions for data already in the database. Some examples:
i. Delete student number 23
ii. Delete all students enrollments for IT371 for the Fall 2018 semester
4. Attach your ER diagram to your post or
Step II
Select at least three classmates whose diagrams and queries sound . Write DML statements for each query and post them as replies to your classmates. Do not use a query generator or graphical tool to write these; write them on your own and test them in your chosen DBMS before posting. Try to pick classmates whose requirements have not been answered.