curso sql server prog u2-03

17/03/2010

1

Prof. Marlon S. Ramrez M.

Curso de Microsoft SQL Server Programacin

Unidad 2 - Usando Transact-SQL (16hrs) 1. Consulta de los datos2. Consultas multi-tablas3. Funciones SQL 4. Grupos y Agregacin


USANDO TRANSACT-SQL

1. Consultas de los datos

2. Consultas multi-tablas

3. Funciones SQL

4. Grupos y Agregacin

SQL Server - Programacin USANDO TRANSACT-SQL 2

17/03/2010

2


La instruccin SELECT

Clausula Descripcin

SELECT Seguida de una lista de columnas o un asterisco. Indicando que quieres obtener todas las columnas

FROM Seguida del nombre de la tabla o vista, o mltiples tabla con expresiones de JOIN

WHERE Seguida por criterios de filtraje

ORDER BY Seguida por una lista de columna de ordenacin


Ejemplos

SELECT Name, StandardCost, Color FROM Production.Product

SELECT * FROM Production.Product


Consulta Multi-Tabla


SELECT ProductID,Name,Color,StandardCost,ListPrice,ProductModelId,Name

FROM Production.ProductINNER JOIN Production.ProductModel

ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID

SELECT ProductID,Name,Color,StandardCost,ListPrice

FROM Production.Product

SELECT ProductID, Production.Product.Name,Color,StandardCost,ListPrice, Production.ProductModel.ProductModelID, Production.ProductModel.Name



SELECT Production.Product.ProductID,Production.Product.Name AS Product,Production.Product.Color,Production.Product.StandardCost,Production.Product.ListPrice,Production.ProductModel.ProductModelId,Production.ProductModel.Name AS Model



PRCTICA 3.1: Ejecutar las siguientes consultas y analizar sus diferencias.

Agregando informacin

sobre el Modelo

Falla corregida

Nombre completo

17/03/2010

3


Esquemas y Resolucin de Nombres

Cada objeto en SQL Server es identificado por cuatro partes Servidor Base de datos Esquema Objeto

Algunos de acuerdo al contexto pueden ser omitidos

EjemploSELECT AdventureWorks2008.Production.Product.ProductIDFROM WoodVista.AdventureWorks2008.Production.Product

El esquema por defecto en dbo.



Alias de Columnas

Razones para cambiar el nombre a una columna

Ms fcil de entender

Nombre ms descriptivo

Compatibilidad hacia atrs

Nombre repetidos en consultas multi-tablas

PRACTICA 3.2: Ejecutar el ejemplo de tres diferentes sintaxis para implementar un alias en una columna.


Sintaxis Descripcin Ejemplo

Column AS Alias

La tcnica ms legible.

SELECT ListPrice - StandardCost AS MarginFROM Production.Product

Column Alias La tcnica mas comn

SELECT ListPrice - StandardCost MarginFROM Production.Product

Alias = Column No es comn en T-SQL

SELECT Margin = ListPrice - StandardCostFROM Production.Product

17/03/2010

4


Columnas Calculadas y Derivadas

La columna calculada es resultado de una expresin o calculo. PRCTICA 3.3: Ejecutar los ejemplos mostrados

1. Calculando un cantidad a partir de dos columnasSELECT SalesOrderID, ProductID

,UnitPrice * OrderQty As PurchasePriceFROM Sales.SalesOrderDetail

2. Usando una funcinSELECT NationalIDNumber,BirthDate

,DATEDIFF(YY, BirthDate, GETDATE()) As AgeFROM HumanResources.Employee

3. Con un valor constanteSELECT Name, ListPrice,

'Mountain Bike' AS SubCategoryNameFROM Production.Product WHERE ProductSubCategoryID = 1

4. Concatenando campos y con alias en columnas y tablasSELECT

PP.FirstName + ' ' + PP.LastName AS Name, PP.Title AS Titulo

FROM Person.Contact AS PPORDER BY Titulo



Filtrando Registros

