Azure DP-900: Práctica2

Descargamos los archivos: aquí

Creación de Base de datos

Crear recurso

Seleccionamos bases de datos->SQL Database

Asignar los siguientes parámetros a cada campo.
- Suscripción: Azure para estudiantes
- Grupo de recursos: PruebaBD (Opcional)
- Nombre de la base de datos: DP900 (Opcional)
- En servidor, seleccionar [Crear nuevo].

Para el nuevo servidor, colocar los siguientes valores.
- Nombre del servidor: serverdp900 (Opcional, debe ser irrepetible) gmatamor
- Inicio de sesión del administrador del servidor: dp900 (Opcional) gmatamor
- Contraseña: pswd123- (Opcional) A1
- Confirmar contraseña: pswd123- (Opcional) A1
- Ubicación: (US) Centro-Sur de EE.UU. (Opcional, elige la más cercana que se encuentre
- disponible)

Para reducir el costo del servicio, es necesario seleccionar otro nivel de servicio, para ello dar
clic en [Configurar base de datos].

En la siguiente ventana es posible verificar el costo mensual del servicio y para reducirlo
damos clic en [Básico (Para cargas de trabajo menos exigentes)].

Posteriormente verificar que el costo es aproximadamente 100 MXN y dar clic en
[Aplicar].

Seleccionar “Almacenamiento de copias de seguridad con redundancia local (versión
preliminar)”

Una vez que se han establecido todos los parámetros, dar clic en [Revisar y crear].

A continuación, se muestra el resumen del servicio a implementar, dar clic en [Crear]

Una vez que el servicio se ha implementado, es posible acceder al servicio, mediante [Ir
al recurso].

Información general de la base de datos:

Configuraciones relevantes para el costo del servicio

Nivel

Es un aspecto determinante seleccionar correctamente el nivel que se requiere, contemplando la
capacidad de memoria, ya que puede existir una gran diferencia respecto al costo, por ejemplo, el
nivel “Básico” y “Estándar” son considerablemente más económicos que un nivel “Premium”, de
“Uso general” o “Hiperescala”.

Ubicación

La ubicación para aprovisionar el servicio debe ser la más cercana a la localización de la
organización, ya que puede influir tanto en el rendimiento como en el costo.

Servicios adicionales

Activar opciones adicionales, puede generar un costo mayor; antes de habilitar alguna opción extra
es importante saber si tiene un costo adicional o si después de cierto limite puede comenzar a
cobrarse.

Herramientas para realizar consultas

Existen diversas herramientas para realizar consultas de datos en Azure SQL Database, por
ejemplo:

El editor de consultas en Azure Portal
SQLCMD desde la línea de comandos o Azure Cloud Shell
SQL Server Management Studio
Azure Data Studio

Algunos datos que te pueden ayudar a elegir que herramienta utilizar son:

Se puede utilizar Azure Data Studio para consultar un clúster de macrodatos de Microsoft SQL Server.
Microsft SQL Server Management Studio (SSMS) permite consultar un almacén de datos de Azure Synapse Analytics.

Microsoft SQL Server Management Studio (SSMS)

Una herramienta gráfica para la gestión de bases de datos de SQL Server o SQL Azure que las
tareas de acceso de soportes, configuración, gestión y administración.

Microsoft Visual Studio Code

Un editor de código fuente ligero con una extensión mssql que admite conexiones al servidor SQL
y una rica experiencia de edición para T-SQL.

Azure Data Studio

Un editor ligero que puede ejecutar consultas SQL bajo demanda y ver y guardar resultados como
archivos de texto, JSON o Microsoft Excel.

Microsoft SQL Server Data Tools (SSDT)

Una herramienta de desarrollo para crear bases de datos de Azure SQL, bases de datos relacionales
de servidor Microsoft SQL, modelos de datos de servicios de análisis de servidor SQK (SSAS),
paquetes de servicios de integración de servidor SQL (SSIS) e informes de servicios de informes
de servidor SQL (SSRS).

Instalación de Azure Data Studio

Es necesario descargar el ejecutable que nos permitirá realizar la instalación, por medio de la
siguiente URL: https://docs.microsoft.com/es-es/sql/azure-data-studio/download-azure-
data-studio

Práctica 2: Conexiones

Para poder continuar con la práctica es necesario investigar los siguientes conceptos y contestar el
cuestionario en Microsoft Forms: Práctica 2: Conexiones.

