Week3 Practice

Download as pdf or txt
Download as pdf or txt
You are on page 1of 13

Problem Definition

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:

Napkin Design - Improved data model for Projects


Rather than just creating a single Project Tasks table based on the spreadsheet, you can
define a collection of data structures to better model the relationships and collect
additional information that would be valuable.

Below is a napkin design for collecting project information:


You should notice that the new model allows the assignment of Team Members to
Projects, as well as optionally to Tasks, and To Dos. The concept of a Project having
Milestones has been introduced, however, it is optional that Tasks are associated with a
Milestone. Further, To Dos and Links have been added to Tasks.

Step 1 - Open Quick SQL


1. Log into your workspace

2. Click SQL Workshop

3. Click SQL Scripts

4. Click Quick SQL

Step 2 - Enter Shorthand for Tables


Quick SQL provides an easy way to generate the SQL required to create a relational data model
from an indented text document. This tool is designed to reduce the time and effort required to
create SQL tables, triggers, and index structures.

{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.}

1. Enter your first table and some columns.

In Quick SQL (left pane), enter the Table Name:


Team Members

Indent 2 or more spaces and enter several column names:


username
full name
email

{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.}

2. Enter your second table and some columns.

In the Quick SQL (left pane), enter the Table Name in the first column:
Projects

Indent 2 or more spaces and enter the Column Names:


name
project lead
budget
status
Step 3 – Improve the Shorthand
So far you have defined some basic tables and used the defaults, however, there are numerous
directives and data types you can add to the shorthand to improve the SQL generated.

1. Click Help
Click Table Directives

2. Click Column Directives

3. Click Data Types


4. Close Help

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:

o Table: projects - add /insert 20 {This will insert 20 sample records}


o Column: name - add /nn
o Column: project lead - add /references team_members
{This will add a foreign key relationship to the Team Members table}
o Column: budget - add num {This will make the column numeric}
o Column: status - add vc30 /nn /check
ASSIGNED,IN_PROGRESS,COMPLETED {This will define the length of the
mandatory column and add a check constraint using the three values given}
{Note: You will need to scroll down within the SQL (right pane) to review the team
member trigger for the implementation of the /upper on username. If you scroll down
further, you will see all of the insert statements.}

Step 4 - Enter a Child Table


By indenting a table name, you can define the new table as a child table of the table above, and
the SQL will generate a foreign key relationship between the two tables.

1. Enter a child table related to Projects.

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.}

2. Enter another child table related to 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

Step 5 – Update the Settings


You can define numerous settings to further improve the generated SQL. You can
click Settings within the toolbar under SQL (right pane), if you only plan to run the script once.
As outlined in the Help, you can also define settings in Quick SQL (left pane). Settings defined
in Quick SQL do not need to be reentered each time you return to Quick SQL to rerun a
previously run script.

1. In the SQL (right pane) toolbar, click Settings

In the Settings dialog, enter the following:

o Object Prefix - enter hol


o On Delete - select Restrict
o Primary Keys - select 12c Identity Data Types
o Date Data Type - select TIMESTAMP WITH LOCAL TIME ZONE
o Audit Columns - Check
o Row Version Number - Check
Click Save Changes
The generated SQL has changed markedly. The table names are updated, there are
additional columns, and updated triggers.

Step 6 – Copy in the Complete Shorthand


1. In Quick SQL (left pane), replace all of the existing shorthand with the following:

2. Copy# settings = { prefix: "hol", ondelete: "restrict", pk:


"identity" }

3. # date: "timestamp with local time zone"

4. # auditcols: true

5. # rowVersion: true
6.

7. team_members /insert 10

8. username /nn /upper

9. full name

10. email /nn

11. phone_number

12. profile

13. photo file

14. projects /insert 20

15. name /nn

16. project_lead /nn /references team_members

17. budget num

18. status vc30 /nn /check ASSIGNED, IN-PROGRESS, COMPLETED

19. completed_date

20. description

21. milestones /insert 30

22. name /nn

23. due_date /nn

24. description

25. tasks /insert 100

26. name /nn

27. assignee /references team_members

28. milestone_id /references milestones


29. start_date /nn

30. end_date

31. cost num

32. description

33. is_complete_yn /check Y, N

34. to dos /insert 20

35. todo vc(255) /nn

36. assingee /references team_members

37. due_date

38. details

39. links /insert 10

40. url vc(255) /nn /lower

41. name

42. description

43.

44. view project_tasks projects tasks

Click Generate SQL


{Note: The full shorthand defines all of the required settings. It also includes additional
columns on many of the tables, and additional directives and data types. There is also a
view defined.}

Step 7 – Implement the Script


At this stage you have created a list of SQL statements. However, you first need to save the
statements as a script file and then run the script. This will create the database objects and insert
the data.

1. In the SQL (right pane) toolbar, click Save SQL Script.

2. In the Save Script dialog, for Script Name, enter hol.


Click Save Script.

3. In the SQL (right pane) toolbar, click Review and Run.


The script will be displayed in the Script Editor, within SQL Scripts.

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.

You might also like