Existen dos maneras para limitar la cantidad de registros resultantes de una consulta WHERE revisa cada registro contra un criterio de

filtraje TOP limita los registros de acuerdo a una cantidad de

registros

Ejemplos


SELECT Name, StandardCost, ColorFROM Production.ProductWHERE Color = Black

SELECT Name, ListPriceFROM Production.ProductWHERE ListPrice < 5.00

17/03/2010

5


Operadores de Comparacin

Operadores

Operador Descripcin

= Igual que

!= No igual que

< Menor que

> Mayor que

!< No menor que

!> No mayor que

= Mayor o igual que

LIKE Para comparar valores de caracteres con comodines

Operador LIKE

Comodn Descripcin

% Cualquier cadena de cero o ms caracteres

_ Cualquier nico carcter

[] Cualquier nico carcter dentro del rango especificado

[^] Cualquier nico carcter fuera del rango especificado



PRCTICA 3.4a: Creando Tabla de pruebas

Tabla de Pruebas

Lastname Firstname Position

Flintstone Fred Bronto Driver

Rubble Barney Accountant

Turley Paul Developer

Wood Dan DBA

Rockhead Don SystemAdministrator

Rockstone Pauline Manager

Ejecutar las siguientes Instrucciones USE AdventureWorks2008GO

CREATE TABLE dbo.SlateGravel( LastName varchar(25) NULL

,FirstName varchar(25) NULL,Position varchar(25) NULL);

INSERT SlateGravelVALUES

(Flintstone, Fred, Bronto Driver),(Rubble, Barney, Accountant),(Turley, Paul, Developer),(Wood, Dan, DBA),(Rockhead, Don, System Administrator),(Rockstone, Pauline, Manager)


17/03/2010

6


PRCTICA 3.4b: Analizando el uso del operador LIKE

1. Ejecutar las siguientes instrucciones y analizar el resultado.


No Instruccin Resultado Esperado

1 SELECT * FROM SlateGravelWHERE LastName LIKE Flint%

Flintstone

2 SELECT * FROM SlateGravelWHERE LastName LIKE %stone

Flintstone y Rockstone

3 SELECT * FROM SlateGravelWHERE LastName LIKE %sto%

Flintstone y Rockstone

4 SELECT * FROM SlateGravelWHERE LastName LIKE _urley

Turley


PRCTICA 3.4c: Analizando el uso del operador LIKE

1. Ejecutar las siguientes instrucciones y analizar el resultado.


No Instruccin Resultado Esperado

5 SELECT * FROM SlateGravelWHERE FirstName LIKE D*ao+n

Dan y Don

6 SELECT * FROM SlateGravelWHERE FirstName LIKE D*a-o+n

Dan y Don

7 SELECT * FROM SlateGravelWHERE FirstName LIKE D*^o+n

Dan

8 SELECT * FROM SlateGravelWHERE FirstName NOT LIKE Dan

Dan

17/03/2010

7


Comparaciones Lgicas

PRACTICA 3.5: Ejecutar los ejemplos con diferentes operadores lgicos.


Sintaxis Descripcin Ejemplo

AND Todos los criterios deben ser verdaderos

SELECT ProductID, Name, ListPriceFROM Production.ProductWHERE ProductSubCategoryID = 1 AND ListPrice < 1000

OR Al menos uno de los criterios debe ser verdadero

SELECT ProductID, Name, ListPriceFROM Production.ProductWHERE ProductSubCategoryID = 1 OR ListPrice < 1000

NOT El criterio debe ser falso SELECT ProductID, Name, ListPriceFROM production.ProductWHERE NOT ProductSubCategoryID = 2

NULL La columna tiene un valor nulo

SELECT ProductID, Name, ColorFROM Production.ProductWHERE Color IS NULL

NOT NULL La columna no tiene un valor nulo

SELECT ProductID, Name, ColorFROM Production.ProductWHERE Color IS NOT NULL


El operador BETWEEN y la funcin IN

PRACTICA 3.6aEl operador BETWEEN

