Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave

Store Details

Using the tables provided above, provide SQL statements for the following queries.

a. Show the store information along with its departments for all stores that opens the latest during weekend. [5 marks]

b. Show the name and address of all team members for store S001. [5 marks]

c. List the franchise application details for each department of H2H along with the department id and title. Include the departments that have not received any application yet. [5 marks]

d. Show the team information and number of members for all successful franchise application. [5 marks]

e. Show the details of the franchise application that took the longest time to reach a decision. [5 marks]

f. List the team member details (including shareholder and employee details such as id, name, phone and email) for all the team members who are part of a franchise team of a successful application. For each of the members, also include the date on which their franchise application was granted. [5 marks]

g. List the team member details (shareholder/employee details) who has the maximum credit to his name, based on his percentage of share in the team and the positive account balance of the franchising department. (Hint: account balance can be found in the Account table) [10 marks]

h. Show the top 4 employees (name, salary earned) that have earned the highest salary from January 2017 – December 2017. Assume that all casual employees work 12 hours per week and there are 45 working weeks a year. [10 marks]

Task 2 [30 marks] Provide the implementation of the following stored procedures and function. For submission, please include both the PL/SQL code and an execute procedure/SQL statement to demonstrate the functionality.

a. Write a procedure that takes two dates as input (a duration) and updates the commission amount of every weekly report for which the start date falls under this duration. The commission amount is the result of multiplication of sale amount and commission rate (%). The procedure also generates an output in the following format for every affected row: The commission amount for report has been updated to dollars, which is % of the total sale amount of dollars.

b. Write a procedure that takes an employee id as argument and lists the details of an employee’s department and reporting boss. Note that an employee’s reporting boss is the person who has the highest percentage of share in the franchise team. The reporting boss can be either a full-time employee or a shareholder. If the reporting boss is a shareholder, display number of shares along with contact details and if the boss is an employee, mention DOB, gender and joining date along with the contact information.

c. Write a function that takes an employee id and a month-year (date of ‘MON-YYYY’ format) and returns the total bonus amount the employee have earned by making exceptional sales during that month.

If the employee was not awarded a bonus for that month, the function should return zero. For the execution of the function, write a simple SQL query that lists down all the employees and their total bonus amount earned for the month of April 2018. Hint: Although we did not maintain referential integrity constraint between EMPLOYEE and WEEKLY_SALES_REPORT, you can safely assume that the employee listed as the best performing employee in a sales report is a valid employee, who is present in the employee table. [10 marks each]

Task 3 [20 marks] Provide the implementation of the following triggers. For submission, please include both the PL/SQL code and a DML statement (insert, update or delete) to demonstrate the trigger functionality. a. A trigger that prevents a full-time employee from applying for a franchise if he/she has less than 10 years of experience as an employee of H2H. b. A trigger that automatically backs-up a weekly sales report when it is deleted. To preserve the data, the trigger stores the deleted report into a backup table: BackupReport (ReportID, StartDate, EndDate, SaleAmount) [10 marks each]

Store Details

STOREID    STOREADDRESS                                       WEEKDAYSHOURS        WEEKENDHOURS         DEPARTMENTTITLE              

---------- -------------------------------------------------- -------------------- -------------------- ------------------------------

S005       Waterdale Rd, Heidelberg, VIC 3074                 09:00 AM - 09:00 PM  10:00 AM - 04:00 PM  Heidelberg Electronics       

S005       Waterdale Rd, Heidelberg, VIC 3074                 09:00 AM - 09:00 PM  10:00 AM - 04:00 PM  Heidelberg Computers         

S005       Waterdale Rd, Heidelberg, VIC 3074                 09:00 AM - 09:00 PM  10:00 AM - 04:00 PM  Heidelberg Beddings          

S005       Waterdale Rd, Heidelberg, VIC 3074                 09:00 AM - 09:00 PM  10:00 AM - 04:00 PM  Heidelberg Furniture         

S005       Waterdale Rd, Heidelberg, VIC 3074                 09:00 AM - 09:00 PM  10:00 AM - 04:00 PM  Heidelberg Kitchenware       