Conexión de Azure Data Studio

Para realizar la conexión es necesario configurar el firewall, ingresando al apartado de
“Configuración de firewall”, mediante [Agregar IP a la lista de elementos permitidos].

Dentro de Azure Data Studio, dar clic en [New Connection]:

Dentro del apartado de conexión, ingresar los siguientes datos, deben corresponder a los
datos que utilizaste para crear la base de datos en el Portal de Azure:
• Server: gmatamor.database.windows.net
• Authentication type: SQL Login
• User name: gmatamor
• Password: A1

Instrucciones DDL y DML

Nuestras instrucciones deben partir del modelo lógico, creado en la práctica anterior, es importante
tomar en cuenta las relaciones que existen entre las entidades para poder determinar las
características de cada atributo.

Para comenzar el tema de instrucciones DDL y DML es necesario investigar los conceptos presentados
y contestar el cuestionario: Practica 2: DDL y DML

Inicialmente realizaremos la creación de las tablas que no contienen llaves foráneas, por ejemplo, los
catálogos:

CREATE TABLE AUTOR (
    claveAutor VARCHAR(5) PRIMARY KEY,
    nombre VARCHAR(40) NOT NULL,
    apPaterno VARCHAR(40) NOT NULL,
    apMaterno VARCHAR(40) NULL
);

CREATE TABLE CATEGORIA (
     claveCategoria VARCHAR(5) PRIMARY KEY,
     descripcion VARCHAR(40) NOT NULL
);

CREATE TABLE LIBRO (
     claveLibro VARCHAR(5) PRIMARY KEY,
     titulo VARCHAR(150) NOT NULL,
     claveAutor VARCHAR(5) NOT NULL,
     CONSTRAINT claveAutor_fk FOREIGN KEY (claveAutor)
     REFERENCES AUTOR (claveAutor)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
     claveCategoria VARCHAR(5) NOT NULL,
     CONSTRAINT claveCategoria_fk FOREIGN KEY (claveCategoria)
     REFERENCES CATEGORIA (claveCategoria)
     ON DELETE CASCADE
     ON UPDATE CASCADE
);

Para insertar datos en la tabla se ejecutan las siguientes instrucciones:
Un dato importante para considerar es el hecho de que la tabla “Autor” tiene un campo null, y la
instrucción INSERT cambia por este hecho, además las fechas se insertan con el formato “MM-DD-
AAAA”.

INSERT INTO AUTOR (claveAutor, nombre, apPaterno, apMaterno)
VALUES ('A0001','Adrian','Curiel','Rivera'),
('A0012','Carlos','Ruiz','Zafon'),
('A0017','Federico','Garcia','Lorca');

INSERT INTO AUTOR (claveAutor, nombre, apPaterno)
VALUES ('A0002','Aka','Akasaka'),
('A0003','Albert','Camus');

INSERT INTO CATEGORIA (claveCategoria, descripcion)
VALUES ('C0001','Comic'),
('C0002','Cuento'),
('C0003','Cultura'),
('C0004','Educacion'),
('C0005','Enciclopedia'),
('C0006','Ensayo'),
('C0007','Filosofia');

INSERT INTO LIBRO (claveLibro, titulo, claveAutor, claveCategoria)
VALUES ('LB001','1984','A0023','C0015'),
('LB002','A prueba de fuego','A0032','C0015'),
('LB003','Almendra','A0079','C0016'),
('LB004','Amores veganos','A0001','C0017'),
('LB005','Anatomia con orientacion clinica' ,'A0040','C0005'),
('LB006','Anatomia del mexicano','A0063','C0006');

En caso de cometer un error existen instrucciones DDL que nos permiten corregirlo.

ALTER: modifica la estructura de un objeto. Por ejemplo, en una tabla agregar, renombrar o
eliminar una columna.

ALTER TABLE <Nombre_de_tabla> [ADD/REMOVE/RENAME] COLUMN <Nombre_columna>;

RENAME: permite cambiar el nombre de un objeto existente.

ALTER TABLE <Nombre_actual_de_tabla> RENAME <Nuevo_nombre_de_tabla>;

DROP: elimina un objeto de la base de datos.

DROP TABLE <Nombre_de_tabla>;

Implementación en SQL

