CST2355 – DATABASE SYSTEM ASSIGNMNET 1

Approach:
Van Whinkle owns multiple hotels. I was given the data of a single hotel owned by the
company Van Whinkle. Assuming the current year 2020 as the year to which this data
belongs to. I analyzed each data in the following approach:

1. Bob Smith is the manager of the hotel with 20 years’ experience. He makes
$80,000 per year and lives in Windsor Ontario. His manager is Deena Donor.
 Employee name: Bob Smith
 Employee type: manager
 Experience: 20 years
 Salary per year: $80,000
 Address: Windsor Ontario
 Manager: Deena Donor

2. Bob Smith is the desk clerk with only 5 years of experience at the hotel. He
makes $15 per hour and lives in Toronto Ontario. His manager is the other Bob
Smith.
 Employee name: Bob Smith
 Employee type: desk clerk
 Experience: 5 years
 Salary per hour: $15
 Address: Toronto Ontario
 Manager: Bob Smith

3. Tanya Duncan does maintenance and has been with the hotel for 10 years. She
makes $5,000 per month and lives in London Ontario. Her manager is Bob
Smith.
 Employee name: Tanya Duncan
 Employee type: maintenance
 Experience: 10 years
 Salary per month: $5000
 Address: London Ontario
  Manager: Bob Smith

4. Kristoff Kurn was a customer that reserved a room Jan 3rd and checked in on
Feb 3rd . She checked out on Feb 17th. She paid a deposit of $50 and her room
rate per day was $50. She was a smoker so she was charged $25 for smoke
damage and $30 for the carpet burns. The remainder of her deposit was
returned. She lives in Vancouver, British Columbia.
 First name: Kristoff
 Last name: Kurn
 Reversed room date: 3 Jan 2020
 Check in date: 3 Feb 2020
 Check out date: 17 Feb 2020
 Charges: $50
 Other charges (smoking + carpet burns): $55
 Address: Vancouver, British Columbia

5. Billy Elliot was a customer that reserved a room online the same day he
checked in. He checked in on Jan 20th at 4pm. He is a student at Queens
University and paid the student discount rate of $30 per day. Because he is a
loyal customer he didn’t need to pay a deposit. He lives in Toronto. He used to
live in Winnipeg Manitoba.
 First name: Billy
 Last name: Elliot
 Reversed room date: 20 Jan 2020
 Check in date: 20 Jan 2020
 Check out date: -
 Charges: $30
 Other charges: -
 Address: Toronto

6. Justin Hackman is in sales and travels back and forth from Detroit and Toronto.
Because of his frequent stays at the hotel he doesn’t need a reservation and
pays a discount rate of $35 per day and a deposit of $20 because he is a smoker.
He lives in Woodstock and stayed at the hotel overnight on Feb 2nd, 8th, 17th,
and 28th .
 First name: Justin
 Last name: Hackman
  Reversed room date: -
 Check in date: 2 Feb 2020, 8 Feb 2020, 17 Feb 2020, 28 Feb 2020
 Check out date: 2 Feb 2020, 8 Feb 2020, 17 Feb 2020, 28 Feb 2020
 Charges: $35 per day
 Other charges (smoking): - $20 per day
 Address: Woodstock

7. Deena Donor is the operation manager of the hotel. She oversees the hotel by
coming, in person on the 1st of every month. She has been doing this from
January through to April. Because of her position she is not charged a deposit or
room rate. When she does checkout the desk clerk sends the invoice directly to
Van Winkle for reimbursement.
 First name: Deena
 Last name: Donor
 Reversed room date: -
 Check in date: 1 Jan 2020, 1 Feb 2020, 1 Mar 2020, 1 April 2020
 Check out date: 1 Jan 2020, 1 Feb 2020, 1 Mar 2020, 1 April 2020
 Charges: -
 Other charges: - -
 Address: -

 Employee name: Deena Donor

 Employee type: operational manager
 Experience: -
 Salary per month: -
 Address: -
 Manager: -

