Índices y Vistas

Clase 01

1 Material Clase

  • Github TI3V22: LINK

1.1 Palabras Clave

  • Índices
  • Vistas
  • SQL Server: INDEX, VIEW

2 Índices

  • Mejora la velocidad de las operaciones de consulta de una tabla.
  • Similar al índice de un libro, permite encontrar los datos más rápido sin tener que escanear todas las filas de la tabla.
  • Para SQL Server se utiliza el comando INDEX.

Ejemplo

  • Supongamos que tienes una tabla ‘Clientes’ con millones de registros.
  • Consultar frecuentemente por el ‘Cliente_ID’ escanea todos los registros hasta encontrar el deseado.
  • Crear un índice en ese atributo puede mejorar el rendimiento.
CREATE INDEX ix_Cliente_ID
ON Clientes(Cliente_ID);
  • el prefijo ix_ es una convención para indicar que se trata de un índice.

3 Vistas

  • Permite almacenar una consulta como si fuese una tabla más de la base de datos.
  • Para SQL Server se utiliza el comando VIEW.
  • Ideal cuando se hace una consulta avanzada, con muchos JOIN o condiciones, ya que al almacenarlo con VIEW no es necesario volver a realizar la misma consulta una y otra vez.

Ejemplo

  1. Creo una vista VistaInformacionEmpleados que contiene información de ambas tablas Empleados y Departamentos.
CREATE VIEW VistaInformacionEmpleados AS
    SELECT e.ID, e.Nombre, e.Apellido, d.Nombre AS NombreDepartamento
    FROM Empleados e
    JOIN Departamento d ON e.Departamento_ID = d.ID;
  1. Si quiero conocer los empleados del departamento Ventas, puedo consultar la “vista” en vez de realizar nuevamente una consulta avanzada.
SELECT *
FROM VistaInformacionEmpleados
WHERE NombreDepartamento = 'Ventas';

4 Ejercicios (1ra parte)

Se crea la base de datos Clase01_Parte_Uno.

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'Clase01_Parte_Uno')
BEGIN
    CREATE DATABASE Clase01_Parte_Uno;
END;

USE Clase01_Parte_Uno;
python code 
from sqlalchemy import create_engine, text
import pandas as pd

# connection string
server = r'.\SQLEXPRESS'
username = r'sauk'
password = r'123'
database = r'master' 
driver = r'SQL+Server'
connection_string = f'mssql+pyodbc://{username}:{password}@{server}/{database}?driver={driver}'

# SQLAlchemy engine
engine = create_engine(connection_string)

# queries
queries = []

queries.append(text('''
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'Clase01_Parte_Uno')
BEGIN
    CREATE DATABASE Clase01_Parte_Uno;
END;
'''))
queries.append(text('''
USE Clase01_Parte_Uno;
'''))

# execute queries
with engine.connect() as conn:
    for query in queries:
        conn.execute(query)

4.1 Ejercicio 01

Creación de Tablas:

Crear una tabla llamada Estudiantes que contenga las siguientes columnas:

  • ID como número entero, que sea la clave primaria y que se autoincremente.
  • Nombre como texto (cadena de caracteres) que no puede ser nulo.
  • Edad como número entero.
  • FechaIngreso como fecha.

Agregar los siguientes registros:

  • Juan Pérez, 20, 2022-03-15
  • Ana López, 22, 2022-05-21
  • Luis Martínez, 17, 2022-01-11
  • Carmen Ríos, 19, 2022-02-07
  • Carlos Sánchez, 23, 2022-08-25
  • Lucía Fernández, 21, 2022-09-30
  • Diego Alonso, 18, 2022-04-14
  • Isabel Mora, 25, 2022-07-19

Solución

CREATE TABLE Estudiantes (
    ID INT IDENTITY PRIMARY KEY,
    Nombre VARCHAR(100) NOT NULL,
    Edad INT,
    FechaIngreso DATE
);

INSERT INTO Estudiantes (Nombre, Edad, FechaIngreso)
VALUES
    ('Juan Pérez', 20, '2022-03-15'),
    ('Ana López', 22, '2022-05-21'),
    ('Luis Martínez', 17, '2022-01-11'),
    ('Carmen Ríos', 19, '2022-02-07'),
    ('Carlos Sánchez', 23, '2022-08-25'),
    ('Lucía Fernández', 21, '2022-09-30'),
    ('Diego Alonso', 18, '2022-04-14'),
    ('Isabel Mora', 25, '2022-07-19');

