CREATE DATABASE myDB;
USE myDB;
CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
hourly_pay DECIMAL(5, 2),
hire_date DATE
);
CREATE TABLE products (
product_id INT,
product_name VARCHAR(25) UNIQUE,
price DECIMAL(4, 2)
);
ALTER TABLE products
ADD CONSTRAINT UNIQUE(product_name);
ALTER TABLE products
ALTER price SET DEFAULT 0;
SELECT * FROM products;
INSERT INTO products
VALUES (100, "Hamburger", 3.99),
(101, "Fries", 1.89),
(102, "Soda", 1.00),
(103, "Ice cream", 1.49);
INSERT INTO products (product_id, product_name)
VALUES (104, "Straw"),
(105, "Napkin"),
(106, "Fork"),
(107, "Spoon");
SELECT * FROM employees;
RENAME TABLE workers TO employees;
ALTER TABLE employees ADD phone_number VARCHAR(15);
ALTER TABLE employees
CHANGE phone_number email VARCHAR(50);
ALTER TABLE employees
MODIFY COLUMN email VARCHAR(100);
ALTER TABLE employees
MODIFY COLUMN email VARCHAR(100)
AFTER last_name; /* Con la palabra reservada AFTER puedo indicar luego de que
columna va a ir la columna modificada */
/* Si quiero que se ubique al principio uso la
palabra reservada FIRST */
ALTER TABLE employees
DROP COLUMN email;
DESC employees;
INSERT INTO employees
VALUES (2, "Squidward", "Tentacles", 15.00, "2023-01-03"),
(3, "Spongebob", "Squarepants", 12.50, "2023-01-04"),
(4, "Patrick", "Star", 12.50, "2023-01-05"),
(5, "Sandy", "Cheeks", 17.25, "2023-01-06");
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (6, "Sheldon", "Plankton");
SELECT * FROM employees;
SELECT first_name, last_name
FROM employees;
SELECT *
FROM employees
WHERE hourly_pay >= 15;
SELECT *
FROM employees
WHERE hire_date IS NULL;
USE myDB;
UPDATE employees
SET hourly_pay = 10.50,
hire_date = "2023-01-07"
WHERE employee_id = 6;
UPDATE employees
SET hire_date = NULL
WHERE employee_id = 6;
ALTER TABLE employees
ADD CONSTRAINT chk_hourly_pay CHECK (hourly_pay >= 10); /* Con CHECK puedo hacer
una verificacion de un dato para darle entrada a la base de datos */
ALTER TABLE employees
DROP CHECK chk_hourly_pay;
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
CREATE TABLE transactions (
transaction_id INT PRIMARY KEY AUTO_INCREMENT,
amount DECIMAL(5, 2),
customer_id INT,
CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers
(customer_id)
);
ALTER TABLE transactions
DROP FOREIGN KEY fk_customer_id; /* Con esta accion eliminaria la FOREIGN KEY de la
tabla sin eliminar la columna */
ALTER TABLE transactions
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id) REFERENCES customers (customer_id); /* Se puede alterar
el nombre de la CONSTRAINT si nos olvidamos de ponerle uno */
DROP TABLE transactions;
ALTER TABLE transactions
ADD CONSTRAINT PRIMARY KEY (transaction_id);
ALTER TABLE transactions
AUTO_INCREMENT = 1000;
INSERT INTO customers (first_name, last_name) VALUES
("Fred", "Fish"),
("Larry", "Lobster"),
("Bubble", "Bass"),
("Poppy", "Puff");
INSERT INTO transactions (amount, customer_id) VALUES
(4.99, 3),
(2.89, 2),
(3.38, 3),
(4.99, 1),
(1.00, NULL);
DELETE FROM transactions
WHERE customer_id IS NULL;
SELECT * FROM customers;
SELECT * FROM transactions;
/*DROP TABLE customers;
DROP TABLE transactions;*/
use myDB;
SELECT transaction_id, amount, first_name, last_name
FROM transactions t INNER JOIN customers c
ON t.transaction_id = c.customer_id;
