Refer to the entity-relation scheme this assignment to test the queries. Use the file DreamHome.db included...

    

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