SQL QUERIES.txt

1-	TOTAL PROFIT, COST AND NUMBER OF TICKETS FOR JANUARY

		SELECT SUM (extended_total_profit)     profit,
		       SUM (FLIGHT_COST)               tcost,
		       SUM (TOTAL_TICKETS)             t_ticket_number
		  FROM total_flight_fact
		 WHERE date_key < 20210201



2-	TOTAL SEATS UTILIZED FOR JANUARY

		SELECT (SUM (tf.TOTAL_TICKETS) / SUM (ff.seats_number))     AS utlization
		  FROM total_flight_fact tf, flight_fact ff
		 WHERE tf.flight_key = ff.flight_key AND tf.date_key < 20210201


3- 	SHOWING ALL INQUIRIES WITH THEIR TIME, STATUS AND SEVERITY LEVEL

		SELECT Pa.FIRST_NAME,
		       Pa.LAST_NAME,
		       Pa.MEMBERSHIP_STATUS,
		       S.STAFF_NAME,
		       S.ROLE,
		       I.INQUIRY_ID,
		       I.INTQUIRY_TIME,
		       I.INQUIRY_status,
		       I.SEVERITY
		  FROM REPORT_INQUIRY_fact
		       JOIN PASSANGER Pa USING (PASSENGER_KEY)
		       JOIN STAFF S USING (STAFF_KEY)
		       JOIN INQUIRIES I USING (INQUIRY_KEY)




4-	NUMBER OF TICKETS BOOKED FROM AGENCIES

		SELECT COUNT (passenger_Key)
		  FROM booking_fact
		 WHERE website_key IS NULL;



5-	NUMBER OF PASSENGERS THAT HAVE DISCOUNT LARGER THAN 50$

		SELECT COUNT (passenger_Key)
		  FROM booking_fact
		 WHERE promotional_discount > 50




6-	NUMBER OF FREQUENT FLYERS IN EACH MEMBERSHIP STATUS

		SELECT COUNT (FQ_Key), MEMBERSHIP_STATUS
		    FROM loyalty_fact
		         JOIN passanger USING (passenger_key)
		         JOIN frequent_flyer USING (fq_key)
		GROUP BY MEMBERSHIP_STATUS



7-	NUMBER OF INQUIRIES IN EACH PERIOD

		  SELECT I.INTQUIRY_TIME, COUNT (I.INQUIRY_ID)
		    FROM REPORT_INQUIRY_fact
		         JOIN PASSANGER Pa USING (PASSENGER_KEY)
		         JOIN INQUIRIES I USING (INQUIRY_KEY)
		GROUP BY I.INTQUIRY_TIME
		ORDER BY I.INTQUIRY_TIME


8-	DURATION OF FREQUENT FLYERS AT THE HOTELS FOR JANUARY

		SELECT FQ.FQ_ID,
		       H.HOTEL_NAME,
		       HS.RESERVATION_ID,
		       H.HOTEL_RATING,
		       ST.DURATION
		  FROM STAYING_FACT  ST
		       JOIN FREQUENT_FLYER FQ USING (FQ_KEY)
		       JOIN HOTEL H USING (HOTEL_KEY)
		       JOIN HOTEL_STAYING HS USING (HOTEL_STAYING_KEY)
		       JOIN DATE_dim D USING (DATE_KEY)
		 WHERE TO_CHAR (D.DATE_d, 'MON') = 'JAN';



9-	TOTAL DURATION SPENT BY EVERY FREQUENT FLYER

		  SELECT FQ.FQ_ID, SUM (ST.DURATION)
		    FROM STAYING_FACT ST
		         JOIN FREQUENT_FLYER FQ USING (FQ_KEY)
		         JOIN HOTEL_STAYING HS USING (HOTEL_STAYING_KEY)
		GROUP BY FQ.FQ_ID;


10-	HOTELS WITH HIGH DURATION FOR JANUARY

		  SELECT H.HOTEL_NAME, SUM (ST.DURATION)
		    FROM STAYING_FACT ST
		         JOIN FREQUENT_FLYER FQ USING (FQ_KEY)
		         JOIN HOTEL H USING (HOTEL_KEY)
		         JOIN HOTEL_STAYING HS USING (HOTEL_STAYING_KEY)
		         JOIN DATE_dim D USING (DATE_KEY)
		   WHERE TO_CHAR (D.DATE_d, 'MON') = 'JAN'
		GROUP BY H.HOTEL_NAME