• Author / Uploaded
• Gerson Duran

Citation preview

Universidad de El Salvador Facultad Multidisciplinaria Paracentral Departamento de informática.

Temática: Tarea 3 SQL server, consultas Presentado por: Boris Ricardo Miranda Ayala Carnet: MA14049 Romario Abelardo Villalobos Rivas Carnet: VR14022 Juan Carlos Moz Alfaro Carnet: MA14031

Docente: Ing. Herbert Monge

TAREA SQL 1-Crear una base de datos con nombre NBA y dentro de esta crear las tablas estadísticas, jugadores, equipos, partidos como lo muestra el siguiente script. USE master GO if exists (select * from sysdatabases where name='nba') drop database nba go EXECUTE (N'CREATE DATABASE nba') go if object_id('estadisticas') is not null drop table estadisticas ;

if object_id('partidos') is not null drop table partidos ; go if object_id('jugadores') is not null drop table jugadores ; if object_id('equipos') is not null drop table equipos ; go use nba go create table estadisticas ( temporada varchar(5) NOT NULL, jugador int NOT NULL, Puntos_por_partido float NULL, Asistencias_por_partido float NULL, Tapones_por_partido float NULL, Rebotes_por_partido float NULL, primary key (temporada, jugador) ) go create table jugadores ( codigo int NOT NULL, Nombre varchar(30) NULL, Procedencia varchar(20) NULL, Altura varchar(4) NULL, Peso int NULL, Posicion varchar(5) NULL, Nombre_equipo varchar(20) NULL, primary key (codigo)

) go create table equipos ( Nombre varchar(20) NOT NULL, Ciudad varchar(20) NULL, Conferencia varchar(4) NULL, Divicion varchar(9) NULL, primary key (Nombre) ) go create table partidos ( codigo int NULL, equipo_local varchar(20) NULL, equipo_visitante varchar(20) NULL, puntos_local int NULL, puntos_visitante int NULL, temporada varchar(5) NULL constraint fk_equipo_local foreign key (equipo_local) references equipos (Nombre), constraint fk_equipo_visitante foreign key (equipo_visitante) references equipos (Nombre) on update cascade ) go alter table jugadores add constraint fkjugador_equipo foreign key (Nombre_equipo) references equipos (Nombre) on update cascade go alter table estadisticas add constraint fk_estadistica_jugador foreign key (jugador) references jugadores (codigo) on update cascade on delete cascade go

2-Obtener los primeros 100 jugadores que pesan más que la media de peso de todos los jugadores españoles y ordenados por el peso. select top 100 Nombre,peso,Procedencia from jugadores where peso>(select avg(peso) from jugadores where Procedencia like '%spain') order by peso go

3- Obtener la suma de los puntos por partido de Pau Gasol en toda su carrera. SELECT SUM(Puntos_por_partido) AS PUNTOS_PAU from estadisticas where jugador = (select codigo from jugadores where Nombre LIKE ('Pau Gasol'))

4- Obtener los puntos por partido y el número de asistencias de los jugadores de los Cavaliers en la temporada 2007/2008 no utilice INNER. select sum(Puntos_por_partido),sum(Asistencias_por_partido) from estadisticas where jugador in ( SELECT distinct (codigo) From jugadores where Nombre_equipo in ('cavaliers') ) and temporada = '07/08'

5- Obtener los puntos máximos y mínimos de los jugadores de la conferencia este. select max(e.Puntos_por_partido) as puntosMax,min(e.Puntos_por_partidos) as puntosMin from estadisticas as e inner join jugadores as j on e.jugador=j.codigo inner join equipos as eq on eq.Nombre=j.Nombre_equipo where eq.Conferencia='East' go

6- Obtener la media de puntos por partido de Marko Jaric en las temporadas del 2003/2004 al 2005/2006. SELECT ROUND (avg(Puntos_por_partido),2) as media from estadisticas where jugador = (SELECT codigo FROM jugadores where Nombre = 'Marko Jaric') and temporada = '03/04' or temporada = '05/06'

