base de datos para aplicaciones

Upload: ferrer-juan

Post on 14-Jan-2016

18 views

Category:

Documents


0 download

DESCRIPTION

Consultar avanzadas para base de datos

TRANSCRIPT

  • Base de datos para aplicaciones

    Manipulacin avanzada de datos con SQL

    Jess Javier Torres Yaez

    GITI0681-E

    Ferrer Bautista Juan

    1212100376

    Lunes 19 de enero de 2015

  • Introduccin

    Una base de datos es un conjunto de datos almacenados sistemticamente para

    su posterior uso, estos datos son tiles para una organizacin, actualmente con el

    avance y desarrollo que se tiene tanto informticamente como electrnicamente ya

    la mayora de las bases de datos son en formato digital. Estas bases de datos

    estn conformadas por tablas y estas tienen una o varias columnas y en estas

    tablas son almacenados varios conjuntos de datos e informacin relevante para

    una organizacin.

    Debido a que se puede tener un conflicto al tratar con una base de datos se

    ofrecen soluciones al problema del almacenamiento, creacin y manipulacin de

    las mismas, con esto se han desarrollado varios sistemas gestores de base de

    datos los cuales nos permiten a realizar estas acciones de forma rpida y

    estructurada.

    Entre las diferentes acciones que se pueden hacer en un gestor de base de datos

    esta el buscar informacin, crear, registrar, actualizar, modificar y eliminar. Para

    esto nos enfocaremos en las consultas que se pueden definir como un comando

    que se puede ejecutar dentro del gestor de base de datos y gracias a este

    comando podemos buscar informacin que se encuentre en la base de datos.

    Una consulta puede ser una bsqueda simple o compleja de algn registro que se

    tiene en la base de datos, estas consultas tienen que estar acompaadas de

    ciertas condiciones, clausula, sentencia o funciones, esto dependiendo de lo que

    uno quiere buscar.

  • Assessment Questions

    1.-The SELECT statement used to select data from a database.

    2.-The INSERT INTO statement is used to insert a new row in a table.

    3.-The AVG() function returns the average value of a numeric column.

    4.-The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

    5.-The AND & OR operators are used to filter records based on more than one condition.

    6.-The IN operator allows you to specify multiple values in a WHERE clause.

    7.-The TOP clause is used to specify the number of records to return.

    8.-The COUNT() function returns the number of rows that matches a specified criteria.

    9.-The DISTINCT keyword can be used to return only distinct (different) values.

    10.-The GROUP BY statement is used in conjunction with the aggregate functions to group the

    result-set by one or more columns.

    11.-The UPDATE statement is used to update existing records in a table.

    12.-In SQL, a VIEW is a virtual table based on the result-set of an SQL statement.

    13.-The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no

    matches in the right table (table_name2).

    14.-The HAVING clause was added to SQL because the WHERE keyword could not be used with

    aggregate functions.

    15.-The INNER JOIN keyword returns rows when there is at least one match in both tables.

    16.-The BETWEEN operator is used in a WHERE clause to select a range of data between two

    values.

    17.-The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there

    are no matches in the left table (table_name1).

    18.-The MIN() function returns the smallest value of the selected column.

    19.-The ORDER BY keyword is used to sort the result-set.

    20.-The MAX () function returns the largest value of the selected column

  • Consultas a desarrollar

    1.- Proyecta el nmero de ciudades existentes

    select COUNT (ID_Ciudad)as

    Numero_de_Ciudades

    from Ciudad

    2.- Proyecta el peso de la pieza con mayor y menor peso.

    select MAX (p.Peso) as

    Pieza_Mayor_Peso,

    MIN(p.Peso)as

    Pieza_Menor_Peso

    from Piezas as p

    3.- Proyecta el nombre de la pieza con mayor peso (2 soluciones).

    select Nombre_Pza as

    Pieza_Mayor_Peso

    from Piezas where

    Peso=(select MAX(Peso) from piezas)

    select MAX(Nombre_Pza)as

    Pieza_Mayor_Peso

    from Piezas

  • 4.- Proyecta el nombre de la pieza con mayor y menor peso (usa subconsulta como tabla

    derivada).

    select p.Nombre_Pza

    from Piezas as p

    where Peso =(select

    MAX(pz.Peso)from Piezas as pz) or

    Peso = (select MIN(peso) from

    Piezas )

    5.- Proyecta el nombre y peso de la pieza con mayor y menor peso (3 soluciones, subconsulta

    como tabla derivada, para correlacionar datos y el IN).

    select p.Nombre_Pza,p.Peso

    from Piezas as p

    where Peso =(select

    MAX(pz.Peso)from Piezas as pz) or

    Peso = (select MIN(peso) from

    Piezas )

    Nota: la solucin 2 no es posible

    realizarse ya que la consulta es

    solo de una tabla y con una

    consulta derivada es requerida 2

    tablas, as que por lo tanto no se

    puede realizar con tabla derivada

    select Nombre_Pza as Pieza,Peso

    from Piezas

    where Peso in(select MAX(pz.Peso)

    from Piezas as pz)or Peso in(select

    MIN(peso) from Piezas )

    6.- Proyecta la cantidad mayor de proyectos en una ciudad.

    select COUNT(ID_Ciudad)as

    Mayor_Cantidad

    from Proyecto

    group by ID_Ciudad

    having COUNT(ID_Ciudad)>1

  • 7.- Proyecta la clave de la ciudad y la cantidad de proyectos de la ciudad con ms proyectos

    select ID_Ciudad,COUNT (ID_Ciudad)

    as Total_proyectos

    from Proyecto

    group by ID_Ciudad

    having COUNT(ID_Ciudad)>1

    8.- Proyecta el nombre de los proyectos que utilizan una pieza de peso 5 (usa inner join).

    select p.Nombre_Proy as

    Nombre_Proyecto

    from Piezas as pz

    inner join Suministra as s on

    s.ID_Pza=pz.ID_Pza

    inner join Proyecto as p on

    p.ID_Proyecto=s.ID_Proyecto

    where pz.Peso=5

    9.- Proyecta el nombre del proyecto, nombre del proveedor y nombre de la pieza con cantidades

    menores a 90 (usa inner join).

    select pro.Nombre_Proy as

    Nombre_Proyecto , prv.Nombre_Prov

    as Nombre_Proveedor, pie.Nombre_Pza

    as Nombre_pieza

    from Proyecto as pro

    inner join Suministra as su on

    su.ID_Proyecto=pro.ID_Proyecto

    inner join Proveedor as prv on

    su.ID_Prov=prv.ID_Prov

    inner join Piezas as pie on

    su.ID_Pza=pie.ID_Pza

    where Cantidad

  • 11.- Proyecta el nombre del proveedor y las piezas que suministra.

    Usar inner join

    select Nombre_Prov,pie.Nombre_Pza

    from Proveedor as pro

    inner join Suministra as su on

    su.ID_Prov=pro.ID_Prov

    inner join Piezas as pie on

    pie.ID_Pza = su.ID_Pza

    Usa sub consulta como tabla derivada, no puedes usar inner join en ningn caso

    select Nombre_Prov,Nombre_Pza

    from Suministra as s ,(select

    Nombre_Prov,ID_Prov from Proveedor

    ) as pro,

    (select Nombre_Pza, ID_Pza from

    Piezas )as pza

    where s.ID_Prov=pro.ID_Prov and

    pza.ID_Pza=s.ID_Pza

    Usa sub consulta como una expresin, no puedes usar inner join en ningn caso

    select

    (select Nombre_Prov from Proveedor

    as pro where

    pro.ID_Prov=su.ID_Prov)as

    Nombre_Prov,

    (select Nombre_Pza from Piezas as

    pza where pza.ID_Pza=su.ID_Pza) as

    Nombre_Pza

    from Suministra as su

    12.- Proyecta la clave del proveedor, clave de pieza y clave de proyecto donde la cantidad de

    piezas este entre 50 y 100.

    Usa inner join

    select pro.ID_Prov as ID_Proveedor,

    pza.ID_Pza as ID_Pieza,

    py.ID_Proyecto

    from Proveedor as pro

    inner join Suministra as su on

    su.ID_Prov=pro.ID_Prov

    inner join Piezas as pza on

    pza.ID_Pza=su.ID_Pza

    inner join Proyecto as py on

    py.ID_Proyecto=su.ID_Proyecto

    where su.Cantidad between 50 and

    100

  • Sin usar inner join ni sub consultas

    select ID_Prov as

    ID_Proveedor,ID_Pza as

    ID_Pieza,ID_Proyecto

    from Suministra

    where Cantidad between 50 and 100

    Usa sub consulta como tabla derivada, no puedes usar inner join en ningn caso

    select pro.ID_Prov,pza.ID_Pza,

    py.ID_Proyecto

    from Suministra as s,

    (select ID_Prov from Proveedor ) as

    pro,

    (select ID_Pza from Piezas) as pza,

    (select ID_Proyecto from Proyecto)

    as py

    where s.ID_Prov=pro.ID_Prov and

    pza.ID_Pza=s.ID_Pza and

    py.ID_Proyecto=s.ID_Proyecto and

    s.Cantidad between 50 and 100

    Usa sub consulta como una expresin, no puedes usar inner join en ningn caso

    select

    (select ID_Prov from Proveedor as

    pro where pro.ID_Prov=su.ID_Prov)as

    ID_Prov,

    (select pza.ID_Pza from Piezas as

    pza where pza.ID_Pza=su.ID_Pza)as

    ID_Pza,

    (select pry.ID_Proyecto from

    Proyecto as pry where

    pry.ID_Proyecto=su.ID_Proyecto)as

    ID_Proyecto

    from Suministra as su

    where su.Cantidad between 50 and

    100

    13.- Proyecta el nombre del proveedor, nombre de pieza y nombre de proyecto donde la cantidad

    de piezas este entre 50 y 100.

    Usar inner join

    select pro.Nombre_Prov as Nombre_Proveedor,

    pza.Nombre_Pza as Nombre_Pieza,

    py.Nombre_Proy as Nombre_Proyecto from

    Proveedor as pro inner join Suministra as

    su on su.ID_Prov=pro.ID_Prov

    inner join Piezas as pza on

    pza.ID_Pza=su.ID_Pza

    inner join Proyecto as py on

    py.ID_Proyecto=su.ID_Proyecto

    where su.Cantidad between 50 and 100

  • Sin usar inner join ni sub consultas

    select pro.Nombre_Prov as Nombre_Proveedor,

    p.Nombre_Pza as Nombre_Pieza,

    py.Nombre_Proy as Nombre_Proyecto

    from Proveedor as pro, Suministra as s,

    Piezas as p , Proyecto as py

    where s.ID_Prov=pro.ID_Prov and

    p.ID_Pza=s.ID_Pza and

    py.ID_Proyecto=s.ID_Proyecto and

    s.Cantidad between 50 and 100

    Usa sub consulta como tabla derivada, no puedes usar inner join en ningn caso

    select

    py.Nombre_Proy,pr.Nombre_Prov,pz.Nombre_Pza

    from Suministra as s,

    (select Nombre_Proy,ID_Proyecto from

    Proyecto) as py,

    (select Nombre_Prov,ID_Prov from

    Proveedor)as pr,

    (select Nombre_Pza,ID_Pza from Piezas)as pz

    where py.ID_Proyecto=s.ID_Proyecto and

    pr.ID_Prov=s.ID_Prov

    and pz.ID_Pza=s.ID_Pza and s.Cantidad

    between 50 and 100

    Usa sub consulta como expresin, no puedes usar inner join en ningn caso

    select

    (select pro.Nombre_Prov from Proveedor as

    pro where pro.ID_Prov=su.ID_Prov)as

    Nombre_Proveedor,

    (select pza.Nombre_Pza from Piezas as pza

    where pza.ID_Pza=su.ID_Pza)as Nombre_Pieza,

    (select pry.Nombre_Proy from Proyecto as

    pry where pry.ID_Proyecto=su.ID_Proyecto)as

    Nombre_Proyecto

    from Suministra as su

    where su.Cantidad between 50 and 100

    14. - Consulta que muestre el nombre de las ciudades donde no exista proveedores

    select c.Nombre_Ciu as

    Ciudad_Sin_Proveedor

    from Ciudad as c

    where NOT EXISTS

    (select * from Proveedor as p

    where p.ID_Ciudad=c.ID_Ciudad )

  • 15.- Crea una vista que muestre el nombre del proveedor y nombre del proyecto que estn en la

    misma ciudad.

    Usa inner join

    create view Nom_Prov_Proye

    select pv.Nombre_Prov as

    nombre_Proveedor,py.Nombre_Proy as

    Nombre_Proyecto

    from Proveedor as pv

    inner join Proyecto as py on

    pv.ID_Ciudad=py.ID_Ciudad

    Sin usar inner join ni sub consultas

    create view Nom_Prov_Proye_dos

    select pv.Nombre_Prov as

    Nombre_Proveedor,py.Nombre_Proy as

    NOmbre_Proyecto

    from Proveedor as pv, Proyecto as

    py

    where pv.ID_Ciudad=py.ID_Ciudad

    Usa sub consulta como tabla derivada, no puedes usar inner join en ningn caso

    create view Nom_Prov_Proye_tres

    select pr.Nombre_Prov as

    Nombre_Proveedor,py.Nombre_Proy as

    Nombre_Proyecto

    from Proyecto as py,(select

    Nombre_Prov,ID_Ciudad from

    Proveedor ) as pr

    where pr.ID_Ciudad=py.ID_Ciudad

    Usa sub consulta como una expresin, no puedes usar inner join en ningn caso

    create view Nom_Prov_Proye_Cuatro

    as

    select Nombre_Prov as

    Nombre_Proveedor, Nombre_Proy as

    Nombre_Proyecto

    from Proyecto py ,Proveedor pr

    where py.ID_Ciudad

    in (select ID_Ciudad from Ciudad)

    and Pr.ID_Ciudad

    in (select ID_Ciudad from Ciudad)

    and py.ID_Ciudad=Pr.ID_Ciudad

  • 16.- Consulta que muestre el nombre de las ciudades donde no se encuentre un proveedor, pieza

    ni tampoco se desarrolle un proyecto.

    select c.Nombre_Ciu as

    Ciudad_Sin_Piezas_Proveedor_Proyecto

    from Ciudad as c

    where NOT EXISTS

    (select * from Proyecto as py where

    py.ID_Ciudad=c.ID_Ciudad)

    and NOT EXISTS (select * from Piezas

    as pz where

    pz.ID_Ciudad=c.ID_Ciudad)

    and NOT EXISTS (select * from

    Proveedor as pr where

    pr.ID_Ciudad=c.ID_Ciudad)

    17.- Consulta que muestre el total de piezas utilizadas por proyecto.

    select SUM(Cantidad)as

    Piezas_Utilizadas_Por_Proyecto

    from Suministra as s

    group by s.ID_Proyecto

    18.- Consulta que muestre el total proyectos desarrollados por ciudad

    select COUNT(ID_Ciudad) as

    Proyecto_Desarrollados_Por_Ciudad

    from Proyecto

    group by ID_Ciudad

    19.- Consulta que muestre el nombre de los proveedores que suministran las piezas Pieza1(511),

    Pieza2(522) y Pieza3(533), entendiendo estos ltimos como el nombre de la pieza.

    select p.Nombre_Prov as

    Nombre_Proveedor

    from Proveedor as p

    inner join Suministra as s on

    s.ID_Prov=p.ID_Prov

    inner join Piezas as pz on

    pz.ID_Pza=s.ID_Pza

    where s.ID_Pza in(511,522,533)

    group by Nombre_Prov

    20.- Mostrar el nombre de las piezas que hayan sido utilizadas, con la cantidad mxima registrada.

    select p.Nombre_Pza

    from Suministra as s inner join

    Piezas as p

    on s.ID_Pza=p.ID_Pza

    where s.Cantidad=(Select

    MAX(Cantidad) from Suministra)

    group by p.Nombre_Pza

  • Conclusin

    Como pudimos observar las consultas son de suma importancia ya que nos

    ayudan para poder realizar bsqueda de informacin dentro de nuestras bases de

    datos, esto sin importar si lo que buscamos es simple o complejo, se vieron

    algunas formas de como se puede buscar informacin si es que la consulta fuese

    compleja. Hay diversos comandos y clausulas los cuales nos sirvieron para poder

    resolver las consultas complejas, tambin pudimos saber cuando tienen que ser

    usadas y para que sirve cada y poder usar algunas al mismo tiempo.

    Hoy en da las consultas son utilices ya que con ellas podemos reducir tiempo al

    estar buscando informacin, poder optimizar las bsquedas y la informacin

    mostrada al usuario sea concreta, sencilla y til. Se pueden crear un sinfn de

    combinaciones utilizando clausulas para las consultas y las relaciones con otras

    tablas, pero estas combinaciones son el reflejo de lo que el usuario intenta pedir a

    la consulta y si esta es simple o compleja.