PART II: Import the midterm script and use the following logical schema to answe
ID: 3703105 • Letter: P
Question
PART II: Import the midterm script and use the following logical schema to answer the questions below. Inclu de 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 in the class (e.g., aggregate function, View, GROUP BY or ORDER BY). AgtFirstName CustomeriD PK EndDate StopTime AgtState FK Agentio DateHired FK ENTERTANEN9 Entertainere PK ENTERTAINER MEMBERS EntertainerlD Status CPK CPK CustomerlD EntCity EntState EntPhoneNbe PK MbrFirstName ENTERTAINER STYLES STYLES PK StylelD Queries 1. Show all engagement information. s 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 contractExplanation / Answer
--1
SELECT * FROM Engagements;
--2
SELECT c.CustFirstName,c.CustLastName,es.StyleStrength FROM Customers c
INNER JOIN Musical_PreFerences MP ON c.CustomerID=MP.CustomerID
INNER JOIN Musical_Styles ms ON MP.StyleID=ms.StyleID
INNER JOIN Entertainer_Styles es ON ms.StyleID=es.StyleID;
--3
SELECT TOP 1 * FROM Agents ORDER BY DateHired ASC;
--4
SELECT * FROM Customers
WHERE CustomerID NOT IN (SELECT e.CustomerID FROM Engagements e INNER JOIN Agents a
ON e.AgentID=a.AgentID);
--5
SELECT * FROM Customers WHERE CustStreetAddress='Avenue';
--6
SELECT A.AgtFirstName,a.AgtLastName,a.AgtStreetAdsress,
SUM(a.Salary+(a.Salary*CommissionRate)) AS TotalSalary
FROM Agents a INNER JOIN Engagements e
ON a.AgentID=e.AgentID
GROUP BY e.EngagementNumber;
--7
SELECT * FROM Entertainers
WHERE EntertainerID NOT IN
(SELECT e.EntertainerID FROM Entertainer_Styles e
INNER JOIN Musical_Styles m ON e.StyleID=m.StyleID)
--8
SELECT EntStreetAddress FROM Entertainers WHERE EntStreetAddress IN
(SELECT c.CustStreetAddress FROM Customers c
INNER JOIN Musical_PreFerences MP ON c.CustomerID=MP.CustomerID
INNER JOIN Musical_Styles ms ON MP.StyleID=ms.StyleID
INNER JOIN Entertainer_Styles es ON ms.StyleID=es.StyleID);
--9
SELECT a.* FROM Engagements e INNER JOIN Agents a
ON e.AgentID=a.AgentID INNER JOIN Customers c
ON a.CustomerID=c.CustomerID;
--10
SELECT DISTINCT a.* FROM Engagements e INNER JOIN Agents a
ON e.AgentID=a.AgentID
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.