20130901220940-1

Unidad 3. Consultas simples (I)3.1. IntroduccinVamos a empezar por la instruccin que ms se utiliza en SQL, la sentencia SELECT. La sentencia SELECT es, con diferencia, la ms compleja y potente de las sentencias SQL, con ella podemos recuperar datos de una o ms tablas, seleccionar ciertos registros e incluso obtener resmenes de los datos almacenados en la base de datos. Es tan compleja que la estudiaremos a lo largo de varias unidades didcticas incorporando poco a poco nuevas funcionalidades.El resultado de una SELECT es una tabla lgica que alberga las filas resultantes de la ejecucin de la sentencia.La sintaxis completa es la siguiente:SELECT sentencia::=[WITH [,...n]] [ORDER BY {expression_columna|posicion_columna [ASC|DESC] } [ ,...n ]] [COMPUTE {{AVG|COUNT|MAX|MIN|SUM} (expression)}[ ,...n ] [BY expression[ ,...n ]] ] [] [OPTION ([ ,...n ])] ::= { | ( < expresion_consulta > ) } [ {UNION [ALL]|EXCEPT|INTERSECT} | () [...n ] ] ::= SELECT [ALL|DISTINCT] [TOP expresion [PERCENT] [WITH TIES] ] [INTO nueva_tabla] [FROM { } [ ,...n ] ] [WHERE ] [GROUP BY [ ALL ] expresion_agrupacion [ ,...n ] [WITH { CUBE | ROLLUP } ] ] [HAVING < condicion_busqueda > ] Debido a la complejidad de la sentencia (en la sintaxis anterior no se han detallado algunos elementos), la iremos viendo poco a poco, empezaremos por ver consultas bsicas para luego ir aadiendo ms clusulas.Empezaremos por ver las consultas ms simples, basadas en una sola tabla y nos limitaremos a la siguiente sintaxis:SELECT [ALL|DISTINCT] [TOP expresion [PERCENT] [WITH TIES]] FROM [WHERE ] [ORDER BY {expression_columna|posicion_columna [ASC|DESC]} [ ,...n ]] 3.2. Origen de datos FROMDe la sintaxis anterior, el elemento indica de dnde se va a extraer la informacin y se indica en la clusula FROM, es la nica clusula obligatoria. En este tema veremos un origen de datos basado en una sola tabla. La sintaxis ser la siguiente:::= nb_tabla | nb_vista [[ AS ] alias_tabla ] nb_tabla representa un nombre de tabla.nb_vista un nombre de vista.Tanto para las tablas como para las vistas, podemos hacer referencia a tablas que estn en otras bases de datos (siempre que tengamos los permisos adecuados), en este caso tenemos que cualificar el nombre de la tabla, indicando delante el nombre de la base de datos (Lgica) y el nombre del esquema al que pertenece la tabla dentro de la base de datos.Por ejemplo: MiBase.dbo.MiTabla se refiere a la tabla MiTabla que se encuentra en el esquema dbo de la base de datos MiBase. Cuando no se definen esquemas, SQL-Server crea uno por defecto en cada base de datos denominado dbo. Opcionalmente podemos definir un nombre de alias. Un nombre de alias (alias_tabla) es un nombre alternativo que se le da a la tabla dentro de la consulta. Si se define un nombre de alias, dentro de la consulta, ser el nombre a utilizar para referirnos a la tabla, el nombre original de la tabla ya no tendr validez.Se utilizan los nombres de alias para simplificar los nombres de tablas a veces largos y tambin cuando queremos combinar una tabla consigo misma; ya volveremos sobre los alias de tabla cuando veamos consultas multitabla.La palabra AS no aade ninguna operatividad, est ms por esttica.Podemos escribir:SELECT ...FROM tabla1 Sacamos los datos de la tabla tabla1SELECT ...FROM tabla1 t1 Sacamos los datos de la tabla tabla1 y le asignamos un alias de tabla: t1 SELECT ...FROM tabla1 AS t1 Es equivalente a la sentencia anterior.Si la tabla o la vista estn en otra base de datos del mismo equipo que est ejecutando la instancia de SQL Server, se utiliza el nombre cualificado con el formato nbBaseDatos.nbEsquema.nbTabla. Si la tabla o la vista estn fuera del servidor local en un servidor vinculado, se utiliza un nombre de cuatro partes con el formato nbservidor.catalogo.nbEsquema.nbTabla. Volveremos ms adelante sobre las conexiones remotas.3.3. La lista de seleccinEn la lista de seleccin indicamos las columnas que se tienen que visualizar en el resultado de la consulta. ::= { * | {nombre_tabla|nombre_vista|alias_tabla}.* | { [{nombre_tabla|nombre_vista|alias_tabla}.] {nb_columna|$IDENTITY|$ROWGUID} | }[[AS] alias_columna] | alias_columna = } [ ,...n ] Separamos la definicin de cada columna por una coma y las columnas del resultado aparecern en el mismo orden que en la lista de seleccin.Para cada columna del resultado su tipo de datos, tamao, precisin y escala son los mismos que los de la expresin que da origen a esa columna.Podemos definir las columnas del resultado de varias formas, mediante: Una expresin simple: una referencia a una funcin. una variable local una constante una columna del origen de datos,Una subconsulta escalar, que es otra instruccin SELECT que devuelve un nico valor y se evala para cada fila del origen de datos (esto no lo veremos de momento).Una expresin compleja generada al usar operadores en una o ms expresiones simples. La palabra clave *. La asignacin de variables con el formato @variable_local = expresin. La palabra clave $IDENTITY. La palabra clave $ROWGUID. 3.4. Columnas del origen de datosCuando queremos indicar en la lista de seleccin una columna del origen de datos, la especificamos mediante su nombre simple o nombre cualificado. El nombre cualificado consiste en el nombre de la columna precedido del nombre de la tabla donde se encuentra la columna. Si en el origen de datos hemos utilizado una vista o un nombre de alias, deberemos utilizar ese nombre. Es obligatorio utilizar el nombre cualificado cuando el nombre de la columna aparece en ms de una tabla del origen de datos.Ejemplos de consulta simple. Listar nombres, oficinas, y fechas de contrato de todos los empleados:SELECT nombre, oficina, contratoFROM empleados;El resultado sera:Nombreoficinacontrato

