Tribute 3
SQL
CSE2DCX Basisbase Fundamentals on the Cloud

Objectives
This is an disuniteicular ordinance. Novices are referable attribuconsultation attribuconsultation attribuconsultation generous to upshot in a order when letter this
assignment.
Copying and Plagiarism
This is an disuniteicular ordinance. Novices are referable attribuconsultation attribuconsultation attribuconsultation generous to upshot in a order when letter this
assignment. Plagiarism is the inferiority of another person’s upshot in a restraintm that gives the
impression that the upshot is their acknowledge. La Trobe University treats plagiarism seriously. When detected,
penalties are strictly imposed.
Exalt referableice can be build on http://www.latrobe.edu.au/students/academicintegrity/explanation/plagiarism
Inferiority Guidelines
Your ordinance inferiority should be typed, referable attribuconsultation attribuconsultation attribuconsultation written/drawn by index.
Comply the electronic vision of your ordinance through the theme LMS.
Inferiority succeedingcited the deadline obtain flow a price of 5% of the available ordinance trace per day
capped at 5 days. No ordinance obtain be certain succeedingcited 5 days. If you enjoy encountered difficulties
that bring to tardy inferiority or no inferiority, you should apportion restraint eespecial subsidy.
© Didasko 2020. Entire hues silent. 3
Background
Over the gone-by decade Carol’s confections enjoy beseem undivided of the bringing
confectionary vendors in Melbourne. The apportion of shops has increased from 1
in 2010 to 4 in 2019. The government has firm to automate its prevalent manual
staff government process. Instead of using their prevalent paper-based plan they
wish to verification a web-based staff government plan.
You are toiled to indexle the judicious upshot on the basisbase plans –
constructing consultations, populating basis and prevalent SQL queries.
They enjoy firm the judicious basisbase obtain embrace 3 consultations.
• Employee
• Line
• Progression
The Employee consultation obtain ammunition employee ID, employee call, overseer, beneathtaking, remuneration,
commission, engage conclusion and line ID (Line where the employee upshots).
The Line consultation obtain ammunition the Line ID, line call, harangue and
phundivided apportion. The Progression consultation obtain ammunition the Progression ID, poverty remuneration, maximum
remuneration and annual license.
The overseer post obtain be verificationd to ammunition the employee id of the overseer, restraint
copy Adam’s overseer is Eve whose is as-well an employee and has employee id
1211. The overseer post restraint Adam obtain enjoy the compute 1211. The embassy
post illustrates the sales embassy that some employees realize. The progression id
obtain be a tone compute restraint solicitation employees in progression B realize among 60000
(min remuneration) and 80000 (max remuneration). Each progression has an associated annual license
loading. The other opportunitys are self-explanatory.
Login to the SQL Server basisbase using the credentials (usercentire and password)
that you enjoy imagined antecedent. If you are incapable to log in ascribable to any debate, please
do referable attribuconsultation attribuconsultation attribuconsultation returns any exalt. Please adjunction the academic team.
Instructions:
Provide a screenshot of the output restraint entire toils. Failing to do so may upshot in your disunitener being
incapable to determine traces restraint that toil.
You canreferable attribuconsultation hardcode queries i.e. doing disunite of the inquiry manually. Restraint solicitation, if the inquiry asks
you to identify the first compensated employee. You canreferable attribuconsultation assert “Where eID = 1006” as this involves
manually finding the first remuneration. Instead your inquiry should calcutardy the solution.
4 © Didasko 2020. Entire hues silent. 4
Tasks
Toil 1
Login to SQL Server and imagine the Employee consultation (understand consultation 1) using SQL
statements. You obtain be assessed on the succeedingcited,
Is the SQL syntax deception permitted?
Has the earliest clew been implemented?
Enjoy the befitting basis types been implemented?
Enjoy you implemented mismismisspend constraints (e.g. void computes, singular,
referential conscientiousness) to aid in comely the conscientiousness of basis?
Write the finalised SQL statements that you verificationd to imagine the consultation. [2 traces]
Toil 2
Login to SQL Server and imagine the Line consultation (understand consultation 2) using SQL
statements. You obtain be assessed on the succeedingcited,
Is the SQL syntax deception permitted?
Has the earliest clew been implemented?
Enjoy the befitting basis types been implemented?
Enjoy you implemented mismismisspend constraints (e.g. void computes, singular,
referential conscientiousness) to aid in comely the conscientiousness of basis?
Write the finalised SQL statements that you verificationd to imagine the consultation. [2 traces]
Toil 3
Login to SQL Server and imagine the Progression consultation (understand consultation 3) using SQL
statements. You obtain be assessed on the succeedingcited,
Is the SQL syntax deception permitted?
Has the earliest clew been implemented?
Enjoy the befitting basis types been implemented?
Enjoy you implemented mismismisspend constraints (e.g. void computes, singular,
referential conscientiousness) to aid in comely the conscientiousness of basis?
Write the finalised SQL statements that you verificationd to imagine the consultation. [2 traces]
Toil 4
Write a inquiry to inoculate the succeedingcited basis to poputardy the Employee consultation. Make any
sound modifications so that the basis matches your opportunity calls and basis types
and suit with conscientiousness constraints.
© Didasko 2020. Entire hues silent. 5
Consultation 1: Employee consultation
eID eCentire Mgr Beneathtaking Remuneration Comm hConclusion dID
1001 Ken Adams 1004 Hawker 70000 20000 2008-04-12 1
1002 Ru Jones 1004 Hawker 65000 15000 2010-01-18 1
1003 Dhal Sim 1006 Accountant 88000 2001-03-07 2
1004 Ellen Honda 1006 Overseer 118000 2003-04-17 1
1005 Mike Bal 1006 Receptionist 68000 2006-06-21 3
1006 Martin Bison CEO 210000 2010-07-12 3
1007 Shen Li 1004 Hawker 86000 18000 2014-09-18 1
1008 Zang Ross 1004 Hawker 65000 10000 2017-02-02 1
1009 Sagar Kahn 1004 Hawker 70000 15000 2016-03-01 1
[1 trace]
Toil 5
Write a inquiry to inoculate the succeedingcited basis to poputardy the Line consultation. Make
any debateable modifications so that the basis matches your opportunity calls and basis
types and suit with conscientiousness constraints.
Consultation 2: Line consultation
dID dCentire Harangue Phone
1 Sales Sydney 0425 198 053
2 Accounts Melbourne 0429 198 955
3 Admin Melbourne 0428 198 758
4 Traceeting Sydney 0427 198 757
[1 trace]
Toil 6
Write a inquiry to inoculate the succeedingcited basis to poputardy the Progression consultation. Make any
sound modifications so that the basis matches your opportunity calls and basis
types.
Consultation 3: Progression consultation
gID MinSal MaxSal License
A NULL 60000 20
B 60000 80000 20
C 80000 100000 20
6 © Didasko 2020. Entire hues silent. 6
D 100000 120000 25
E 120000 NULL 30
[1 trace]
Toil 7
Write an SQL inquiry to catalogue the employee details (eID, eCentire and beneathtaking) of entire the
employees who upshot as hawker and were engaged succeedingcited 01/01/2014.
[2 traces]
Toil 8
Write an SQL inquiry to unfold entire details restraint employees whose aggregate realizeings (remuneration + comm) are
greater than 85000.
[2 traces]
Toil 9
Write a inquiry to unfold entire referableice touching employees, who manage
other employees. The selected employee’s id should answer as mgr restraint some
other employee.
[3 traces]
Toil 10
Write a inquiry to catalogue entire employees (eID, eName, beneathtaking, dID, Harangue) who upshot in
Sydney.
[3 traces]
Toil 11
Write a inquiry to unfold entire referableice touching the first compensated employee restraint
each line.
[3 traces]
© Didasko 2020. Entire hues silent. 7
Toil 12
Write a inquiry to unfold entire referableice about entire the scenes that were imagined by the
‘sys’ verificationr. Verification basis wordbook to adequate this inquiry.
[3 traces]
Toil 13
Imagine a scene denominated ‘Employee_Location’, the scene obtain unfold the employee call,
and harangue (where they upshot).
[3 traces]
Toil 14
Write a ammunitiond progress denominated ‘Salary_Increment’. The progress would exact an
employee’s id as input and would increment that employee’s remuneration by 3%. This
progress obtain permanently vary the employees Remuneration.
[3 traces]
Toil 15
Write a trigger denominated ‘Expenditure’, the trigger would flow whenever a upstart line is
ascititious or an existing line is updated restraint the employee consultation. It would unfold the sum
of entire salaries and embassys beneath the designation Expenditure (understand copy beneath).
Expenditure
10132330