S005       Waterdale Rd, Heidelberg, VIC 3074                 09:00 AM - 09:00 PM  10:00 AM - 04:00 PM  Heidelberg Cookwares         

S005       Waterdale Rd, Heidelberg, VIC 3074                 09:00 AM - 09:00 PM  10:00 AM - 04:00 PM  Heidelberg Laundry           

7 rows selected. 

ENAME                          EADDRESS                                          

------------------------------ --------------------------------------------------

Samin Nixon                    34 Waratah St, Thomastown, VIC 3074              

Alex Ferrer                    99 Whitelaw St, Reservoir, VIC 3072              

John Paul                      33 Rathcown Rd, Reservoir, VIC 3072              

Paul James                     32 Rathcown Rd, Reservoir, VIC 3072              

Jacob John                     23 Rathcown Rd, Reservoir, VIC 3072              

Bob Murray                     42 Silas Rd, Reservoir, VIC 3072                 

Andy James                     44 Weston Rd, Reservoir, VIC 3072                

Steve Nixon                    33 Camden Rd, Reservoir, VIC 3072                 

William Oliver                 12 Dylan Rd, Reservoir, VIC 3072                 

Chris Oliver                   12 William Rd, Reservoir, VIC 3072               

Lucas Alexander                12 Jayden Rd, Reservoir, VIC 3072                

ENAME                          EADDRESS                                         

------------------------------ --------------------------------------------------

Christian Levi                 12 Levi Rd, Reservoir, VIC 3072                  

Isaiah Lincoln                 12 Colton Rd, Reservoir, VIC 3072                

Brayden Easton                 12 Andrew Rd, Reservoir, VIC 3072                

Vincent Miles                  12 Alphons Rd, Reservoir, VIC 3072               

Andrew Paul                    32 Rathcown Rd, Reservoir, VIC 3072              

Agnes Peter                    52 Alexander Rd, Reservoir, VIC 3072             

John Jude                      78 Michael Rd, Reservoir, VIC 3072               

Marina Mathew                  10 Holland Rd, Reservoir, VIC 3072               

19 rows selected. 

APPNUMBER  APPDATE   DECISIOND STATUS       DEPARTMENT DEPARTMENTTITLE              

---------- --------- --------- ------------ ---------- ------------------------------

AP026      21-FEB-18 06-APR-18 Under review D001       Thomastown Electronics       

AP025      01-DEC-17 12-APR-18 Under review D001       Thomastown Electronics       

AP001      01-NOV-11 06-APR-12 Granted      D001       Thomastown Electronics       

AP015      15-MAY-17 21-MAR-18 Under review D031       Thomastown Laundry           

AP014      01-MAR-17 20-APR-18 Under review D031       Thomastown Laundry           

AP013      17-JUL-17 15-FEB-18 Under review D031       Thomastown Laundry           

AP012      01-SEP-15 14-MAR-16 Under review D031       Thomastown Laundry           

AP004      01-JAN-18 03-FEB-18 Under review D002       Reservoir Electronics         

AP003      01-JAN-17 11-JAN-18 Under review D002       Reservoir Electronics        

AP002      01-JAN-09 12-APR-10 Granted      D002       Reservoir Electronics        

Employee Addresses

AP019      23-SEP-17 25-MAR-18 Under review D032       Reservoir Laundry             

APPNUMBER  APPDATE   DECISIOND STATUS       DEPARTMENT DEPARTMENTTITLE              

---------- --------- --------- ------------ ---------- ------------------------------

AP018      18-AUG-17 26-MAR-18 Under review D032       Reservoir Laundry             

AP017      19-APR-17 22-MAR-18 Under review D032       Reservoir Laundry            

AP016      01-JUN-17 22-MAR-18 Under review D032       Reservoir Laundry            

AP007      01-APR-17 02-FEB-17 Under review D003       Preston  Electronics         

AP006      11-APR-17 19-APR-17 Under review D003       Preston  Electronics          

