Ejercicios Northwind

Insertar en Customers la compañía ‘Compañía XX’ con ‘Ana’, ‘Pi’ como contacto

insert into customers (company,first_name,last_name)
values ('Compañía XX','Ana','Pi')

Modificar en Customer el campo email_address y poner ‘Sin email’

update customers
set email_address='Sin email'

Eliminar el Customer con id = 20

delete from customers
where id=20

Seleccionar todos los proveedores (suppliers) cuyo trabajo sea ‘Sales Manager’

SELECT * from suppliers where job_title='Sales manager'

Seleccionar todos los pedidos (orders) de junio

select * from orders
where month(order_date)=6

Calcular el número de pedidos por cliente

SELECT company,count(orders.id) total FROM customers left join orders on customers.id=orders.customer_id
group by customers.id

Calcular el total de ventas por empleado

SELECT last_name,first_name,sum(quantity*unit_price*(1-discount)) total FROM employees join orders on employees.id=orders.employee_id
join order_details on orders.id=order_details.order_id
group by employees.id

Averiguar el producto más caro

SELECT * FROM `products` order by list_price desc limit 1

Averiguar el número de ventas por producto

select products.*, sum(quantity*unit_price*(1-discount)) total from products join order_details on products.id= order_details.product_id
group by products.id

Mostrar los clientes con más de 10000 en ventas

select customers.*, sum(quantity*unit_price*(1-discount)) total from customers join orders on customers.id= orders.customer_id
join order_details on orders.id= order_details.order_id
group by customers.id
having total>10000