Week3 Practice
Week3 Practice
Week3 Practice
Within most organizations, the use of spreadsheets is prolific. Spreadsheets are excellent
for doing simple data review and tracking personal data, however, they are very ill suited
to gathering data amongst team members for example.
A very common use case is that a manager needs to track the different projects his team
is working on. The manager has built a spreadsheet with the critical data elements.
However, now that the team is growing, trying to manage the spreadsheet updates is
becoming unmanageable, and it is near impossible to get a single source of truth.
Below is an excerpt from the spreadsheet the manager has been sending out to the
team:
{Note: You will only be entering some of the tables and a few columns for each table. This
exercise is designed to teach you the concepts behind Quick SQL, and not a typing exercise. A
completed script will be provided later in the lab.}
{Note: As you type in each row the SQL (right pane) is updated with the required SQL to
create the table you are defining. If the output does not look like the image ensure that
you have indented the columns username, and so forth correctly.}
In the Quick SQL (left pane), enter the Table Name in the first column:
Projects
1. Click Help
Click Table Directives
5. In the Quick SQL (left pane), update the Team Members table with the following:
o Table: team members - add /insert 10 {This will insert 10 sample records}
o Column: username - add /nn /upper {this will make the column uppercase and
mandatory}
o Column: email - add /nn
Update the Projects table with the following:
In Quick SQL (left pane), enter the Table Name and directive:
Milestones /insert 30 {Use the same indent as the columns in the Projects table}
Indent 2 or more spaces and enter the Column Names and directives:
name /nn
due_date /nn {If you do not include the underscore the column will be
called due instead of due_date.}
description
{Note: The SQL for the Milestones table includes the project_id column and a foreign key
relationship to the parent table, Projects.}
In Quick SQL (left pane), enter the Table Name and directive:
Tasks /insert 100
{The indent should be the same as you used for the Milestones table to ensure this new
table is also a parent of Projects, not of the table above.}
Indent 2 or more spaces and enter the Column Names and directives:
name /nn
assignee /references team_members
milestone id /references milestones
4. # auditcols: true
5. # rowVersion: true
6.
7. team_members /insert 10
9. full name
11. phone_number
12. profile
19. completed_date
20. description
24. description
30. end_date
32. description
37. due_date
38. details
41. name
42. description
43.
Click Run.
4. On the Run Script page, click Run Now.
5. The Script Results page will be displayed listing the statements processed, successful,
and with errors.
{Note: If you do not see 217 statements processed then return to Quick SQL and
click Generate SQL, resave the script and then run it again. If you do not see 217
successful, then review the errors displayed in Feedback within the results.}
Summary
You now know how to utilize Quick SQL to build complex data structures, complete with
sample data.