Question

Create a view that lists the guests who have weekend reservations that include a Saturday night stay and ends before the following Monday. Include the following columns: •

Q.1) (20 points) Create a view that lists the guests who have weekend reservations that include a Saturday night stay and ends before the following Monday. Include the following columns: a concatenated version of the guest's name (e.g., Granger, Hermoine) the check-in date, formatted as mm/dd/yyyy (e.g., 03/02/2018) the check-in day of the week spelled out fully (e.g., Friday) the check-out date, formatted as mm/dd/yyyy (e.g., 03/04/2018) the check-out day of the week spelled out fully (e.g., Sunday) Use aliases to provide descriptive and user-friendly column names. Sort the results by check-in date, check-in day of week, last name, first name Hint: when sorting by day of week you don't want to sort alphabetically; instead you want to sort by the numeric position of the day of the week, using DATE_FORMAT code %w (instead of %W)

Programming Language to use is SQL**

Answer 1

Lets assume all this data comes from a single table called CUSTOMER. The SQL to create view would look like

CREATE VIEW V_WEEKEND_RESERVE                                                        
AS
   SELECT CONCAT(c.lastname, ',', c.firstname) AS CUSTNAME ,             
           CONVERT(VARCHAR(10), CAST(c.cin_date AS CINDATE), 101)
           DATENAME(weekday, @c.cin_date) AS CINDAY
           CONVERT(VARCHAR(10), CAST(c.cout_date AS COUTDATE), 101)
           DATENAME(weekday, @c.cout_date) AS COUTDAY
   FROM Customer C
   WHERE CINDAY = 'Saturday'
   AND COUTDAY = 'Monday'
   ORDER BY mod(to_char(c.cin_date, 'D') + 5, 7)