CREATE [OR REPLACE] FUNCTION function_name  
   [ (parameter [,parameter]) ]  
RETURN return_datatype  
IS | AS  
 [declaration_section]  
BEGIN  
   executable_section  
[EXCEPTION  
   exception_section]  
END [function_name];  
--------------------------------------------------------------

create or replace function adder(n1 in number, n2 in number)    
return number    
is     
n3 number(8);    
begin    
n3 :=n1+n2;    
return n3;    
end;    


DECLARE    
   n3 number(2);    
BEGIN    
   n3 := adder(11,22);    
   dbms_output.put_line('Addition is: ' || n3);    
END;   

--------------------------------------------------
 
CREATE OR REPLACE FUNCTION totalCustomers  
RETURN number IS  
   total number(2) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM customers;  
    RETURN total;  
END; 

DECLARE  
   c number(2);  
BEGIN  
   c := totalCustomers();  
   dbms_output.put_line('Total no. of Customers: ' || c);  
END;


CREATE OR REPLACE FUNCTION AllBooking  
RETURN number IS  
   total number(2) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM Booking;  
    RETURN total;  
END; 


DECLARE  
   c number(2);  
BEGIN  
   c := AllBooking();  
   dbms_output.put_line('Total no. of Customers: ' || c);  
END;



Select * From Booking

CREATE OR REPLACE FUNCTION CustomerBooking(Customer in number)   
RETURN number IS  
   total number(2) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM Booking
   WHERE  CUSTOMER_ID = Customer;

    RETURN total;  
END; 


DECLARE  
   c number(2);  
BEGIN  
   c := CustomerBooking(1);  
   dbms_output.put_line('Total no. of Customers: ' || c);  
END;

Select CUSTOMER_ID,	
    NAME,
    PHONE,	
    EMAIL,
    ADDRESS,
    CustomerBooking(CUSTOMER_ID) AS BookingCount
FROM Customer
--WHERE CustomerBooking(CUSTOMER_ID) = 0
ORDER BY CustomerBooking(CUSTOMER_ID) DESC


CREATE OR REPLACE FUNCTION AllBooking  
RETURN number IS  
   total number(2) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM Booking;  
    RETURN total;  
END; 


DECLARE  
   c number(2);  
BEGIN  
   c := AllBooking();  
   dbms_output.put_line('Total no. of Customers: ' || c);  
END;
DECLARE  
   c number(2);  
BEGIN  
   c := AllBooking();  
   dbms_output.put_line('Total no. of Customers: ' || c);  
END;



Select * From Booking

Q1 : List the Services with the number of booking?
    
SELECT service_id, service_name
FROM Service
      
CREATE OR REPLACE FUNCTION ServicesBooking(serviceid in number)   
RETURN number IS  
   total number(2) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM Booking INNER JOIN Booking_Services ON Booking.book_id = Booking_Services.book_id
   WHERE  service_id = serviceid;

    RETURN total;  
END; 
     
SELECT service_id, service_name, ServicesBooking(service_id) AS SrvCnt
FROM Service

Q2: Show Customer name and sum of  his/her service costs

CREATE OR REPLACE FUNCTION Customercosts(Customer in number)   
RETURN number IS  
   total number(10) := 0;  
BEGIN  
   SELECT sum(price) into total  
   FROM Booking INNER JOIN Booking_Services ON Booking.book_id = Booking_Services.book_id
   WHERE  CUSTOMER_ID = Customer;

    RETURN total;  
END; 

Select CUSTOMER_ID,	
    NAME,
    PHONE,	
    EMAIL,
    ADDRESS,
    CustomerBooking(CUSTOMER_ID) AS BookingCount,
    Customercosts(CUSTOMER_ID) AS costs
FROM Customer
WHERE Customercosts(CUSTOMER_ID) > 200
ORDER BY CustomerBooking(CUSTOMER_ID) DESC

    
Q3 : List the booking which have more than 2 guests?


CREATE OR REPLACE FUNCTION BookingGuests(bookid in number)   
RETURN number IS  
   total number(5) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM  Guests
   WHERE  book_id  = bookid;

    RETURN total;  
END; 

Select BOOK_ID,	TYPE_ID,	ROOM_ID,	LOCATION_ID,
    CUSTOMER_ID,	CHECKIN_DATE,	CHECKOUT_DATE, BookingGuests(BOOK_ID) GuestCnt
