Question

SQL:

The schema name should be mxxws, where mxx is your MySQL user name. To refresh your memory – here is the schema for that database:

Create the rest of the contact management database by writing the statements that do each of the following items below. For each one: (1) execute them on your copy of the database, and (2) write the statements in the space:

Statement to create the Contact table (one statement)

Statement to create the Employee table (one statement)

Statement to create the ContactEmployee table (one statement):

1. Statements that add the following two Companies to the Company table (there are two statements):

CompanyID	CompanyName	Street	City	State	Zip
110	Urban Outfitters, Inc.	5000 South Broad St.	Philadelphia	PA	19112
111	Toll Brothers	250 Gibraltar Rd.	Horsham	PA	19044

5. Statements that add the following two Contacts to the Contact table (there are two statements):

ContactID	CompanyID	FirstName	LastName	Street	City	State	Zip	IsMain	Email	Phone
501	110	Jack	Lee	4777 Cameron Rd.	Buffalo	NY	14209	1	[email protected]	215-454-5500
502	111	Bonnie	Johnson	3600 Elk City Rd.	Ridley Park	PA	19078	1	[email protected]	215-938-8000

6. Statements that add the following two Employees to the Employee table (there are two statements):

EmployeeID	FirstName	LastName	Salary	HireDate	JobTitle	Email	Phone
1001	Dianne	Connor	85,000	August 12, 2011	Sales Manager	[email protected]	215-555-5679
1002	Lesley	Bloom	70,000	July 1, 2012	Sales Representative	[email protected]	215-555-5678

7. Statements that record the following contact events in the Contact-Employee table. Remember, a “contact event” occurs when an employee of MarketCo communicates with one of its customers (contact). MarketCo wants to keep track all contact between its own employees and its clients.

Contact:            Bonnie Johnson Employee:        Lesley Bloom Date:                February 5, 2018 Description:      Emailed new marketing plan for approval

Contact:            Jack Lee Employee:        Dianne Connor Date:                February 8, 2018 Description:      Phone call to discuss pricing for advertising

8. In the Employee table, the statement that changes Lesley Bland’s phone number to 215-555-8800 (one statement).
9. In the Company table, the statement that changes the name of “Urban Outfitters, Inc.” to “Urban Outfitters” (one statement).
10. In ContactEmployee table, the statement that removes Dianne Connor’s contact event with Jack Lee (one statement).
11. Write the SQL SELECT query that displays the names of the employees that have contacted Toll Brothers (one statement). Run the SQL SELECT query in MySQL Workbench. Copy the results below as well.

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using WAMP (Windows , Apache , MySQL and PHP) Server.Database and Tables are created using PHPMyAdmin.

NOTE : ALL SQL STATEMENTS WILL EXECUTES AS IT IS IN MySQL Workbench.

1.Table Name :Company

SQL Create table Statement :

Create TABLE Company (
CompanyID int primary key,
CompanyName varchar(100) ,
Street varchar(100),
City varchar(50),
State varchar(2),
Zip int not null
);

Inserting records into companty table :

INSERT INTO company values (110,'Urban Outfitters, Inc.','5000 South Broad St.','Philadelphia','PA',19112);
INSERT INTO company values (111,'Toll Brothers','250 Gibraltar Rd.','Horsham','PA',19044);

Select records :

select * from company;

Screen in PHPMyAdmin :

*******************************

2.Table Name :

SQL Create table Statement :

CREATE TABLE contact(
ContactID int primary key,
CompanyID int ,
FirstName varchar(100),
LastName varchar(100),
Street varchar(100),
City varchar(10),
State varchar(2),
Zip int ,
IsMain TINYINT(1),
Email varchar(100),
Phone varchar(100),
foreign key(CompanyID) REFERENCES company (CompanyID)
);

Inserting records :

insert INTO contact values (501,110,'Jack','Lee','4777 Cameron Rd.','Buffalo','NY',14209,1,'[email protected]','215-454-5500');
insert INTO contact values (502,111,'Bonnie','Johnson','3600 Elk City Rd.','Ridley Park','PA',19078,1,'[email protected]','215-938-8000');

Selecting records :

select * from contact;

Screen in PHPMyAdmin :

*******************************

3.Table Name :

SQL Create table Statement :

CREATE TABLE Employee (
EmployeeID int PRIMARY KEY,
FirstName varchar(100),
LastName varchar(100),
Salary decimal(8,2),
HireDate date,
JobTitle varchar(100),
Email varchar(100),
Phone varchar(100)
);

Inserting records :

insert into Employee values (1001,'Dianne','Connor',85000,'2011-12-8','Sales Manager','[email protected]','215-555-5679');
insert into Employee values (1002,'Lesley','Bloom',70000,'2012-07-1','Sales Representative','[email protected]','215-555-5678');

Selecting records :

select * from Employee;

Screen in PHPMyAdmin :

*******************************

SQL Query :

update employee set phone=' 215-555-8800' where firstName='Lesley' and lastName='Bloom';

Screen in SSMS :

************************

SQL Query :

update company set companyName='Urban Outfitters' where companyName="Urban Outfitters, Inc.";

Screen in PHPMyADmin :

*****************************

SQL Query :

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.