Question

Using VIEW functions to CREAT VIEW

reservation_id	last_name	first_name	room_number	check_in_date	check_out_date
6	Gamgee	Sam	102	2018-04-06	2018-04-08
7	Katniss	Everdeen	201	2018-04-10	2018-04-12
3	Granger	Hermoine	201	2018-05-01	2018-05-08
4	Weasley	Ron	201	2018-05-01	2018-05-08
1	Potter	Harry	301	2018-05-01	2018-05-08
2	Potter	Harry	201	2018-09-10	2018-09-13

1. Create a view that lists the guests who have reservations that begin on a Monday, Tuesday, Wednesday, or Thursday. 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., 05/01/2018)

• the day of the week spelled out fully (e.g., Monday)

Use aliases to provide descriptive and user-friendly column names. Sort the results by 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 week, using DATE_FORMAT code %w (instead of %W).

2. Create a view that lists guests whose reservation is longer than 3 days. Include the following columsn:

• a concatenated version of the guest’s name (e.g., Granger, Hermoine)

• the check-in date, formatted as mm/dd/yyyy (e.g., 05/01/2018)

• the check-out date, formatted as mm/dd/yyyy (e.g., 05/05/2018)

• the number of days for the reservation

Use aliases to provide descriptive and user-friendly column names. Sort the results by descending reservation length, then by last name, first name.

DATA BELOW

Answer 1

--MySql

--1

CREATE VIEW V_Guest_1

AS

SELECT CONCAT(last_name,',',first_name) AS Guest_Name,

DATE_FORMAT(check_in_date,'%m/%d/%Y')AS check_in_date,

DATE_FORMAT(check_in_date,%W) AS 'Day Name'

FROM view_name/*U need to give view namae same like table name*/

ORDER BY DATE_FORMAT(check_in_date,%w),last_name,first_name;

--2

CREATE VIEW V_Guest_2

AS

SELECT (last_name+' '+first_name) AS Guest_Name,

DATE_FORMAT(check_in_date,'%m/%d/%Y') AS check_in_date,

DATE_FORMAT(check_out_date,'%m/%d/%Y') AS check_out_date,

DATEDIFF(check_out_date, check_in_date) AS Days_Reserved

FROM view_name/*U need to give view namae same like table name*/

ORDER BY DATEDIFF(check_out_date, check_in_date),last_name,first_name;