myassignmenthelp.com
Sign In

New User? Start here.

icon
arrow
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
Phone no. Missing!

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

Add File

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Have a coupon code?
Guaranteed Higher Grade!
home arrow Free Samples arrow Store And Department Information

Store And Department Information

34 Pages / 8,331 Words Published On: 07-11-2020

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]

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 03 October 2023].

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

My Assignment Help. Store And Department Information [Internet]. My Assignment Help. 2020 [cited 03 October 2023]. Available from: https://myassignmenthelp.com/free-samples/cse2dbf-fundamentals/sql-statements.html.
Stuck on Any Question

Our best expert will help you with the answer of your question with best explanation.

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

question
We will use e-mail only for:

arrow Communication regarding your orders

arrow To send you invoices, and other billing info

arrow To provide you with information of offers and other benefits

Phone no. Missing!

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

loader
250 words
Error goes here

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Have a coupon code?
Other Samples

Importance Of Securing Transportation As A Critical Infrastructure

samples

Comparison Of B2C Financial Accounting Services In Australia - Essay.

samples

Essay: Expenses Of International Students In Melbourne, Australia.

samples

Building DMZ Network And Radius Server Configuration Essay.

samples

Expanding Expedia Inc In Asia Pacific Region, An Essay In Progress.

samples

The Essay On IS Success Model's Impact In E-Commerce Industry.

samples

Analyzing Position And Performance – Where To Now For Tesla? Essay.

samples

Impact Of E-Business On Healthcare: Risks, Benefits, And Changes - An Essay.

samples

Impact Of Environmental Regulations And Marketing Cues On Consumer Perceptions Of Quality

samples

AASB 16's Implications On BHP And Rio Tinto's Financial Statements (Essay).

samples

Exploring The Tesla Turbine: An Overview Of Designs And Considerations In An Essay.

samples

Strategic Management Essay For Bayut.com's Real Estate Brand.

samples

Penguins - Event Management And Learning Models

samples
icon
5% Cashback

On APP - grab it while it lasts!

Download app now (or) Scan the QR code

*Offer eligible for first 3 orders ordered through app!

screener
ribbon
callback request mobile
Have any Query?
close
Subtraction Payment required!

Only one step away from your solution of order no.

Order Detail