Create view vwemployeemanager select emp

-- ----------------------------------
-- DBS211 - Lab 04
-- Name: Sharon Ogadinma
-- StudentID: 145962163
-- Date: 8th October, 2020
-- ----------------------------------
SET AUTOCOMMIT ON;

/*Question 1a
Create a query that shows employee number, first name, last name, city, phone number
and postal code for all employees in France.

c. Make sure the date is displayed clearly to know what date it is. (i.e. what date is
02-04-19??? Feb 4, 2019, April 2, 2019, April 19, 2002, .) */
SELECT C.customernumber, C.customername, to_char(P.paymentdate, 'Month DD, YYYY') AS paymentdate, P.amount
FROM customers C INNER JOIN payments P ON C.customernumber = P.customernumber WHERE P.amount > 0 AND C.country = 'Canada' ORDER BY customernumber;

/*Question 3
Create a query that shows all USA customers who have not made a payment. Display only the customer number and customer name sorted by customer number. */ SELECT C.customernumber, C.customername
FROM customers C LEFT JOIN payments P ON C.customernumber = P.customernumber WHERE P.amount is null AND C.country = 'USA' ORDER BY C.customernumber;

SELECT vco.*, od.orderlinenumber
FROM vwCustomerOrder vco INNER JOIN orderdetails od
ON vco.ordernumber = od.ordernumber AND vco.productcode = od.productcode WHERE vco.customernumber = 124
ORDER BY vco.ordernumber, od.orderlinenumber;

/* Question 6
Create a query that displays the customer number, first name, last name, phone, and
credit limits for all customers who do not have any orders. */
SELECT C.customernumber, C.contactfirstname, C.contactlastname, C.phone, C.creditlimit
FROM customers C FULL OUTER JOIN orders O ON C.customernumber = O.customernumber WHERE O.ordernumber IS NULL;