sqlh18.3

-- Opdracht 1

/* Maak van de onderstaande SQL instructie een view en noem deze OrderList. Toon vervolgens alle rows. */

CREATE VIEW OrderList AS
SELECT C.cust_name, I.order_num, I.prod_id, I.quantity, I.item_price
FROM Customers AS C, Orders as O, OrderItems as I
WHERE C.cust_id = O.cust_id AND O.order_num = I.order_num;

SELECT * FROM 
OrderList;

-- Opdracht 2

/* Breid opdracht 1 uit met een expanded_price (quantity * item_price) en noem de view OrderListExpanded. Toon vervolgens alle rows met een expanded_price hoger dan 200. */

CREATE VIEW OrderListExpanded AS
SELECT C.cust_name, I.order_num, I.prod_id, I.quantity, I.item_price, I.quantity * I.item_price AS expanded_price
FROM Customers AS C, Orders as O, OrderItems as I
WHERE C.cust_id = O.cust_id AND O.order_num = I.order_num;

SELECT * FROM 
OrderListExpanded
WHERE expanded_price > 200;

-- Opdracht 3

/* Maak de view CustomerEmailList die de cust_id, cust_name en cust_email toont, exclusief lege e-mails. Toon vervolgens alle rows. */

CREATE VIEW CustomerEmailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

SELECT *
FROM CustomerEmailList;

-- Opdracht 4

/* Maak de view VendorLocations die de vend_name en vend_country toont in 1 column. Toon vervolgens alle rows. */

CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
       AS vend_title
FROM Vendors;

SELECT *
FROM VendorLocations;