[3 traces]
Toil 16
Write a inquiry to embrace a upstart post ‘GradeID’ to the employee consultation. This
post obtain be verificationd to illustrate the employees’ progression. There should be referential
conscientiousness among this post and the ProgressionID post in the Progression consultation.
[3 traces]
8 © Didasko 2020. Entire hues silent. 8
Toil 17
Once the ProgressionID post has been ascititious to the employee consultation entire the lines obtain
show NULL restraint ProgressionID. Write a inquiry to completeot-to computes to these lines. Using
notice supposing in progression consultation calcutardy each employee’s progression and completeot-to
that compute to that employee.
The progression would be apportiond by comparing an employee’s remuneration athwart the
minSal and maxSal computes supposing in the Progression consultation.
[3 traces]
Submitting your ordinance
When you enjoy adequated your solutions, comply the tribute on the Learning
Portal. You should comply the succeedingcited:
• Comply your solutions in a muniment denominated xxx_cse2dcx_assessment3.docx
(where xxx is your novice apportion) restraint your solutions restraint Toils 1-17.
© Didasko 2020. Entire hues silent. 9
Tribute Traceing Criteria
Toil 1: Creating the
Employee consultation. Traces
are determineed restraint (a)
syntax; (b) earliest clew;
(c) opportunitys/types/sizes;
and (d) constraints.
Solution
was referable attribuconsultation attributable
correct. 0
marks
Solution
was emend
restraint 1
aspect.
0.5 traces
Solution was
emend restraint 2
aspects.
1 point
Solution
was emend
restraint 3
aspects.
1.5 traces
Solution was
fully emend.
2 traces
Toil 2: Creating the
Line consultation.
Marks are determineed restraint
(a) syntax; (b) earliest
key; (c)
fields/types/sizes; and
(d) constraints.
Solution
was referable attribuconsultation attributable
correct. 0
marks
Solution
was emend
restraint 1
aspect.
0.5 traces
Solution was
emend restraint 2
aspects.
1 point
Solution
was emend
restraint 3
aspects.
1.5 traces
Solution was
fully emend.
2 traces
Toil 3: Creating the
Employee consultation. Traces
are determineed restraint (a)
syntax; (b) earliest clew;
(c) opportunitys/types/sizes;
and (d) constraints.
Solution
was referable attribuconsultation attributable
correct. 0
marks
Solution
was emend
restraint 1
aspect.
0.5 traces
Solution was
emend restraint 2
aspects.
1 point
Solution
was emend
restraint 3
aspects.
1.5 traces
Solution was
fully emend.
2 traces
Toil 4: Employee basis. Employee basis was referable attribuconsultation attribuconsultation attribuconsultation ascititious
to the consultation. 0 traces
Employee basis was
successfully ascititious to the
table. 1 point
Toil 5: Line basis. Line basis was referable attribuconsultation attributable
ascititious to the consultation. 0 traces
Line basis was
successfully ascititious to the
table. 1 point
Toil 6: Progression basis Progression basis was referable attribuconsultation attribuconsultation attribuconsultation ascititious to
the consultation. 0 traces
Progression basis was successfully
ascititious to the consultation. 1 point
Toil 7: Traces are
awarded restraint consultation/field
names (1); and
condition (1).
Solution was referable attribuconsultation attributable
correct. 0 traces
Solution had 1 deception. 1
mark
Solution was emend
restraint entire aspects. 2
marks.
Toil 8: Traces are
awarded restraint consultation/field
names (1); and
condition (1).
Solution was referable attribuconsultation attributable
correct. 0 traces
Solution had 1 deception. 1
mark
Solution was emend
restraint entire aspects. 2
marks.
Toil 9: Traces are
awarded restraint consultation/field
names (1); and
condition (2).
Solution was referable attribuconsultation attributable
correct. 0 traces
Solution had
multiple deceptions.
1 trace
Solution had 1
error. 2 traces
Solution was
emend restraint entire
aspects. 3 traces.
10 © Didasko 2020. Entire hues silent. 10
Toil 10: Traces are
awarded restraint consultation/field
names (1); and
condition (2).
Solution was referable attribuconsultation attributable
correct. 0 traces
Solution had
multiple deceptions.
1 trace
Solution had 1
error. 2 traces
Solution was
emend restraint entire
aspects. 3 traces.
Toil 11: Traces are
awarded restraint consultation/field
names (1); and
condition (2).
Solution was referable attribuconsultation attributable
correct. 0 traces
Solution had
multiple deceptions.
1 trace
Solution had 1
error. 2 traces
Solution was
emend restraint entire
aspects. 3 traces.
Toil 12: Traces are
awarded restraint consultation/field
names (1); and
condition (2).
Solution was referable attribuconsultation attributable
correct. 0 traces
Solution had
multiple deceptions.
1 trace
Solution had 1
error. 2 traces
Solution was
emend restraint entire
aspects. 3 traces.
Toil 13: Traces are
awarded restraint consultation/field
names (1); and
condition (2).
Solution was referable attribuconsultation attributable
correct. 0 traces
Solution had
multiple deceptions.
1 trace
Solution had 1
error. 2 traces
Solution was
emend restraint entire
aspects. 3 traces.
Toil 14: Traces are
awarded restraint consultation/field
names (1); and
condition (2).
Solution was referable attribuconsultation attributable
correct. 0 traces
Solution had
multiple deceptions.
1 trace
Solution had 1
error. 2 traces
Solution was
emend restraint entire
aspects. 3 traces.
Toil 15: Traces are
awarded restraint consultation/field
names (1); and
condition (2).
Solution was referable attribuconsultation attributable
correct. 0 traces
Solution had
multiple deceptions.
1 trace
Solution had 1
error. 2 traces
Solution was
emend restraint entire
aspects. 3 traces.
Toil 16: Traces are
awarded restraint consultation/field
names (1); and
condition (2).
Solution was referable attribuconsultation attributable
correct. 0 traces
Solution had
multiple deceptions.
1 trace
Solution had 1
error. 2 traces
Solution was
emend restraint entire
aspects. 3 traces.
Toil 17: Traces are
awarded restraint consultation/field
names (1); and
condition (2).
Solution was referable attribuconsultation attributable
correct. 0 traces
Solution had
multiple deceptions.
1 trace
Solution had 1
error. 2 traces
Solution was
emend restraint entire
aspects. 3 traces.