Bazy danych lab6

SELECT e.LAST_NAME, d.name FROM employee e join  department d ON e.department_id = d.department_id WHERE d.name='RESEARCH' OR d.name='SALES' ORDER BY e.last_name,d.name;

SELECT e.last_name, l.regional_group FROM employee e join job j ON j.job_id = e.job_id  join department d ON e.department_id = d.department_id
join location l ON l.location_id = d.location_id WHERE j.FUNCTION='ANALYST' AND (l.regional_group='NEW YORK' OR l.regional_group='BOSTON');

SELECT 'Pracownik', e.last_name, 'pracuje na etacie', j.FUNCTION, 'od', e.hire_date FROM employee e join job j ON j.job_id = e.job_id;

SELECT e.last_name, d.name, SUM(s.total) FROM employee e join department d ON e.department_id = d.department_id
join job j ON j.job_id = e.job_id
join customer c ON e.employee_id = c.salesperson_id
join sales_order s ON s.customer_id = c.customer_id
WHERE j.FUNCTION='SALESPERSON' AND (s.order_date BETWEEN ADD_MONTHS(TRUNC((SELECT MAX(order_date) FROM sales_order),'mm'),-1) AND LAST_DAY(ADD_MONTHS(TRUNC((SELECT MAX(order_date) FROM sales_order),'mm'),-1)))
GROUP BY e.last_name, d.name;

SELECT c.name, SUM(s.total) FROM customer c
full join sales_order s ON s.customer_id = c.customer_id
GROUP BY c.name;

SELECT l.regional_group, COUNT(s.total), AVG(s.total) FROM location l
join  department d ON l.location_id = d.location_id
join employee e ON e.department_id = d.department_id
join customer c ON e.employee_id=c.salesperson_id
join sales_order s ON s.customer_id = c.customer_id
WHERE d.name='SALES' GROUP BY l.regional_group;

SELECT c.name, s.order_date, i.quantity, p.description FROM customer c
full join sales_order s ON s.customer_id = c.customer_id
full join item i ON s.order_id = i.order_id
full join product p ON p.product_id = i.product_id;