Assume that Twig has created a database with the following table: OWNER (OwnerNa

Question

Assume that Twig has created a database with the following table: OWNER (OwnerName, Phone, Street, City, State. Zip) SERVICE (DataOfService, OwnerName, Description, AmountBilled, AmountPaid, Date Of Payment) CHIP_DELIVERY (CustomerName, DateDelivered, LoadSize, AmountBilled, AmountPaid, Date Of Payment) Code SQL for the folliwng. Assume that SERBVICE.OwnerName and CHIP_DELLIVERY.CustmerName are foreign keys to OWNER.OwnerName. Also assume that all dates are in the format mmddyyy. a. Show all of the data in each of the three Twigs tables. b. List the Cities of all Owners c. List the Cities or all owners without duplicates d. List the OwnerName and DateOf Service for all services performed in year 2002 e. List the CustomerName and DateDelivered for all chip deliveries paid for in February. Use the underscore (_) wildcard. f. Same as question E but use the (%) wildcard. g. List the ames and cities of all owners who have a phone number h. List the names of all ownders who do not have a phone number and present the results in descending sorted order of CustomerName i. Determine the maximum amount billed for a service j. Determine the average load size of a chip delivery.

Explanation / Answer

a. Show all of the data in each of the three Twigs tables.

select * from OWNER
Select * from SERVICE
select * from CHIP_DELIVERY

b. List the Cities of all Owners
select City from OWNER

c. List the Cities or all owners without duplicates
select distinct City from OWNER

d. List the OwnerName and DateOf Service for all services performed in year 2002
select o.OwnerName, s.DataOfService from OWNER o, SERVICE s where o.OwnerName = s.OwnerName and DataOfService like '%002' groupby o.OwnerName

e. List the CustomerName and DateDelivered for all chip deliveries paid for in February. Use the underscore (_) wildcard.
select CustomerName, DateDelivered from CHIP_DELIVERY c where DateOfPayment like '02_____' groupby CustomerName

f. Same as question E but use the (%) wildcard.
select CustomerName, DateDelivered from CHIP_DELIVERY c where DateOfPayment like '02%' groupby CustomerName

g. List the names and cities of all owners who have a phone number
select OwnerName, City from OWNER where Phone != null;

h. List the names of all ownders who do not have a phone number and present the results in descending sorted order of CustomerName
select OwnerName from OWNER where Phone == null order by OwnerName desc

i. Determine the maximum amount billed for a service
select Max(AmountBilled) from SERVICE

j. Determine the average load size of a chip delivery.
select avg(LoadSize) from CHIP_DELIVERY

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.