sqlh5.3

-- Opdracht 1 | Products

/* Toon de vend_id, prod_name en prod_price. De vend_id moet gelijk zijn aan DLL01 of BRS01. Alle prijzen 
moeten groter of gelijk zijn dan 5. */

--	vend_id		prod_name				prod_price
--	BRS01 		8 inch teddy bear		5.99
--	BRS01 		12 inch teddy bear 		8.99
--	BRS01 		18 inch teddy bear 		11.99

SELECT vend_id, prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id ='BRS01') AND prod_price >=5;

-- Opdracht 2 | Products

/* Toon alle products m.u.v. de vend_id DLL01 en FNG01. */

--	prod_id		vend_id		prod_name				prod_price		prod_desc
--	BR01		BRS01 		8 inch teddy bear		5.99			8 inch teddy bear, comes with cap and jacket
--	BR02		BRS01 		12 inch teddy bear 		8.99			12 inch teddy bear, comes with cap and jacket
--	BR03		BRS01 		18 inch teddy bear 		11.99			18 inch teddy bear, comes with cap and jacket

SELECT *
FROM Products
WHERE vend_id NOT IN ('DLL01', 'FNG01');

-- Opdracht 3 | Customers

/* Toon alle Fun4All customers of customers zonder e-mail. */

--	cust_id			cust_name		cust_address			cust_city		cust_state		cust_zip	cust_country	cust_contact		cust_email
--	1000000002		Kids Place		333 South Lake Drive	Columbus		OH 				43333 		USA 			Michelle Green		NULL
--	1000000003		Fun4All 		1 Sunny Place 			Muncie			IN 				42222 		USA 			Jim Jones 			jjones@fun4all.com 
--	1000000004		Fun4All 		829 Riverside Drive 	Phoenix 		AZ 				88888 		USA 			Denise L. Stephens	dstephens@fun4all.com
--	1000000005		The Toy Store 	4545 53rd Street		Chicago 		IL 				54545 		USA 			Kim Howard			NULL

SELECT * 
FROM Customers 
WHERE cust_name = 'Fun4All' OR cust_email IS NULL;

-- Opdracht 4 | Products

/* Toon alle products waarvan de prod_desc begint met een numerieke waarde. */

--	prod_id	vend_id	prod_name			prod_price	prod_desc
--	BR01	BRS01 	8 inch teddy bear	5.99		8 inch teddy bear, comes with cap and jacket
--	BR02	BRS01 	12 inch teddy bear 	8.99		12 inch teddy bear, comes with cap and jacket
--	BR03	BRS01 	18 inch teddy bear 	11.99		18 inch teddy bear, comes with cap and jacket
--	RGAN01	DLL01 	Raggedy Ann			4.99		18 inch Raggedy Ann doll
--	RYL01 	FNG01 	King doll			9.49		12 inch king doll with royal garments and crown
--	RYL02 	FNG01 	Queen doll 			9.49		12 inch queen doll with royal garments and crown

SELECT *
FROM Products
WHERE ISNUMERIC(LEFT(prod_desc,1)) = 1;

-- MySQL
SELECT *
FROM Products
WHERE LEFT(prod_desc,1) REGEXP '^-?[0-9]+$' = 1