Queries About Jardineria - Solutions

Practice: Various queries with the Jardineria database(Solutions)
Code in SQL sentences to obtain the following information:
1. The office code and the city where there are offices.
SELECT CodigoOficina, ciudad

FROM Oficina;
2. How many employees there are in the company.
SELECT Count(*)
FROM Empleados;
3. How many clients each country has.
SELECT Count (*), País

FROM Clientes
GROUP BY País;
4. How many orders are in each state in descending order of the number of
orders.
SELECT Count (*), Estado

FROM Pedidos
GROUP BY Estado
ORDER BY Count(*) DESC;
5. The price of the most expensive and the cheapest product.
SELECT Max(PrecioVenta), Min(PrecioVenta)

FROM Productos;
6. Get the name of the customer with the highest credit limit.
SELECT NombreCliente
FROM Clientes
WHERE LimiteCredito=(SELECT Max(LimiteCredito) FROM Clientes);
7. Get a list with the name of all the customers and the name and surname of their
sales representative.
SELECT NombreCliente, Nombre AS NombreEmp, Apellido1 as ApeEmp

FROM Clientes AS C, Empleados AS E
WHERE C.CodigoEmpleadoRepVentas=E.CodigoEmpleado;
8. The city and phone number of the US office.
SELECT ciudad, telefono

FROM Oficinas
WHERE Pais='EEUU’;
9. The first name, surname, email and position of those employees who do not
have a manager.
SELECT Nombre,Apellido1,Apellido2,email,Puesto
FROM Empleados
WHERE CodigoJefe is null;
10. The first name, surname and position of those employees who are not sales
representatives.
1. representantes de ventas.
SELECT Nombre,Apellido1,Apellido2, Puesto
FROM Empleados
WHERE not Puesto='Representante Ventas';
#WHERE Puesto<>'Representante Ventas' #Otra opción
11. The number of customers the company has.
SELECT Count(CodigoCliente)
FROM Clientes;
12. The name of the Spanish customers.
//tres opciones
SELECT NombreCliente FROM Clientes WHERE Pais='España';
SELECT NombreCliente,pais FROM Clientes WHERE Pais='España' OR
pais='SPAIN';
SELECT NombreCliente,pais FROM Clientes WHERE Pais IN ('España' ,
'SPAIN');
13. How many clients each country has.
SELECT Pais,count(*)
FROM Clientes
GROUP BY Pais;
14. How many clients does the city of Madrid have?
SELECT count(*)
FROM Clientes
WHERE ciudad ='Madrid';
15. How many clients does the city beginning with M have?
SELECT Count(*),ciudad
FROM Clientes
WHERE ciudad like 'M%'
GROUP BY ciudad;
16. The employee code and the number of customers served by each sales
representative.
SELECT Count(CodigoCliente), codigoEmpleadoRepVentas

FROM Clientes
GROUP BY codigoEmpleadoRepVentas;
17. The number of customers who have no sales representative assigned to them.
SELECT count(CodigoCliente)
FROM Clientes
18. What was the first and last payment made by any customer.
SELECT Min(FechaPago),Max(FechaPago)
FROM Pagos;
19. The customer code of those customers who made payments in 2008.
SELECT codigoCliente
FROM Pagos
WHERE FechaPago like '2008-%-%';
20. The different statuses an order can go through.
SELECT distinct Estado

FROM Pedidos;
21. The order code, customer code, required date and delivery date for orders that
have not been delivered on time.
SELECT CodigoPedido,CodigoCliente, FechaEsperada,FechaEntrega

FROM Pedidos
WHERE FechaEntrega>FechaEsperada;
22. How many products are in each range.
SELECT Count(*),Gama
FROM Productos
GROUP BY Gama;
23. The name of the most expensive product.
SELECT Nombre
FROM Productos
WHERE PrecioVenta = (SELECT Max(PrecioVenta) FROM Productos);
24. The name of the customers and the name of their representatives together with
the city of the office to which each representative belongs.
SELECT C.NombreCliente, E.Nombre, O.Ciudad

FROM Clientes AS C, Empleados AS E, Oficinas AS O,
WHERE C.CodigoEmpleadoRepVentas=E.CodigoEmpleado AND
E.CodigoOficina=O.CodigoOficina;
25. A list with the name of the employees together with the name of their bosses.
SELECT E.Nombre as Subordinado, J.Nombre as Jefe

FROM Empleados AS E, Empleados AS J
WHERE E.CodigoJefe = J.CodigoEmpleado;
26. A list of customers indicating the name of the customer and how many orders
he/she has placed (each customer).
SELECT NombreCliente, Count(*)

FROM Pedidos,Clientes
WHERE Pedidos.CodigoCliente=Clientes.CodigoCliente
GROUP BY NombreCliente;
27. The average number of units in stock grouped by range.
SELECT AVG(CantidadEnStock), Gama

FROM Productos
GROUP BY Gama;

Queries About Jardineria - Solutions

Uploaded by

Copyright:

Available Formats

Queries About Jardineria - Solutions

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Queries About Jardineria - Solutions

Uploaded by

Copyright:

Available Formats

Practice: Various queries with the Jardineria database(Solutions)

Code in SQL sentences to obtain the following information:

SELECT CodigoOficina, ciudad

SELECT Count (*), País

SELECT Count (*), Estado

SELECT Max(PrecioVenta), Min(PrecioVenta)

SELECT NombreCliente, Nombre AS NombreEmp, Apellido1 as ApeEmp

SELECT ciudad, telefono

SELECT Count(CodigoCliente), codigoEmpleadoRepVentas

SELECT distinct Estado

have not been delivered on time.

SELECT CodigoPedido,CodigoCliente, FechaEsperada,FechaEntrega

the city of the office to which each representative belongs.

SELECT C.NombreCliente, E.Nombre, O.Ciudad

SELECT E.Nombre as Subordinado, J.Nombre as Jefe

he/she has placed (each customer).

SELECT NombreCliente, Count(*)

SELECT AVG(CantidadEnStock), Gama

You might also like