Looking at the general attributes of the data above. I envisioned about two tables.
1. Employee:
 Employee_id (PK)
 Employee_name
 Employee_salary
 Employee_experience
 Employee_manager
 Employee_type
2. Customer: -
  Customer_id (PK)
 Customer_first_name
 Customer_last_name
 Customer_address
 Room_reserved_date
 Check_in_date
 Check_out_date
 Charges
 Other_charges
Now we see that there are three employees, salary per , salary per month, salary per
hours. So I converted their salaries
Presently we see that there are three employee, one's compensation is given each
year, one's compensation is given each month and one's compensation is given each
hour. Thus, I changed over their pay rates into each month. Accepting that employee
work 9am – 5pm at inn from Monday – Friday. The changed over pay rates are as
per the following:

- Bob smith : 80000 / 12 = $6666.667 per month

- Bob smith: (15 * 8) = $120 per day

(120 * 5) = $600 per

week (600 * 4) =

$2400 per month

- Tanya Duncan: $5000 per month

Presently, as I see that there can be numerous work area agents, various upkeep
people in inn. Thus, I wanted to make a different table for representative sorts.
Presently the tables are as per the following:

1. Employee_type:
 Id (PK)
 Type

2. Employee:
 Employee_id (PK)
 Employee_name
 Employee_salary
  Employee_experience
 Employee_manager
 Employee_type_id (FK)

3. Customer:
 Customer_id (PK)
 Customer_first_name
 Customer_last_name
 Customer_address
 Room_reserved_date
 Check_in_date
 Check_out_date
 Charges
 Other_charges

Presently I feel that customer’s very own information and customer’s Hotel subtleties
ought to be isolated as they are unimportant to be at one spot. So now my table
constructions becomes:

1. Employee_type:
- Id (PK)
- Type

2. Employee:
- Employee_id (PK)
- Employee_name
- Employee_salary
- Employee_experience
- Employee_manager
- Employee_type_id (FK)

3. Customer:
- Customer_id (PK)
- Customer_first_name
- Customer_last_name
- Customer_address

4. Customer_room_details:
- Id (PK)
- Room_reserved_date
 - Check_in_date
- Check_out_date
- Charges
- Other_charges
- Customer_id (FK)

After reaching at this point, I heard that my owner plans on purchasing golf
courses, casinos, and day spas in the near future, all of which will carry the
Van Whinkle brand name. So, I decided to make a table which contains all
the services Van Whinkle has. Van Whinkle may have services like: Hotels,
Casinos, Gold courses and Day spas. I thought to make a table which lists the
services and another table which contains description of the services Van
Whinkle has. I finalized these two additional table:
1. Service_type:
- Id (PK)
- service_type
2. Services:
- Id (PK)
- Service_name
- Service_type (FK)

Finalize tables are as follows

 Assumption:

After the formation of tables from the information. I was given inquiries to make
from the tables. I examined the questions as follows:

1. For each line recovered, show all clients that have a clear in at any rate one of its
segments. Sort from Z to An on last name, at that point Z to An on first name.
 This query needs all customers, which have in any
event one missing information. There are two
tables which contains customer details: customer
and customer_service_details. To get customers
all information, we apply a join between these
two tables which presents customers all
information. And afterward we can sift through
the customers who have in any event one missing
field. Furthermore, toward the end, we sort the
sifted through information in slipping request
dependent on last name. And afterward on first
name.

 By seeing this inquiry, I changed the customers

table and changed over customer_name into
two sections customer_first_name and
customer_last_name

2. For each line recovered, show the customer name and number of days remained
at the hotel, for at any rate 1 day, 3 days and at most 4 days.
This query needs all customers’ name and the number of days the
customers stayed at the hotel. From this data, we will filter out the
customers who stayed at hotel for 1, 3 or 4 days. We will apply join
between customer and customer_service_details, so that we get
customer’s name from customer table and customer’s stay at hotel
from difference between check out date and check in date
multiplied with sum of room charges and extra charges from
customer_service_details.
3. For each row retrieved, show the customer name, each expense, and
their total expenses when total expenses (including room rate) are
larger than $50. Sort by highest expenses to lowest.

 This query needs all customers’ name, charges, extra charges and
