Group Project: Populate and Validate Data

In this unit, you will work with your group to insert data into the tables.

To prepare for this assignment, review the Create Table statements from the previous unit.

Define data for all of the tables you were assigned in the previous unit. Include at least three data items for each table (parent table) on the one side of the one-to-many relationship and at least six data items for each table (child table) on the many side of the one-to-many relationship.
Complete the following tasks:

• For the tables you were assigned, use the SQL Command Line utility to insert at least three data items into each table (parent table) on the one side of the one-to-many relationship and at least six data items into each table (child table) on the many side of the one-to-many relationship.
  • Each data item in the parent table must have at least one data item in the child table that it links to. (E.g. The production, accounting, and finance departments must each include at least one employee).
• Execute one join command to display all of the data included in the two tables for each set of tables.
• Include a screen shot to verify the successful completion of each Insert and Join command. Each Screen shot must be preceded by a comment that includes the student’s name, the instructor’s name, and the date the command was executed. You may group the insert commands for each table into one screen shot.
  • E.g. SQL>/* Student: John Doe Instructor: Sally Smith Date: 4/4/2021 */
  • Don’t forget the /* and */ at the start and end of the command to make it into a comment!!
  • The screen shot must include the SQL command and the output from the command.
  • Any errors while creating the comment or executing the SQL statement will require retyping the comment.

  

By Day 7

individually submit a 2- to 4-page word document that contains the following:

• Screen shots to verify the insertion of the required data items and execution of join commands on all tables you were assigned by the group.

Prepare: Intermediate SQL 2

The SQL SELECT command allows you to retrieve data from one or more tables. With the SELECT, FROM, and WHERE clauses, you can specify the sources of the data you want, the columns to display, and conditions for the rows to display. With these clauses and joins, you can identify, for example, all inventory items that are low in stock (stock count is less than 10), and the vendor who supplies those items.

In this Unit, you will learn more ways to express your data retrieval needs. You will learn about three additional clauses in the SQL SELECT command. These let you specify the sorting order for rows, to aggregate rows, and to operate on the aggregated data.

First, the ORDER BY clause lets you specify the sequence of the result rows. Suppose that the auto supply store has a list of inventory items that are in low supply. If you sort the results by vendor, all parts supplied by a particular vendor will appear together. This may simplify the task of placing orders with vendors.

Next, the GROUP BY clause lets you subdivide results into categories, and operate on the aggregated data in each category, rather than on single rows. Perhaps you want to know the number of items that you buy from each vendor, rather than a list of the particular parts. The GROUP BY clause aggregates results by the value in one or more columns and lets you perform the same calculation, such as MIN, MAX, or COUNT, on each group.

HAVING, the third SQL clause, is similar to the WHERE clause, except that its conditions apply to a group. For example, you can list only those vendors who supply fewer than 5 different items (HAVING COUNT < 5).

You will also learn how to use functions, expressions, and calculations with SQL statements. A column in a SELECT command can calculate a result based on values in other columns. For example, SQL can display the extended price as the product of the unit price of an item and the quantity ordered. Similarly, you can include expressions and SQL built-in functions to summarize (or aggregate) the data in a group.

Finally, you will see how a subquery permits you to perform a preliminary calculation, and then use that result as a criterion in a WHERE or HAVING clause. For example, you can list the inventory items whose cost is less than the average cost of all inventory items by calculating the average cost in a subquery. Subqueries easily identify entities that are not in a list—for example, all vendors who do not currently supply any parts. This subquery would return a list of vendors, rather than a single value. The main query would consider only vendors that are not in the list. SQL lets you use subqueries in WHERE, IN, HAVING, and FROM clauses.

Applications often use these techniques when they generate reports or analyze data.

Learning Outcomes

By the end of this unit, you will be able to:

• Practice populating and validating database data
• Evaluate alternative query strategies
• Apply techniques for dynamically selecting and aggregating data

https://www.1keydata.com/sql/sqlgroupby.html

ITEC 2060 Week 4 Group Project – Thoughts

The Week4 Group Project builds on the work we have been doing in the previous Project assignments.

Compared to the Applications, the Week 4 Group Project is relatively straight forward. You need to do the following:

1. There are no specific individual contributions required by Day 4 of the Week 4 project. I still expect each of you to check in to the group discussion area by Day 4 with some substantive information to move the group forward this week. At a minimum, you could specify which tables you will be working with so the group can verify that all tables included in the final design are assigned to someone in the group. By Day 7, you must each submit your individual results.
2. Create SQL statements to insert data into the Project tables you built in Oracle during Week 3. If you did not get the tables built, you will need to get that done before proceeding. If you continue to have problems creating the tables, you could use this opportunity to reach out to your group members for some assistance. I would like to see at least three rows of data per table. Some tables may have more.
3. Some of you had problems related to foreign keys in the week 3 project. If you did not include foreign keys in your tables, you will need to use the Alter Table command to add them. Remember, FKs go on the many side of all one-to-many relationships. If you were one of the few who did not get the project tables created in week 3, you have a lot of work to get done this week. Again, you can use the group discussion forum to ask for assistance from your group.
4. For each table in your design, please include the following in a Word Document. First, a copy of ONE of the successful Insert commands for each table. Second, a screen shot of ALL the data in the table (At least three data items per table). Use the Select * from Station; (Or whatever table name) to output the data to the screen. Please do not include more than this in the screenshots or it can get very confusing when grading.