Assignment: Sorting, Grouping, and Nesting Data
In this assignment, you will use SQL DML statements that aggregate, sort, and nest data about employees and departments of a company.
You will use the Employee and Department tables you created previously. Create, run, and test SQL queries to display each set of information. Save a copy of your queries in a Microsoft Word document.
Include a screen shot to verify the successful completion of each SQL 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.
E.g. SQL>/* Student: John Doe Instructor: Sally Smith Date: 4/4/2021 */
Note: 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. Include one screen shot per item. The screen shots must be well formatted and the output easily readable. Points may be subtracted from the total score for poor presentation of the assignment material.
See examples below:
Click on this link to load the Unit 4 Application assignment.
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
| Assignment: Sorting, Grouping, and Nesting Data |
Coronel, C. & Morris, S. (2019). Database systems: Design, implementation, and management (13th ed.). Stamford, CT: Cengage.
Media:
Special Requirement:
|