7- Obtener el nombre de equipos los jugadores Nuggets y las conferencias que pertenece sin usar INNER JOIN select a.equipo,a.jugador,a.conferencia from (select j.Nombre as jugador,j.Nombre_equipo as equipo, (select conferencia from equipos where Nombre like 'Nuggets') as conferencia from jugadores as j where j.Nombre_equipo in(select Nombre from equipos as eq where eq.Nombre like 'Nuggets') )a go

8- Obtener el nombre y peso de los jugadores de la NBA que hayan hecho una media de más de 25 puntos por partido en alguna temporada. select Nombre,Peso from jugadores where codigo in(select distinct (jugador) from estadisticas where Puntos_por_partidos>25) order by Nombre

9- Obtener el nombre, la altura y el peso de los jugadores que juegan en la ciudad de Los Ángeles y su lugar de procedencia comience con S. select nombre,Altura from jugadores where Nombre_equipo in (select nombre from equipos where ciudad = 'Los Angeles' ) and Procedencia like 'S%'

10- Obtener la media de puntos por temporada de los jugadores que se llamen Steve y pesen entre 150 y 300 libras no usar join. select temporada, Puntos_por_partido,jugador from estadisticas where jugador in ( select codigo from jugadores where nombre like 'Steve_%' and Peso between 150 and 300) order by jugador

11- ¿Cuántas letras tiene el equipo con el nombre más largo de la NBA?. Obtener también el nombre del equipo y la ciudad de donde procede. (Usar la función LENGTH, aunque en realidad esta función lo que nos dice es el nº de caracteres que ocupa el nombre del equipo en la BBDD incluido los espacios en blanco, no su número de letras, pero haremos una excepción). select max(Len(Nombre)) as cantidad,Nombre,Ciudad from equipos group by Nombre,Ciudad having LEN(Nombre)>=all (select (Len(Nombre)) from equipos)

12- Obtener la ciudad con el equipo cuya media de altura de los jugadores sea la más baja. select Nombre, Ciudad

from equipos where Nombre = (select top 1 Nombre_equipo from jugadores as j group by Nombre_equipo order by avg(CONVERT (decimal(6,2), REPLACE(j.altura,'-','.'))))

13- Obtener el número de jugadores que tiene cada equipo de la Conferencia Oeste. select Nombre_equipo,count(*) from jugadores where Nombre_equipo in (select Nombre from equipos where Conferencia = 'West') group by Nombre_equipo order by count(*)

14- seleccione los equipos cuya ciudad es Toronto y su procedencia es española select e.Nombre, e.Ciudad,j.Procedencia from equipos as e inner join jugadores as j on j.Nombre_equipo=e.Nombre where e.Ciudad='Toronto' and j.Procedencia='Spain'; go

15- Obtener las asistencias por partido y los tapones por partido de los jugadores de los Chicago Bulls en la temporada 2005/2006 (Utilice procedimientos). create procedure AsistenciaTApobesBulls as select sum (Asistencias_por_partido) AS asistencias, sum (Tapones_por_partido) as tapones ,temporada from estadisticas where jugador in( select codigo from jugadores where Nombre_equipo = 'Bulls') and temporada = '05/06' group by temporada go execute AsistenciaTApobesBulls go

16- Obtener la media de puntos por partido y la media de asistencias por partido de los Warriors en todas las temporadas (Utilice procedimientos) create procedure puntos_warrios as select p.temporada, (select avg(puntos_visitante)from partidos p2 where equipo_visitante = 'Warriors' and p.temporada = p2.temporada) as punto_visitante , (select avg(puntos_local)from partidos p3 where equipo_local = 'Warriors' and p.temporada = p3.temporada) as punto_local from partidos as p group by temporada order by p.temporada

go execute puntos_warrios go