Antonio Viguer121986-10-20

Alvaro Jaumes211986-12-10

Juan Rovira12 1987-03-01

Jos Gonzlez121987-05-19

Vicente Pantalla131988-02-12

Luis Antonio11 1988-06-14

Jorge Gutirrez221988-11-14

Ana Bustamante211989-10-12

Mara Sunta 111999-10-12

Juan VictorNULL1990-01-13

Listar una tarifa de productos:SELECT idfab, idproducto, descripcion, productos.precioFROM productos;Hemos cualificado la columna precio aunque no es necesario en este caso. El resultado sera:Idfabidproductodescripcionprecio

aci41001arandela0,58

aci41002bisagra0,80

aci41003art t31,12

aci41004art t41,23

aci4100xjunta0,26

aci4100y extractor28,88

aci4100zmont26,25

bic41003manivela6,52

bic41089rodamiento2,25

3.5. Alias de columnaPor defecto, en el encabezado de cada columna del resultado, aparece el nombre de la columna origen, pero esto se puede cambiar definiendo un alias de columna, el alias de columna es un nombre alternativo que se le da a esa columna.El alias de columna se indica mediante la clusula AS. Se escribe el nuevo texto tal cual sin comillas siguiendo las reglas de los identificadores.Ejemplo: SELECT numclie,nombre AS nombreclienteFROM clientes;El resultado ser :NumclieNombrecliente

2101Luis Garca Antn

2102Alvaro Rodrguez

2103Jaime Llorens

en vez de:NumclieNombre

2101Luis Garca Antn

2102Alvaro Rodrguez

2103Jaime Llorens

La palabra AS es opcional.SELECT numclie,nombre nombreclienteFROM clientes; Sera equivalente a la consulta anterior Si queremos incluir espacios en blanco en el nombre lo debemos encerrar entre corchetes. SELECT numclie,nombre AS [nombre cliente]FROM clientes;Nota importante: Este nombre de alias se podr utilizar en la lista de seleccin y en la clusula ORDER BY pero no en la clusula WHERE.3.6. FuncionesExisten funciones que podemos utilizar en la lista de seleccin, e incluso en otras clusulas que veremos ms adelante, como el WHERE. Las principales funciones son las siguientes:Funciones de fecha:FuncinDescripcin

GETDATEDevuelve la fecha actual.

GETUTCDATEDevuelve la hora UTC.

DATEPARTDevuelve un entero que corresponde a la parte de la fecha solicitada.

DAYDevuelve el da de la fecha indicada.

MONTHDevuelve el mes de la fecha indicada.

YEARDevuelve el ao de la fecha indicada.

DATENAMEDevuelve una cadena de caracteres que representa el valor de la unidad especificada de una fecha especificada.

DATEADDDevuelve un valor datetime nuevo que resulta de sumar un intervalo de tiempo a una fecha especificada.>

