Hi,

Thank you for your help with last week's assignment. This is a continuation of last week's work. I have attached the assignment requirement (Week4Assignment.PNG), the finished product from last week that will need to be modified for this week (DBM294_starks.txt), and feedback from last week's assignment (Brandonweek3.docx). Apparently there was a problem with Insert Statements, which is highlighted in the feedback file. Thank you in advance for your help. I will have one more week of this course and then hopefully my SQL requirements (which I am obviously not very good with) will be fulfilled.

Thanks! Brandon

 

 

Week 3 – Data Manipulation Language
(DML)
Tables populated. Points Earned Comments 2 1.5 All tables not populated.
Several tables had errors on INSERT. SQL for Update statement 1.5 1.35 SQL for Delete statement 1 1 See example of INSERT to table books.
Update statement did not update any record. Table is
empty. Check for insert errors.
Delete statement executed successfully. SQL contains Between clause 1 1 No need for quotes around table names. SQL contains Like clause 2 1.8 Query executed after I removed the quotes.
No results because table is empty. SQL contains Top clause 1.5 1.5 Top clause demonstrated. SQL contains Distinct clause 1 0.9 No results because table is empty. SQL uses Named columns 2 1.8 No results because table is empty. SQL uses Calculated columns 2 2 Calculated query demonstrated. Total 14 12.85 Good job.
Check Insert statements for errors and fix, so that all
tables can be populated.

