ejemplos select funciones

PROFESOR: LIÑÁN RODRÍGUEZ, Julio César Página 1

FUNCIONES (CADENA, FECHAS) DE SQL SERVER

Funciones Matemáticasselect sqrt(9) --3.00 – Raiz Cuadradaselect round(1234.56,0) --1235.00 – Redondear sin decimalesselect round(1234.56,1) --1234.60 – Redondear a un decimalselect round($1234.56,1) --1234.6000select power(2,8) –256 -- Potenciaselect floor(1332.39) –1332 – el menor enteroselect floor(1332.59) --1332 – el menor enteroselect ceiling(1332.39) –1333 – el mayor enteroselect ceiling(1332.59) --1333 – el mayor enteroselect square(9) --81.0 – cuadrado de un numero

Funciones de Cadenaselect ascii('A') --65select char(65) --Aselect charindex('bc','abcd') --2select patindex('%cd%','abcd') --3select replicate('abc',2) --abcabcselect right('abcd',2) --cdselect reverse('abcd') --dcbaselect reverse(123) --321select substring('instituto Idat',11,4) -- Idat

Ejemplo: Mostrar solo el Apellidodeclare @nombre varchar(30)set @nombre='Juan, Perez Vargas'select apellidos=ltrim(substring(@nombre,

charindex(',',@nombre)+1, len(@nombre)-charindex(',',@nombre)+1))go

-- Perez Vargas

mailto:@nombre

mailto:@nombre

mailto:@nombre

mailto:@nombre

mailto:@nombre

mailto:@nombre



Funciones de Fecha

Valores Datepart

datepart Abreviación Valoresday dd 1-31

day of year dy 1-366

hour hh 0-23

millisecond ms 0-999

minute mi 0-60

month mm 1-12

second ss 0-59

week wk 0-53

weekday dw 1-7(dom-sab)

year yy 1753-9999

1.- dateadd(datepart, numero, fecha) : devuelve un valor datetimeque se basa en la suma de un intervalo a la fecha especificada.

select dateadd(mm,6,'1/1/04') -- 2004-07-01 00:00:00.000select dateadd(mm,-5,'1/1/04') -- 2003-08-01 00:00:00.000

2.- datediff(datepart,fecha1,fecha2) : devuelve el numero dedateparts entre 2 fechas.

select datediff(mm,'1/1/04',getdate()) -- 9 meses

3.- datename(datepart,fecha) : devuelve el nombre de undatepart especificado por la fecha mostrada.

select datename(month,getdate()) -- Octubreselect datename(mm,getdate()) -- Octubreselect datename(dw,getdate()) – Viernes



4.- datepart(datepart,fecha) : devuelve el valor entero de undatepart especificada por la fecha mostrada.

day(fecha), month(fecha), year(fecha), getdate()

getdate() : devuelve la fecha y hora delsistema. select getdate() -- 2004-10-0113:47:15.280

-- listar los empleados con el número de años trabajadosuse pubsGo

select emp_id as idempleado,lname+', '+substring(fname,1,1)+'.' as nombre,'ha trabajado durante ' ,datediff(year,hire_date,getdate()),' años '

from employeego

idempleado nombre---------- ------------------------- --------------------- --------- ------PMA42628M Accorti,P.

ha trabajadodurante

12 añosPSA89086M Afonso,

P.ha trabajadodurante

14 años.

.MFS52347MSommer, M.

ha trabajadodurante

14 añosGHT50241M Thomas,

G.ha trabajadodurante

16 añosDBT39435M Tonini,

D.ha trabajadodurante

14 años

(43 filas afectadas)

select ascii('^') -- Alt + 94use pubsgo

-- autores cuyo apellido no estén comprendidos entre c y hselect au_lname from authors where au_lname like '[^c-h]%' ;



au_lname--------------------------------BennetBlotchet-Halls..StraightStringerWhiteYokomoto

(15 filas afectadas)

-- autores cuyo apellido no sean m,c,d,h,g,y,s,k ó rselect au_lname from authors where au_lname like '[^mcdhgyskr]%' ;

au_lname--------------------------------BennetBlotchet-HallsLocksleyO'LearyPanteleyWhite

(6 filas afectadas)

-- lista los libros en donde en el campo notas estén configuradoscomo nulosselect title,notes from titles where notes is null ;

title notes-------------------------------------------------------------------The Psychology of Computer Cooking NULL

