SQL Join and Sum Function Code Example

 --SQL Join and Sum Function Code Example

                        

 --#1                                                             
 SELECT SELECTEDITEM.ROOMID, MENUITEM.ITEMNO,                     
  (SELECTEDITEM.UNIT_COUNT*MENUITEM.EST_SERVINGS) AS TOTALSERVINGS,
  SELECTEDITEM.DELIVERY_TIME,                                     
  RESERVATION.START_TIME, RESERVATION.EVENTID                     
  FROM MENUITEM                                                   
  JOIN SELECTEDITEM                                               
  ON MENUITEM.ITEMNO = SELECTEDITEM.ITEMNO                        
  JOIN RESERVATION                                                
  ON SELECTEDITEM.EVENTID = RESERVATION.EVENTID                   
  AND                                                             
  SELECTEDITEM.ROOMID = RESERVATION.ROOMID                        
  WHERE RESERVATION.EVENTID = 2201                                
  ORDER BY SELECTEDITEM.DELIVERY_TIME, RESERVATION.ROOMID,        
  MENUITEM.ITEMNO;                                                
 --#2          

  SELECT ROOM.ROOMID, RESERVATION.EVENTID, RESERVATION.EST_ATTENDANCE,
 ROOM.CAPACITY, (100*RESERVATION.EST_ATTENDANCE)/ROOM.CAPACITY       
 AS PCAPACITY                                                        
 FROM ROOM                                                           
 JOIN RESERVATION                                                    
 ON ROOM.ROOMID = RESERVATION.ROOMID                                 
 WHERE (100*RESERVATION.EST_ATTENDANCE)/ROOM.CAPACITY >=80;          
 --#3                                                                
   SELECT MENUITEM.ITEMNO, MENUITEM.DESCRIPTION, MENUITEM.UNIT_PRICE,
   SELECTEDITEM.EVENTID, SELECTEDITEM.ROOMID, SELECTEDITEM.UNIT_COUNT,
  (MENUITEM.UNIT_PRICE*SELECTEDITEM.UNIT_COUNT) AS EXTENDCOST        
   FROM MENUITEM LEFT JOIN SELECTEDITEM ON MENUITEM.ITEMNO =         
   SELECTEDITEM.ITEMNO                                               
   WHERE MENUITEM.UNIT_PRICE >= 25;                                  
 --#4                                                                
 SELECT AVG(UNIT_PRICE / EST_SERVINGS) AS AVGPRICE                   
   FROM MENUITEM;                                                    
 --#5                                                      

 SELECT MAX(RESERVATION.EST_ATTENDANCE), MIN(RESERVATION.EST_ATTENDANCE)
  FROM EVENT                                                          
  JOIN RESERVATION                                                    
  ON EVENT.EVENTID = RESERVATION.EVENTID                              
  WHERE EVENT.EVENT_DATE BETWEEN '8/31/2005' AND '11/1/2005';         
--#6                                                                  
SELECT EVENTID,SUM(HRS_USED) AS SUMHRS , AVG(EST_ATTENDANCE)          
  AS ESTATTENDANCE                                                    
  FROM RESERVATION                                                    
  GROUP BY EVENTID;