Consultas SQL sobre multiples tablas

11. September 2010 01:21 by Oscar.SS in Desarrollo Empresarial  //  Tags:   //   Comments (6)

Continuando con el artículo anterior, veremos en este las consultas SELECT combinado columnas de distintas tablas. Este tipo de consultas nos permiten explotar nuestro almacén de negocio de forma más efectiva al relacionar datos de distintas tablas. 

 

--------------------------------------------------------------------------------------------------------------------
--CONSULTAS DE VARIAS TABLAS (Producto cartesiano de filas)--
--------------------------------------------------------------------------------------------------------------------

-- Si no se hacen coincidir los valores de las columnas relacionadas se obtiene gran duplicidad de filas. Tantas como
-- el producto cartesiano de las filas de las tablas a las que se hace referencia. Ejemplo:

select from dbo.Products, dbo.Orders

--------------------------------------------------------------------------------------------------------------------
--UTILIZAR ALIAS EN UNA TABLA--
--------------------------------------------------------------------------------------------------------------------

-- Cuando se relacionan varias tablas es normal que una misma columna forme parte de varias tablas. Para evitar 
-- errores de nombres duplicados podemos hacer dos cosas. Una es utilizar la sintaxis de nombre completo:
-- NombreTabla.NombreColumna.

select from dbo.Products, dbo.Categories
where dbo.Products.CategoryID dbo.Categories.CategoryID

-- La otra forma es dar un alias a cada tabla. Ejemplo:

select from dbo.Products P, dbo.Categories C
where P.CategoryID C.CategoryID

--------------------------------------------------------------------------------------------------------------------
--INSTRUCCIÓN JOIN ON(Coincidencia INTERNAS de columnas)--
--------------------------------------------------------------------------------------------------------------------

--NOTA: La instrucción INNER JOIN  es exactamente lo mismo que JOIN, dado que es el valor predeterminado.

--TEORÍA--

-- La instrucción JOIN nos permite combinar varias tablas haciendo coincidir los valores de las columas que nos 
-- interesen. Es decir, si tenemos dos tablas A y B que contienen una (o varias) columnas con el mismo nombre,
-- podemos relacionar ambas tablas por la columna del mismo nombre.

-- Por cada registro de la columna en la tabla A que también esté en la columna de la tabla B, obtendremos un
-- una relación. Lo que quiere decir que se produce un producto cartesiano de cada valor de la columna de la tabla A,
-- por todos los valores coincidentes de la columna en la tabla B.


--VOLVIENDO A LA BASE DE DATOS NORTHWIND--

-- 1. Por ejemplo, la tabla empleados nos facilita bastante información de los mismos. La más significativa es el
-- número de empleado, el nombre y la ciudad donde vive. Sin embargo, no nos dice nada de las ventas efectuadas por
-- cada empleado.

-- Si miramos la tabla de ventas veremos que además del número de ventas, tenemos información del empleado que
-- realizo estas ventas. Por lo tanto, por medio de la columna EmployeeID presente en las dos tablas podemos
-- relacionar los empleados con el número de venta. Ejemplo:

select OrderID, LastName, FirstName, City
from dbo.Orders O join dbo.Employees E
on  O.EmployeeID E.EmployeeID
order by OrderID

-- De este modo podemos concluir que la columna que pertenece a una tabla A y otra B sirve de nexo para relacionar
-- los datos de otras columnas de la tabla A que no estan incluidos en la tabla B y viceversa.

--2. Otro caso es la tabla de productos que nos da mucha información sobre los mismos pero apenas nos dice nada de 
-- la categoría a la que pertenece cada producto. Mostremos una tabla productos personal que muestre la información
-- más interesante de los productos, más el nombre de la categoría y una descripción de esta.

select ProductID, ProductName, C.CategoryID, CategoryName, Description, UnitPrice, UnitsInStock
from dbo.Products P join dbo.Categories C
on P.CategoryID C.CategoryID