AP005      01-FEB-18 08-MAR-18 Under review D003       Preston  Electronics         

AP030      21-SEP-16 16-MAY-17 Granted      D023       Preston Kitchenware          

AP009      05-AUG-17 03-MAR-17 Under review D004       Bundoora Electronics          

AP008      14-APR-17 14-FEB-17 Under review D004       Bundoora Electronics         

AP010      03-SEP-15 10-FEB-16 Under review D029       Bundoora Cookwares           

AP011      01-JUL-15 12-APR-16 Under review D030       Heidelberg Cookwares          

APPNUMBER  APPDATE   DECISIOND STATUS       DEPARTMENT DEPARTMENTTITLE              

---------- --------- --------- ------------ ---------- ------------------------------

AP021      11-DEC-15 12-APR-16 Rejected     D040       Northcote Kitchenware         

AP020      21-NOV-15 10-APR-16 Rejected     D040       Northcote Kitchenware        

AP024      01-JUL-17 05-MAR-18 Rejected     D041       Northcote Cookwares          

AP023      01-JUN-15 04-MAR-17 Rejected     D041       Northcote Cookwares           

AP022      01-OCT-15 14-NOV-17 Rejected     D041       Northcote Cookwares          

AP029      24-JUL-15 24-MAR-16 Rejected     D042       Northcote Laundry            

AP028      22-MAY-15 12-FEB-16 Rejected     D042       Northcote Laundry             

AP027      13-APR-14 22-JAN-15 Rejected     D042       Northcote Laundry

30 rows selected. 

TEAMID     TEAMNAME             TEAMLEADER COUNT(MEMBERSHIP.TEAMID)

---------- -------------------- ---------- ------------------------

T008       Melbourne Masters    M013                              4

T001       Master Franchisee    M001                              4

T005       DePaul               M010                              4 

APPNUMBER  APPDATE   DECISIOND STATUS       DEPARTMENT TEAMID     DIFFERENCE_IN_MINUTES

---------- --------- --------- ------------ ---------- ---------- ---------------------

AP022      01-OCT-15 14-NOV-17 Rejected     D041       T011                     1116000 

EMPLOYEEID ENAME                          EPHONE               EEMAIL                         DECISIOND SHID       SHADDRESS                                          NOOFSHARES

---------- ------------------------------ -------------------- ------------------------------ --------- ---------- -------------------------------------------------- ----------

E001       Samin Nixon                    0450777435           [email protected]               06-APR-12 SH003      11 Edward st, Reservoir, VIC 3073                          60

E001       Samin Nixon                    0450777435           [email protected]               06-APR-12 SH002      22 Dunne St, Bundoora, VIC 3083                            75

E001       Samin Nixon                    0450777435           [email protected]               06-APR-12 SH001      16 plenty rd, Bundoora, VIC 3083                          100

E002       Alex Ferrer                    0450788435           [email protected]               06-APR-12 SH003      11 Edward st, Reservoir, VIC 3073                          60

E002       Alex Ferrer                    0450788435           [email protected]               06-APR-12 SH002      22 Dunne St, Bundoora, VIC 3083                            75

E002       Alex Ferrer                    0450788435           [email protected]               06-APR-12 SH001      16 plenty rd, Bundoora, VIC 3083                          100

E003       John Paul                      0450788422           [email protected]               06-APR-12 SH003      11 Edward st, Reservoir, VIC 3073                          60

E003       John Paul                      0450788422           [email protected]               06-APR-12 SH002      22 Dunne St, Bundoora, VIC 3083                            75

E003       John Paul                      0450788422           [email protected]               06-APR-12 SH001      16 plenty rd, Bundoora, VIC 3083                          100

E004       Paul James                     0450788433           [email protected]               06-APR-12 SH003      11 Edward st, Reservoir, VIC 3073                          60

E004       Paul James                     0450788433           [email protected]               06-APR-12 SH002      22 Dunne St, Bundoora, VIC 3083                            75

Application Details