SELECT *
FROM Estudiantes;
python code 
queries.append(text('''
CREATE TABLE Estudiantes (
    ID INT IDENTITY PRIMARY KEY,
    Nombre VARCHAR(100) NOT NULL,
    Edad INT,
    FechaIngreso DATE
);
'''))
queries.append(text('''
INSERT INTO Estudiantes (Nombre, Edad, FechaIngreso)
VALUES
    ('Juan Pérez', 20, '2022-03-15'),
    ('Ana López', 22, '2022-05-21'),
    ('Luis Martínez', 17, '2022-01-11'),
    ('Carmen Ríos', 19, '2022-02-07'),
    ('Carlos Sánchez', 23, '2022-08-25'),
    ('Lucía Fernández', 21, '2022-09-30'),
    ('Diego Alonso', 18, '2022-04-14'),
    ('Isabel Mora', 25, '2022-07-19');
'''))
query_select = text('''
SELECT *
FROM Estudiantes; 
''')

# execute queries
with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Nombre Edad FechaIngreso
1 Juan Pérez 20 2022-03-15
2 Ana López 22 2022-05-21
3 Luis Martínez 17 2022-01-11
4 Carmen Ríos 19 2022-02-07
5 Carlos Sánchez 23 2022-08-25
6 Lucía Fernández 21 2022-09-30
7 Diego Alonso 18 2022-04-14
8 Isabel Mora 25 2022-07-19

4.2 Ejercicio 02

Creación de Vistas

Crea una vista llamada VistaEstudiantes que muestre el nombre y la edad de los estudiantes que tengan más de 18 años.

Solución

CREATE VIEW VistaEstudiantes AS
    SELECT Nombre, Edad
    FROM Estudiantes
    WHERE Edad > 18;

SELECT *
FROM VistaEstudiantes;
python code 
queries.append(text('''
CREATE VIEW VistaEstudiantes AS
    SELECT Nombre, Edad
    FROM Estudiantes
    WHERE Edad > 18;
'''))
query_select = (text('''
SELECT *
FROM VistaEstudiantes;
'''))

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
Nombre Edad
Juan Pérez 20
Ana López 22
Carmen Ríos 19
Carlos Sánchez 23
Lucía Fernández 21
Isabel Mora 25

4.3 Ejercicio 03

Creación de Índices

Crea un índice para la columna Edad en la tabla Estudiantes para mejorar la velocidad de las consultas que filtren por edad.

Solución

CREATE INDEX ix_Edad
ON Estudiantes(Edad);

-- consulta opcional: verifica índice
SELECT name AS IndexName, type_desc AS IndexType
FROM sys.indexes
WHERE object_id = OBJECT_ID('Estudiantes');
python code 
queries.append(text('''
CREATE INDEX ix_Edad
ON Estudiantes(Edad);
'''))
query_select = text('''
SELECT name AS IndexName, type_desc AS IndexType
FROM sys.indexes
WHERE object_id = OBJECT_ID('Estudiantes');
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
IndexName IndexType
PK__Estudian__3214EC277A1E47A8 CLUSTERED
ix_Edad NONCLUSTERED
  • Se observa que existe un índice ix_Edad.

5 Ejercicios (2da parte)

  • Crear la base de datos Clase01_Parte_Dos, utilizando el siguiente script:
script_clase01.sql
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'Clase01_Parte_Dos')
BEGIN
    CREATE DATABASE Clase01_Parte_Dos;
END;

USE Clase01_Parte_Dos;

SET DATEFORMAT 'YMD';

CREATE TABLE Autores (
    ID INT PRIMARY KEY,
    Nombre VARCHAR(100),
    Nacionalidad VARCHAR(100)
);

CREATE TABLE Libros (
    ID INT PRIMARY KEY,
    Titulo VARCHAR(100),
    Autor_ID INT,
    FOREIGN KEY (Autor_ID) REFERENCES Autores(ID)
);

INSERT INTO Autores (ID, Nombre, Nacionalidad) VALUES
(1, 'Gabriel García Márquez', 'Colombiano'),
(2, 'Mario Vargas Llosa', 'Peruano'),
(3, 'Julio Cortázar', 'Argentino');

INSERT INTO Libros (ID, Titulo, Autor_ID) VALUES
(1, 'Cien años de soledad', 1),
(2, 'La ciudad y los perros', 2),
(3, 'Rayuela', 3);
python code 
# queries
queries = []

queries.append(text('''
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'Clase01_Parte_Dos')
BEGIN
    CREATE DATABASE Clase01_Parte_Dos;