DATEDIFFDevuelve el n de intervalos que hay entre dos fechas.

@@DATEFIRSTDevuelve el primer da de la semana establecido con SET DATEFIRST.

SET DATEFIRSTEstablece el primer da de la semana en un nmero del 1 al 7.

Funciones de cadena:FuncinDescripcin

ASCIIDevuelve el valor de cdigo ASCII del carcter situado ms a la izquierda de una expresin de caracteres.

CHARDevuelve el carcter ASCII del entero indicado.

NCHARDevuelve el carcter Unicode del entero indicado.

UNICODEDevuelve el entero que se corresponde al carcter Unicode indicado.

LENDevuelve el total de caracteres de una cadena, excluidos los espacios en blanco finales.

LTRIMDevuelve una cadena tras quitarle los espacios en blanco iniciales.

RTRIMDevuelve una cadena tras quitarle los espacios en blanco finales.

LEFTDevuelve los N ltimos caracteres de una cadena.

RIGHTDevuelve los N primeros caracteres de una cadena.

SUBSTRINGDevuelve parte de una expresin.

LOWERDevuelve la cadena convertida a minsculas.

UPPERDevuelve la cadena convertida a maysculas.

REPLACEReemplaza una determinada cadena.

STUFFElimina el nmero de caracteres especificados e inserta otro conjunto de caracteres en el punto de inicio indicado.

QUOTENAMEDevuelve una cadena Unicode con los delimitadores agregados para convertirla en un identificador delimitado vlido de Microsoft SQL Server 2005.

SPACEDevuelve una cadena de espacios repetidos.

STRDevuelve una cadena de caracteres a partir de datos numricos.

REPLICATERepite una cadena N veces.

REVERSEDevuelve una cadena invertida.

CHARINDEXDevuelve la posicin inicial de la expresin especificada en una cadena de caracteres.

PATINDEXDevuelve la posicin inicial de la primera repeticin de un patrn en la expresin especificada, o ceros si el patrn no se encuentra, en todos los tipos de datos de texto y caracteres.

Otras funciones:FuncinDescripcin

ROUNDRedondea un valor a la longitud y precisin indicadas.

CAST y CONVERTConvierten de un tipo de datos a otro de forma explcita.

CASEEvala una lista de condiciones.

ISNULLReemplaza el valor NULL por otro especificado.

COALESCEDevuelve la primera expresin distinta de NULL entre sus argumentos.

3.7. Columnas calculadasAdems de las columnas que provienen directamente de la tabla origen, una consulta SQL puede incluir columnas calculadas cuyos valores se evalan a partir de una expresin.La expresin puede contener cualquier operador vlido (+, -, *, /, &), cualquier funcin vlida, nombres de columnas del origen de datos, nombres de parmetros o constantes y para combinar varias operaciones se pueden utilizar los parntesis.Ejemplos de columnas calculadas:Listar la ciudad, regin y el supervit de cada oficina. Consideraremos el supervit como el volumen de ventas que se encuentran por encima o por debajo del objetivo de la oficina. SELECT ciudad, region, (ventas-objetivo) AS superavitFROM oficinas; El resultado ser:ciudadregionsuperavit

Valenciaeste11800,00

Alicanteeste-6500,00

Castelloneste 1800,00

Badajozoeste11100,00

A Coruaoeste-11400,00

Madridcentro NULL

Madridcentro-10000,00

PamplonanorteNULL

Valenciaeste-90000,00

De cada producto queremos saber el id de fabricante, id de producto, su descripcin y el valor de sus existencias.SELECT idfab,idproducto,descripcion,(existencias*precio) AS valoracionFROM productos;El resultado sera:Idfabidproductodescripcionvaloracion

aci41001arandela160,66

aci41002bisagra133,60

aci41003art t3231,84

aci41004art t4170,97

aci4100xjunta9,62

aci4100y extractor722,00

aci4100zmont735,00



Para practicar puedes realizar este Ejercicio Columna calculada. Nota: Para realizar los ejercicios tienes que abrir una nueva consulta sobre la base de datos GestionSimples. En el Explorador de objetos selecciona la base de datos y elige Nueva Consulta. En la carpeta Ejercicios del curso tienes el PDF Tablas_Gestion para consultar en qu tablas est cada campo.Objetivo Mostrar una columna que sea el resultado de un clculo aritmtico.Ejercicio paso a pasoListar de cada pedido, su nmero, cdigo completo del artculo vendido y precio unitario al que se ha vendido. No es necesario redondear el precio.La informacin precio unitario no se encuentra en la tabla, pero s que disponemos del importe total del pedido y la cantidad, as que podemos calcularlo.SELECT numpedido, fab, producto, importe/cant AS PrecioFROM pedidos;Si quisiramos redondear el precio a dos decimales, por ejemplo, podramos englobar la operacin importe/cant en la funcin ROUND() de la siguiente forma: ROUND(valor, ndecimales) --> ROUND(importe/cant, 2)Resultado (sin redondear):NumpedidoFabProductoPrecio

