Overview
For this assessment, you will practice using built-in SQL functions to INSERT data into a new table and select data from tables for reporting purposes.
Note: Remember to design the new table with the appropriate primary and foreign keys so you can relate these records to volunteers in the PERSON table. Each table should include an ID column as the primary key.
Instructions
Your assessment will be scored on the following criteria. You will use the Weekly Solutions Submission Template to submit your assessment.
1. Create a new table named USERS for your volunteer database. The USERS table will be used to store a USERNAME for each volunteer.
2. Use an INSERT INTO statement with a subquery containing a SQL string function to create user names for the volunteers based on their legal names and insert them into the table.
Hint: Take a look at the CONCAT() and SUBSTRING() functions.
3. Use SQL aggregate functions such as COUNT(), MIN(), and MAX() and write three queries to report on various aspects of the organization’s volunteers.
4. Save your SQL statements for Steps 1–3 to a text file using the MySQL tee command and document the process with screenshots.
5. Review and reflect on all of the work you have completed this quarter.
Briefly summarize in 2–3 paragraphs the key components of the volunteer database.
Discuss how the database life cycle has progressed through the phases of Investigation, System Design, Development, Execution, and Maintenance.