--3. COMBINACIÓN DE TRES TABLAS. Si nos fijamos en las tablas de empleados y de clientes nos damos cuenta que no hay
-- forma de relacionar que empleado atendió (o vendió) a que cliente. Podemos hacerlo a través de la tabla de ventas
-- que tiene los números de empleados y de clientes. 

select E.EmployeeID, LastName, FirstName, OrderID, C.CustomerID, CompanyName, ContactName
from dbo.Orders O 
join dbo.Employees E on O.EmployeeID E.EmployeeID 
join dbo.Customers C on O.CustomerID C.CustomerID
order by EmployeeID

--4. Puede darse el caso en que solo interese esta información para los clientes de España.

select E.EmployeeID, LastName, FirstName, OrderID, C.CustomerID, CompanyName, ContactName
from dbo.Orders O 
join dbo.Employees E on O.EmployeeID E.EmployeeID 
join dbo.Customers C on O.CustomerID C.CustomerID
where C.Country 'Spain'
order by EmployeeID

--5. La instrucción JOIN también podemos utilizarla para sustituir a las subconsultas. La forma de hacerlo es dando
-- dos alias diferentes a una misma tabla. Ejemplo:
--    (Mostrar los empleados que son mayores que el empleado 5 (1955-03-04) ).

-- Técnica de subconsultas.
select EmployeeID, LastName, FirstName, BirthDate from dbo.Employees
where BirthDate > (select BirthDate from dbo.Employees
                    
where EmployeeID 5)

-- Instrucción JOIN.
select E1.EmployeeID, E1.LastName, E1.FirstName, E1.BirthDate from dbo.Employees E1 join dbo.Employees E2
on E1.BirthDate > E2.BirthDate
where E2.EmployeeID 5

--------------------------------------------------------------------------------------------------------------------
--INSTRUCCIÓN OUTER JOIN (Coincidencias EXTERNAS de columnas)--
--------------------------------------------------------------------------------------------------------------------

-- Puede darse el caso que nos interese mostrar todos los valores de una columna (todas las filas) aunque no tengan 
-- correspondencia en la otra tabla. Así podemos tener 3 casos:

--1.Mostrar todos los valores de la tabla IZQ (LEFT), con NULL para la tabla DCH cuando no hay correspondencia
select ProductID, ProductName, C.CategoryID, CategoryName, C.Description, UnitPrice, UnitsInStock
from dbo.Products P left outer join dbo.Categories C
on P.CategoryID C.CategoryID

--2.Mostrar todos los valores de la tabla DCH (RIGHT), con NULL para la tabla IZQ cuando no hay correspondencia.
select ProductID, ProductName, C.CategoryID, CategoryName, C.Description, UnitPrice, UnitsInStock
from dbo.Products P right outer join dbo.Categories C
on P.CategoryID C.CategoryID

--3.Mostrar todos los valores de ambas tablas (FULL) con NULL cuando no hay correspondencia.
select ProductID, ProductName, C.CategoryID, CategoryName, C.Description, UnitPrice, UnitsInStock
from dbo.Products P full outer join dbo.Categories C
on P.CategoryID C.CategoryID

--NOTA: La sentencia OUTER es opcional. Al incluir las sentencias LEFT, RIGHT Y FULL el sistema sabe que es una
--        consulta de combinación externa. Ejemplo anterior:

select ProductID, ProductName, C.CategoryID, CategoryName, C.Description, UnitPrice, UnitsInStock
from dbo.Products P full join dbo.Categories C
on P.CategoryID C.CategoryID

--------------------------------------------------------------------------------------------------------------------
-- INSTRUCCIÓN UNION (Unión de filas en distintas tablas)--
--------------------------------------------------------------------------------------------------------------------

--1. Unir (UNION) todas (ALL) las filas de dos columnas de tablas diferentes.
select City from dbo.Employees
union all
select 
City from dbo.Customers -- Devuelve la suma de todas las filas en ambas columnas.

--2. Unir (UNION) las filas de dos columnas de tablas diferentes sin repetir ningún valor.
select City from dbo.Employees
union
select 
City from dbo.Customers -- Devuelve la suma de todas las filas en ambas columnas pero con valores únicos.