total charges (room charges + extra charges) , whose total charges
are greater than 50$. We will apply join between customer and
customer_service_details, so that we get customer’s name from
customer table and customer’s room expense (number of days
stayed X room charges ), customers’s extra charges (number of
days stayed X extra charges ) and total expenses from
customer_service_details. And then we will check which customer
have total expenses more than $50. Then we will sort the filtered
data in descending order.
4. For each row retrieved, show the customer name, each expense, and
their total expenses when total expenses (excluding room rate) are
larger than $50. Sort by highest total expenses to lowest.

 This query needs all customers’ name, charges, extra charges

and total charges (room charges + extra charges) , whose total
charges excluding room charges are greater than 50$. We will
apply join between customer and customer_service_details,
so that we get customer’s name from customer table and
customer’s room expense (number of days stayed X room
charges), customers’s extra charges (number of days stayed X
extra charges ) and total expenses from
customer_service_details. And then we will check which
customer have total expenses more than $50. Then we will
sort the filtered data in descending order.

5. For each row retrieved, show the employee’s name, their city, their
manager, and their manager’s city.
  This query needs name and city of all employees along with
their manager’s name and city. We will apply a self join
between the table of employee to get results. There is one
employee who doesn’t have a manager, the query will not
display data of that employee.

6. For each row retrieved, show which employees or customers are from
London and Winnipeg without using a table to store the city names

 This query needs to display data of employees and customers

who are from London or Winnipeg, using the
employee_address column of employees table.

7. For each row retrieved, show which employees and customers are from
London and Winnipeg using a table that stores the city names

 This query needs to display data of employees and customers

who are from London or Winnipeg, using another table cities
which contains name of all citi from which employees belong.
We will filter out the cities London and Winnipeg from cities
table and check which employees have address of London and
Winnipeg.

8. For each row retrieved, show which employees are also customers

 This query will display the data of all employees which

are customers, by matching the employee’s names
with customer’s names.

9. For each row retrieved, show all employees data and their corresponding
customer data for employees that have last names starting with D or
have an M in it.
 This query will displays all employees who are customers,
and their last names starts with D or have letter m in it
Differences:

MYSQL:

a. In MS access, we have to use an attribute value on the foreign key

column when using inner join, where as in MYSQL, we use alias
of the tables or the table name only for pointing the foreign key
column.

b. There is a difference in the function datediff, in MS Access the

function is DateDiff("d","date1","date2"): first argument
represents day to calculate the difference in form of days. Whereas
in MYSQL the function is DATEDIFF("date2", "date1"). Also there is
difference in the sequence of arguments i.e. two dates passed to
function.

c. When using like operator in MS ACCESS, * represents more

than one characters. In MYSQL, % represents more than one
characters.

d. In MS ACCESS, ‘&’ operator is used to concatenate strings.

Whereas in MYSQL there is a function CONCAT(str1, str2…) to
concatenate strings.

SQL Server:

a. In MS access, we have to use an attribute value on the foreign key

column when using inner join, where as in SQL Server, we use alias
of the tables or the table name only for pointing the foreign key
column.

b. There is a difference in the function datediff, in MS Access the

function is DateDiff("d","date1","date2"): first argument
represents day to calculate the difference in form of days. Whereas
in SQL SERVER the function is DATEDIFF(day, “date1”, “date2”):
first argument is a keyword day which represents calculating
 difference in days.

c. When using like operator in MS ACCESS, * represents more than

one characters. In SQL Server, % represents more than one
characters.

d. In MS ACCESS, ‘&’ operator is used to concatenate strings.

Whereas in SQL Server there is a function CONCAT(str1, str2…) to
concatenate strings.

Oracle:

a. In MS access, we have to use an attribute value on the foreign key

column when using inner join, where as in Oracle, we use alias of
the tables or the table name only for pointing the foreign key
column.

