Hello, i need help with my SQL assignment! please help. You are the Senior Consu
ID: 3771401 • Letter: H
Question
Hello, i need help with my SQL assignment! please help.
You are the Senior Consultant at Abacus Consulting, tasked with the database project for Amadeus Real Estate client. The company employs real estate agents who work with customers to buy and sell properties (both residential and commercial). As part of your lead role, you are initially responsible for meeting with the client team and gathering requirements. You are then scheduled to design the data model and implement a working prototype to demonstrate the validity of the data model. Your specific tasks are broken down as below
Step 1:
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 2:
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
create table employee(
emp name varchar(50) NOT NULL,
emp id int NOT NULL,
area varchar(50) NOT NULL,
Actual price int NOT NULL,
Sale Price int NOT NULL,
sale date date NOT NULL,
Type of Property varchar(50),PRIMARY KEY(emp id),
status varchar(50));
create table customer(
customer name varchar(50) NOT NULL,
emp name varchar(50) NOT NULL,
emp id int FOREIGN KEY REFERENCES employee(emp id),
area varchar(50) NOT NULL,
Actual price int NOT NULL,
Sale Price int NOT NULL,
sale date date NOT NULL,
Type of Property varchar(50),status varchar(50));
insert into employee values('xxx','123','XXX','$5000','$4500','13-JUN-2015','expensive','Purchased');
insert into employee values('xyx','133','XYX','$5200','$4550','13-FEB-2015','expensive','No');
insert into employee values('xxx','123','XXX','$5060','$4500','13-MAR-2015','Low','Purchased');
insert into employee values('xzx','123','XXX','$5000','$4570','13-FEB-2015','expensive','Purchased');
insert into employee values('xxx','123','XXX','$5000','$4500','13-MAR-2015','expensive','Purchased');
insert into employee values('yxx','123','XXX','$5000','$4500','13-DEC-2015','High','No');
insert into employee values('xux','123','XXX','$5000','$4500','13-FEB-2015','Low','No');
insert into employee values('xxx','123','XXX','$5000','$4500','13-MAR-2015','High','Purchased');
insert into employee values('xzx','123','XXX','$5000','$4500','13-JAN-2015','expensive','No');
insert into employee values('txx','123','XXX','$5000','$4500','13-JUN-2015','expensive','Purchased');
insert into employee values('ixx','123','XXX','$5000','$4500','13-JAN-2015','Low','Purchased');
insert into employee values('oxx','123','XXX','$5000','$4500','13-MAR-2015','expensive','Purchased');
insert into employee values('xpx','123','XXX','$5000','$4500','13-JAN-2015','High','No');
insert into employee values('xqx','123','XXX','$5000','$4500','13-FEB-2015','expensive','Purchased');
insert into employee values('xxx','123','XXX','$5000','$4500','13-DEC-2015','Low','Purchased');
insert into employee values('xxx','123','XXX','$5000','$4500','13-JUN-2015','expensive','Purchased');
insert into employee values('xix','123','XXX','$5000','$4500','13-DEC-2015','High','Purchased');
insert into employee values('iix','123','XXX','$5000','$4500','13-MAR-2015','expensive','Purchased');
insert into employee values('xxx','123','XXX','$5000','$4500','13-DEC-2015','Low','No');
insert into employee values('xpx','123','XXX','$5000','$4500','13-JAN-2015','expensive','Purchased');
insert into customer values('c1','xxx','123','XXX','$5000','$4500','13-JUN-2015','expensive','Purchased');
insert into customer values('c2','xyx','133','XYX','$5200','$4550','13-FEB-2015','expensive','Purchased');
insert into customer values('c3','xxx','123','XXX','$5060','$4500','13-MAR-2015','Low','NO');
insert into customer values('c4','xzx','123','XXX','$5000','$4570','13-FEB-2015','expensive','Purchased');
insert into customer values('c5','xxx','123','XXX','$5000','$4500','13-MAR-2015','expensive','NO');
insert into customer values('c6','yxx','123','XXX','$5000','$4500','13-DEC-2015','High','Purchased');
insert into customer values('c7','xux','123','XXX','$5000','$4500','13-FEB-2015','Low','NO');
insert into customer values('c8','xxx','123','XXX','$5000','$4500','13-MAR-2015','High','NO');
insert into customer values('c9','xzx','123','XXX','$5000','$4500','13-JAN-2015','expensive','Purchased');
insert into customer values('c10','txx','123','XXX','$5000','$4500','13-JUN-2015','expensive','Purchased');
insert into customer values('c11','ixx','123','XXX','$5000','$4500','13-JAN-2015','Low','NO');
insert into customer values('c12','oxx','123','XXX','$5000','$4500','13-MAR-2015','expensive','Purchased');
insert into customer values('c13','xpx','123','XXX','$5000','$4500','13-JAN-2015','High','NO');
insert into customer values('c14','xqx','123','XXX','$5000','$4500','13-FEB-2015','expensive','Purchased');
insert into customer values('c15','xxx','123','XXX','$5000','$4500','13-DEC-2015','Low','NO');
insert into customer values('c16','xxx','123','XXX','$5000','$4500','13-JUN-2015','expensive','Purchased');
insert into customer values('c17','xix','123','XXX','$5000','$4500','13-DEC-2015','High','Purchased');
insert into customer values('c18','iix','123','XXX','$5000','$4500','13-MAR-2015','expensive','Purchased');
insert into customer values('c19','xxx','123','XXX','$5000','$4500','13-DEC-2015','Low','NO');
insert into customer values('c20','xpx','123','XXX','$5000','$4500','13-JAN-2015','expensive','Purchased');
Queries:
select count(*) from employee where status='Purchased' and sale date between('01-MAR-2015','31-MAR-2015');
select max(e.Sale Price)||'~'||c.customer name,e.emp name from employee e,customer c where e.emp id=c.emp id and sale date between('01-FEB-2015','30-JUN-2015');
select cosunt(c.customer name)||'~'||e.emp name||'~'||c.customer name||'~'||e.Sale Price||'~'||e.status from employee e,customer c where e.emp id=c.emp id and e.status='Purchased' and
sale date between('01-JAN-2015','31-MAR-2015');
select count(*) from employee where Type of Property='expensive' and sale date between('01-JAN-2015','30-JUN-2015');
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.