sqlh7.3

-- Opdracht 1 | Customers

/* Toon de cust_name en een column met daarin de cust_state, 1 spatie en de cust_zip. */

--	cust_name		cust_state_zip
--	Village Toys	MI 44444     
--	Kids Place		OH 43333     
--	Fun4All			IN 42222     
--	Fun4All			AZ 88888     
--	The Toy Store	IL 54545     

SELECT cust_name, RTRIM (cust_state ) + ' ' + cust_zip AS cust_state_zip
FROM Customers;

-- MySQL
SELECT cust_name, CONCAT (cust_state, ' ', cust_zip ) AS cust_state_zip
FROM Customers

-- Opdracht 2 | Orders

/* Toon de order_date en een column die 7 dagen optelt bij de order_date. */

--	order_date					order_exp
--	2012-05-01 00:00:00.000		2012-05-08 00:00:00.000
--	2012-01-12 00:00:00.000		2012-01-19 00:00:00.000
--	2012-01-30 00:00:00.000		2012-02-06 00:00:00.000
--	2012-02-03 00:00:00.000		2012-02-10 00:00:00.000
--	2012-02-08 00:00:00.000		2012-02-15 00:00:00.000

SELECT order_date, order_date + 7 AS order_exp
FROM Orders;

-- MySQL
SELECT order_date, DATE_ADD(order_date, INTERVAL 7 DAY) AS order_exp
FROM Orders

-- Opdracht 3 | Products

/* Toon de prod_name, prod_price en een column met het BTW bedrag van 21% gebaseerd op de prod_price. */

--	prod_name			prod_price	prod_vat
--	Fish bean bag toy	3.49	0.7329
--	Bird bean bag toy	3.49	0.7329
--	Rabbit bean bag toy	3.49	0.7329
--	8 inch teddy bear	5.99	1.2579
--	12 inch teddy bear 	8.99	1.8879
--	18 inch teddy bear 	11.99	2.5179
--	Raggedy Ann			4.99	1.0479
--	King doll			9.49	1.9929
--	Queen doll 			9.49	1.9929

SELECT prod_name, prod_price, prod_price*0.21 AS prod_vat
FROM Products;

-- Opdracht 4a

/* Pas de NL datumnotatie toe op opdracht 2. */

--	order_date					order_exp
--	dinsdag 1 mei 2012			dinsdag 8 mei 2012
--	donderdag 12 januari 2012	donderdag 19 januari 2012
--	maandag 30 januari 2012		maandag 6 februari 2012
--	vrijdag 3 februari 2012		vrijdag 10 februari 2012
--	woensdag 8 februari 2012	woensdag 15 februari 2012

SELECT FORMAT(order_date, 'D', 'NL-nl') AS order_date, FORMAT(order_date + 7, 'D', 'NL-nl') AS order_exp
FROM Orders;

-- MySQL
SELECT DATE_FORMAT(order_date, "%d %M %Y") AS order_date, DATE_FORMAT(DATE_ADD(order_date, INTERVAL 7 DAY), "%d %M %Y") AS order_exp
FROM Orders

-- Opdracht 4b

/* Pas de US geldnotatie toe op opdracht 3. */

--	prod_name			prod_price	prod_vat
--	Fish bean bag toy	$3.49		$0.73
--	Bird bean bag toy	$3.49		$0.73
--	Rabbit bean bag toy	$3.49		$0.73
--	8 inch teddy bear	$5.99		$1.26
--	12 inch teddy bear	$8.99		$1.89
--	18 inch teddy bear	$11.99		$2.52
--	Raggedy Ann			$4.99		$1.05
--	King doll			$9.49		$1.99
--	Queen doll			$9.49		$1.99

SELECT prod_name, FORMAT (prod_price, 'C',  'US-us') as prod_price, FORMAT(prod_price*0.21,'C', 'US-us') AS prod_vat
FROM Products;

-- MySQL
SELECT prod_name, concat('$ ', FORMAT (prod_price,2)) as prod_price, concat('$ ', FORMAT(prod_price*0.21,2)) AS prod_vat
FROM Products