(1 filas afectadas)



-- lista los libros donde tengan notas que NO esténconfigurados como nulosselect title,notes from titles where notes is not null ;

--lista los autores que tengan libros y que su apellido--terminen en una vocalselect au_fname,au_lname from authorswhere exists(select au_id from titleauthor wheretitleauthor.au_id=authors.au_id)and right(au_lname,1) like '[a,e,i,o,u]' ;

select au_fname,au_lname from authorswhere au_id in (select au_id from titleauthor wheretitleauthor.au_id=authors.au_id)and right(au_lname,1) like '[a,e,i,o,u]' ;

au_fname au_lname----------------- ------------------------------Michel DeFranceInnes del CastilloJohnson WhiteAkiko Yokomoto

(4 filas afectadas)

-- lista los libros que no se hayan vendidoselect title_id,title from titles where ytd_sales is null ;

title_id title-------- --------------------------------------------------------------------MC3026 The Psychology of Computer CookingPC9999 Net Etiquette

(2 filas afectadas)

-- los grupos que no tienen filas que cumplen con la condición--contendrán NULL



select type,pub_id,avg(price) as prom_precio from titleswhere royalty=12 group by all type,pub_id ;

type pub_id prom_precio------------ ------ -------------------

--psychology 0736 10.9500mod_cook 0877 19.9900psychology 0877 NULLtrad_cook 0877 NULLUNDECIDED

0877 NULLbusiness 1389 NULLpopular_comp

1389 NULL

(7 filas afectadas)

select type,pub_id,avg(price) as prom_precio from titleswhere royalty=12 group by type,pub_id ;

type pub_id prom_precio------------ ------ ---------------psychology 0736 10.9500mod_cook 0877 19.9900

(2 filas afectadas)

select type,pub_id,avg(price) as prom_preciofrom titles group by type,pub_id ;

type pub_id prom_precio------------ -------- -------------------

--psychology 0736 11.4825mod_cook 0877 11.4900psychology 0877 21.5900trad_cook 0877 15.9633UNDECIDED

0877 NULLbusiness 1389 13.7300popular_comp

1389 21.4750



(7 filas afectadas)

Advertencia: valor NULL eliminado por el agregado u otra operación SET.

-- ahora del listado(agrupado) aquellos cuyo precio-- promedio sean mayores a 15select type,pub_id,avg(price) as prom_precio from titlesgroup by type,pub_id having avg(price)>15 ;

type pub_id prom_precio------------ -------- --------------psychology 0877 21.5900trad_cook 0877 15.9633popular_comp 1389 21.4750


-- para utilizar Having sin un group by, se debe tener una función de-- agregado en la lista de selección y en la clausula Having

select sum(price) as suma from titleswhere type='mod_cook' having sum(price)>20 ;

suma---------------------236.2600

-- lista los libros que tengan más de 1 autor y devuelve el-- numero de autores el title_idselect title_id,count(title_id) as num_autores from titleauthorgroup by title_id having count(title_id)>1 ;

title_id num_autores--------- ---------------BU1032 2

BU1111 2MC3021 2PC8888 2PS1372 2



PS2091 2TC7777 3

(7 filas afectadas)

Convert y Cast

Las funciones CAST y CONVERT convierten un valor (una variablelocal, una columna u otra expresión) de un tipo de datos a otro.

convert(tipodato[(longitud)],expresion [,estilo])

sin siglo(aa) con siglo(aaaa) Estandar formato fecha(Salida)1 101 USA mm/dd/yy3 103 Britanico/

Francesdd/mm/yy

5 105 Italiano dd-mm-yy8 108 - hh:mm:ss

Compute y Compute BySintaxis:select lista_columnas from lista_tablaswhere criterio[compute] expresion(es)_agregado(s)[by] lista_columnasgo

select type,sum(ytd_sales) from titles group by type ;

select type,ytd_sales from titles order by type compute sum(ytd_sales)by type ;

type ytd_sales------------ ------business 4095business 3876business 18722business 4095

sum===========



30788

select type,ytd_sales,sum(ytd_sales) assuma from titles group by type,ytd_salesorder by typecompute sum(ytd_sales) by type ;

type ytd_sales suma------------ ----------- ----------

-business 3876 3876business 4095 8190business 18722 18722

sum===========26693

Rollup y Cube