END;
'''))
queries.append(text('''
USE Clase01_Parte_Dos;
'''))
queries.append(text('''
SET DATEFORMAT 'YMD';
'''))
queries.append(text('''
CREATE TABLE Autores (
    ID INT PRIMARY KEY,
    Nombre VARCHAR(100),
    Nacionalidad VARCHAR(100)
);
'''))
queries.append(text('''
CREATE TABLE Libros (
    ID INT PRIMARY KEY,
    Titulo VARCHAR(100),
    Autor_ID INT,
    FOREIGN KEY (Autor_ID) REFERENCES Autores(ID)
);
'''))
queries.append(text('''
INSERT INTO Autores (ID, Nombre, Nacionalidad) VALUES
(1, 'Gabriel García Márquez', 'Colombiano'),
(2, 'Mario Vargas Llosa', 'Peruano'),
(3, 'Julio Cortázar', 'Argentino');
'''))
queries.append(text('''
INSERT INTO Libros (ID, Titulo, Autor_ID) VALUES
(1, 'Cien años de soledad', 1),
(2, 'La ciudad y los perros', 2),
(3, 'Rayuela', 3);
'''))

# execute queries
with engine.connect() as conn:
    for query in queries:
        conn.execute(query)

5.1 Ejercicio 01

Consulta Básica: Mostrar todos los libros de la tabla “Libros”.

Solución

SELECT *
FROM Libros;
python code 
query_select = text('''
SELECT *
FROM Libros;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)

    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Titulo Autor_ID
1 Cien años de soledad 1
2 La ciudad y los perros 2
3 Rayuela 3

5.2 Ejercicio 02

Consulta con Filtro: Mostrar el nombre y nacionalidad del autor del libro con ID 2.

Solución

SELECT a.Nombre, a.Nacionalidad
FROM Autores a
JOIN Libros l ON l.Autor_ID = a.ID
WHERE l.ID = 2;
python code 
query_select = text('''
SELECT a.Nombre, a.Nacionalidad
FROM Autores a
JOIN Libros l ON l.Autor_ID = a.ID
WHERE l.ID = 2;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)

    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
Nombre Nacionalidad
Mario Vargas Llosa Peruano

5.3 Ejercicio 03

Consulta con JOIN: Mostrar el título del libro y el nombre del autor de todos los libros.

Solución

SELECT l.Titulo, a.Nombre
FROM Libros l
JOIN Autores a ON l.Autores_ID = a.ID;
python code 
query_select = text('''
SELECT l.Titulo, a.Nombre
FROM Libros l
JOIN Autores a ON l.Autor_ID = a.ID;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)

    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
Titulo Nombre
Cien años de soledad Gabriel García Márquez
La ciudad y los perros Mario Vargas Llosa
Rayuela Julio Cortázar

5.4 Ejercicio 04

Actualización de Datos: Cambiar la nacionalidad del autor con ID 3 a “Argentino-Francés”.

Solución

UPDATE Autores
SET Nacionalidad = 'Argentino-Francés'
WHERE ID = 3;

SELECT *
FROM Autores
WHERE ID = 3;
python code 
queries.append(text('''
UPDATE Autores
SET Nacionalidad = 'Argentino-Francés'
WHERE ID = 3;
'''))
query_select = text('''
SELECT *
FROM Autores
WHERE ID = 3;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Nombre Nacionalidad
3 Julio Cortázar Argentino-Francés

5.5 Ejercicio 05

Inserción de Datos: Agregar un nuevo autor a la tabla “Autores

  • ID: 4
  • nombre: Isabel Allende
  • nacionalidad: Chilena

Solución

INSERT INTO Autores(ID,Nombre,Nacionalidad) VALUES
(4,'Isabel Allende','Chilena');

SELECT *
FROM Autores
WHERE ID = 4;
python code 
queries.append(text('''
INSERT INTO Autores(ID,Nombre,Nacionalidad) VALUES
(4,'Isabel Allende','Chilena');
'''))
query_select = text('''
SELECT *
FROM Autores
WHERE ID = 4;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)

    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Nombre Nacionalidad
