I could use the help with the SQL code for this ERD.Thanks Implement the table s
ID: 3771691 • Letter: I
Question
I could use the help with the SQL code for this ERD.Thanks
Implement the table structure based on the ERD developed in phase-2. Assume property details (type, area, list price, sale price …etc). Assume 3% commission on all transactions.
Write the SQL code to create the needed tables. Ensure all primary keys, foreign keys and constraints are defined.
After implementing the table structures, insert dummy data for 25 transactions, placed between January-1-2015 and June-30-2015.
Step 4
To validate the data model you have developed in phase-3, you are asked to write some queries and verify the results with expected results.
Write the SQL statements that provide the following information:
Number of sales for the period March-1-2015 to March-31-2015
Agent who sold the most number of properties between Feb-1-2015 to June-30-2015.
Agent who sold the most $$ value of properties between Jan-1-2015 to Mar-31-2015.
Most expensive property sold between Jan-1-2015 to June-30-2015.
Explanation / Answer
1) Number of sales for the period March-1-2015 to March-31-2015
select count(property_id) from sales where saleDate = timestamp '2015-03-01 00:00:00.0000' between ''2015-03-31 00:00:00.0000';
2) Agent who sold the most number of properties between Feb-1-2015 to June-30-2015.
select a.person_ID from Agent a,Sales S where a.personID = S.AgentpersonID and S.saledate = timestamp '2015-02-01 00:00:0000' between '2015-02-30' 00:00:0000';
3) Agent who sold the most $$ value of properties between Jan-1-2015 to Mar-31-2015.
select a.person_ID from Agent a,Sales S,Forsale F where a.personID = S.AgentpersonID and S.saledate = timestamp '2015-01-01 00:00:0000' between '2015-03-31' 00:00:0000' and max(F.suggestedPrice);
4) Most expensive property sold between Jan-1-2015 to June-30-2015.
select max(F.suggestedPrice) from Sales S,Forsale F where S.saledate = timestamp '2015-01-01 00:00:0000' between '2015-06-30' 00:00:0000' and S.propertyID = F.propertyID;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.