Complete and print the following SQL transactions. Log your statements and resul

Question

Complete and print the following SQL transactions. Log your statements and results by spooling your file (with echo on from Oracle if available).

List the customer ID, customer first name, customer last name, and count of reservations for each customer. Combine the first and last name into one column. Sort by reservation count in descending order, then by customer ID in ascending order. Use the following column headings: CustomerID, CustomerName, ResCount. Hint: Use a GROUP BY clause.

ResDetail Customer CustlD CustFName CustLName CustPhone CustType LoyaltylD Reservation ResID CheckinDate CheckOutDate CustID AgentID ReslD Room CustType CustType CustTypeDesc RoomNum RateType RateAmt RoomType Agent AgentlD AgentFName AgentLName AgentType RateType RateType RateTypeDesc RoomType RoomType RoomTypeDesc AgentType AgentType AgentTypeDesc

Explanation / Answer

Answer:

select c.CustID,Concat(c.CustFName , c.CustLname) as "CustomerName" ,count(r.ResID) as"ResCount"
from Customer c
join Reservation r on r.CustID =c.CustID
group by c.CustID
order by count(r.ResID) desc;

/*This query will list down Customer Name, CustomerID, reservation count of the customers */

Output:

CustID Customer Name Rescount 1234 Andy Balbirne 24 1235 Andrew Symonds 12 1245 Mitchell Starc 9

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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