Sintaxis:SELECT lista_columnas FROM lista_tablasWHERE criterio [GROUP BY [ALL]expresion(es)_noagregado(s) [WITH{ROLLUP|CUBE}]]GO

USE pubsgo

select type,pub_id,Convert(Varchar(10),sum(Convert(Money,ytd_sales)),1) as sumafrom titles group by type,pub_idwith rollup



type pub_id suma------------ -------- ----------business 1389 30,788.00business NULL 30,788.00mod_cook 0877 24,278.00mod_cook NULL 24,278.00popular_comp 1389 12,875.00popular_comp NULL 12,875.00psychology 0736 9,564.00psychology 0877 375.00psychology NULL 9,939.00trad_cook 0877 19,566.00trad_cook NULL 19,566.00UNDECIDED 0877 NULLUNDECIDED NULL NULLNULL NULL 97,446.00(14 filas afectadas)


-- ROLLUP produce una fila en la salida x cada fila de la tabla titles con unsolo tipo(type) y pub_id

select type,pub_id,Convert(Varchar(10),sum(Convert(Money,ytd_sales)),1) assuma from titles group by type,pub_id with cube ;

type pub_id suma------------ -------- ----------business 1389 30,788.00business NULL 30,788.00mod_cook 0877 24,278.00mod_cook NULL 24,278.00popular_comp 1389 12,875.00popular_comp NULL 12,875.00psychology 0736 9,564.00psychology 0877 375.00psychology NULL 9,939.00trad_cook 0877 19,566.00trad_cook NULL 19,566.00



UNDECIDED 0877 NULLUNDECIDED NULL NULLNULL NULL 97,446.00NULL 0736 9,564.00NULL 0877 44,219.00NULL 1389 43,663.00(17 filas afectadas)


-- aparte de la fila resumen de tipo ROLLUP, CUBE te muestra otra fila-- al final antes del total general con la suma x cada pub_id(o columna de-- grupo)

Vistas

CREATE VIEW nomb_vista(campo_1,…, campo_n) [WITH ENCRYPTION]AS

instruccion_sql

[WITH CHECKOPTION] GO

No pueden incluirse Order by salvo que incluya la clausula TOP nNo puede incluirse compute, ni compute byNo se pueden utilizar tablas temporales, esto significa que no se puedeutilizar select into

-- creando una tabla con los registros de la tabla Authors a traves deSELECT … INTO

select * into tblautores from authors ;

-- creando la vista utilizando la tabla creada recientementecreate view v_autoresasselect * from tblautores-- where au_lname like 'g%'go



-- listando el contenido de la vista, como si fuera una tablaselect * from v_autores ;

-- insertando una fila directamente a la vistainsert v_autores(au_id,au_lname,au_fname, phone,contract)values('100-05-1000','perez', 'juan','12345',1)go

-- si listamos la vista o la tabla el registro se muestraselect * from v_autores ;select * from tblautores ;

-- mostrando el codigo de la vistasp_helptext v_autores ;

-- modificando la vista para incluir un filtro en el apellido y-- activando with check option

alter view v_autoresasselect * from tblautoreswhere au_lname like 'g%'with check optiongo

with check option : obliga a que todas las instrucciones sql demodificación de datos utilicen los criterios establecidos en la clausulawhere.

-- volviendo a insertar una nueva fila en la vistainsert v_autores(au_id,au_lname,au_fname,phone,contract)values('200-10-2000','diaz','pedro','12345',1) ;

-- no va a insertar porque la vista exige que los valores a ingresar en el-- campo au_lname empiezen con g



insert v_autores(au_id,au_lname,au_fname,phone,contract)values('200-10-2000','gomez','pedro','12345',1) ;

-- ahora si se pudo insertar y lo mostraremosselect * from v_autores ;

-- listando las tablas y/o vistas que dependan de una vista puede-- utilizarse tambien para stored procedure y triggerssp_depends v_autores ;

-- eliminando la vistadrop view v_autores ;

-- creando la vista pero de manera encriptadacreate view v_autoreswith encryptionasselect * from authors where au_lname like 'g%'with check optiongo

-- al tratar de ver nuevamente el codigo de la vista no muestra nada,-- ya que fueron cifradossp_helptext v_autoresgo

ejemplos select funciones

Documents

de sql server

autores cuyo apellido

otra operacin set

lista los libros

octubre select datename

type compute sum

7 filas afectadas

bennet blotchet