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 [Link]=[Link]
inner join equipos as eq on [Link]=j.Nombre_equipo
where [Link]='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 [Link],[Link],[Link] from
(select [Link] 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
[Link] 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([Link],'-','.'))))
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 [Link], [Link],[Link] from equipos as e inner join jugadores as j on
j.Nombre_equipo=[Link]
where [Link]='Toronto' and [Link]='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 [Link],
(select avg(puntos_visitante)from partidos p2 where equipo_visitante = 'Warriors' and [Link] =
[Link]) as punto_visitante ,
(select avg(puntos_local)from partidos p3 where equipo_local = 'Warriors' and [Link] =
[Link]) as punto_local
from partidos as p
group by temporada
order by [Link]
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 [Link]()
returns @mediaTempora table(nombre nvarchar(50),peso float,media numeric(9,2))
as
begin
insert @mediaTempora
select [Link],[Link],avg(e.Puntos_por_partido) from jugadores as j inner join estadisticas as e
on [Link]=[Link] where [Link] like '%Steve%' and [Link]>210
group by [Link],[Link];
return
end
go
select*from [Link]()
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 [Link](@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 [Link], Puntos_por_partido, temporada
from estadisticas join jugadores on jugadores.Nombre_equipo like '%Lakers'
and [Link] = [Link]
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 [Link] from jugadores as j
where [Link]=(select top 1 jugador from estadisticas
group by jugador
order by COUNT(*) desc))
union
(select j.Nombre_equipo from jugadores as j
where [Link]=(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 [Link] =p1.equipo_local ) equipo_local,
puntos_local,
(select Nombre from equipos as e3 where [Link] = 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, [Link], Posicion,
[Link], partidos.equipo_visitante
from jugadores full join estadisticas on [Link] = [Link]
full join equipos on jugadores.Nombre_equipo = [Link]
full join partidos on [Link] = partidos.equipo_visitante
where [Link] ='03/04' and [Link] = 'C' and
partidos.equipo_visitante = [Link]
group by [Link], Posicion, [Link], partidos.equipo_visitante
opcion 2
select avg(Puntos_por_partido) as puntos, [Link], Posicion
from jugadores full join estadisticas on [Link] = [Link]
where [Link] ='03/04' and [Link] = 'C' and
jugadores.Nombre_equipo in(select Nombre
from equipos join partidos on [Link] = partidos.equipo_visitante)
group by [Link], Posicion
23-De la conferencia este verificar que jugador ha jugado en diferente equipo.