110036aci4100z2,50

110037rei2a44l4,50

112963aci410040,117

112968aci410041,17

112975rei2a44g3,50

112979aci4100z25,00

112983aci410041,17

112987aci4100y25,00

112989fea1142,43

112992aci410020,76

112993rei2a45c0,79

112997bic410036,52

113003imm779c18,75

113007imm773c9,75

113012aci410031,07

113013bic410036,52

113024qsaxk473,55

113027aci410028,3333

113034rei2a45c0,79

113042rei2a44r45,00

113045rei2a44r45,00

113048imm779c18,75

113049qsaxk473,88

113051qsaxk473,55

113055aci4100x0,25

113057aci4100xNULL

113058fea1121,48

113062bic410032,43

113065qsaxk473,55

113069imm773c14,25

De cada producto queremos saber el id de fabricante, id de producto, su descripcin y el valor de sus existencias.El valor de las existencias es un campo calculado, tendremos que multiplicar el nmero de existencias por su precio para ver en cunto se valora el stock.SELECT idfab,idproducto,descripcion,(existencias*precio) AS valoracionFROM productos;Resultado:IdfabIdproductoDescripcinValoracion

aci41001arandelaNULL

aci41002bisagraNULL

aci41003art t3NULL

aci41004art t4NULL

aci4100xjuntaNULL

aci4100yextractorNULL

aci4100zmontNULL



bic41672plato0,00

fea112cubo170,20

fea114cubo36,45

imm773creostato273,00

imm775creostato 271,25

imm779creostato 30,00

imm887hcaja clavos120,42

imm887pperno6,00

imm887xmanivela152,00

qsaxk47red134,90

qsaxk48red272,02

qsaxk48ared54,76

rei2a44gpas49,00

rei2a44lbomba l540,00

rei2a44rbomba r540,00

rei2a45cjunta165,90

Listar el nombre, mes y ao del contrato de cada vendedor.SELECT nombre, MONTH(contrato) AS [Mes de contrato], YEAR(contrato) AS [Ao de contrato]FROM empleados;El resultado ser:NombreMes de contratoAo de contrato

Antonio Viguer101986

Alvaro Jaumes121986

Juan Rovira31987

Para practicar puedes realizar este Ejercicio Funciones en la lista de seleccin.Nota: Para realizar los ejercicios, utiliza la base de datos GestionSimples.ObjetivoRealizar consultas simples con funciones en la lista de seleccin.Ejercicio paso a pasoListar de cada empleado su cdigo, nombre, oficina, y fecha de contrato (fecha corta). La nica dificultad es convertir la fecha de contrato completa en una fecha corta. Para eso utilizamos la funcin CONVERT() de la siguiente forma: SELECT numemp, nombre, oficina, CONVERT (CHAR(8),contrato,3) AS [Fecha contrato]FROM empleados;Adems, le hemos incluido un alias para que el ttulo de la columna sea ms descriptivo.Resultado:NumempNombreOficinaFecha Contrato

101Antonio Viguer1220/10/86

102Alvaro Jaumes2110/12/86

103Juan Rovira1201/03/87

104Jos Gonzlez1219/05/87

105Vicente Pantalla1312/02/88

106Luis Antonio1114/06/88

107Jorge Gutirrez2214/11/88

108Ana Bustamante2112/10/89

109Mara SuntaNULL12/10/99

110Juan VictorNULL13/01/90

111Juan GrisNULL01/05/05

112Julin MartorellNULL01/05/06

113Juan GrisNULL01/01/07

Listar los empleados indicando cdigo, nombre y da de la semana en que fue contratado.En este caso, la funcin DATENAME() es la que calcula automticamente el da de la semana para la fecha indicada. Tendremos que pasarle los parmetros de formato: dw (day week) y de datos: contrato.SELECT numemp, nombre, DATENAME(dw,contrato) AS [Da de contrato]FROM empleados;Resultado:NumempNombreDa de contrato

101Antonio ViguerLunes

102Alvaro JaumesMircoles

103Juan RoviraDomingo

104Jos GonzlezMartes

105Vicente PantallaViernes

106Luis AntonioMartes

107Jorge GutirrezLunes

