llaves - SQL - 18 dec

gistfile1.txt

-- Users
DROP TABLE users;

-- ID strings -- x ()

-- ADMIN - 1
-- SOFT delete
CREATE TABLE users(
    id INT AUTO_INCREMENT PRIMARY KEY, -- Indexados -- COUNT(id) -- COUNT(username)
    username VARCHAR(50) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100)
);

INSERT INTO users (username, first_name, last_name) VALUES ('cody1', 'Cody', 'Cody'); -- 1
INSERT INTO users (username, first_name, last_name) VALUES ('cody2', 'Cody', 'Cody');
INSERT INTO users (username, first_name, last_name) VALUES ('cody3', 'Cody', 'Cody');
INSERT INTO users (username, first_name, last_name) VALUES ('cody4', 'Cody', 'Cody');
INSERT INTO users (username, first_name, last_name) VALUES ('cody5', 'Cody', 'Cody'); -- 5

-- 1: M
-- BELONGS to
DROP TABLE tasks;

CREATE TABLE tasks(
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    title VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE --En pertenencia
);

INSERT INTO tasks(user_id, title) VALUES (6, 'Terminar el curso');
INSERT INTO tasks(user_id, title) VALUES (6, 'Practicar');
INSERT INTO tasks(user_id, title) VALUES (6, 'Apagar la instancia de AMAZON!!!! $');

-- TRUNCATE users;
-- Courses 
DROP TABLE courses;

CREATE TABLE courses(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

INSERT INTO courses(title) VALUES ('Base de datos'); -- 1
INSERT INTO courses(title) VALUES ('Python');
INSERT INTO courses(title) VALUES ('Ruby');
INSERT INTO courses(title) VALUES ('Rust'); -- 4

-- Enrollments
-- M:M
-- Un usuario puede poseer múltiples cursos
-- Un curso puede ser tomado por múltiples usuarios

DROP TABLE enrollments;

-- M:M
CREATE TABLE enrollments(
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT, -- FK
    course_id INT, -- FK
    -- attempts: INT
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                                            -- ON DELETE SET NULL
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);
INSERT INTO enrollments (user_id, course_id) VALUES(2, 1);

-- @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1


-- SELECT
-- JOINs

-- INNER JOIN
-- LEFT JOIN 
-- RIGHT JOIN

-- CROSS JOIN

-- SELECT 
--    *
-- FROM users FULL JOIN tasks

SELECT 
    *
FROM users INNER JOIN tasks ON users.id = tasks.user_id;
WHERE users.username = 'cody6';


-- 8 Jan 2026
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  country VARCHAR(2) NOT NULL,
  status ENUM('active', 'inactive') NOT NULL,
  created_at DATETIME NOT NULL,
  last_login DATETIME NULL
);

-- User :  M
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL, -- FK
  total DECIMAL(10,2) NOT NULL,
  status ENUM('pending', 'paid', 'cancelled') NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_orders_user (user_id),
  INDEX idx_orders_created (created_at)
);

CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  payment_method ENUM('card', 'paypal', 'bank_transfer') NOT NULL,
  status ENUM('pending', 'completed', 'failed') NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  INDEX idx_payments_order (order_id),
  INDEX idx_payments_status (status)
);

-- INSERT --

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  country VARCHAR(2) NOT NULL,
  status ENUM('active', 'inactive') NOT NULL,
  created_at DATETIME NOT NULL,
  last_login DATETIME NULL
);

-- User :  M
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL, -- FK
  total DECIMAL(10,2) NOT NULL,
  status ENUM('pending', 'paid', 'cancelled') NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_orders_user (user_id),
  INDEX idx_orders_created (created_at)
);

CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  payment_method ENUM('card', 'paypal', 'bank_transfer') NOT NULL,
  status ENUM('pending', 'completed', 'failed') NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  INDEX idx_payments_order (order_id),
  INDEX idx_payments_status (status)
);

-- INSERT --

INSERT INTO users (name, email, country, status, created_at, last_login) VALUES
('Alice', 'alice@example.com', 'MX', 'active', '2023-05-10', '2025-01-10'),
('Bob', 'bob@example.com', 'AR', 'active', '2022-08-15', '2024-12-01'),
('Charlie', 'charlie@example.com', 'MX', 'inactive', '2021-01-20', NULL),
('Diana', 'diana@example.com', 'CO', 'active', '2024-02-01', '2025-01-05'),
('Eve', 'eve@example.com', 'MX', 'active', '2024-06-10', NULL),
('Frank', 'frank@example.com', 'AR', 'inactive', '2020-11-03', '2023-07-20');