1. Ejecutar las siguientes instruccionesSELECT NationalIDNumber, LoginID

FROM HumanResources.Employee

WHERE BirthDate > = 1962-1-1

AND BirthDate < = 1985-12-31

2. Ejecutar utilizando su equivalente con el operador BETWEENSELECT NationalIDNumber, LoginID

FROM HumanResources.Employee

WHERE BirthDate BETWEEN 1962-1-1 AND 1985-12-31

PRACTICA 3.6bLa funcin IN

1. Ejecutar las siguientes instruccionesSELECT ProductID

,Name AS Product


WHERE ProductSubCategoryID = 1

OR ProductSubCategoryID = 2


2. Ejecutar utilizando su equivalente con la funcin INSELECT ProductID

,Name AS Product


WHERE ProductSubCategoryID IN (1,2,3)


17/03/2010

8


La funcin IN con sub-consultas

PRACTICA 3.6c: Ejecutar la siguiente consulta que recupera todos los productos cuyos ProductCategoryID son 1 2. Como la tabla de productos solo contiene sub-categorias se debe hacer una sub consulta a la tabla de categora.

SELECT ProductID

,Name AS Product


WHERE ProductSubCategoryID IN (

SELECT ProductSubCategoryID

FROM Production.ProductSubCategory

WHERE ProductCategoryID IN (1,2))



Precedencia de los Operadores

Orden de Precedencia

Primero se procesa el

NOT

despus

AND

despus

OR

PRCTICA 3.7: Encontrar error1. Verificar que la siguiente consulta

genera una lista de bicicletas montaeras y bicicleta de carretera con precios mayores de $500 y menores de $1000

SELECT Name

,ProductNumber

,ListPrice

,ProductSubCategoryID


WHERE ProductSubCategoryID = 1


AND ListPrice > 500

AND ListPrice < 1000

2. Utilizar parntesis para lograr que la consulta funcione correctamente


17/03/2010

9


Orden de Registros

Se utiliza la clausula ORDER BY despus de WHERE

Puede contener una o mas columnas delimitadas por comas

Se puede especificar la direccin ASC (ascendente) DESC (descendente)

Si no se especifica es ascendente

Tambin se pueden escribir campos calculados en lugar de columnas

PRCTICA 3.8: Ejecutar las siguientes consultas y analizar los resultados

1.SELECT Name AS Product

,ListPrice

,StandardCost


WHERE ListPrice > 0

ORDER BY ListPrice DESC, StandardCost

2.SELECT SalesOrderID, ProductID

,UnitPrice * OrderQty As PurchasePrice

FROM Sales.SalesOrderDetail

order by UnitPrice * OrderQty

3.SELECT SalesOrderID, ProductID

,UnitPrice * OrderQty As PurchasePrice

FROM Sales.SalesOrderDetail

order by PurchasePrice



Operadores TOP, TIE y PERCENT

TOP n Se escribe despus de SELECT y n

indica que se mostrarn las primeras n lneas segn el orden establecido en la consulta

WITH TIES Se escribe a continuacin de TOP

n y mostrar las lneas que coincidan con la lnea n a continuacin de las primeras n lneas

PERCENT Muestra un porcentaje de la

cantidad de registros en la tabla en lugar de una cantidad de lneas

PRCTICA 3.9: Ejecutar las siguientes consultas y analizar los resultados1.

SELECT TOP 10 Name, ListPrice


ORDER BY ListPrice DESC

2.SELECT TOP 10 WITH TIES Name,

ListPrice



3.SELECT TOP 10 PERCENT Name,

ListPrice




17/03/2010

10


La instruccin CASE

Es una expresin escalar que regresa un valor basado en una lgica condicional

Se puede usar en SELECT, WHERE, HAVING, ORDER BY y otros

Si no se escribe ELSE entonces por defecto se tiene ELSE NULL

Dos formatos Una funcin CASE simple compara una expresin con

un juego simple de expresiones para determinar el resultado

Una funcin CASE de bsqueda evala una expresin booleana para determinar el resultado