--NOTA: Se debe cumplir que las columnas en ambas instruciones SELECT coincidan en el tipo de datos.

-------------------------------------------------------------------------------------------------------------------- 
-- INSTRUCCIÓNES EXCEPT Y INTERSECT (Diferencia e Intersección de conjuntos)--
--------------------------------------------------------------------------------------------------------------------

-- EXCEPT -- Devuelve los valores de la primera consulta que no se encuentran en la segunda.
select City from dbo.Employees
except
select 
City from dbo.Customers

-- INTERSECT -- Devueleve una intersección de todos los valores, es decir, solo los que se encuentran ambas columnas.
select City from dbo.Employees
intersect
select 
City from dbo.Customers
--------------------------------------------------------------------------------------------------------------------

 

Descarga: Consultas multi Tablas.rar (2,45 kb)

Diseño Centrado en el Programador vs Diseño Centrado en el Usuario

11. September 2010 00:03 by Oscar.SS in Experiencia Usuario, Formación  //  Tags: ,   //   Comments (0)

La primera vez que escuché mencionar el termino usabilidad tuve que ir corriendo a Google y pasarme un buen rato leyendo. Después, cuando me introduje un poco más en este mundillo vinieron otros términos como experiencia de usuario, arquitectura de la información, accesibilidad, diseño centrado en el usuario, etc.

Lo cierto es que para mí ha sido toda una revelación. ¡Ahora no entiendo como desarrollaba antes las aplicaciones sin conocer todo esto!. De hecho, cuando veo proyectos en los que participé antes de conocer la usabilidad y los comparo con los que ahora desarrollo...¡no hay color!.

Lo normal, cuando estamos en la oficina trabajando como desarrolladores, analistas o jefes de proyecto (este tema atañe a todos los perfiles), es poner el foco en uno mismo (Diseño Centrado en el Programador). Es decir, que la idea básica es entregar a tiempo el producto o tareas, que compile y no tenga errores de programación de bulto y por encima de todo...¡NO COMPLICARME LA VIDA!.

Ahora bien, ¿que ocurre con el producto que le entregamos al usuario?. Y no digo al cliente, me refiero al usuario final, que es el que va utilizar la aplicación y el que va a determinar el éxito o el fracaso de la misma en función de su experiencia al interactuar con ella.

Como regla general, podemos decir que siempre que un desarrollador no se complica la vida se la está complicando al usuario. Sin embargo, lo contrario no es necesariamente cierto. Muchas veces con pequeñas premisas, pequeños cambios, facilitamos las tareas y el uso de la aplicación para el usuario sin que por ello nosotros hayamos tenido que hacer el pino puente sobre el teclado.

Diseño Centrado en el Programador

                                                              

Usabilidad al pelar una naranja
Diseño Centrado en el Programador

                                                              

Diseño Centrado en el Usuario

 

Este no es un blog sobre usabilidad ni pretende serlo. Pero me parece importante, mejor dicho, imprescindible que todo profesional que se dedique al desarrollo de aplicaciones tenga unas nociones básicas sobre los conceptos mencionados anteriormente. Sobre todo cuando, como ocurre en la mayoría de las empresas, no existe un responsable que se dedique profesionalmente a tales funciones.

Os puedo decir, que el esfuerzo de aprendizaje es mínimo y los resultados son espectaculares. Resumiendo mucho se podría decir que las únicas habilidades necesarias son el sentido común y la capacidad de abstraerse y poner el foco en el usuario. ¡Hay que cambiar el chip!.

Cuando estés diseñando una pantalla, cuando pienses donde pongo este botón, que mensaje escribo si hay un error, como muestro los datos de un informe, etc y etc...¡Aplica el sentido común y piensa en que le gustaría al usuario final!. Que a fin de cuentas, es para quién se desarrollan la aplicaciones. No para tu jefe, ni para el cliente, ni para nadie más que el que lo usa. Repito...¡hay que cambiar el chip!.