INSERT INTO orders (user_id, total, status, created_at) VALUES
(1, 150.00, 'paid',      '2024-01-15'),
(1, 200.00, 'paid',      '2024-06-20'),
(1, 50.00,  'cancelled', '2023-11-10'),

(2, 300.00, 'paid',      '2024-03-05'),
(2, 700.00, 'paid',      '2024-10-01'),

(3, 120.00, 'pending',   '2024-02-14'),

(4, 500.00, 'paid',      '2024-07-22'),

(5, 400.00, 'pending',   '2024-09-01'),
(5, 700.00, 'paid',      '2024-11-15');


INSERT INTO payments (order_id, amount, payment_method, status, created_at) VALUES
(1, 150.00, 'card', 'completed', '2024-01-15'),
(2, 200.00, 'paypal', 'completed', '2024-06-20'),

(4, 300.00, 'card', 'completed', '2024-03-05'),
(5, 700.00, 'bank_transfer', 'completed', '2024-10-01'),

(7, 500.00, 'card', 'completed', '2024-07-22'),

(9, 700.00, 'paypal', 'completed', '2024-11-15'),

(8, 400.00, 'card', 'failed', '2024-09-01');


-- 1.- Listar todos los usuarios activos con órdenes en 2024.
SELECT DISTINCT
    users.id,
    users.email
FROM users
INNER JOIN orders ON users.id = orders.user_id
WHERE users.status = 'active'
AND YEAR(orders.created_at) = '2024';

SELECT
    users.id,
    users.email,
    COUNT(*) as total
FROM users
INNER JOIN orders ON users.id = orders.user_id -- 1
WHERE users.status = 'active'
AND YEAR(orders.created_at) = '2024'
GROUP BY users.id;

-- SELECT orders.status, COUNT(*) FROM orders GROUP BY orders.status;

SELECT 
    users.id,
    users.email,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id AND YEAR(created_at) = 2024) as total
FROM users
WHERE users.id IN (
    SELECT user_id FROM orders WHERE YEAR(orders.created_at) = '2024' -- set
) AND users.status = 'active';


-- 2.- Listar los correos de todos los usuarios sin ordenes.

SELECT
    users.email
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;


-- 3.- Usuarios con almenos un orden pagada (EXISTS)

SELECT 
    users.id,
    users.email
FROM users 
WHERE EXISTS( 
    SELECT 1 
    FROM orders WHERE orders.user_id = users.id AND orders.status = "paid"
);

SELECT 
    users.id,
    users.email
FROM users 
INNER JOIN orders ON users.id = orders.user_id and orders.status = "paid"
GROUP BY users.id;

-- 4.- Total pagado por usuarios en 2024. Listar todos los usuarios que hayan gastado más de $500

SELECT 
    users.id,
    users.email,
    SUM(orders.total) AS total
FROM users 
INNER JOIN orders ON users.id = orders.user_id and orders.status = "paid"
GROUP BY users.id
HAVING total > 500
ORDER BY total DESC
LIMIT 3;


SELECT 
    orders_users_with_total.email
FROM
(
    SELECT 
    users.id,
    users.email,
    SUM(orders.total) AS total
    FROM users 
        INNER JOIN orders ON users.id = orders.user_id and orders.status = "paid"
    GROUP BY users.id
)AS orders_users_with_total
WHERE total > 500
ORDER BY total DESC
LIMIT 3;

-- Origen , JOIN , Where (Sub consultas) - Group by - Agregación, Having, SELECT , Order, Limit

-- 5.-Usuarios con más de una orden.

SELECT 
    users.name
FROM users 
JOIN orders ON users.id = orders.user_id 
GROUP BY users.name 
HAVING COUNT(orders.id) > 1;


-- 6.- Obtener todos los usuarios que: Están activos, tienen al menos 2 órdenes pagadas, gastarón más de 500 dólares en 2024.
-- 7.- Usuarios con pagos completados.
-- 8.- Monto total pagado por usuario.
-- 9.- Usuarios con al menos un pago completado.
-- 10.- Obtén todos los usuarios activos que tuvieron al menos una orden en 2024, sin importar el estado de la orden.
-- 11.- Obtén todas las órdenes que NO tienen ningún pago asociado.
-- 12.- Obtén los usuarios que tengan al menos un pago completado, sin usar JOINs (Exists)
-- 13.- Obtén los usuarios activos que tengan al menos una orden pagada en 2024, y cuya orden tenga al menos un pago completado