Al ingresar a la plataforma y establecer la conexión, es posible comenzar la ejecución de las
consultas dando clic en [New Query].

Es importante seleccionar “DP900” como la base de datos en la que se ejecutaran las instrucciones,
ya que con las credenciales no tenemos permisos para modificar la base de datos “master”.

Se pueden ejecutar las instrucciones una a una…

O ejecutarlas todas en un script, para esta opción hay que asegurar que las instrucciones son
tienen la estructura y sintaxis correcta:

Consultas

Para concluir la práctica y comprobar los conocimientos adquiridos, es necesario contestar el
cuestionario Práctica 2: Consultas:

Antes de realizar las consultas es recomendable ejecutar los siguientes scripts, que cuentan con la
información homologada, corregida y optimizada de acuerdo con el diseño lógico tomado como
referencia inicialmente, es importante que los scripts se ejecuten en el orden indicado:

deleteBiblioteca: Para eliminar registros anteriores, en caso de haber utilizado otro formato
para los nombres de las tablas, deberás eliminar tus datos con la instrucción DROP y el
nombre de tus tablas. [Da clic para descargar]

createBiblioteca: Crea las tablas de la base de datos. [Da clic para descargar]

insertBiblioteca: Inserta todos los datos de la biblioteca la tabla correspondiente. [Da clic
para descargar]

Considera que al final del formulario se incluye una última pregunta la cual consiste en que subas un
archivo en PDF con tus la sintaxis de tus consultas y la captura de los resultados.

¿Cuántos libros (sólo títulos distintos) hay en la biblioteca del autor “Mario Benedetti”?

SELECT 
    COUNT(l.claveLibro) 
FROM LIBRO AS l 
INNER JOIN AUTOR AS a ON (a.claveAutor = l.claveAutor)
WHERE (a.nombre LIKE '%Mario%') AND (apPaterno LIKE '%Benedetti%');

¿Cuántos prestamos se realizaron en el año 2020?

SELECT COUNT(clavePrestamo) FROM PRESTAMO AS p WHERE DATEPART(yy,fechaPrestamo) = '2020';

¿Cuántos ejemplares se encuentran en estado de “Reparación”?

SELECT 
    COUNT(ej.claveEjemplar) 
FROM EJEMPLAR AS ej
INNER JOIN ESTADO AS es ON (es.claveEstado = ej.claveEstado)
WHERE (es.descripcion LIKE '%Reparacion%');

A la biblioteca ha llegado el siguiente libro por lo que se requiere agregarlo a la base de datos:

Título: La chica del tren
Autor: Paula Hawkings
Categoria: Novela
Editorial: Planeta
Edición: Primera
Número de páginas: 496
Condición: Bueno
Estado: Disponible

Por último se requiere mostrar los datos que se han insertado.

Indica, ¿cuántas tablas fue necesario modificar para insertar este dato?

--*******************************************************
--************* CONSULTAR CATEGORIA    ******************
--*******************************************************
SELECT * FROM CATEGORIA WHERE descripcion LIKE '%Novela%';
--RESULTADO: C0015

--*******************************************************
--************* CONSULTAR EDITORIAL    ******************
--*******************************************************
SELECT * FROM EDITORIAL WHERE nombre LIKE '%Planeta%';
--RESULTADO: E0037

--*******************************************************
--************* CONSULTAR CONDICIÓN    ******************
--*******************************************************
SELECT * FROM CONDICION WHERE descripcion LIKE '%Bueno%';
--RESULTADO: B

--*******************************************************
--************* CONSULTAR ESTADO       ******************
--*******************************************************
SELECT * FROM ESTADO WHERE descripcion LIKE '%Disponible%';
--RESULTADO: ES003

--*******************************************************
--************* CONSULTAR AUTOR        ******************
--*******************************************************
SELECT * FROM AUTOR WHERE (nombre LIKE '%Paula%') AND apPaterno LIKE '%Hawkings%';
--RESULTADO: NULL

--*******************************************************
--************* INSERTAR AUTOR    ***********************
--*******************************************************
INSERT INTO AUTOR (claveAutor, nombre, apPaterno)
VALUES 	('A0081','Paula','Hawkings');

--*******************************************************
--************* CONSULTAR AUTOR        ******************
--*******************************************************
SELECT * FROM AUTOR WHERE (nombre LIKE '%Paula%') AND apPaterno LIKE '%Hawkings%';
--RESULTADO: A0081

