Assignment SQL 2 Putting Information into a Database

Assignment: SQL #2 Putting Information into a Database Solution Key

Details You May Miss in SQL • Basic Syntax offered in the Exam! • CREATE TABLE: • Do not forget the foreign key • Use curly braces foreign key REFERENCES • foreign key REFERENCES: Primary table name is optional, foreign referred table name is required!!!

1. Statement to create the Contact table CREATE TABLE m 80 ws. Contact ( Contact. ID INT NOT NULL, Company. ID INT NULL, First. Name VARCHAR(45) NULL, Last. Name VARCHAR(45) NULL, Street VARCHAR(45) NULL, City VARCHAR(45) NULL, State VARCHAR(2) NULL, Zip VARCHAR(10) NULL, Is. Main BOOLEAN NULL, Email VARCHAR(45) NULL, Phone VARCHAR(12) NULL, PRIMARY KEY (Contact. ID), FOREIGN KEY (Company. ID) REFERENCES m 80 ws. Company(Company. ID) );

2. Statement to create the Employee table CREATE TABLE m 80 ws. Employee ( Employee. ID INT NOT NULL, First. Name VARCHAR(45) NULL, Last. Name VARCHAR(45) NULL, Salary DECIMAL(10, 2) NULL, Hire. Date DATE NULL, Job. Title VARCHAR(25) NULL, Email VARCHAR(45) NULL, Phone VARCHAR(12) NULL, PRIMARY KEY (Employee. ID) );

3. Statement to create the Contact. Employee table CREATE TABLE m 80 ws. Contact. Employee ( Contact. Employee. ID INT(10) NOT NULL, Contact. ID INT(10) NULL, Employee. ID INT(10) NULL, Contact. Date DATE NULL, Description VARCHAR(100) NULL, PRIMARY KEY (Contact. Employee. ID), FOREIGN KEY (Contact. ID) REFERENCES m 80 ws. Contact(Contact. ID), FOREIGN KEY (Employee. ID) REFERENCES m 80 ws. Employee(Employee. ID));

4. Statements that add the following two Companies to the Company table INSERT INTO m 80 ws. Company (Company. ID, Company. Name, Street, City, State, Zip) VALUES (110, ' Urban Outfitters, Inc. ', 5000 South Broad St. ', ' Philadelphia ', 'PA', '19112'); INSERT INTO m 80 ws. Company (Company. ID, Company. Name, Street, City, State, Zip) VALUES (111, ' Toll Brothers', '250 Gibraltar Rd. ', 'Horsham', 'PA', '19044');

5. Statements that add the following three Contacts to the Contact table INSERT INTO m 80 ws. Contact (Contact. ID, Company. ID, First. Name, Last. Name, Street, City, State, Zip, Is. Main, Email, Phone) VALUES (501, 110, 'Jack', 'Lee', '4777 Cameron Rd. ', 'Buffalo', 'NY', '14209', 1, 'jlee@urbanout. com', '215 -454 -5500'); INSERT INTO m 80 ws. Contact (Contact. ID, Company. ID, First. Name, Last. Name, Street, City, State, Zip, Is. Main, Email, Phone) VALUES (502, 111, 'Bonnie', 'Johnson', '3600 Elk City Rd. ', 'Ridley Park', 'PA', '19078', 1, 'bj@tollbrothers. com', '215 -938 -8000');

6. Statements that add the following three Employees to the Employee table INSERT INTO m 80 ws. Employee (Employee. ID, First. Name, Last. Name, Salary, Hire. Date, Job. Title, Email, Phone) VALUES (1001, 'Dianne', 'Connor', 85000, '2011 -08 -12', 'Sales Manager', 'dconnor@marketco. com', '215 -555 -5678’); INSERT INTO m 80 ws. Employee (Employee. ID, First. Name, Last. Name, Salary, Hire. Date, Job. Title, Email, Phone) VALUES (1002, 'Lesley', ’Bloom', 70000, '2012 -07 -01', 'Sales Representative', ’lbloom@marketco. com', '215 -555 -5679');

7. Statements that record the following contact events in the Contact. Employee table INSERT INTO m 80 ws. Contact. Employee (Contact. Employee. ID, Contact. ID, Employee. ID, Contact. Date, Description) VALUES (5001, 502, 1002, '2018 -02 -05', Emailed new marketing plan for approval'); INSERT INTO m 80 ws. Contact. Employee (Contact. Employee. ID, Contact. ID, Employee. ID, Contact. Date, Description) VALUES (5002, 501, 1001, '2018 -02 -08', 'Phone call to discuss pricing for advertising');

8. In the Employee table, the statement that changes Lesley Bloom’s phone number to 215 -5558800 UPDATE m 80 ws. Employee SET Phone='215 -555 -8800' WHERE Employee. ID = 1002; 9. In the Company table, the statement that changes the name of “Urban Outfitters, Inc. ” to “Urban Outfitters” UPDATE m 80 ws. Company SET Company. Name=' Urban Outfitters' WHERE Company. ID = 110; 10. In Contact. Employee table, the statement that removes Dianne Connor’s contact event with Jack Lee DELETE FROM m 80 ws. Contact. Employee WHERE Contact. Employee. ID=5002;

11. SQL SELECT query that displays the names of the employees that have contacted Wegmans Food Markets SELECT Employee. First. Name, Employee. Last. Name FROM m 80 ws. Employee, m 80 ws. Contact, m 80 ws. Company WHERE Company. ID=Contact. Company. ID AND Contact. ID=Contact. Employee. Contact. ID AND Employee. ID=Contact. Employee. ID AND Company. Name=‘Toll Brothers'; Results: • Lesley Bloom