From booking
ORDER BY BookingGuests(BOOK_ID)     

Q4 : List the customers with the number of their bookings?


CREATE OR REPLACE FUNCTION CustomerBooking(Customer in number)   
RETURN number IS  
   total number(2) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM Booking
   WHERE  CUSTOMER_ID = Customer;

    RETURN total;  
END; 


DECLARE  
   c number(2);  
BEGIN  
   c := CustomerBooking(1);  
   dbms_output.put_line('Total no. of Customers: ' || c);  
END;

Select CUSTOMER_ID,	
    NAME,
    PHONE,	
    EMAIL,
    ADDRESS,
    CustomerBooking(CUSTOMER_ID) AS BookingCount
FROM Customer
--WHERE CustomerBooking(CUSTOMER_ID) = 0
ORDER BY CustomerBooking(CUSTOMER_ID) DESC



DECLARE  
   c number(2);  
BEGIN  
   c := AllBooking();  
   dbms_output.put_line('Total no. of Customers: ' || c);  
END;


CREATE OR REPLACE FUNCTION AllBooking  
RETURN number IS  
   total number := 0;  
BEGIN  
   
    SELECT count(*) into total  
   FROM Booking; 

    RETURN total;  
END; 


DECLARE  
   c number(2);  
BEGIN  
   c := AllBooking();  
   dbms_output.put_line('Total no. of Customers: ' || c);  
END;



Select * From Booking

Q1 : List the Services with the number of booking?
    
SELECT service_id, service_name
FROM Service
      
CREATE OR REPLACE FUNCTION ServicesBooking(serviceid in number)   
RETURN number IS  
   total number(2) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM Booking INNER JOIN Booking_Services ON Booking.book_id = Booking_Services.book_id
   WHERE  service_id = serviceid;

    RETURN total;  
END; 
     
SELECT 
    service_id, 
    service_name, 
    ServicesBooking(service_id) AS SrvCnt
FROM Service

Q2: Show Customer name and sum of  his/her service costs

CREATE OR REPLACE FUNCTION Customercosts(Customer in number)   
RETURN number IS  
   total number(10) := 0;  
BEGIN  
   SELECT sum(price) into total  
   FROM Booking INNER JOIN Booking_Services ON Booking.book_id = Booking_Services.book_id
   WHERE  CUSTOMER_ID = Customer;

    RETURN total;  
END; 

Select CUSTOMER_ID,	
    NAME,
    PHONE,	
    EMAIL,
    ADDRESS,
    CustomerBooking(CUSTOMER_ID) AS BookingCount,
    Customercosts(CUSTOMER_ID) AS costs
FROM Customer
--WHERE Customercosts(CUSTOMER_ID) > 200
ORDER BY CustomerBooking(CUSTOMER_ID) DESC

    
Q3 : List the booking which have more than 2 guests?


CREATE OR REPLACE FUNCTION BookingGuests(bookid in number)   
RETURN number IS  
   total number(5) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM  Guests
   WHERE  book_id  = bookid;

    RETURN total;  
END; 

Select BOOK_ID,	TYPE_ID,	ROOM_ID,	LOCATION_ID,
    CUSTOMER_ID,	CHECKIN_DATE,	CHECKOUT_DATE, 
    BookingGuests(BOOK_ID) GuestCnt
From booking
ORDER BY BookingGuests(BOOK_ID)     

Q4 : List the customers with the number of their bookings?


CREATE OR REPLACE FUNCTION CustomerBooking(Customer in number)   
RETURN number IS  
   total number(2) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM Booking
   WHERE  CUSTOMER_ID = Customer;

    RETURN total;  
END; 


DECLARE  
   c number(2);  
BEGIN  
   c := CustomerBooking(1);  
   dbms_output.put_line('Total no. of Customers: ' || c);  
END;

Select CUSTOMER_ID,	
    NAME,
    PHONE,	
    EMAIL,
    ADDRESS,
    CustomerBooking(CUSTOMER_ID) AS BookingCount
FROM Customer
--WHERE CustomerBooking(CUSTOMER_ID) = 0
ORDER BY CustomerBooking(CUSTOMER_ID) DESC