4 Isabel Allende Chilena

5.6 Ejercicio 06

Eliminación de Datos: Eliminar el libro con ID 1 de la tabla “Libros”.

Solución

DELETE FROM Libros
WHERE ID = 1;

SELECT *
FROM Libros;
python code 
queries.append(text('''
DELETE FROM Libros
WHERE ID = 1;
'''))
query_select = text('''
SELECT *
FROM Libros;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Titulo Autor_ID
2 La ciudad y los perros 2
3 Rayuela 3

5.7 Ejercicio 07

Crear un nuevo libro: Inserta un nuevo registro en la tabla “Libros” con los siguientes datos:

  • ID: 4
  • Título: El amor en los tiempos del cólera
  • Autor_ID: 1

Solución

INSERT INTO Libros(ID,Titulo,Autor_ID) 
VALUES
    (4,'El amor en los tiempos del cólera',1);

SELECT *
FROM Libros
WHERE ID = 4;
python code 
queries.append(text('''
INSERT INTO Libros(ID,Titulo,Autor_ID)
VALUES
    (4,'El amor en los tiempos del cólera',1);
'''))
query_select = text('''
SELECT *
FROM Libros
WHERE ID = 4;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Titulo Autor_ID
4 El amor en los tiempos del cólera 1

5.8 Ejercicio 08

Actualizar un libro: Actualiza el título del libro con ID 2 a “Conversación en la catedral”.

Solución

UPDATE Libros
SET Titulo = 'Conversación en la catedral'
WHERE ID = 2;

SELECT *
FROM Libros
WHERE ID = 2;
python code 
queries.append(text('''
UPDATE Libros
SET Titulo = 'Conversación en la catedral'
WHERE ID = 2;
'''))
query_select = text('''
SELECT *
FROM Libros
WHERE ID = 2;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Titulo Autor_ID
2 Conversación en la catedral 2

5.9 Ejercicio 09

Eliminar un autor: Elimina el autor con ID 3 de la tabla “Autores” y todos los libros asociados a este autor.

Solución

DELETE FROM Libros
WHERE Autor_ID = 3;

DELETE FROM Autores
WHERE ID = 3;

SELECT *
FROM Libros;

SELECT *
FROM Autores;
python code 
queries.append(text('''
DELETE Libros
WHERE Autor_ID = 3;
'''))
queries.append(text('''
DELETE Autores
WHERE ID = 3;
'''))
query_select1 = text('''
SELECT *
FROM Libros;
''')
query_select2 = text('''
SELECT *
FROM Autores;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result1 = conn.execute(query_select1)
    columns1 = result1.keys()
    data1 = result1.fetchall()
    result1.close()

    result2 = conn.execute(query_select2)
    columns2 = result2.keys()
    data2 = result2.fetchall()

df1 = pd.DataFrame(data1,columns=columns1)
html_output1 = df1.to_html(index=False)
html_output1
ID Titulo Autor_ID
2 Conversación en la catedral 2
4 El amor en los tiempos del cólera 1
python code 
df2 = pd.DataFrame(data2,columns=columns2)
html_output2 = df2.to_html(index=False)
html_output2
ID Nombre Nacionalidad
1 Gabriel García Márquez Colombiano
2 Mario Vargas Llosa Peruano
4 Isabel Allende Chilena

5.10 Ejercicio 10

Leer todos los libros de un autor: Muestra todos los libros escritos por el autor con ID 1.

Solución

SELECT *
FROM Libros
WHERE Autor_ID = 1;
python code 
query_select = text('''
SELECT *
FROM Libros
WHERE Autor_ID = 1;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Titulo Autor_ID
4 El amor en los tiempos del cólera 1

5.11 Ejercicio 11

Actualizar la nacional de un autor: Cambia la nacionalidad del autor con ID 2 a “Peruana”.

Solución

UPDATE Autores
SET Nacionalidad = 'Peruana'
WHERE ID = 2;

SELECT *
FROM Autores
WHERE ID = 2;
python code 
queries.append(text('''
UPDATE Autores
SET Nacionalidad = 'Peruana'
WHERE ID = 2;
'''))

query_select = text('''
SELECT *
FROM Autores
WHERE ID = 2;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Nombre Nacionalidad
2 Mario Vargas Llosa Peruana