b. There is a difference in the function datediff, in MS Access the

function is DateDiff("d","date1","date2"): first argument
represents day to calculate the difference in form of days. Whereas
in Oracle there is no function datediff , but rather we can use
TO_DATE(“date”, 'YYYY-MM-DD') to convert string to a date and
then by the minus (“-“) operator, we can calculate difference in
form of days.

c. When using like operator in MS ACCESS, * represents more

than one characters. In Oracle, % represents more than one
characters.

d. In MS ACCESS, ‘&’ operator is used to concatenate strings.

Whereas in Oracle there is a function CONCAT(str1, str2…) to
concatenate strings
 Queries:
Microsoft Access:
The database is developed in Microsoft Access 2013.

Visual data model:

Tables:

1. Employee
2. Employee Type

3. Customer

4. Customer_service_details
5. Service

6. Service Type
 7. Cities

Queries:

2. For each row retrieved, show all customers that have a blank in at least
one of its columns. Sort from Z to A on last name, then Z to A on first
name.

SELECT *
FROM customer INNER JOIN customer_service_details
ON customer.customer_id = customer_service_details.customer_id.Value
WHERE (((customer.customer_address) Is Null)) OR
(((customer_service_details.reserved_date) Is Null)) OR
(((customer_service_details.check_in_date) Is Null)) OR
(((customer_service_details.check_out_date) Is Null)) OR
(((customer_service_details.charges) Is Null)) OR
(((customer_service_details.other_charges) Is Null))
 ORDER BY customer.customer_last_name DESC ,
customer.customer_first_name DESC;

3. For each row retrieved, show the customer name and number of days
stayed at the hotel, for at least 1 day, 3 days and at most 4 days.

SELECT customer.customer_first_name,
customer.customer_last_name,
DateDiff("d",check_out_date,check_in_date) AS
number_of_days_stayed FROM customer INNER JOIN
customer_service_details
ON customer.customer_id =
customer_service_details.customer_id.Value WHERE
DateDiff("d",check_out_date,check_in_date) = 1 OR
DateDiff("d",check_out_date,check_in_
date) = 3 OR
DateDiff("d",check_out_date,check_in_
date) = 4;

4. For each row retrieved, show the customer name, each expense, and
their total expenses when total expenses (including room rate) are
larger than $50. Sort by highest expenses to lowest.

SELECT customer_first_name, customer_last_name,charges *

DateDiff("d",check_in_date,check_out_date) AS room_expenses,
other_charges * DateDiff("d",check_in_date,check_out_date) AS
other_expenses, ((charges + other_charges) *
DateDiff("d",check_in_date,check_out_date)) AS total_expenses FROM
customer INNER JOIN customer_service_details
ON customer.customer_id = customer_service_details.customer_id.Value
WHERE ((charges
+other_charges)*DateDiff("d",che
 ck_in_date,check_out_date)) > 50
ORDER BY ((charges +
other_charges) *
DateDiff("d",check_in_date,check
_out_date)) DESC;

5. For each row retrieved, show the customer name, each expense, and
their total expenses when total expenses (excluding room rate) are
larger than $50. Sort by highest total expenses to lowest.

SELECT customer_first_name,
customer_last_name,

charges * DateDiff("d",check_in_date,check_out_date) AS
room_expenses, other_charges *
DateDiff("d",check_in_date,check_out_date) AS other_expenses,

((other_charges +charges) * DateDiff("d",check_in_date,check_out_date))

AS total_expenses

FROM customer INNER JOIN customer_service_details

ON customer.customer_id =
customer_service_details.customer_id.Value WHERE
((other_charges * DateDiff("d",check_in_date,check_out_date)))
> 50

ORDER BY ((other_charges * DateDiff("d",check_in_date,check_out_date)))

DESC;

6. For each row retrieved, show the employee’s name, their city, their
 manager, and their manager’s city.

SELECT employee.employee_name,
employee.employee_address,

employee_1.employee_name AS manager_name,
employee_1.employee_address AS manager_address