--*******************************************************
--************* CONSULTAR LIBRO        ******************
--*******************************************************
SELECT * FROM LIBRO WHERE titulo LIKE '%La chica del tren%';
--RESULTADO: NULL

--*******************************************************
--************* INSERTAR LIBRO    ***********************
--*******************************************************
INSERT INTO LIBRO(claveLibro, titulo, claveAutor, claveCategoria)
VALUES ('LB101','La chica del tren','A0081','C0015');

--*******************************************************
--************* CONSULTAR LIBRO        ******************
--*******************************************************
SELECT * FROM LIBRO WHERE titulo LIKE '%La chica del tren%';
--RESULTADO: LB101

--*******************************************************
--************* INSERTAR EJEMPLAR ***********************
--*******************************************************
INSERT INTO EJEMPLAR (claveEjemplar, claveLibro, claveCondicion, claveEstado, edicion, claveEditorial, numeroPaginas)
VALUES ('LB101-001','LB101','B','ES003','Primera','E0037','496')


--*******************************************************
--************* DATOS DEL LIBRO   ***********************
--*******************************************************
SELECT 
    li.claveLibro AS 'Clave Libro',
    li.titulo AS 'Título',

    ej.edicion AS 'Edición',
    ej.numeroPaginas AS 'Número de Páginas',

    au.nombre AS 'Nombre Autor',
    au.apPaterno AS 'Apellido Autor',

    ca.descripcion AS 'Categoría',

    es.descripcion AS 'Estado',

    co.descripcion AS 'Condición',

    ed.nombre AS 'Editorial'

FROM LIBRO AS li 
INNER JOIN EJEMPLAR AS ej ON (ej.claveLibro = li.claveLibro)
INNER JOIN AUTOR AS au ON (au.claveAutor = li.claveAutor)
INNER JOIN CATEGORIA AS ca ON (ca.claveCategoria = li.claveCategoria)
INNER JOIN ESTADO AS es ON (es.claveEstado = ej.claveEstado)
INNER JOIN CONDICION AS co ON (co.claveCondicion = ej.claveCondicion)
INNER JOIN EDITORIAL AS ed ON (ed.claveEditorial = ej.claveEditorial)
WHERE li.titulo LIKE '%La chica del tren%'

Se ha solicitado listar los nombres de los libros de forma alfabética. ¿Qué instrucciones se
pueden utilizar para esta consulta?

SELECT * FROM LIBRO ORDER BY titulo ASC;

Indica el número de ejemplares de cada título, de acuerdo a la consulta anterior ¿cuántos ejemplares hay para el libro con clave LB040 y LB052?

SELECT COUNT(claveEjemplar) FROM EJEMPLAR WHERE claveLibro = 'LB040';
SELECT COUNT(claveEjemplar) FROM EJEMPLAR WHERE claveLibro = 'LB052';

Se requiere una lista de los usuarios que hayan solicitado un prestamos en los últimos 6 meses (fecha actual: octubre 2021), ¿cuál es el CURP del usuario que solicito un préstamo?

SELECT u.CURP
FROM USUARIO AS u 
INNER JOIN PRESTAMO AS p ON (p.claveUsuario = u.claveUsuario)
WHERE p.fechaPrestamo BETWEEN CONVERT(date, '2021-05-01') AND CONVERT(date, '2021-10-31')

La biblioteca ha decidido renovar los libros que se encuentran en un estado “Malo”, por lo que se requiere, generar una lista de los libros con esta característica, ¿cuántos ejemplares hay con esta condición?

SELECT COUNT(claveEjemplar) FROM EJEMPLAR WHERE claveCondicion LIKE 'M'

Calcula el promedio de número de páginas por título de los ejemplares con los que cuenta la biblioteca, ¿cuál es el promedio de páginas de los libros con clave LB005?

SELECT AVG(numeroPaginas) FROM EJEMPLAR WHERE claveLibro = 'LB005'

¿Cuál es la categoría de la que se cuenta con más libros?

SELECT 
    COUNT(l.claveCategoria) AS 'CantidadEjemplares',
    c.descripcion 
FROM LIBRO AS l
INNER JOIN CATEGORIA AS c ON (c.claveCategoria = l.claveCategoria)
GROUP BY l.claveCategoria,c.descripcion 
ORDER BY 'CantidadEjemplares' DESC