mariadb -u mnuuser --password="mnupassword" classicmodels

use classicmodels;

SELECT customerName as Kunde, 
	   country as Land 
FROM customers;

SELECT customerName as Kunde, 
       country as Land 
FROM customers 
ORDER BY country;

SELECT count(customerNumber) as Anzahl, 
       country as Land 
FROM customers 
GROUP BY country 
ORDER BY country;

SELECT count(customerNumber) as Anzahl, 
       country as Land 
FROM customers 
GROUP BY country 
ORDER BY Anzahl desc;

SELECT count(customerNumber) as Anzahl, 
       country as Land 
FROM customers 
GROUP BY country 
HAVING Anzahl > 5 
ORDER BY Anzahl desc;

SELECT DISTINCT status FROM orders;

SELECT status as Status, count(orderNumber) as Anzahl 
FROM orders GROUP BY status;

SELECT * from orders where status = "Disputed";

SELECT * from orders where status = "In Process";

SELECT c.customerName as Kunde, o.orderNumber as Bestellung 
FROM customers c, orders o 
WHERE c.customerNumber=o.customerNumber AND o.status="Disputed";

SELECT c.customerName as Kunde, o.orderNumber as Bestellung, p.productName 
FROM customers c, orders o, orderdetails d, products p 
WHERE c.customerNumber=o.customerNumber 
	AND o.orderNumber=d.orderNumber 
	AND d.productCode=p.productCode 
	AND o.status="Disputed";


select c.customerName, c.city, c.country, count(o.orderNumber) as Anzahl 
from customers c, orders o 
where c.salesRepEmployeeNumber is null 
and c.customerNumber = o.customerNumber 
group by c.customerNumber 
order by c.country;