FROM employee AS employee_1 INNER JOIN

employee ON employee_1.ID =
employee.manager_id.Value;

7. For each row retrieved, show which employees or customers are from
London and Winnipeg without using a table to store the city names

select employee_name as
person_name,
employee_address as
person_address
from employee
where employee_address like
'*London*' or
employee_address like
'*Winnipeg*'
UNION
select customer_first_name as
person_name,
customer_address as
person_address
from customer
 where customer_address like
'*London*' or
customer_address like
'*Winnipeg*';

8. For each row retrieved, show which employees and customers are from
London and Winnipeg using a table that stores the city names

SELECT employee_name AS person_name,

employee_address AS
person_address, city_name

FROM employee, cities

WHERE (city_name = 'London' or city_name =
'Winnipeg' ) and

employee_address like '*' & city_name & '*';

9. For each row retrieved, show which employees are also customers

SELECT
employee.employee_name
FROM employee, customer
WHERE [employee.employee_name]
LIKE "*" & customer.customer_first_name & "*";

10. For each row retrieved, show all employees data and their corresponding
customer data for employees that have last names starting with D or
have an M in it.

SELECT
 employee.employee_name,
reserved_date,
check_in_date,
check_out_date, charges,
other_charges
FROM employee, customer INNER JOIN customer_service_details
ON customer.customer_id =
customer_service_details.customer_id.value WHERE
[employee.employee_name] LIKE
customer.customer_first_name & "*"
AND( [customer.customer_last_name] like 'D*' or
[customer.customer_last_name] like '*m*');
 SQL SERVER

Visual data model:

Tables:
1. Employee
2. Employee type

3. Customer
4. Customer Service detail

5. Service type
6. Service

7. Cities
 Queries:

1. For each row retrieved, show all customers that have a blank in at
least one of its columns. Sort from Z to A on last name, then Z to A on
first name.

select *
from customer inner join
customer_service_details csd on customer.id =
csd.customer_id

where customer_address is null or reserved_date is null or check_in_date is null

or check_out_date is null or charges is null or other_charges is null

order by customer_last_name desc , customer_first_name desc;

2. For each row retrieved, show the customer name and number of days
stayed at the hotel, for at least 1 day, 3 days and at most 4 days.

select customer_first_name,
customer_last_name,
DATEDIFF(day, check_in_date, check_out_date) as
number_of_days_stayed from customer inner join
customer_service_details csd
on customer.id = csd.customer_id
where DATEDIFF(day, check_in_date,
check_out_date) = 1 or DATEDIFF(day,
check_in_date, check_out_date) = 3
or DATEDIFF(day, check_in_date, check_out_date) = 4;

3. For each row retrieved, show the customer name, each expense, and
their total expenses when total expenses (including room rate) are
larger than $50. Sort by highest expenses to lowest.

select customer_first_name,
customer_last_name,
charges * DATEDIFF(day, check_in_date, check_out_date) as
room_expenses, other_charges * DATEDIFF(day, check_in_date,
check_out_date) as other_expenses, ((charges + other_charges
)* DATEDIFF(day, check_in_date, check_out_date)) as
total_expenses
from customer inner join
customer_service_details csd on
customer.id = csd.customer_id
 where ((charges + other_charges )* DATEDIFF(day, check_in_date,
check_out_date)) > 50 order by ((charges + other_charges )*
DATEDIFF(day, check_in_date, check_out_date)) desc;

4. For each row retrieved, show the customer name, each expense, and
their total expenses when total expenses (excluding room rate) are
larger than $50. Sort by highest total expenses to lowest.

select customer_first_name,
customer_last_name,
charges * DATEDIFF(day, check_in_date, check_out_date) as
room_expenses, other_charges * DATEDIFF(day, check_in_date,
check_out_date) as other_expenses, ((charges +
other_charges)* DATEDIFF(day, check_in_date,
check_out_date)) as total_expenses
from customer inner join
customer_service_details csd on
customer.id = csd.customer_id
where ((charges )* DATEDIFF(day, check_in_date,
check_out_date)) > 50 order by ((charges )*
DATEDIFF(day, check_in_date, check_out_date)) desc
;

