Refer to the entity-relation scheme this assignment to test the queries. Use the file DreamHome.db included...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Refer to the entity-relation scheme this assignment to test the queries. Use the file DreamHome.db included in Answer the following questions using SQL to produce the view. Only the SQL query will attract marks, not the view. Also, you must include at the end of your query the primary and foreign keys (if any) used. Degree and cardinality of the resulting view, and one sentence explaining the content of the first tuple based on the attributes queried. a) Find the total number of Managers and the sum of their salaries. (5 marks) b) Find the minimum, maximum, and average staff salary. [5 marks] c) For each branch office with more than one member of staff, find the number of staff working in each branch and the sum of their salaries. [5 marks] d) Construct a list of all cities where there is either a branch office or a property. [5 marks] e) Construct a list of all cities where there is both a branch office and a property. [5 marks] Refer to the entity-relation scheme this assignment to test the queries. Use the file DreamHome.db included in Answer the following questions using SQL to produce the view. Only the SQL query will attract marks, not the view. Also, you must include at the end of your query the primary and foreign keys (if any) used. Degree and cardinality of the resulting view, and one sentence explaining the content of the first tuple based on the attributes queried. a) List the details of all viewings on property PA14 where a comment has been supplied (note: you may need to use the clause NULL). [6 marks] b) Find the total number of Assistants, and the sum and average o their salaries [6 marks] c) For each branch office, list the staff numbers and names of staff who manage properties alongside the properties they manage. [6 marks] d) For each branch, you are asked to list the staff numbers and names of staff who manage properties, including the city in which the branch is located and the properties that the staff manage. Note: This a three-table query it is suggested to use an AND for the join. [7 marks]. Branch branchNo street B005 B007 003 B004 B002 PA14 PL94 PG4 22 Deer Rd 16 Argyll St 163 Main St 32 Manse Rd 56 Clover Dr Staff staffNo Name IName position sex DOB salary branchNo SL21 John White Manager M 1-Oct-45 30000 B005 SG37 Ann Beech 10-Nov-60 12000 B003 Assistant F Supervisor M 24-Mar-58 18000 B003 B007 19-Feb-70 9000 SG14 David Ford SA9 Mary Howe SG5 SL41 PropertyForRent property No street PG36 PG21 PG16 Client city London Aberdeen Glasgow Bristol London postcode SW1 4EH AB2 35U G11 9QX BS99 INZ NW10 6EU Assistant F Susan Brand Manager F Julie Lec Assistant F 16 Holhead Argyll St 6 6 Lawrence St 2 Manor Rd 18 Dale Rd S Novar Dr 3-Jun-40 24000 B003 13-Jun-65 9000 B005 city postcode type rooms rent ownerNo staffNo branch No Aberdeen AB7 5SU House 6 London NW2 Glasgow G119QX Glasgow G32 4QX Glasgow G12 Glasgow G12 9AX Flat Flat Flat House 6433 in 5 Flat 4 650 C046 400 CO87 350 CO40 375 CO93 600 CO87 450 C093 SA9 SLAI SG37 SG37 SG14 B007 B005 B003 B003 B003 B003 Activate PG16 5 Novar Dr Glasgow G12 9AX Client clientNo Name IName telNo CR76 John Kay 0207-774-5632 Flat CR56 Aline Stewart 0141-848-1825 Flat CR74 CR62 CO46 CO87 CO40 C093 Private Owner ownerNo Name IName address 425 350 Mike Ritchie 01475-392178 House 750 Mary Tregear 01224-196720 Flat 600 Joe Keogh Carol Farrel Tina Murphy Tony Shaw CR56 PA14 CR76 PG4 CR56 PG4 CR62 PA14 CR56 PG36 Flat 4 prefType maxRent eMail Viewing clientNo property No viewDate comment 24-May-13 too small 20-Apr-13 too remote 26-May-13 14-May-13 no dining room 28-Apr-13 450 C093 telNo 2 Fergus Dr, Aberdeen AB2 7SX 01224-861212 jkeogh@hh.com 6 Achray St. Glasgow G32 9DX 63 Well St. Glasgow G42 0141-357-7419 cfarrel@gmail.com 0141-943-1728 tinam@hotmail.com 0141-225-7025 tony.shaw@ark.com 12 Park Pl, Glasgow G4 0QR CR76 CR56 CR74 CR62 john.kay@gmail.com astewart@hotmail.com mritchie01@yahoo.co.uk maryt@hotmail.co.uk SG14 eMail B005 B003 B003 B007 B003 password ******** ******** Registration clientNo branchNo staffNo dateJoined ******** SL41 2-Jan-13 SG37 SG37 SA9 11-Apr-12 16-Nov-11 7-Mar-12 Refer to the entity-relation scheme this assignment to test the queries. Use the file DreamHome.db included in Answer the following questions using SQL to produce the view. Only the SQL query will attract marks, not the view. Also, you must include at the end of your query the primary and foreign keys (if any) used. Degree and cardinality of the resulting view, and one sentence explaining the content of the first tuple based on the attributes queried. a) Find the total number of Managers and the sum of their salaries. (5 marks) b) Find the minimum, maximum, and average staff salary. [5 marks] c) For each branch office with more than one member of staff, find the number of staff working in each branch and the sum of their salaries. [5 marks] d) Construct a list of all cities where there is either a branch office or a property. [5 marks] e) Construct a list of all cities where there is both a branch office and a property. [5 marks] Refer to the entity-relation scheme this assignment to test the queries. Use the file DreamHome.db included in Answer the following questions using SQL to produce the view. Only the SQL query will attract marks, not the view. Also, you must include at the end of your query the primary and foreign keys (if any) used. Degree and cardinality of the resulting view, and one sentence explaining the content of the first tuple based on the attributes queried. a) List the details of all viewings on property PA14 where a comment has been supplied (note: you may need to use the clause NULL). [6 marks] b) Find the total number of Assistants, and the sum and average o their salaries [6 marks] c) For each branch office, list the staff numbers and names of staff who manage properties alongside the properties they manage. [6 marks] d) For each branch, you are asked to list the staff numbers and names of staff who manage properties, including the city in which the branch is located and the properties that the staff manage. Note: This a three-table query it is suggested to use an AND for the join. [7 marks]. Branch branchNo street B005 B007 003 B004 B002 PA14 PL94 PG4 22 Deer Rd 16 Argyll St 163 Main St 32 Manse Rd 56 Clover Dr Staff staffNo Name IName position sex DOB salary branchNo SL21 John White Manager M 1-Oct-45 30000 B005 SG37 Ann Beech 10-Nov-60 12000 B003 Assistant F Supervisor M 24-Mar-58 18000 B003 B007 19-Feb-70 9000 SG14 David Ford SA9 Mary Howe SG5 SL41 PropertyForRent property No street PG36 PG21 PG16 Client city London Aberdeen Glasgow Bristol London postcode SW1 4EH AB2 35U G11 9QX BS99 INZ NW10 6EU Assistant F Susan Brand Manager F Julie Lec Assistant F 16 Holhead Argyll St 6 6 Lawrence St 2 Manor Rd 18 Dale Rd S Novar Dr 3-Jun-40 24000 B003 13-Jun-65 9000 B005 city postcode type rooms rent ownerNo staffNo branch No Aberdeen AB7 5SU House 6 London NW2 Glasgow G119QX Glasgow G32 4QX Glasgow G12 Glasgow G12 9AX Flat Flat Flat House 6433 in 5 Flat 4 650 C046 400 CO87 350 CO40 375 CO93 600 CO87 450 C093 SA9 SLAI SG37 SG37 SG14 B007 B005 B003 B003 B003 B003 Activate PG16 5 Novar Dr Glasgow G12 9AX Client clientNo Name IName telNo CR76 John Kay 0207-774-5632 Flat CR56 Aline Stewart 0141-848-1825 Flat CR74 CR62 CO46 CO87 CO40 C093 Private Owner ownerNo Name IName address 425 350 Mike Ritchie 01475-392178 House 750 Mary Tregear 01224-196720 Flat 600 Joe Keogh Carol Farrel Tina Murphy Tony Shaw CR56 PA14 CR76 PG4 CR56 PG4 CR62 PA14 CR56 PG36 Flat 4 prefType maxRent eMail Viewing clientNo property No viewDate comment 24-May-13 too small 20-Apr-13 too remote 26-May-13 14-May-13 no dining room 28-Apr-13 450 C093 telNo 2 Fergus Dr, Aberdeen AB2 7SX 01224-861212 jkeogh@hh.com 6 Achray St. Glasgow G32 9DX 63 Well St. Glasgow G42 0141-357-7419 cfarrel@gmail.com 0141-943-1728 tinam@hotmail.com 0141-225-7025 tony.shaw@ark.com 12 Park Pl, Glasgow G4 0QR CR76 CR56 CR74 CR62 john.kay@gmail.com astewart@hotmail.com mritchie01@yahoo.co.uk maryt@hotmail.co.uk SG14 eMail B005 B003 B003 B007 B003 password ******** ******** Registration clientNo branchNo staffNo dateJoined ******** SL41 2-Jan-13 SG37 SG37 SA9 11-Apr-12 16-Nov-11 7-Mar-12
Expert Answer:
Related Book For
Posted Date:
Students also viewed these databases questions
-
What steps must the suppliers of Wireless Devices must follow. How could you insure the wireless devices that you intend to use in your design meets the ACMA regulations:
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Choose an organization in the community to study (church, restaurant, retail, museum...etc). Interview leaders and team members. 1) What elements of a management system do they have in the...
-
Discuss the interface between Purchasing and Supply Management and Logistics Management specifically with respect to selection of a third party logistics provider, analysis of the total cost of...
-
Suppose that in Problem 24.1 the spread between the yield on a five-year bond issued by the same company and the yield on a similar risk-free bond is 60 basis points. Assume the same recovery rate of...
-
In this problem, we derive the put-call parity relationship for European options on stocks that pay dividends before option expiration. For simplicity, assume that the stock makes one dividend...
-
Explain the difference in operation between HEVs with parallel and series designs.
-
(Multiple Choice) 1. A capital expenditure a. records additional capital. b. is a credit like capital (owners equity). c. is expensed immediately. d. adds to an asset. 2. Which of the following items...
-
Can you explain in details one specific social media platform that I desire like Facebook. How this medium could be used, noting the different visual persuasion delivery methods. How this medium...
-
Jackson Manufacturing holds a 150-day note of $10,000 that has an interest rate of 7% annually. After 90 days, the note is sold to a bank at a discount rate of 6% annually. Find the proceeds on the...
-
The tax benefit associated with a loan to an employee from their employer should take into account the difference between the interest rate prescribed by CRA and the interest rate charged by the...
-
Define the marketing environment. Are all elements of the marketing environment considered uncontrollable? Why or why not?
-
Outline the strategic marketing process, and comment on how it is related to the external and internal contingencies.
-
The EpiPen is a life-saving device used by people with severe allergies. The U.S. manufacturer of the EpiPen raised its price by nearly 25% per year for nearly a decade. For each price increase of...
-
What is mental accounting? Set out the model including the different types of mental accounts and how people use them differently in their everyday financial decision-making.
-
Describe the contingency framework for strategic sports marketing. Why is the contingency approach especially useful to sports marketers?
-
P and Q started a business in partnership by investing in the ratio of 7: 9. After 4 months P withdraw 1/3 of its money and after 6 months from the B withdraws 33 1/3 % of its money. If a total...
-
Synthesize the products by drawing out reagents and intermediates along the way. `N H. OH HO HO
-
Summarize the types of reports described in this chapter.
-
A. Design a data warehouse star schema for a dimensional database named JRJ-DW. The fact table measure will be Retail Price. B. Create the JRJ-DW database in a DBMS product. C. What transformations...
-
What is the purpose of the Oracle SQL Developer utility?
-
A manager has been using a certain technique to forecast demand for project man- agement software at her store. Actual demand and her corresponding predictions are shown below: a. What was the...
-
Why aren't the effective capacity and the optimal operating level of any operation necessarily the same amount of activity?
-
A certain facility has a design capacity of 2,500 units a day and an effective capacity of 2,225 units. Name one potential determinant of its effective capacity.
Study smarter with the SolutionInn App