PART I: Import the midterm script and use the following logical schema to answer

Question

PART I: Import the midterm script and use the following logical schema to answer the below. Include the typed query and the screenshot of the result (50 points: 5 points each query) Note that you will not get credit if you use techniques that haven't been taught aggregate function, View, GROUP BY or ORDER BY) in the class (et AGENTS Entertainero ENTERTANERS CPK Queries 1. Show all engagement information 2. Show customer first name, customer last name, and his/her entertainer style strength. 3. Show all information of the first hired agent(s). 4. List customers who do not have agents assigned. 5. List customers who live in 'Avenue street address 6. Show agent first name, agent last name, agent street address, and agent's total salary of each engagement contract. Total salary is calculated by the sum of agents' salary, and commission salary for each engagement contract 7. Display entertainers who have no musical style 8. List entertainers who live in the same area as his/her customer. 9. List agents who work for every customer 10. Display detail of first hired agents for each engagement contract

Explanation / Answer

Ans1) select * from ENGAGEMENT;
Ans2) select C.CustFirstName, C.CustLastName, ES.StyleStrength from CUSTOMERS as C where C.CustomerId = ( select E.CustomerId from ENGAGEMENTS as E where E.CustomerId = C.CustomerId and E.EnterTainerId= (select ES.EnterTainerId from ENTERTAINERS_STYLES as ES where ES.EnterTainerId = E.EnterTainerId))
Ans3) select * from AGENTS Limit 1;
Ans4) select * from CUSTOMERS as C where C.CustomerId = ( select E.CustomerId from ENGAGEMENTS as E where E.CustomerId = C.CustomerId and E.AgentId IS NULL)
Ans5) select * from CUSTOMERS where custStreetAddress LIKE '%Avenue%'
Ans6) select A.AgtFirstName,A.AgtLastName, A.AgtStreetAddress, SUM(A.Salary + A.CommisionRate) from AGENTS as A where A.AgentId= (select E.AgentId from ENGAGEMENTS E where E.AgentId = A.AgentId)
Ans7) select * from ENTERTAINERS as E where E.EntertainerId= (select EA.EntertainerId from ENTERTAINERS_STYLES as ES where ES.EntertainerId= E.EntertainerId and ES.styleId IS NULL);

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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