Using VIEW functions to CREAT VIEW 2018-09-13 1. Create a view that lists the gu

Question

Using VIEW functions to CREAT VIEW

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

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

Explanation / Answer

--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;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.