Saltar al contenido
Apuntes FS(+)

Clase 24 - SQL - Relaciones y funciones

#sql #postgresql #pgadmin #docker-compose #uml #relations #join

Clave Primaria (PK)

  • Es un campo (o combinación de campos) que identifica de manera única cada registro en una tabla.
  • Nos asegura una integridad de los datos y evita la duplicación o inconsistencia de ellos.
  • Debe ser única.
  • No puede contener valores nulos.
  • Solo existe una PK por tabla
CREATE TABLE estudiantes (
    id_estudiante INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre VARCHAR(50) NOT NULL,
    apellido VARCHAR(50) NOT NULL,
    edad INTEGER NOT NULL,
    correo VARCHAR(255) NOT NULL,
    telefono VARCHAR(50) NOT NULL,
    direccion VARCHAR(255) NOT NULL,
);

Clave Foránea (FK)

  • Establece una relación entre dos tablas en una base de datos.
  • La clave foránea en una tabla hace referencia a la clave primaria de otra tabla, creando así una relación entre ellas.
  • Garantiza la integridad referencial, lo que significa que no se pueden agregar registros que hagan referencia a valores inexistentes en la tabla relacionada.
  • Puede haber múltiples claves foráneas en una tabla, y pueden apuntar a la misma tabla o a diferentes tablas
CREATE TABLE cursos (
  id INT PRIMARY KEY,
  nombre VARCHAR(50),
  profesor_id INT,
  FOREIGN KEY (profesor_id) REFERENCES profesores(id)
);


CREATE TABLE profesores (
  id INT PRIMARY KEY,
  nombre VARCHAR(50),
  especialidad VARCHAR(50)
);

Tipos de Relaciones entre tablas

Relación Uno a Uno (One-to-One)

  • Un registro en una tabla se relaciona con exactamente un registro en otra tabla, y viceversa.
  • Se utiliza cuando la información en una tabla está estrechamente vinculada a otra tabla y cada registro tiene una correspondencia única en la otra tabla.
  • La establecemos mediante la inclusión de la clave primaria de una tabla como clave foránea en la otra tabla
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50),
  password VARCHAR(50)
);


CREATE TABLE profiles (
  id INT PRIMARY KEY,
  bio VARCHAR(50),
  photo VARCHAR(50),
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Relación Uno a Muchos (One-to-Many)

  • Un registro en una tabla se relaciona con varios registros en otra tabla.
  • Cada registro en la segunda tabla solo se relaciona con un registro en la primera tabla.
  • Se establece colocando la clave primaria de la tabla “uno” como clave foránea en la tabla “muchos”.
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50),
  password VARCHAR(50)
);


CREATE TABLE posts (
  id INT PRIMARY KEY,
  title VARCHAR(50),
  body VARCHAR(50),
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Relación Muchos a Muchos (Many-to-Many)

  • Varios registros en una tabla pueden relacionarse con varios registros en otra tabla.
  • Para representar esta relación, se requiere una tabla adicional, conocida como tabla de unión o tabla de enlace, que vincula las dos tablas principales.
  • La tabla de enlace contiene las claves primarias de ambas tablas y puede contener atributos adicionales relacionados con la relación.
  • Nota: La forma vista para crear una relación many to many se denomina forma explícita. Existe también la forma implícita o cascada
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50),
  password VARCHAR(50)
);


CREATE TABLE roles (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);


CREATE TABLE user_roles (
  user_id INT,
  role_id INT,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

JOIN

  • INNER JOIN: Devuelve solo los registros que tienen coincidencias en ambas tablas.
  • LEFT JOIN: Devuelve todos los registros de la tabla izquierda y los registros coincidentes de la tabla derecha. Si no hay coincidencias, se devuelven valores NULL para los campos de la tabla derecha.
  • RIGHT JOIN: Devuelve todos los registros de la tabla derecha y los registros coincidentes de la tabla izquierda. Si no hay coincidencias, se devuelven valores NULL para los campos de la tabla izquierda.
  • FULL JOIN: Devuelve todos los registros de ambas tablas, coincidentes o no. Si no hay coincidencias, se devuelven valores NULL para los campos no coincidentes. JOIN

Funciones

AGGREGATE:

  • COUNT(*): devuelve el número de filas que coinciden con un criterio específico.
  • AVG(column): devuelve el valor promedio de una columna numérica.
  • SUM(column): devuelve la suma total de una columna numérica.
  • MAX(column): devuelve el valor más grande de la columna seleccionada.
  • MIN(column): devuelve el valor más pequeño de la columna seleccionada.

STRING:

  • CONCAT(exp1, exp2): concatena dos o más expresiones juntas.
  • LENGTH(string): devuelve la longitud de la cadena.
  • UPPER(string): devuelve la misma cadena en mayúscula.
  • LOWER(string): devuelve la misma cadena en minúscula.

NUMERIC:

  • RANDOM(): devuelve un número aleatorio entre 0 y 1.
  • ROUND(number, decimals): devuelve el número decimal de entrada redondeado a la posición decimal definida más cercana.
  • FLOOR(number): devuelve el número decimal de entrada redondeado hacia abajo.
  • CEIL(number): devuelve el número decimal de entrada redondeado hacia arriba.

DATE:

LIMIT

SELECT <column> FROM <table_name> WHERE <condition> LIMIT <by_number>;


SELECT * FROM estudiantes WHERE age > 10 LIMIT 10;

ORDER BY

SELECT <column> FROM <table_name> WHERE <condition> ORDER BY <column> ASC|DESC;


SELECT * FROM estudiantes WHERE age > 10 ORDER BY age DESC;

LIKE

SELECT <column> FROM <table_name> WHERE <column> LIKE <pattern>;


SELECT * FROM estudiantes WHERE first_name LIKE 'A%';

GROUP BY

SELECT <column> FROM <table_name> GROUP BY <column>;


SELECT age, COUNT(*) FROM estudiantes GROUP BY age;

Ejercicios

A partir de la base de datos suministrada y mediante DQL. Hacer las siguientes consultas (queries):

  • Contar cuántas películas empiezan con la letra S.
  • Sumar la duración en minutos de todas las películas clasificadas para PG-13.
  • Mostrar todos los emails de los clientes junto con la suma de todas las películas que rentaron.
  • Mostrar el número de teléfono de la dirección que tiene el código postal más grande.
  • Mostrar la dirección, el distrito y el nombre de la ciudad del primer cliente inactivo.