PRCTICAS con la instruccin CASE

PRCTICA 3.10a: Ejecutar la siguiente consulta y analizar los resultados

SELECT ProductNumber

,Category =

CASE ProductLine

WHEN 'R' THEN 'Road

WHEN 'M' THEN 'Mountain

WHEN 'T' THEN 'Touring

WHEN 'S' THEN 'Other sale items'

ELSE 'Not for sale'

END

,Name


ORDER BY ProductNumber

PRCTICA 3.10b: Ejecutar la siguiente consulta y analizar los resultados

SELECT ProductNumber

,Name

,'Price Range' = CASE

WHEN ListPrice = 0

THEN 'Mfg item - not for resale'

WHEN ListPrice < 50

THEN 'Under $50'

WHEN ListPrice >= 50 and ListPrice < 250

THEN 'Under $250'

WHEN ListPrice>=250 and ListPrice < 1000

THEN 'Under $1000'

ELSE 'Over $1000'

END


ORDER BY ProductNumber


17/03/2010

11


Ejercicios 1/3

Ejercicio 3.1: Escriba una consulta de regrese los registros de empleados de AdventureWorks. Incluya las columnas NationalIDNumber, LoginID, JobTitle, BirthDate, MaritalStatus, y HireDate en el resultado. Ejecutar la consulta y vea los resultados.

Ejercicio 3.2: Modificar el ejercicio 3.1 para aadir una nueva columna AgeAtHire, la cual es el resultado de la diferencia entre las columnas HireDate y BirthDate. (Pista: utilice la funcin DATEDIFF)

Ejercicio 3.3 Regrese todos los registros de Product en la tabla [Production.Product] en AdventureWorks que tiene 3 das o ms para su produccin. Incluya el nombre [Name] y el precio de lista [ListPrice]

Ejercicio 3.4: Regrese una lista de los 10 productos ms caros de la tabla [Production.Product] en AdventureWorks que tiene un nmero de producto comenzando con BK. Incluya solamente las columnas *ProductID], [Name], [ProductNumber], [Color], y [ListPrice]. Cuando termine, revise si existe algn otro producto con el mismo precio que el decimo producto en la lista.



Ejercicios 2/3

Ejercicio 3.5: Recuperar todas las lneas y columnas de la tabla [ProductSubcategory] y hacer las modificaciones que se solicitan:1. Obtenga todas las filas y columnas en la tabla [ProductSubcategory].

2. Modifique la consulta para mostrar solo las columnas: [ProductSubcategoryID], [ProductCategoryID], [Name] y [ModifiedDate]

3. Modifique la consulta para mostrar solo las filas donde bike esta en alguna parte de la columna [Name]

4. Modifique la consulta para agregar un alias a las columna [Name]

5. Modifique la consulta para ordenar el resultado por el nombre de la sub-categoria (columna Name)


17/03/2010

12


Ejercicios 3/3

Ejercicio 3.6: Obtenga las ordenes puestas en Junio 2004. (Tablas: SalesSalesOrderHeader)

Ejercicio 3.7: Obtenga las ordenes puestas en el ltimo da del mes. (Tablas: Sales. SalesOrderHeader)

Ejercicio 3.8: Obtenga los empleados cuyo apellido (LastName) contienen la letra a tres veces o ms. (Tablas: Person.Contact)

Ejercicio 3.9: Encuentre la instruccin SELECT que regresa para cada empleado el sexo basado en su titulo (Title). Por ejemplo Ms. y Mrs. regresa Female; para Mr regresa Male; y para cualquier otro caso (por ejemplo Dr.) regresar Unknown. Mostrar el ID, Nombre, Apellido, Titulo y Sexo. (Tablas: Person.Contact)

Ejercicio 3.10: En el ejercicio anterior ordene los resultados por el titulo, pero teniendo los NULL ordenados al final. Note que el orden por defecto pone los NULL al principio o sea antes de los valores NOT NULL. (Tablas: Person.Contact)


curso sql server prog u2-03

Documents