5. For each row retrieved, show the employee’s name, their city, their
manager, and their manager’s city.

select e.employee_name,
e.employee_address,
e1.employee_name as
manager_name,
 e1.employee_address as
manager_city
from employee e1 inner join
employee e on e1.employee_id
= e.manager_id;
6. For each row retrieved, show which employees or customers are from
London and Winnipeg without using a table to store the city names

select employee_name as
person_name,
employee_address as
person_address
from employee
where employee_address like '%London%'
or employee_address like
'%Winnipeg%' UNION
select customer_first_name as
person_name,
customer_address as
person_address
from customer
where customer_address like '%London%'
or customer_address like '%Winnipeg%';

7. For each row retrieved, show which employees and customers are from
London and Winnipeg using a table that stores the city names

select employee_name as
person_name, employee_address as
person_address
from employee
where
employee_address like
concat ('%', (select cities.city_name from cities where cities.city_name =
'London'), '%')
or
employee_address like
concat ('%', (select cities.city_name from cities where
cities.city_name = 'Winnipeg'), '%') union
select concat(customer_first_name,
customer_last_name) as person_name,
customer_address as person_address
from customer
where
customer_address like
concat ('%', (select cities.city_name from cities where cities.city_name =
'London'), '%')
or
customer_address like
concat ('%', (select cities.city_name from cities where cities.city_name =
'Winnipeg'), '%')
8. For each row retrieved, show which employees are also customers

select

employee_name

from employee ,

customer

where employee_name like CONCAT(customer_first_name, '%');

9. For each row retrieved, show all employees data and their
corresponding customer data for employees that have last names
starting with D or have an M in it.

select

employee_nam

e,

reserved_date,

check_in_date,

check_out_dat

e, charges,

other_charges

from employee, customer_service_details inner

join customer c on

customer_service_details.customer_id = c.id
 where employee_name like CONCAT(customer_first_name, '%')
and( customer_last_name like 'D%' or customer_last_name like '%m%');

Oracle
1. Employee

Data:

2. Employee_type
Data:

3. Customer

Data:
 4. Customer_service_detail

Data:
 5. Service

Data:

6. Service_type
Data:

7. Cities

Data:

Queries:
1. For each row retrieved, show all customers that have a blank in at
 least one of its columns. Sort from Z to A on last name, then Z to A on
first name.

select *
from customer inner join
customer_service_details csd on customer.id =
csd.customer_id

where customer_address is null or reserved_date is null or check_in_date is null

or check_out_date is null or charges is null or other_charges is null

order by customer_last_name desc , customer_first_name desc;

2. For each row retrieved, show the customer name and number of days
stayed at the hotel, for at least 1 day, 3 days and at most 4 days.

select customer_first_name,
customer_last_name,