CREATE TABLE TerritoryDescription( TerritoryID INT PRIMARY KEY, TerritoryDescription VARCHAR(50) NOT NULL ); CREATE TABLE Technician( EmployeeID INT PRIMARY KEY, LastName VARCHAR(20) NOT NULL, FirstName VARCHAR(20) NOT NULL, LocationID INT NOT NULL, Address VARCHAR(100) NOT NULL, City VARCHAR(30) NOT NULL, PostalCode VARCHAR(15) NOT NULL, MobilePhone VARCHAR(10), Email VARCHAR(30) NOT NULL, CONSTRAINT fk_TechLoc FOREIGN KEY(LocationID) REFERENCES TerritoryDescription(TerritoryID) ); CREATE TABLE TechnicianTerritory( EmployeeID INT NOT NULL, TerritoryID INT NOT NULL, CONSTRAINT pk_TechTtry PRIMARY KEY(EmployeeID, TerritoryID), CONSTRAINT fk_TechTryTech FOREIGN KEY(EmployeeID) REFERENCES Technician(EmployeeID), CONSTRAINT fk_TechTryTry FOREIGN KEY(TerritoryID) REFERENCES TerritoryDescription(TerritoryID) ); CREATE TABLE Employee( EmployeeID INT PRIMARY KEY, LocationID INT NOT NULL, Address VARCHAR(50) NOT NULL, City VARCHAR(30) NOT NULL, ZipCode VARCHAR(15) NOT NULL, Phone VARCHAR(10) NOT NULL, Email VARCHAR(30) NOT NULL, CONSTRAINT fk_EmpLoc FOREIGN KEY(LocationID) REFERENCES TerritoryDescription(TerritoryID) ); CREATE TABLE WorkTicket( TicketID INT PRIMARY KEY, EmployeeID INT NOT NULL, TechnicianID INT NOT NULL, Address VARCHAR(50) NOT NULL, City VARCHAR(30) NOT NULL, Region VARCHAR(20), PostalCode VARCHAR(15) NOT NULL, Phone VARCHAR(15) NOT NULL, Email VARCHAR(30) NOT NULL, CONSTRAINT fk_TktEmp FOREIGN KEY(EmployeeID) REFERENCES Employee(EmployeeID), CONSTRAINT fk_TktTech FOREIGN KEY(TechnicianID) REFERENCES Technician(EmployeeID) ); CREATE TABLE Materials( PartID INT PRIMARY KEY, PartName VARCHAR(30) NOT NULL, Qty INT NOT NULL, Cost NUMERIC(5, 2) NOT NULL ); CREATE TABLE TicketDetails( TicketID INT NOT NULL, PartID INT NOT NULL, WorkDetail VARCHAR(100), CONSTRAINT pk_TktDtl PRIMARY KEY(TicketID, PartID), CONSTRAINT fk_TktDtlTkt FOREIGN KEY(TicketID) REFERENCES WorkTicket(TicketID), CONSTRAINT fk_TktDtlMtrl FOREIGN KEY(PartID) REFERENCES Materials(PartID) ); INSERT INTO TerritoryDescription(TerritoryID, TerritoryDescription) VALUES ('1', 'CS Teritory'), ('2', 'Comics'), ('3', 'Agricultural'), ('4', 'Pure Maths'), ('5', 'Biotics'), ('6', 'Science'); INSERT INTO technicianterritory(EmployeeID, TerritoryID) VALUES ('1', '1'), ('1', '3'), ('2', '2'), ('3', '5'), ('4', '4'), ('5', '6'); INSERT INTO technician (EmployeeID, LastName, FirstName, LocationID, Address, City, PostalCode, MobilePhone, Email) VALUES ('1', 'Parker', 'wendy', '1234', '71 wall street', 'New Hamshire', '298 345', '9002345432', 'rob@fantastic.com'), ('2', 'Mario', 'Di Silva', '345676', '1265 Bramley', 'Huddersfield', 'HD1 7DF', '01484 555669', 'Silva@dwp.gsi.gov.uk'), ('3', 'Arthur', 'Great', '1234', '123 The Heathers', 'Northallerton', 'DL6 7TW', '9002345432', 'ag@ibm.co.uk'), ('4', 'Parker', 'wendy', '1234', 'The Grange Grange', 'Durham', 'DH9 8OQ', '0191 555 3654', 'mike@grange.co.uk'), ('5', 'Parker', 'wendy', '1234', 'Folyfoot Lane', 'New HamshireNewcastle-upon-Tyne ', 'NE1 3KA ', '0191 555 5287', 'grant@newc.co.uk'); INSERT INTO employee (`EmployeeID`, `LocationID`, `Address`, `City`, `ZipCode`, `Phone`, `Email`) VALUES ('1', '1', '82 Stafford Street', 'middlesbrough', 'TS7 7FG', '01642 555222', 'Sam.Jackson@dwp.gsi.gov.uk'), ('2', '2', 'Jingle Bell Lane', 'Durham', 'DH4 6DP', '0191 555 6567', 'Judith.Claxon@dwp.gsi.gov.uk'), ('3', '3', '89,High Street', 'Hartelepool', 'TS24 6RT', '01429 555865', 'Peter.Singh@dwp.gsi.gov.uk'), ('4', '4', '88,Thimblemill', 'middlesbrough', 'TS5 7YU', '01642 555987', 'Judith.Lacy@dwp.gsi.gov.uk'), ('5', '5', '84,Helperby', 'Hartelepool', 'TS24 9UY', '01429 555645', 'Jon.Smith@dwp.gsi.gov.uk'); INSERT INTO materials (`PartID`, `PartName`, `Qty`, `Cost`) VALUES ('1', 'Lever', '23', '345'), ('2', 'Conner', '2', '33'), ('3', 'Glass', '20', '19'), ('4', 'Piper', '23', '4'), ('5', 'Exhaust', '3', '53'); INSERT INTO ticketdetails (`TicketID`, `PartID`, `WorkDetail`) VALUES ('1', '1', 'plastering'), ('2', '2', 'elecric repair'), ('3', '3', 'window panes'), ('4', '4', 'water leakage'), ('5', '5', 'garage tools'); INSERT INTO workticket (`TicketID`, `EmployeeID`, `TechnicianID`, `Address`, `City`, `Region`, `PostalCode`, `Phone`, `Email`) VALUES ('1', '1', '1', '123 The Heathers', 'Northallerton', 'Central', 'DHG VFG', '01642 555987', 'Singh@dwp.gsi.gov.uk'), ('2', '2', '2', '88,Thimblemill Lane', 'Middlesbrough', 'western', 'TS5 7YU', '01429 555865', 'Lacy@dwp.gsi.gov.uk'), ('3', '3', '3', '84,Helperby High', 'Hartelepool', 'western', 'TS24 9UY', '01642 555222', 'Smith@dwp.gsi.gov.uk'), ('4', '4', '4', '82,Stafford Street', 'leeds', 'northern', 'HD1 7XZ', '01429 555645', 'mike@grange.co.uk'), ('5', '5', '5', '79,Wakefield Road', 'Northallerton', 'eastern', 'TS7 7FG', '01484 555698', 'Walters@dwp.gsi.gov.uk'); UPDATE WorkTicket SET email='Judith.Lacy@dwp.gsi.gov.uk' WHERE employeeID = 3; DELETE FROM `territorydescription` WHERE `TerritoryID`='5'; SELECT * FROM `territorydescription` WHERE `TerritoryID` BETWEEN 5 AND 6; SELECT * FROM workticket WHERE Email LIKE "%Lacy%"; SELECT TOP 50 PERCENT * FROM materials; SELECT DISTINCT(Address) FROM technician; SELECT COUNT(PartID) as theCount FROM ticketdetails; SELECT SUM(cost) FROM materials;

 

• 1493794439-Week4Assignment.PNG
• 1493794439-DBM294_starks.txt
• 1493794440-Brandonweek3.docx

ComputerScienceExpert

(11)

Status NEW Posted 03 May 2017 06:05 AM My Price 11.00

-----------

Attachments

file 1493794446-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer