Question

Hotels that took order more than five times:
Write a query to display hotel id, hotel name, and number of orders taken by hotels that have taken orders more than 5 times. Give an alias name for number of orders as 'NO_OF_ORDERS'.sort the result based on hotel id in ascending order.

(HINT: Use Hotel_details and Orders tables to retrieve records.)

TABLE 1:: hotel_details {hotel_id,hotel_name,hotel_type,rating}
TABLE 2::orders {order_id,customer_id,hotel_id,partner_id,order_date,order_amount}

Answer 1

Answer: select h.hotel_id, h.hotel_name, count(o.order_id) as no_of_orders from hotel_details h

Join orders o on o.hotel_id = h.hotel_id

Group by h.hotel_id

Having no_of_orders >5

Order by h.hotel_id;

Explanation:

Answer 2

Answer:

The SQL query for performing the given task will be:

SELECT h.hotel_id, h.hotel_name, COUNT(o.order_id) AS no_of_orders FROM hotel_details h

JOIN orders o ON o.hotel_id = h.hotel_id

GROUP BY h.hotel_id

HAVING no_of_orders >5

ORDER BY h.hotel_id ASC;

Explanation:

• SELECT command of SQL is used to select the subset of the given table. Here we want to display hotel id, hotel name and number of orders taken by the hotel. So they are specified in the select command.
• Furthermore, the question is asking for hotels that have taken more than 5 orders so we put a condition with HAVING clause to display only those hotels that match our criteria.
• Lastly data should be displayed in the ascending order based on hotel id that's why we use ORDER BY clause with ASC keyword that means ascending order.

#SPJ3