Y por cierto, la usabilidad está en todas partes, no solo en el desarrollo de aplicaciones para la web. Desde un dispositivo con pantalla táctil hasta unas tijeras ergonómicas o en la forma de pelar una naranja.

A continuación os dejo un libro con el que podréis entrar en materia poco a poco. El título del mismo, es lo que dicen a gritos los usuarios de tus aplicaciones...¡no me hagas pensar!.


Y Google, ¿cómo lo haría?

Título: No me hagas pensar
Autor: Steve Krug
Editorial: Pearson. Prentice Hall
I.S.B.N.: 9788483222867
Fecha publicación: 2006
Nº páginas: 200
Nº capítulos: 12
Precio: 29.15 euros.


 

Normalmente cuando presento un libro hago una pequeña evaluación personal del mismo resaltando sus puntos fuertes y débiles. Pero en esta ocasión me lo he saltado porque el libro no tiene crítica alguna. Es simple, es claro, es corto, es fácil de leer y sobre todo...¡es usable!.

 

Consultas SQL sobre una tabla

9. September 2010 19:27 by Oscar.SS in Desarrollo Empresarial  //  Tags:   //   Comments (0)

Estaba haciendo limpieza de archivos en el ordenata (muy recomendable de vez en cuando) y me he encontrado unos cuantos scripts de SQL de cuando yo intentaba aprender todo esto de las consultas a bases de datos. Y me he dicho, en lugar de borrarlos...¿por qué no los publico?...¡lo mismo le son de utilidad a álguien!...¡como lo fueron para mí en su día!.

Así que en este artículo y el siguiente colocaré el código de estos scripts SQL que pueden servir de consulta rápida por si algo se olvida. También, al final de cada artículo dejaré para descarga los archivos de scripts por si queréis ejecutarlos en vuestra máquina.

Las consultas atacan la conocida base de datos Northwind de ejemplo de Microsoft y que podéis descargar gratuitamente en este enlace.

Antes de comenzar con el código mencionar que estos ejemplos solo abarcan las consultas SELECT del Lenguaje de Manipulación de Datos (DML) o ingles Data Definition Language. En el código podremos encontrar algunos elementos únicos del lenguaje Transact SQL y que por tanto no se encuentran en el estándar ANSI de SQL.

 

--------------------------------------------------------------------------------------------------------------------
--SINTAXIS COMPLETA DE LA INSTRUCCIÓN SELECT--
--------------------------------------------------------------------------------------------------------------------

