Youtube SQL Intro

introToSQL.sql

#https://www.youtube.com/watch?v=nWeW3sCmD2k
# Topics:
# 1. Creating DB and Tables
# 2. Inserting Data
# 3. Updating Data
# 4. Deleting Data
# 5. Alter Tables
# 6. Basic Querying
# 7. SQL Operators
# 8. Indexes
# 9. Foreign Key Constraints
# 10. Joins
# 11. Aliases
# 12. Aggregate Functions and GroupBy

#1 Create DB and Tables
CREATE DATABASE test;

DROP DATABASE test;

CREATE DATABASE acme;

CREATE TABLE customers(
	id INT NOT NULL AUTOINCREMENT,
	firstName VARCHAR(255),
	lastName VARCHAR(255),
	email VARCHAR(255),
	address VARCHAR(255),
	city VARCHAR(255),
	state VARCHAR(255),
	zipcode VARCHAR(255),
	PRIMARY KEY(id) #Create Primary Keuy
);

#2. Insert Data; ID will autoincrement and create on its own
INSERT INTO customers (firstName, lastName, email, address, city, state, zipcode) VALUES 
('John', 'Doe', 'jdoe@gmail.com', '55 Main St'. 'Boston', 'MASS', '20902'),
('Jane', 'Doe', 'jdoe@gmail.com', '55 Main St'. 'Boston', 'MASS', '20902'),
('Jerry', 'Doe', 'jdoe@gmail.com', '55 Main St'. 'Boston', 'MASS', '20902'),
('Jessica', 'Doe', 'jdoe@gmail.com', '55 Main St'. 'Boston', 'MASS', '20902'),
('Jan', 'Doe', 'jdoe@gmail.com', '55 Main St'. 'Boston', 'MASS', '20902');


#3. Update Data
UPDATE customers
SET email = 'test@gmail.com'
WHERE id = 3;

#4 Delete Data
DELETE FROM customers
WHERE id = 3;

#5.Alter Table - add columns, change datatypes, etc
ALTER TABLE customers ADD testCol VARCHAR(255);

ALTER TABLE customers 
MODIFY COLUMN testCol INT(11); #some DB's use different syntax, ALTER COLUMN

ALTER TABLE customers
DROP COLUMN testCol;

#6. Querying DB
SELECT firstName, lastName FROM customers;
SELECT * FROM customers WHERE id=3;
SELECT * FROM customers ORDER BY lastName;
SELECT * FROM customers ORDER BY lastName DESC;
SELECT DISTINCT state FROM customers;

ALTER TABLE customers ADD COLUMN age INT(3);
SELECT * FROM customers WHERE age < 30;

#7. SQL Operators
#=, <>, >, <, >=, <=, BETWEEN, LIKE, IN, IS, IS NOT, IS NOT DISTINCT FROM, address
SELECT firstName, lastName WHERE age BETWEEN 22 AND 40; #find customers w/ ages between 22 and 40
#Citites ending with n
SELECT * FROM customers WHERE city LIKE '%n';
#Cities with n in it
SELECT * FROM customers WHERE city LIKE '%n%';
SELECT * FROM customers WHERE city NOT LIKE '%n%';
#Specify multiple values in where clause with IN
SELECT * FROM customers
WHERE state IN ('New York', 'New Hampshire');

#8. Indexes
# You only want to put them on columns that will be frequently searched against
CREATE INDEX CIndex ON customers(city);
SELECT city FROM customers; #Now much faster
DROP INDEX ON customers(city);

#9 Foreigne Key Constraints
CREATE TABLE products (
	id INT NOT NULL AUTOINCREMENT;
	name VARCHAR(255),
	price INT,
	PRIMARY KEY(id)
)
CREATE TABLE orders (
	id INT NOT NULL AUTOINCREMENT;
	orderNumber INT,
	productId INT,
	age INT,
	orderDate DATETIME default CURRENT_TIMESTAMP,
	PRIMARY KEY(id),
	PRIMARY KEY(id),
	FOREIGN KEY(customerId) REFERENCES customer(id),
	FOREIGN KEY(productId) REFERENCES customer(id)
)

#10 Joins (inner, outer, full)
INSERT INTO products(orderNumber, productId, customerId) VALUES
(001, 1, 4),
...
(009, 2, 12);

SELECT customers.firstname, customers.lastName, orders.id, orders.orderNumber
FROM customers 
	INNER JOIN orders
		ON customers.id = orders.customerId
ORDER BY customers.lastName;

SELECT customers.firstName, customers.lastName, orders.orderNumber, orders.orderDate
FROM customers
	LEFT JOIN orders ON customers.id = orders.customerId

SELECT orders.orderNumber, customers.firstName, customers.lastName
FROM customers
	RIGHT JOIN orders ON customers.id = orders.customerId
		ON orders.customerId = customers.id
ORDER BY orders.orderNumber

#Now we want orderNumber (orders table), Customers first and last name (custoemrs table), and Product ID (From product table)
SELECT orders.orderNumber, customers.firstName, customers.lastName, orders.id, products.name
FROM orders
	INNER JOIN products
		ON orders.productId = products.id
	INNER JOIN customers
		ON orders.customerId = customers.id
ORDER BY customers.lastName;

#11. Aliass - temporary names
SELECT firstName AS 'First Name', lastName AS 'Last Name';
SELECT CONCAT(firstName, ' ', lastName) AS 'Name', address, city, state;
SELECt CONCAT(firstName, ' ', lastName) AS 'Name', address, city, state
	CONCAT(address, ' ', city, ' ', state, ' ', zipcode) AS 'Address';
SELECT o.id, o.orderDate, c.firstName, c.lastName
FROM customers as c, orders as o;

#12. Aggregate Functions
#avg count min UCASE
SELECT AVG(age) FROM customers;
SELECT COUNT(age) FROM customers;
SELECT MIN(c.age) FROM customers AS c;

SELECT age, COUNT(age)
FROM customers
WHERE age > 30
GROUP BY age;

#Get Counts of ages that have counts of 2 or more
SELECT age, COUNT(age) FROM Customers
WHERE age > 2
GROUP BY age
HAVING COUNT(age) >= 2;

SELECT UCASE(lastName), LCASE(firstName) from customers