TO_DATE(check_out_date, 'YYYY-MM-DD') - TO_DATE(check_in_date, 'YYYY-

MM-
DD') as number_of_days_stayed
from customer inner join
customer_service_details csd on customer.id =
csd.customer_id

where TO_DATE(check_out_date, 'YYYY-MM-DD') - TO_DATE(check_in_date,

'YYYY-
MM-DD') = 1
or TO_DATE(check_out_date, 'YYYY-MM-DD') - TO_DATE(check_in_date, 'YYYY-
MM-
DD') = 3
 or TO_DATE(check_out_date, 'YYYY-MM-DD') - TO_DATE(check_in_date,
'YYYY-MM- DD') = 4;

3. For each row retrieved, show the customer name, each expense, and
their total expenses when total expenses (including room rate) are
larger than $50. Sort by highest expenses to lowest.

select customer_first_name,
customer_last_name,

charges * (TO_DATE(check_out_date, 'YYYY-MM-DD') -

TO_DATE(check_in_date, 'YYYY-MM-DD')) as room_expenses,
other_charges * (TO_DATE(check_out_date, 'YYYY-MM-DD') -
TO_DATE(check_in_date, 'YYYY-MM-DD')) as other_expenses,

((charges + other_charges )* (TO_DATE(check_out_date, 'YYYY-MM-DD') -

TO_DATE(check_in_date, 'YYYY-MM-DD')))
as total_expenses

from customer inner join

customer_service_details csd on customer.id =
csd.customer_id

where ((charges + other_charges )* (TO_DATE(check_out_date, 'YYYY-MM-DD') -

TO_DATE(check_in_date, 'YYYY-MM-DD'))) > 50
order by ((charges + other_charges )* (TO_DATE(check_out_date, 'YYYY-MM-
DD') - TO_DATE(check_in_date, 'YYYY-MM-DD'))) desc;

4. For each row retrieved, show the customer name, each expense, and
their total expenses when total expenses (excluding room rate) are
larger than $50. Sort by highest total expenses to lowest.

select customer_first_name,
customer_last_name,
 charges * (TO_DATE(check_out_date, 'YYYY-MM-DD') -
TO_DATE(check_in_date, 'YYYY-MM-DD')) as room_expenses,
other_charges * (TO_DATE(check_out_date,
'YYYY-MM-DD') - TO_DATE(check_in_date, 'YYYY-
MM-DD')) as other_expenses,
((charges + other_charges )* (TO_DATE(check_out_date, 'YYYY-MM-
DD') -
TO_DATE(check_in_date, 'YYYY-
MM-DD'))) as total_expenses
from customer inner join
customer_service_details csd on
customer.id = csd.customer_id
where ((charges)* (TO_DATE(check_out_date, 'YYYY-MM-DD') -
TO_DATE(check_in_date, 'YYYY-MM-DD'))) > 50
order by ((charges )* (TO_DATE(check_out_date, 'YYYY-MM-DD') -
TO_DATE(check_in_date, 'YYYY-MM-DD'))) desc;

5. For each row retrieved, show the employee’s name, their city, their
manager, and their manager’s city.

select e.employee_name,
 e.employee_address,
e1.employee_name as
manager_name,
e1.employee_address as
manager_city

from employee e1 inner join

employee e on e1.employee_id =
e.manager_id;

6. For each row retrieved, show which employees or customers are from
London and Winnipeg without using a table to store the city names

select employee_name as
person_name,
employee_address as
person_address
from employee
where employee_address like '%London%'
or employee_address like
'%Winnipeg%' UNION
select customer_first_name as
person_name,
customer_address as
person_address
from customer
where customer_address like '%London%'
or customer_address like '%Winnipeg%';
 7. For each row retrieved, show which employees and customers are from
London and Winnipeg using a table that stores the city names

select employee_name as
person_name,
employee_address as
person_address
from employee
where
employee_address like
concat ((select cities.city_name from cities where
cities.city_name = 'London' and ROWNUM = 1), '%') or
employee_address like
concat ((select cities.city_name from cities where
cities.city_name = 'Winnipeg' and ROWNUM = 1), '%')union

select concat(customer_first_name, customer_last_name) as

person_name, customer_address as person_address
from customer
where
customer_address like
concat ((select cities.city_name from cities where cities.city_name
= 'London' and ROWNUM = 1), '%') or
customer_address like
concat ((select cities.city_name from cities where
cities.city_name = 'Winnipeg' and ROWNUM = 1), '%');

8. For each row retrieved, show which employees are also

 customers select employee_name
from employee , customer
where employee_name like CONCAT(customer_first_name, '%');

9. For each row retrieved, show all employees data and their
corresponding customer data for employees that have last names
starting with D or have an M in it.

select

employee_na

me,

reserved_date

check_in_date

check_out_dat

e, charges,

other_charges

from employee, customer_service_details inner

join customer c on

customer_service_details.customer_id = c.id

where employee_name like CONCAT(customer_first_name, '%')

and( customer_last_name like 'D%' or customer_last_name like '%m%');