EMPLOYEEID ENAME                          EPHONE               EEMAIL                         DECISIOND SHID       SHADDRESS                                          NOOFSHARES

---------- ------------------------------ -------------------- ------------------------------ --------- ---------- -------------------------------------------------- ----------

E004       Paul James                     0450788433           [email protected]               06-APR-12 SH001      16 plenty rd, Bundoora, VIC 3083                          100

E005       Jacob John                     0450788444           [email protected]               06-APR-12 SH003      11 Edward st, Reservoir, VIC 3073                          60

E005       Jacob John                     0450788444           [email protected]               06-APR-12 SH002      22 Dunne St, Bundoora, VIC 3083                            75

E005       Jacob John                     0450788444           [email protected]               06-APR-12 SH001      16 plenty rd, Bundoora, VIC 3083                          100

E006       Marin Albert                   0450788421           [email protected]               12-APR-10 SH014      12 main rd, greensborough, VIC 3090                        90

E006       Marin Albert                   0450788421           [email protected]               12-APR-10 SH013      22 childs rd, Millpark, VIC 3078                           80

E007       Joseph Mathew                  0450788212           [email protected]               12-APR-10 SH014      12 main rd, greensborough, VIC 3090                        90

E007       Joseph Mathew                  0450788212           [email protected]               12-APR-10 SH013      22 childs rd, Millpark, VIC 3078                           80

E059       Damian Juan                    0454788426           [email protected]               16-MAY-17 SH010      33 Grandview rd, Fairfield, VIC 3078                       60

E059       Damian Juan                    0454788426           [email protected]               16-MAY-17 SH009      12 Christmas st, Brunswick, VIC 3057                       50

E060       Braxton Camden                 0455788426           [email protected]               16-MAY-17 SH010      33 Grandview rd, Fairfield, VIC 3078                       60

EMPLOYEEID ENAME                          EPHONE               EEMAIL                         DECISIOND SHID       SHADDRESS                                          NOOFSHARES

---------- ------------------------------ -------------------- ------------------------------ --------- ---------- -------------------------------------------------- ----------

E060       Braxton Camden                 0455788426           [email protected]               16-MAY-17 SH009      12 Christmas st, Brunswick, VIC 3057                       50

23 rows selected. 

EMPLOYEEID ENAME                          EPHONE               EEMAIL                         DECISIOND SHID       SHADDRESS                                          NOOFSHARES PERCENTAGEOFSHARE    BALANCE

---------- ------------------------------ -------------------- ------------------------------ --------- ---------- -------------------------------------------------- ---------- ----------------- ----------

E006       Marin Albert                   0450788421           [email protected]               12-APR-10 SH014      12 main rd, greensborough, VIC 3090                        90                10     550000

E007       Joseph Mathew                  0450788212           [email protected]               12-APR-10 SH014      12 main rd, greensborough, VIC 3090                        90                10     550000

E060       Braxton Camden                 0455788426           [email protected]               16-MAY-17 SH009      12 Christmas st, Brunswick, VIC 3057                       50                10     290000

E059       Damian Juan                    0454788426           [email protected]               16-MAY-17 SH009      12 Christmas st, Brunswick, VIC 3057                       50                10     290000

E059       Damian Juan                    0454788426           [email protected]               16-MAY-17 SH010      33 Grandview rd, Fairfield, VIC 3078                       60                10     290000

E060       Braxton Camden                 0455788426           [email protected]               16-MAY-17 SH010      33 Grandview rd, Fairfield, VIC 3078                       60                10     290000

E003       John Paul                      0450788422           [email protected]               06-APR-12 SH002      22 Dunne St, Bundoora, VIC 3083                            75                20     100000

E002       Alex Ferrer                    0450788435           [email protected]               06-APR-12 SH002      22 Dunne St, Bundoora, VIC 3083                            75                20     100000

E001       Samin Nixon                    0450777435           [email protected]               06-APR-12 SH002      22 Dunne St, Bundoora, VIC 3083                            75                20     100000

E005       Jacob John                     0450788444           [email protected]               06-APR-12 SH002      22 Dunne St, Bundoora, VIC 3083                            75                20     100000