108Ana BustamanteJueves

109Mara SuntaMartes

110Juan VictorSbado

111Juan GrisDomingo

112Julin MartorellLunes

113Juan GrisLunes

Listar de cada empleado su cdigo, nombre y ttulo (sustituir la abreviatura dir por la palabra completa Director).En esta ocasin utilizaremos la funcin REPLACE() en que indicaremos la columna sobre la que actuar la funcin, el valor a modificar y el valor que lo va a substituir.SELECT numemp, nombre, REPLACE(titulo, 'Dir ', 'Director ') AS CargoFROM empleados;Fjate que hemos remplazado el valor con un espacio al final de la palabra. Esto se debe a que, en la tabla, no aparece la palabra Dir simplemente, sino que a continuacin indica el tipo de director (dir ventas). Si no insertramos el espacio veramos como resultado Directorventas, Directorgeneral, etc.Resultado:NumempNombreCargo

101Antonio Viguerrepresentante

102Alvaro Jaumesrepresentante

103Juan Rovirarepresentante

104Jos GonzlezDirector ventas

105Vicente Pantallarepresentante

106Luis Antoniodirector general

107Jorge Gutirrezrepresentante

108Ana BustamanteDirector ventas

109Mara Suntarepresentante

110Juan Victorrepresentante

111Juan Grisrepresentante

112Julin Martorellrepresentante

113Juan Grisrepresentante

Listar las ventas en cada oficina con el formato: 22 tiene ventas de 186,042.00 SELECT oficina, 'tiene ventas de ' AS [ ], ventasFROM oficinas;El resultado sera:oficinaventas

11tiene ventas de69300,00





23tiene ventas deNULL


26tiene ventas deNULL


El incluir una constante como columna en la lista de seleccin puede parecer intil (se repetir el mismo valor en todas las filas) pero veremos ms adelante que tiene utilidad en ciertos casos.Tambin podemos utilizar la sintaxis:alias_columna = Ejemplo:SELECT oficina, superavit = ventas-objetivoEsto tiene el mismo efecto que SELECT oficina, ventas-objetivo AS superavit3.8. Utilizacin del asterisco *Si queremos visualizar todas las columnas del origen de datos, en lugar de indicar todas las columnas una a una se puede utilizar el carcter de sustitucin *.Mostrar todos los datos de la tabla oficinas.SELECT *FROM oficinas;Obtener todos los datos y el supervit de cada oficina.SELECT *, (ventas-objetivo) AS superavitFROM oficinas;Tambin podemos cualificar el * con un nombre de tabla, de vista o un alias de tabla:SELECT oficinas.*FROM oficinas;oficinas.* se interpreta como: todas las columnas de la tabla oficinas. Esta forma se utiliza normalmente cuando el origen est basado en varias tablas y queremos indicar todas las columnas no del origen completo sino de una tabla concreta. Para practicar puedes realizar este Ejercicio La palabra clave *.Nota: Para realizar los ejercicios, utiliza la base de datos GestionSimples.ObjetivoRealizar consultas simples utilizando la palabra clave *.Ejercicio paso a pasoObtener los datos de los empleados.El signo * muestra todos los campos de la tabla:SELECT * FROM empleados; Resultado:NumempNombreEdadOficinaTituloContratoJefeCuotaventas

101Antonio Viguer4512representante1986-10-20 00:00:00.00010430000,0030500,00

102Alvaro Jaumes4821representante1986-12-10 00:00:00.00010835000,0047400,00

103Juan Rovira2912representante1987-03-01 00:00:00.00010427500,0028600,00

104Jos Gonzlez3312dir ventas1987-05-19 00:00:00.00010620000,0014300,00

105Vicente Pantalla3713representante1988-02-12 00:00:00.00010435000,0036800,00

106Luis Antonio5211director general1988-06-14 00:00:00.000NULL27500,0029900,00

107Jorge Gutirrez4922representante1988-11-14 00:00:00.00010830000,0018600,00

108Ana Bustamante6221dir ventas1989-10-12 00:00:00.00010635000,0036100,00

109Mara Sunta31NULLrepresentante1999-10-12 00:00:00.0001063000,0039200,00

110Juan Victor41NULLrepresentante1990-01-13 00:00:00.000104NULL7600,00

111Juan Gris50NULLrepresentante2005-05-01 00:00:00.000NULL10000,0060000,00

112Julin Martorell50NULLrepresentante2006-05-01 00:00:00.000NULL10000,0091000,00

113Juan Gris18NULLrepresentante2007-01-01 00:00:00.000NULL10000,000,00

20130901220940-1

Documents