¿Cómo a través de un ejercicio puedo mejorar mis conocimientos en SQL?

SQL Journey

A continuación se enuncian una serie de puntos destinados a mejorar de manera evolutiva los skills en SQL, cada punto va ligado al desarrollo y ejecución del punto anterior por lo cual no es necesario algún nivel de conocimiento en SQL.

Herramientas necesarias:

  • Equipo de cómputo para desarrollar los ejercicios.
  • http://sqlfiddle.com/ o motor de base de datos de preferencia.

Ejercicio:

BUILDINGS

COLUMN_NAME

COLUMN_TYPE

COLUM_SIZE

CONSTRAINTS

ID

NUMBER

10

PK

NAME

VARCHAR2

50

ADDRESS

VARCHAR2

150

NUMBER_OF_FLOORS

NUMBER

3

END_BUILD_DATE

DATE

CREATION_DATE

TIMESTAMP

LAST_UPDATE

TIMESTAMP

  1. Teniendo en cuenta la descripción de BUILDINGS, crear la sentencia SQL para crear esta tabla en el motor de base de datos.

        Palabras clave: CREATE, TABLE, CONSTRAINT, PRIMARY KEY.

Link: Creación de tablas

Link: Llaves primarias

  1. En base a la siguiente definición, agregar la columna CODE y aplicar el Unique Constraint enunciado:

Palabras clave: ADD, CONSTRAINT, UNIQUE, ALTER, TABLE.

Link: Llaves unicas

BUILDINGS

COLUMN_NAME

COLUMN_TYPE

COLUM_SIZE

CONSTRAINTS

ID

NUMBER

10

PK

BUILDING_CODE

VARCHAR2

15

UK

NAME

VARCHAR2

50

ADDRESS

VARCHAR2

150

NUMBER_OF_FLOORS

NUMBER

3

END_BUILD_DATE

DATE

CREATION_DATE

TIMESTAMP

LAST_UPDATE

TIMESTAMP

  1. Insertar diez (10) registros en la tabla BUILDINGS teniendo en cuenta las siguientes reglas:
  1. Tres (3) de los edificios deben tener más de quince (15) pisos.
  2. Cuatro (4) de los edificios deben tener menos de cuatro (4) pisos.
  3. Para siete (7) de los edificios la construcción terminó antes del 10/12/2005
  4. La fecha de creación del registro debe ser provista por el sistema.
  5. Seis (6) de los registros deben tener fecha de última actualización y esta debe ser mayor en un (1) día a la fecha de creación.

Palabras claves: INSERT, INTO, VALUES, COMMIT, CURRENT_TIMESTAMP.

Link: Población de tablas

  1. Crear un Query para obtener toda la información de la tabla BUILDINGS.

        Palabras claves: SELECT, FROM.

Link: Consultar la data

  1. Teniendo en cuenta que por legislación, los edificios de cinco (5) o más pisos deben tener ascensores, crear un query que código, nombre y número de pisos de los edificios que deben cumplir con la legislación y contar con ascensores.

Palabras claves: SELECT, FROM, WHERE.

Link: Filtrar data

  1. La legislación que exige que los edificios de cinco (5) o más pisos deben tener ascensores fue publicada el 10/12/2005, es necesario conocer qué edificios están violando la ley por ende se necesita realizar un query que obtenga el nombre, la dirección y la fecha de finalización de la construcción, para los edificios que fueron construidos después de 10/12/2005 y tienen cinco (5) o más pisos.

Nota: Actualizar al menos tres de los edificios para que cumplan con la condición de este punto.

Palabras claves: UPDATE, SET, SELECT, FROM, WHERE, AND.

  1. Entre 01/06/2007 y 31/12/2007 por una apelación a la restricción de la legislación anterior, se permitió construir edificios de cinco (5) o más pisos sin ascensor, por esta razón se necesita un query para identificar código y nombre de los edificios construidos antes del 10/12/2005 o entre 01/06/2007 y 31/12/2007 con cinco (5) o más pisos.

Nota: Actualizar al menos tres de los edificios para que cumplan con la condición de este punto.

Palabras claves: UPDATE, SET, SELECT, FROM, WHERE, OR, BETWEEN, AND.

  1. Es necesario agregar la siguiente estructura a nuestra base de datos:

APARTMENTS

COLUMN_NAME

COLUMN_TYPE

COLUM_SIZE

CONSTRAINTS

ID

NUMBER

10

PK

BUILDING_ID

NUMBER

10

FK, UK

