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]
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.
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 14 November 2024].
My Assignment Help. 'Store And Department Information' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/cse2dbf-fundamentals/sql-statements.html> accessed 14 November 2024.
My Assignment Help. Store And Department Information [Internet]. My Assignment Help. 2020 [cited 14 November 2024]. Available from: https://myassignmenthelp.com/free-samples/cse2dbf-fundamentals/sql-statements.html.
