15 dz

######## 1
SELECT orderNumber, productCode, quantityOrdered*priceEach AS total
FROM orderdetails
ORDER BY total DESC
LIMIT 10

########## 2
SELECT orderNumber, SUM(priceEach*quantityOrdered) AS total
FROM orderdetails
GROUP BY orderNumber
HAVING total  > 10000
ORDER BY total DESC
LIMIT 10

###### 3
SELECT orderdetails.orderNumber, orderDate, STATUS, SUM(quantityOrdered*priceEach) AS total
FROM orderdetails
INNER JOIN orders
ON orderdetails.orderNumber = orders.orderNumber
GROUP BY orderdetails.orderNumber
HAVING total > 59000

#############4
SELECT
customers.contactFirstName, customers.contactLastName , customers.country, t2.customerNumber,
t2.orderNumber,t2.orderDate, t2.STATUS, t2.total
FROM
#######################################################t2
(
SELECT orderdetails.orderNumber, orders.orderDate, orders.STATUS, SUM(orderdetails.priceEach*orderdetails.quantityOrdered) AS total, orders.customerNumber
FROM orderdetails
INNER JOIN orders
ON orderdetails.orderNumber = orders.orderNumber
GROUP BY orderNumber
HAVING total  > 59000
ORDER BY total DESC
)
t2
#######################################################t2
INNER JOIN customers
ON t2.customerNumber = customers.customerNumber

##########5
SELECT  products.productName, MAX(orderdetails.priceEach*orderdetails.quantityOrdered) AS total
FROM orderdetails
INNER JOIN products
ON orderdetails.productCode = products.productCode
GROUP BY orderdetails.productCode
ORDER BY total DESC
LIMIT 10


#############6
SELECT  employees.firstName, employees.lastName, customers.contactFirstName , customers.contactLastName
FROM employees
CROSS JOIN customers
WHERE employees.employeeNumber =  customers.salesRepEmployeeNumber


#############7
SELECT employees.lastName, employees.firstName, t2.jobTitle,  t2.subFirstName, t2.sublastName
FROM employees
CROSS JOIN (SELECT t1.reportsTo, t1.employeeNumber,  t1.jobTitle , employees.firstName AS subFirstName, employees.lastName AS sublastName
FROM (SELECT * FROM employees) t1
LEFT JOIN  employees
ON t1.reportsTo = employees.employeeNumber) t2
WHERE t2.employeeNumber   =   employees.employeeNumber;