APARTMENT_NUMBER

VARCHAR2

5

UK

AREA_M2

NUMBER

4,2

FLOOR_NUMBER

NUMBER

3

CREATION_DATE

TIMESTAMP

LAST_UPDATE

TIMESTAMP

Notas: * BUILDING_ID y APARTMENT_NUMBER forman juntas la UK.

* El COLUMN_SIZE para la columna AREA_M2 significa que son 4 numeros en la parte entera y 2 en la parte decimal.

Palabras clave: CREATE, TABLE, CONSTRAINT, FOREIGN KEY, REFERENCES, UNIQUE, PRIMARY KEY.

Link: Llaves foraneas

  1. Insertar apartamentos en la tabla APARTMENTS teniendo en cuenta las siguientes reglas:
  1. Dos de los edificios no deben tener apartamentos.
  2. El resto de los edificios deben tener al menos dos apartamentos.

Palabras claves: INSERT, INTO, VALUES, COMMIT, CURRENT_TIMESTAMP.

  1. Por asuntos de auditoría se necesita conocer el número de apartamentos registrados para cada edificio en el sistema. Solo se deben mostrar los edificios con apartamentos registrados.

        Palabras claves: SELECT, FROM, COUNT, GROUP BY, INNER JOIN.

Link: Consulta con más de una tabla

Link: Inner Join

  1. La lista del punto anterior fue oportuna para la auditoría, sin embargo se hace necesario conocer en orden descendente la misma lista mostrando primero los edificios con más apartamentos registrados y por último los que menos tienen.

Palabras claves: SELECT, FROM, COUNT, GROUP BY, INNER JOIN, ORDER BY, DESC.

  1. Esta vez la necesidad fue diferente, en la auditoría se dieron cuenta que la información estaba incompleta dado que los edificios sin apartamentos registrados no aparecían en la lista, por lo tanto se solicitó este cambio: Es necesario mostrar todos los edificios con el número de apartamentos registrados en orden ascendente, mostrando primero los que tienen cero apartamentos registrados y por último los que más tienen.

Palabras claves: SELECT, FROM, COUNT, GROUP BY, LEFT OUTER JOIN, ORDER BY, ASC.

Link: Left outer join

Link: Right outer join

  1. La auditoría determinó que no existe razón alguna para mantener edificios registrados que no cuentan con apartamentos registrados, en aras de eliminar los que están de más o adelantar el registro de apartamentos de los que tienen esa tarea pendiente, se necesita la lista de edificios que no cuentan con apartamentos en este momento.

        Palabras claves: SELECT, FROM, WHERE, LEFT OUTER JOIN, IN.

  1. La empresa constructora decidió que comprar apartamentos en diferentes locaciones también es buen negocio, para esto colocó las siguientes condiciones:
  1. La columna BUILDING_ID en la tabla de APARTMENTS seguirá siendo FOREIGN KEY con la tabla BUILDINGS, sin embargo para los apartamentos comprados no se registrará edificio, por el contrario esta columna será NULL.
  2. Se agregara una nueva columna ADDRESS a la tabla APARTMENTS, para contemplar estos casos y colocar la dirección de estos apartamentos directamente en la tabla.

        Palabras clave: ADD, ALTER, TABLE, MODIFY, COLUMN, NULL.

  1. Insertar apartamentos en la tabla APARTMENTS que no tengan edificios relacionados.

  1. Es necesario tener toda la información de los diferentes apartamentos y edificios, para ello se necesita la siguiente información:
  1. Nombre del edificio
  2. Piso del apartamento
  3. Número del apartamento
  4. Dirección del apartamento

        Tenga en cuenta las siguientes reglas:

  1. Si el apartamento no tiene edificio, la dirección a mostrar es la de la tabla APARTMENTS.
  2. Si el apartamento tiene edificio, la dirección a mostrar es de la de la tabla BUILDING.
  3. Si el apartamento no tiene edificio, la información del edificio debe venir NULL.
  4. Si el edificio no tiene apartamentos, la información del apartamento debe venir NULL.

        Palabras clave: SELECT, FROM, CASE, WHEN, FULL OUTER JOIN.

  1. Para finalizar, es necesaria otra auditoría en la cual se requiere la información de los edificios con menos de cinco (5) apartamentos registrados, dado que esto significa que el trabajo de registro no se está haciendo correctamente.

        Palabras clave: SELECT, FROM, GROUP BY, HAVING, LEFT OUTER JOIN, COUNT.

Posted in

Deja un comentario