3. Sorting Retrieved Data

-- Oefening 1

SELECT prod_name
FROM Products;

SELECT prod_name
FROM Products
ORDER BY prod_name;

-- Oefening 2

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

SELECT prod_id, prod_price, prod_name
FROM Products 
ORDER BY 2, 3;

-- Oefening 3

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price;

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;

-- Opdracht 1 | Products

/* Toon alle producten oplopend gesorteerd op prod_name. */

--	prod_id		vend_id		prod_name				prod_price		prod_desc
--	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
--	BR01		BRS01 		8 inch teddy bear		5.99			8 inch teddy bear, comes with cap and jacket
--	BNBG02		DLL01 		Bird bean bag toy		3.49			Bird bean bag toy, eggs are not included
--	BNBG01		DLL01 		Fish bean bag toy		3.49			Fish bean bag toy, complete with bean bag worms with which to feed it
--	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
--	BNBG03		DLL01 		Rabbit bean bag toy		3.49			Rabbit bean bag toy, comes with bean bag carrots
--	RGAN01		DLL01 		Raggedy Ann				4.99			18 inch Raggedy Ann doll

SELECT 

-- Opdracht 2 | Products

/* Toon de unieke vend_id aflopend gesorteerd. */

--	vend_id
--	FNG01     
--	DLL01     
--	BRS01     

SELECT 

-- Opdracht 3 | Orders

/* Toon de cust_id, de order_num en sorteer respectievelijk op oplopend en aflopend. */

--	cust_id			order_num
--	1000000001		20009
--	1000000001		20005
--	1000000003		20006
--	1000000004		20007
--	1000000005		20008

SELECT 

-- Opdracht 4 | Products

/* Toon de prod_name oplopend gesorteerd op getallen en dan pas op tekst. */

--	prod_name
--	8 inch teddy bear
--	12 inch teddy bear 
--	18 inch teddy bear 
--	Bird bean bag toy
--	Fish bean bag toy
--	King doll
--	Queen doll 
--	Rabbit bean bag toy
--	Raggedy Ann

SELECT 

-- Opdracht 1 | Products

/* Toon alle producten oplopend gesorteerd op prod_name. */

--	prod_id		vend_id		prod_name				prod_price		prod_desc
--	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
--	BR01		BRS01 		8 inch teddy bear		5.99			8 inch teddy bear, comes with cap and jacket
--	BNBG02		DLL01 		Bird bean bag toy		3.49			Bird bean bag toy, eggs are not included
--	BNBG01		DLL01 		Fish bean bag toy		3.49			Fish bean bag toy, complete with bean bag worms with which to feed it
--	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
--	BNBG03		DLL01 		Rabbit bean bag toy		3.49			Rabbit bean bag toy, comes with bean bag carrots
--	RGAN01		DLL01 		Raggedy Ann				4.99			18 inch Raggedy Ann doll

SELECT *
FROM Products 
ORDER BY prod_name;

-- Opdracht 2 | Products

/* Toon de unieke vend_id aflopend gesorteerd. */

--	vend_id
--	FNG01     
--	DLL01     
--	BRS01     

SELECT DISTINCT vend_id 
FROM Products 
ORDER BY vend_id DESC;

-- Opdracht 3 | Orders

/* Toon de cust_id, de order_num en sorteer respectievelijk op oplopend en aflopend. */

--	cust_id			order_num
--	1000000001		20009
--	1000000001		20005
--	1000000003		20006
--	1000000004		20007
--	1000000005		20008

SELECT cust_id, order_num
FROM Orders
ORDER BY cust_id, order_num DESC;

-- Opdracht 4 | Products

/* Toon de prod_name oplopend gesorteerd op getallen en dan pas op tekst. */

--	prod_name
--	8 inch teddy bear
--	12 inch teddy bear 
--	18 inch teddy bear 
--	Bird bean bag toy
--	Fish bean bag toy
--	King doll
--	Queen doll 
--	Rabbit bean bag toy
--	Raggedy Ann

SELECT prod_name
FROM Products
ORDER BY IIF(ISNUMERIC(LEFT(prod_name,2))>0,LEFT(prod_name,2),100),prod_name

-- MySQL
SELECT prod_name
FROM Products
ORDER BY CAST(IF(LEFT(prod_name,1) REGEXP '^-?[0-9]+$' > 0, LEFT(prod_name,2),100) AS INTEGER), prod_name