SELECT [ALL DISTINCT] [* / ListaColumnas] AS NombreNuevo,  
        Funciones Agregación [COUNT(* / ListaColumnas), MAX, MIN, SUM, 
AVG]
FROM NombreTabla
WHERE NombreColumna [<, >, ,..., BETWEEN ANDLIKE % / _, IS (NOTNULLIN]
GROUP BY ListaColumnas(Funciones Agregación)
HAVING Condiciones Grupos
ORDER BY ListaColumnas [ASC DESC]


--------------------------------------------------------------------------------------------------------------------
--INSTRUCCIONES SELECT ELEMENTALES--
--------------------------------------------------------------------------------------------------------------------

--1. Mostrar todas (*) las columnas de una tabla (from).
select from dbo.Products

--2. Mostrar solo las columnas especificadas.
select ProductID, ProductName, UnitPrice, UnitsInStock from dbo.Products

--3. Incluir (ALL) filas duplicadas (valor predeterminado).
select all from dbo.Products --Es exactamente lo mismo que la instrucción 1.

--4. No incluir (DISTINCT) filas duplicadas.
select distinct from dbo.Products

--5. Renombrar (AS) columnas.
select ProductID as Identificador, ProductName as [Nombre Producto], UnitPrice as [Precio Unidad],
UnitsInStock 
as UnidadesStock from dbo.Products

--6. Renombrar (AS) tablas.
select from dbo.Products as Productos

--------------------------------------------------------------------------------------------------------------------
--INSTRUCCIONES WHERE (Condiciones en Select)--
--------------------------------------------------------------------------------------------------------------------

--1. Condiciones con expresiones matemáticas boleanas (>, <, =, >=, <=, != ó <>).
    --(Mostrar solo los productos con un precio mayor que 25)
select from dbo.Products
where UnitPrice > 25

    
--(Mostrar el nombre de los productos con precio igual a 21)
select ProductName as Nombre from dbo.Products
where UnitPrice 21

--2. Condiciones de intervalos numéricos (BETWEEN AND).
    --(Mostrar los productos cuyo precio esta entre 25 y 35)
select from Products
where UnitPrice between 25 and 35

--3. Condiciones de cadenas de caracteres (LIKE _ , %).
    --(Mostrar todos los datos de los clientes cuyo nombre comienza por A)
select  from Customers
where ContactName like 'A%'  -- El '%' significa cualquier cadena de caracteres.

    --(Mostrar los Paises que comienzan por U y después un solo caracter).
select Country as Paises from Customers
where Country like 'U_'  --El '_' significa cualquier caracter.

--4. Mostrar solamente las filas con valores nulos (IS NULL) en una columna determinada.
select from dbo.Customers
where Region is null

--5. Mostrar solamente las filas con valores no nulos (IS NOT NULL) en una columna determinada.
select from Customers
where Fax is not null

--6. Mostrar valores determinados (IN).
select from Products
where UnitPrice in(10203040)

--7. Mostrar valores condicionalmente en función de un grupo de valores de una columna (ANY/SOME)
    --La clausula ANY es equivalente a SOME.
    --Cuando tenemos una consulta que devuelve un conjunto de resultados podemos necesitar compararlos
    --todos ellos y de una vez con otro valor. Lo mejor es ver un ejemplo de estas expresiones:

--   Condición          Expresión Equivalente
-- x > ANY (3, 5, 9)         x > 3
-- x > ANY (1, 2)            x > 1
-- x < ANY (1, 2)            x < 2
-- x = ANY (1, 2)           (x = 1) OR (x = 2)
-- x <> ANY (1, 2)          (x <> 1) OR (x <> 2)

    --Mostrar los productos cuando su precio es mayor que los precios de los detalles 
    --de pedidos, cuando la cantidad del pedido es 130.
   
select from Products
where UnitPrice > ANY
    
(select UnitPrice from [Order Details] where Quantity 130-- Esta consulta devuelve 18.00 y 35.25.
    
    --Por lo tanto solo se mostrarán productos cuyo precio sea mayor que 18.00, incluido 35.25 si lo hubiera.
   
    
--8. Con la clausula ALL podemos hacer lo mismo pero en este caso devolverá todos los productos 
     --cuyo precio sea mayor que todos los precios de los detalles de pedidos de 130. Es decir, mayor que
     --todos los valores devueltos por la segunda consulta (18.00, 35.25), o lo que es lo mismo,
     --mayor que 35.25

select from Products
where UnitPrice > ALL
    
(select UnitPrice from [Order Details] where Quantity 130)

--9. Comprobar la existencia de un registro.
    
    --Mostrar los empleados cuya ciudad también exista en la tabla de empleados.

select LastName, FirstName, City from Employees E
where exists
    
(select from Customers C
    
where E.City C.City)


--------------------------------------------------------------------------------------------------------------------
--INTRUCCIONES ORDER BY (Ordenación en Select)--
--------------------------------------------------------------------------------------------------------------------

--1. Orden ascendente de filas.
    --(Tabla Productos ordenada por el precio de menor a mayor).
select from dbo.Products 
order by UnitPrice asc 

-- ASC es el valor predeterminado, se puede omitir.
select from Products 
order by UnitPrice

--2. Orden descendente de filas.
select from Products 
order by UnitsInStock desc

--3. Combinaciones de orden en distintas filas.
select ProductID, ProductName, UnitPrice, UnitsInStock from dbo.Products
order by UnitPrice, UnitsInStock desc

--------------------------------------------------------------------------------------------------------------------
--SUBCONSULTAS EN SELECT O CONSULTAS ANIDADAS--
--------------------------------------------------------------------------------------------------------------------

-- Una subconsulta es una consulta SELECT dentro de un WHERE en una consulta previa SELECT. Es decir, realizamos 
-- dentro de una consulta, otra consulta para mostrar datos especificos de la primera consulta. Ejemplos:

--1. Mostrar todos los empleados que tengan el mismo sexo (TitleOfCourtesy) que el empleado 9 (EmployeeID).
select from dbo.Employees
where TitleOfCourtesy (select TitleOfCourtesy from dbo.Employees 
                            
where EmployeeID 9--Esta subconsulta devuelve 'Ms'.

--2. Mostrar todos los productos de las categorías (bdo.Categories) bebidas (Beverages) y condimentos (Condiments).
select from dbo.Products
where CategoryID in (select CategoryID from dbo.Categories
                    
where CategoryName in ('Beverages','Condiments')) --Esta subconsulta devuelve 1 y 2.

--------------------------------------------------------------------------------------------------------------------
--FUNCIONES DE AGREGACIÓN EN SELECT--
--------------------------------------------------------------------------------------------------------------------

--1. Obtener el número de filas de una tabla --> COUNT(*).
select count(*) from dbo.Customers --Se cuentan todos los valores, incluidos los NULL.

-- Se puede dar nombre a esta columna de la siguiente forma.
select count(*) as [Número de filas] from dbo.Customers

--2. Obtener el múmero de filas de una columna determinada --> COUNT(NombreColumna).
select count(Region) as [NºFilas no NULL]from dbo.Customers --No se incluyen los valores NULL.

--3. Obtener número de filas para varias columnas determinadas.
select count(Region) as FilasNoNULL_Region, count(Fax) as FilasNoNULL_Fax from dbo.Customers

--4. Obtener el valor máximo (MAX) y mínimo (MIN) de una columna.
select max(UnitPrice) as [Precio máximo], min(UnitsInStock) as [Stock mínimo] from dbo.Products

--5. Obtener un sumatorio (SUM) de una columna determinada.
select sum(Freight) as [Peso Total] from dbo.Orders

--6. Obtener la media (AVG) de los valores de una columna determinada.
select avg(Freight) as [Peso Medio] from dbo.Orders

--------------------------------------------------------------------------------------------------------------------
--INSTRUCCIÓN GROUP BY (Agrupamiento de filas en funciones de agregación)--
--------------------------------------------------------------------------------------------------------------------

--La instrucción GROUP BY permite especificar las columnas sobre las que se quieren aplicar las funciones de 
--agregación en lugar de hacerlo en una tabla completa. Es decir, muestra un resultado de la función de agregación
--para cada elemento de la columna que especifiquemos. Ejemplos:

--1. Mostrar cuantas veces se repite cada precio de los productos.
select UnitPrice, count(*) as [Filas con este precio] from Products
group by UnitPrice

--2. Mostrar número de clientes de cada region y ciudad de USA.
select Region, City, count(*) as [Nº Clientes] from Customers
where Country 'USA'
group by Region, City
order by [Nº Clientes] desc

--NOTA 1: Siempre debe especificarse las mismas columnas en SELECT que en GROUP BY.
--NOTA 2: Es una buena práctica ordenar (ORDER BY) los elementos cuando se realizan este tipo de consultas.

--------------------------------------------------------------------------------------------------------------------
--INSTRUCCIÓN HAVING (Condiciones en los resultados de GROUP BY)--
--------------------------------------------------------------------------------------------------------------------

--La instrucción HAVING es similar a WHERE pero para agrupamientos de funciones de agregación. Ejemplo:

--1. Mostrar lo mismo que el ejercicio 1 de la sección anterior, con precios mayor que 10 y repiticiones mayor que 2.
select UnitPrice, count(*) as [Filas con este precio] from Products
where UnitPrice > 10
group by UnitPrice
having count(*) > 2

--------------------------------------------------------------------------------------------------------------------

 

Descarga: Consultas mono Tabla.rar (2,38 kb)

 

Recent Comments

Comment RSS

Month List