E004       Paul James                     0450788433           [email protected]               06-APR-12 SH002      22 Dunne St, Bundoora, VIC 3083                            75                20     100000

Team Information

EMPLOYEEID ENAME                          EPHONE               EEMAIL                         DECISIOND SHID       SHADDRESS                                          NOOFSHARES PERCENTAGEOFSHARE    BALANCE

---------- ------------------------------ -------------------- ------------------------------ --------- ---------- -------------------------------------------------- ---------- ----------------- ----------

E004       Paul James                     0450788433           [email protected]               06-APR-12 SH003      11 Edward st, Reservoir, VIC 3073                          60                25     100000

E005       Jacob John                     0450788444           [email protected]               06-APR-12 SH003      11 Edward st, Reservoir, VIC 3073                          60                25     100000

E001       Samin Nixon                    0450777435           [email protected]               06-APR-12 SH003      11 Edward st, Reservoir, VIC 3073                          60                25     100000

E003       John Paul                      0450788422           [email protected]               06-APR-12 SH003      11 Edward st, Reservoir, VIC 3073                          60                25     100000

E002       Alex Ferrer                    0450788435           [email protected]               06-APR-12 SH003      11 Edward st, Reservoir, VIC 3073                          60                25     100000

E003       John Paul                      0450788422           [email protected]               06-APR-12 SH001      16 plenty rd, Bundoora, VIC 3083                          100                30     100000

E002       Alex Ferrer                    0450788435           [email protected]               06-APR-12 SH001      16 plenty rd, Bundoora, VIC 3083                          100                30     100000

E004       Paul James                     0450788433           [email protected]               06-APR-12 SH001      16 plenty rd, Bundoora, VIC 3083                          100                30     100000

E001       Samin Nixon                    0450777435           [email protected]               06-APR-12 SH001      16 plenty rd, Bundoora, VIC 3083                          100                30     100000

E005       Jacob John                     0450788444           [email protected]               06-APR-12 SH001      16 plenty rd, Bundoora, VIC 3083                          100                30     100000

E007       Joseph Mathew                  0450788212           [email protected]               12-APR-10 SH013      22 childs rd, Millpark, VIC 3078                           80                50     550000

EMPLOYEEID ENAME                          EPHONE               EEMAIL                         DECISIOND SHID       SHADDRESS                                          NOOFSHARES PERCENTAGEOFSHARE    BALANCE

---------- ------------------------------ -------------------- ------------------------------ --------- ---------- -------------------------------------------------- ---------- ----------------- ----------

E006       Marin Albert                   0450788421           [email protected]               12-APR-10 SH013      22 childs rd, Millpark, VIC 3078                           80                50     550000

23 rows selected.

ENAME                          Salary Earned

------------------------------ -------------

Ella John                              17820

Edward Paul                            17820

Andrew Paul                            17820

Mia Liam                               17550

The commission amount for report R001 has been updated to 7500 dollars, which is 5 of the total sale amount of 150000 dollars.

PL/SQL procedure successfully completed.

EMPLOYEEBONUS('E003','04','2018')                                                                                                                                                                                                                                                                                                                                          -500

500

500

500

500

500

500

500

30 rows selected.

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). Store And Department Information. Retrieved from https://myassignmenthelp.com/free-samples/cse2dbf-fundamentals/sql-statements.html.

"Store And Department Information." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/cse2dbf-fundamentals/sql-statements.html.

My Assignment Help (2020) Store And Department Information [Online]. Available from: https://myassignmenthelp.com/free-samples/cse2dbf-fundamentals/sql-statements.html
[Accessed 10 November 2024].

My Assignment Help. 'Store And Department Information' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/cse2dbf-fundamentals/sql-statements.html> accessed 10 November 2024.

My Assignment Help. Store And Department Information [Internet]. My Assignment Help. 2020 [cited 10 November 2024]. Available from: https://myassignmenthelp.com/free-samples/cse2dbf-fundamentals/sql-statements.html.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
close