17- Obtener la media de puntos por temporada de los jugadores que se llamen Steve y pesen más de 210 libras (Utilice funciones). create function dbo.mediaTempora() returns @mediaTempora table(nombre nvarchar(50),peso float,media numeric(9,2)) as begin insert @mediaTempora select j.Nombre,j.Peso,avg(e.Puntos_por_partido) from jugadores as j inner join estadisticas as e on e.jugador=j.codigo where j.Nombre like '%Steve%' and j.Peso>210 group by j.Nombre,j.Peso; return end go select*from dbo.mediaTempora() go

18-Obtener el nombre, la altura y el peso de los jugadores que juegan en la ciudad de Los Ángeles y que sean españoles (Utilice funciones). create function dbo.jugadorCiudadProcedencia(@ciudad nvarchar(20), @procedencia nvarchar (10)) returns @jugadorCiudadProcedencia TABLE (nombreJugador nvarchar (20) , altura nvarchar (10)) as begin insert @jugadorCiudadProcedencia

select Nombre,Altura from jugadores where Nombre_equipo in (select Nombre from equipos where Ciudad = @ciudad )and Procedencia = @procedencia returns END GO select * from dbo.jugador_ciudad_procedenci('los angeles', 'spain')

19-Obtener los puntos por partido de los jugadores de los Lakers en todas las temporadas, por temporada utilizando vistas. create view puntoslakers as select jugadores.Nombre, Puntos_por_partido, temporada from estadisticas join jugadores on jugadores.Nombre_equipo like '%Lakers' and jugadores.codigo = estadisticas.jugador group by Nombre, Puntos_por_partido, temporada go select * from puntoslakers go

20-Obtener el equipo y jugador que haya jugado más temporadas use UNION y vistas create view masTemporad as (select j.Nombre from jugadores as j where j.codigo=(select top 1 jugador from estadisticas group by jugador order by COUNT(*) desc)) union (select j.Nombre_equipo from jugadores as j where j.codigo=(select top 1 jugador from estadisticas group by jugador order by COUNT(*) desc)) union (select convert(varchar(12),count(temporada))from estadisticas where jugador=(select top 1 jugador from estadisticas group by jugador order by COUNT(*) desc)) go select*from masTemporad; go

21-seleccionar el nombre de equipo local, puntos equipo local, nombre equipo visitante, puntos equipo visitante, diferencia de puntos Opcion 1 select (select Nombre from equipos as e2 where e2.Nombre =p1.equipo_local ) equipo_local, puntos_local, (select Nombre from equipos as e3 where e3.Nombre = p1.equipo_visitante ) equipo_visitante, puntos_visitante, (puntos_local - puntos_visitante) as diferencia from partidos as p1 where temporada = '00/01'

opción 2 select equipo_local,puntos_local,equipo_visitante,puntos_visitante, (puntos_local - puntos_visitante) as diferencia from partidos where temporada = '00/01'

22-Optener para temporada 03-04 el número promedio de puntos que hace el jugador centro como visitante opcion 1 select avg(Puntos_por_partido) as puntos, jugadores.Nombre, Posicion, estadisticas.temporada, partidos.equipo_visitante from jugadores full join estadisticas on jugadores.codigo = estadisticas.jugador full join equipos on jugadores.Nombre_equipo = equipos.Nombre full join partidos on equipos.Nombre = partidos.equipo_visitante where estadisticas.temporada ='03/04' and jugadores.Posicion = 'C' and partidos.equipo_visitante = equipos.Nombre group by jugadores.Nombre, Posicion, estadisticas.temporada, partidos.equipo_visitante

opcion 2 select avg(Puntos_por_partido) as puntos, jugadores.Nombre, Posicion from jugadores full join estadisticas on jugadores.codigo = estadisticas.jugador where estadisticas.temporada ='03/04' and jugadores.Posicion = 'C' and jugadores.Nombre_equipo in(select Nombre from equipos join partidos on equipos.Nombre = partidos.equipo_visitante) group by jugadores.Nombre, Posicion

23-De la conferencia este verificar que jugador ha jugado en diferente equipo.