mysql-ctl start   /* c9 command for start */
mysql-ctl cli     /* c9 command for cli */
mysql-ctl stop    /* c9 command for stop */

show databases;
create database db_name; /* make a new database */
use db_name;             /* use the database */
SELECT database(); /* check which database is used/active */
drop database db_name;   /* delete the database */


CREATE TABLE tablename   /* make a new table */
  (
    column_name data_type,
    column_name data_type
  );

CREATE TABLE cats        /* make a new table called cats */
  (
      name VARCHAR(100),
      age INT
  );
show tables;             /* show table currently used */
show columns from cats;  /* show columns from cats in table format */
DESC cats;               /* show(or describe) columns from cats in table format */
DROP TABLE cats;         /* delete the table */

INSERT INTO cats(name, age)
VALUES ('Jetson', 7);
/* or */
INSERT INTO cats(age, name) VALUES (12, 'Victoria');
/* or */
INSERT INTO cats(name, age)
VALUES ('Charlie', 10),
       ('Sadie', 3),
       ('Lazy Bear', 1);
SELECT * FROM cats;  /*  view the values from the table called cats in table format  */


CREATE TABLE people (first_name VARCHAR(20), last_name VARCHAR(20), age INT);
INSERT INTO people (age, first_name, last_name) VALUES (13, 'Tina', 'Belcher');
SELECT * FROM people;

INSERT INTO people (first_name, last_name, age)
VALUES ('Linda', 'Belcher', 45),
       ('Phillip', 'Frond', 38),
       ('Calvin', 'Fischoeder', 70);

SHOW WARNINGS;    /* if there's a warning, will show the warnings */

CREATE TABLE cats2 (name VARCHAR(100) NOT NULL, age INT NOT NULL); /* requires values or data */
INSERT INTO cats2(name) VALUES('Texas'); /* no age will produce warning and value 0 for age */
INSERT INTO cats2(age) VALUES(7); /* no name will produce warning and '' empty string for name */

CREATE TABLE cats3 (name VARCHAR(100) DEFAULT 'unnamed', age INT DEFAULT 99);

/* Primary Key - when for example name and age have same values but supposedly they are diff
 users with similar values ...  KEY can identify which is which! (Unique Identifier of a row) */
 CREATE TABLE unique_cats (cat_id INT NOT NULL, name VARCHAR(100), age INT,
 PRIMARY KEY (cat_id));

INSERT INTO unique_cats(cat_id, name, age) VALUES (1, "Fred", 23);
SELECT * FROM unique_cats;
INSERT INTO unique_cats(cat_id, name, age) VALUES (2, "Louise", 3);

CREATE TABLE cats4 (cat_id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100), age INT, PRIMARY KEY(cat_id));

CREATE TABLE employees2(
  emp_id INT NOT NULL AUTO_INCREMENT
  , last_name VARCHAR(50) NOT NULL
  , first_name VARCHAR(50) NOT NULL
  , middle_name VARCHAR(50)
  , age INT NOT NULL
  , current_status VARCHAR(50) NOT NULL DEFAULT 'Employed',
  Primary KEY (emp_id));

/* CRUD - Create Read Update Delete  */

CREATE TABLE cats(
cat_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100),
breed VARCHAR(100),
age INT,
PRIMARY Key (cat_id)
);

INSERT INTO cats(name,breed, age)
VALUES ('Ringo', 'Tabby', 4),
       ('Cindy', 'Maine Coon', 10),
       ('Dumbledore', 'Main Coon', 11),
       ('Egg', 'Persian', 4),
       ('Misty', 'Tabby', 13),
       ('George Michael', 'Ragdoll', 9),
       ('Jackson', 'Sphynx', 7);

SELECT * FROM cats WHERE age=4;
SELECT * FROM cats WHERE name='egG';   /* by default NOT CASE SENSITIVE so will get 1 query */
SELECT cat_id FROM cats;
SELECT name, breed FROM cats;
SELECT name, age FROM cats WHERE breed='Tabby';
SELECT cat_id, age FROM cats WHERE cat_id=age;
SELECT * FROM cats WHERE cat_id=age;

SELECT cat_id as ID, name FROM cats; /* Alias for cat_id is ID */

UPDATE cats SET breed = 'Shorthair' WHERE breed='Tabby';
UPDATE cats SET age=14 WHERE name='Misty';

UPDATE cats SET name='Jack' WHERE name='Jackson';
UPDATE cats SET breed='British Shorthair' WHERE name='Ringo';
UPDATE cats SET breed='Main Coon' WHERE breed='Maine Coon';
SELECT * FROM cats WHERE breed='Main Coon';
UPDATE cats SET age=12 WHERE breed='Main Coon';

DELETE FROM cats WHERE name='Egg';
DELETE FROM cats; /* DONT DO THIS AS IT WILL DELETE ALL THE CATS! */

/* Delete all 4 year old cats */
Select * From cats where age = 4;
DELETE FROM cats where age = 4;
SELECT * FROM cats;

DELETE FROM cats where cat_id=age;



/* Running SQL Files */
mysql-ctl cli
use db_name;
source filename.sql; /* Where filename.sql contains codes to create a table  */
DESC tablename;

SELECT CONCAT(author_fname, ' ', author_lname) AS 'Full Name'  FROM books;
/* or */
SELECT author_fname AS first, author_lname AS last,
CONCAT(author_fname, ' ', author_lname) AS 'Full Name'  FROM books;
/* or */
SELECT CONCAT_WS(' - ', title, author_fname, author_lname) FROM books;
SELECT SUBSTRING('Hello World', 1, 4); /* Passes Hell - from char 1 to 4 */
SELECT SUBSTRING('Hello World, 7'); /* Passes World -starts at 7th char*/
SELECT SUBSTRING('Hello World, -3'); /* Passes rld -starts from last char and 3 chars*/
SELECT title FROM books;
SELECT SUBSTRING("Where I'm Calling From: Selected Stories", 1, 10);
SELECT SUBSTRING(title, 1, 10) FROM books;
SELECT SUBSTRING(title, 1, 10) AS 'short title' FROM books;
SELECT SUBSTR(title, 1, 10) AS 'short title' FROM books;
SELECT CONCAT
    (
        SUBSTRING(title, 1, 10),
        '...'
    ) AS 'short title'
FROM books;
source book_code.sql

/* replacing is CASE SENSITIVE! */
SELECT REPLACE('Hello World', 'l', '7'); /* result is He770 Wor7d */
SELECT REPLACE('cheese bread coffee milk', ' ', ' and ');
SELECT REPLACE(title,'e', '3') AS 'funkytitle' FROM books;

SELECT
    SUBSTRING(REPLACE(title, 'e', '3'), 1, 10) AS 'weird string'
FROM books;

SELECT REVERSE('Hello World'); -- dlroW olleH
SELECT CONCAT(author_fname, REVERSE(author_fname)) FROM books;

SELECT CHAR_LENGTH('Hello World');
SELECT author_lname, CHAR_LENGTH(author_lname) AS 'length' FROM books;
SELECT CONCAT(author_lname, ' is ', CHAR_LENGTH(author_lname), ' characters long') FROM books;

SELECT UPPER('Hello World'); -- HELLO WORLD
SELECT LOWER('Hello World'); -- hello world
SELECT CONCAT('MY FAVORITE BOOK IS ', UPPER(title)) FROM books;
SELECT CONCAT('MY FAVORITE BOOK IS ', LOWER(title)) FROM books;
SELECT UPPER(CONCAT(author_fname, ' ', author_lname)) AS "full name in caps"
FROM books;

INSERT INTO books
    (title, author_fname, author_lname, released_year, stock_quantity, pages)
    VALUES ('10% Happier', 'Dan', 'Harris', 2014, 29, 256),
           ('fake_book', 'Freida', 'Harris', 2001, 287, 428),
           ('Lincoln In The Bardo', 'George', 'Saunders', 2017, 1000, 367);

SELECT DISTINCT author_lname FROM books;
SELECT DISTINCT released_year FROM books;
SELECT DISTINCT CONCAT(author_fname,' ',author_lname) FROM books;
SELECT DISTINCT author_fname,author_lname FROM books;

SELECT author_lname FROM books ORDER BY author_lname;
SELECT title FROM books ORDER BY title DESC; -- where DESC is descending not describe
SELECT title FROM books ORDER BY title; --ascending DEFAULT
SELECT title, released_year, pages FROM books ORDER BY released_year;
SELECT title, pages FROM books ORDER BY released_year;
SELECT title, author_fname, author_lname FROM books ORDER BY 2;
SELECT author_fname, author_lname FROM books ORDER BY author_lname, author_fname;

SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 14; -- where DESC is descending not describe
SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 5;
SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 1,3;
SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 10,1;
SELECT * FROM tbl LIMIT 95,18446744073709551615;

SELECT title, author_fname FROM books WHERE author_fname LIKE '%da%';
SELECT title, author_fname FROM books WHERE author_fname LIKE 'da%';

SELECT title, stock_quantity FROM books WHERE stock_quantity LIKE '__'; -- 2 underscores will pull ##
-- if searching for actual % or _, use \%
SELECT title FROM books WHERE title LIKE '%\%%'; -- query shows the book with title *10% Happier*

-- Select using Range
SELECT * from books WHERE released_year BETWEEN 2000 AND 2018;

SELECT COUNT(*) FROM books;
SELECT COUNT(DISTINCT author_fname, author_lname) FROM books;   -- count how many unique authors

-- how many titles contain 'the'?
SELECT COUNT(*) FROM books WHERE title LIKE '%the%';
-- to manually check:
SELECT title FROM books WHERE title LIKE '%the%';


-- GROUP BY
SELECT title, author_lname FROM books;
SELECT title, author_lname FROM books GROUP BY author_lname;
SELECT author_lname, COUNT(*) FROM books GROUP BY author_lname;
SELECT CONCAT(author_lname, ' ', author_fname), COUNT(*) FROM books GROUP BY CONCAT(author_lname, ' ', author_fname);
-- or
SELECT author_fname, author_lname, COUNT(*) FROM books GROUP BY author_lname,author_fname;

SELECT released_year, COUNT(*) FROM books GROUP BY released_year;
-- or
SELECT CONCAT('In ', released_year, ' ', COUNT(*), ' book(s) released') AS '# of Books Released by Year' FROM books GROUP BY released_year;


-- Min and Max
--Find the minimum released Year
SELECT MIN(released_year) FROM books;
--Find the maximum released Year
SELECT MAX(released_year) FROM books;
-- Find book with minimum pages and show the title of the book
/* WRONG WAY */ SELECT MIN(pages), title FROM books; /* WRONG WRONG WRONG */
SELECT * FROM books WHERE pages = (SELECT MIN(pages) FROM books); /* 1st RIGHT WAY */
SELECT title, pages FROM books WHERE pages = (SELECT MIN(pages) FROM books); /* 2nd RIGHT WAY */
SELECT * FROM books WHERE pages = 176; /*3rd RIGHT WAY */
SELECT title,pages FROM books ORDER BY pages ASC LIMIT 1; /*4th RIGHT WAY, FASTEST WAY!!! */
--
-- Find the year each author published their first book
SELECT author_fname, author_lname, MIN(released_year) FROM books
GROUP BY author_lname,author_fname;
--
SELECT title, MAX(released_year) FROM books
GROUP BY author_lname,author_fname;
--
-- Find the longest page count for each author
SELECT author_fname, author_lname, MAX(pages) FROM books
GROUP BY author_lname,author_fname;
-- or
SELECT CONCAT(author_fname, ' ', author_lname) AS author,
MAX(pages) AS 'longest book'
FROM books
GROUP BY author_lname, author_fname;

-- sum
SELECT Sum(pages) FROM books;
SELECT SUM(released_year) FROM books;
--
-- sum all pages each author has written
SELECT SUM(pages), author_fname, author_lname FROM books
GROUP BY author_lname, author_fname;

--average
-- Calcualate the average released_year across all books
SELECT AVG(released_year) FROM books;
--
-- Calculate the average stock quantity for books released in the same Year
SELECT AVG(stock_quantity) FROM books GROUP BY released_year;
SELECT AVG(stock_quantity), released_year FROM books GROUP BY released_year;
SELECT author_fname, author_lname, AVG(pages) FROM books GROUP BY author_lname,author_fname;

-- EXERCISES --
-- Print the # of books in the databases
SELECT COUNT(*) FROM books;
-- Print out how many books were released in each year
SELECT released_year, COUNT(*) FROM books GROUP BY released_year;
-- Print out the total number of books in stock_quantity
SELECT SUM(stock_quantity) FROM books;
-- Find the average released_year for each author
SELECT CONCAT(author_fname,' ',author_lname) AS 'AUTHOR',AVG(released_year) AS 'Average Release Year' FROM books GROUP BY author_lname,author_fname;
-- Find the fullname of the author who wrote the longest book title.
SELECT CONCAT(author_fname,' ',author_lname) AS 'AUTHOR', title FROM books ORDER BY LENGTH(TITLE) DESC LIMIT 1;
-- Find the fullname of the author who wrote the longest book (most pages).
SELECT CONCAT(author_fname,' ',author_lname) AS 'AUTHOR', title,pages  FROM books WHERE pages = (SELECT MAX(pages) FROM books);
-- or
SELECT CONCAT(author_fname, ' ', author_lname) AS 'AUTHOR', title, pages FROM books ORDER BY pages DESC LIMIT 1;
-- print data with released_year-descending, #books, and average pages
SELECT released_year AS 'year', COUNT(*), AVG(pages) FROM books GROUP by released_year;

--CHAR-- has a fixed Length.
CREATE TABLE dogs (name CHAR(5), breed VARCHAR(10));
INSERT INTO dogs (name, breed) VALUES ('bob', 'beagle');
INSERT INTO dogs (name, breed) VALUES ('robby', 'corgi');
INSERT INTO dogs (name, breed) VALUES ('Princess Jane', 'Retriever');
SELECT * FROM dogs;
INSERT INTO dogs (name, breed) VALUES ('Princess Jane', 'Retrievesadfdsafdasfsafr');
SELECT * FROM dogs;

-- DECIMAL
CREATE TABLE items (price DECIMAL(5,2));
INSERT INTO items(price) VALUES(7);
INSERT INTO items(price) VALUES(7987654);
INSERT INTO items(price) VALUES(34.88);
INSERT INTO items(price) VALUES(298.9999);
INSERT INTO items(price) VALUES(1.9999);
SELECT * FROM items;

CREATE TABLE thingies (price FLOAT);
INSERT INTO thingies(price) VALUES (88.45);
INSERT INTO thingies(price) VALUES (8877.45);
INSERT INTO thingies(price) VALUES (8877665544.45);
SELECT * FROM thingies;

-- DATE: YYYY-MM-DD
-- TIME: HH:MM:SS
-- DATETIME: YYYY--MM-DD HH:MM:SS
CREATE TABLE people (name VARCHAR(100), birthdate DATE, birthtime TIME, birthdt DATETIME);
INSERT INTO people (name, birthdate, birthtime, birthdt)
VALUES('Padma', '1983-11-11', '10:07:35', '1983-11-11 10:07:35');
INSERT INTO people (name, birthdate, birthtime, birthdt)
VALUES('Larry', '1943-12-25', '04:10:42', '1943-12-25 04:10:42');
SELECT * FROM people;

-- CURDATE() gives current date, CURTIME() gives current time, NOW() -- FYI: the now is current datetime
INSERT INTO people (name, birthdate, birthtime, birthdt) VALUES('Toaster', CURDATE(), CURTIME(), NOW());
SELECT * FROM people;

-- DAY(), DAYNAME(), DAYOFWEEK(), DAYOFYEAR(), MONTHNAME(), YEAR(), MINUTE(), ETC.
SELECT DATE_FORMAT(NOW(), '%W %M %D %Y'); -- Check MYSQL documentation for more symbol representations
SELECT CONCAT(MONTHNAME(birthdate), ' ', DAY(birthdate), ' ', YEAR(birthdate)) FROM people;
SELECT DATE_FORMAT(birthdt, 'Was born on a %W') FROM people;
SELECT DATE_FORMAT(birthdt, '%m/%d/%Y') FROM people;
SELECT DATE_FORMAT(birthdt, '%m/%d/%Y at %h:%i') FROM people;

-- DATEDIFF(), DATE_ADD, +/-
SELECT name, birthdate, DATEDIFF(NOW(), birthdate) FROM people;
SELECT birthdt, DATE_ADD(birthdt, INTERVAL 3 QUARTER, INTERVAL 2 SECOND) FROM people;
--
SELECT * FROM people;
SELECT DATEDIFF(NOW(), birthdate) FROM people;
SELECT name, birthdate, DATEDIFF(NOW(), birthdate) FROM people;
SELECT birthdt FROM people;
SELECT birthdt, DATE_ADD(birthdt, INTERVAL 1 MONTH) FROM people;
SELECT birthdt, DATE_ADD(birthdt, INTERVAL 10 SECOND) FROM people;
SELECT birthdt, DATE_ADD(birthdt, INTERVAL 3 QUARTER) FROM people;
SELECT birthdt, birthdt + INTERVAL 1 MONTH FROM people;
SELECT birthdt, birthdt - INTERVAL 5 MONTH FROM people;
SELECT birthdt, birthdt + INTERVAL 15 MONTH + INTERVAL 10 HOUR FROM people;

--TIMESTAMP
CREATE TABLE comments (
    content VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO comments (content) VALUES('lol what a funny article');
INSERT INTO comments (content) VALUES('I found this offensive');
INSERT INTO comments (content) VALUES('Ifasfsadfsadfsad');
SELECT * FROM comments ORDER BY created_at DESC;
CREATE TABLE comments2 (
    content VARCHAR(100),
    changed_at TIMESTAMP DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO comments2 (content) VALUES('dasdasdasd');
INSERT INTO comments2 (content) VALUES('lololololo');
INSERT INTO comments2 (content) VALUES('I LIKE CATS AND DOGS');
UPDATE comments2 SET content='THIS IS NOT GIBBERISH' WHERE content='dasdasdasd';
SELECT * FROM comments2;
SELECT * FROM comments2 ORDER BY changed_at;
CREATE TABLE comments2 (
    content VARCHAR(100),
    changed_at TIMESTAMP DEFAULT NOW() ON UPDATE NOW()
);



--EXERCISES
--1. WHAT'S A GOOD USE CASE FOR CHAR?
--//ANSWER - Any with fixed length such as passwords, abbreviation of US country, etc.
--2. CREATE TABLE inventory(item_name ________, price ___________, quantity__________); -- price is always < 1,000,000
CREATE TABLE inventory (item_name VARCHAR(25), price DECIMAL(8,2), quantity INT); -- decimal comes up to max 999,999.99 choose DECIMAL over FLOAT because it is more precise.
--3. WHAT'S THE DIFFERENCE BETWEEN DATETIME AND TIMESTAMP?
--//ANSWER - TIMESTAMP HAS A SMALLER RANGE BEFORE AND AFTER PRESENT TIME TIME STAMP FROM YEAR (1970-2038) AND DATETIME IS FROM YEAR (1000-9999)
--// also, DATETIME Takes twice the memorysize than TIMESTAMP
--//TIMESTAMP is usually used for when creating metadata when something is created,  other purpose we use DATETIME
--4. Print Out The Current Time
SELECT CURTIME();
--5. Print Out The Current Date (but not time)
SELECT CURDATE();
--6. Print Out The Current Day Of The Week (the number)
SELECT DAYOFWEEK(NOW());
--7. Print Out The Current Day Of The Week (the day name)
SELECT DAYNAME(NOW());
--8. Print Out the current day and time using this format: mm/dd/YYYY
SELECT DATE_FORMAT(NOW(), '%m/%d/%Y');
--9. Print Out the current day and time using this format: January 2nd at 3:15  and  April 1st at 10:18
SELECT DATE_FORMAT(NOW(), '%M %D at %h:%s');
--10. Create a tweets table that stores: a. The Tweet content(limit 140 chars) b. A Username(limit to 20 chars)   c. Time it was created_at
CREATE TABLE tweets(content VARCHAR(140), username VARCHAR(20), created_at TIMESTAMP DEFAULT NOW());

--LOGICAL OPERATORS
--
-- != (Not Equal) ie. select books that were not released in 2017
SELECT title FROM books WHERE released_year != 2017;
--
-- NOT LIKE ie. Select books with titles that don't start with 'W'
SELECT title FROM books WHERE title NOT LIKE 'W%';
--
-- > (Greater Than) ie. Select books released after the year 2000;
SELECT * FROM books WHERE released_year > 2000;
--
-- >= (Greater than or equal to) ie. Select books released from year 2000 and up;
SELECT * FROM books WHERE released_year >= 2000;
-- another sample: any books that have 100 stock or more will be on sale;
SELECT title, stock_quantity FROM books WHERE stock_quantity >= 100;
--
SELECT 99 > 1 -- produces 1 for boolean true;
SELECT 99 > 567 -- produces 0 for boolean false;
100 > 5 -- true
-15 > 15 -- false
9 > -10 -- true
 1 > 1 -- false
 'a' > 'b' -- false
 'A' > 'a' -- false
 'A' >=  'a' -- true
SELECT title, author_lname FROM books WHERE author_lname = 'Eggers';
SELECT title, author_lname FROM books WHERE author_lname = 'eggers';
SELECT title, author_lname FROM books WHERE author_lname = 'eGGers';
--
-- < (less than) ie. Select books released before the year 2000;
SELECT * FROM books WHERE released_year < 2000 ORDER BY released_year;
-- <= (less than or equal to) ie. Select books released before and during the year 2000;
SELECT * FROM books WHERE released_year <= 2000 ORDER BY released_year;
SELECT 3 < -10; -- false
 SELECT -10 < -9; -- true
SELECT 42 <= 42; -- true
 SELECT 'h' < 'p'; -- true
SELECT 'Q' <= 'q'; -- true
--
-- && (same with AND) ie. SELECT books written by Dave Eggers, and published after the year 2010;
SELECT * FROM books WHERE author_lname = 'Eggers' && released_year > 2010;
--or--
SELECT * FROM books WHERE author_lname = 'Eggers' AND released_year > 2010;
--SELECT books written by Dave Eggers, and published after the year 2010, and title contains 'novel':
SELECT * FROM books WHERE author_lname = 'Eggers' && released_year > 2010 && title LIKE '%novel%';
--
-- ││ (OR)
SELECT * FROM books WHERE author_lname = 'Eggers' ││ released_year > 2010;
--
-- BETWEEN
SELECT title, released_year FROM books  WHERE released_year BETWEEN 2004 and 2015;
--
-- NOT BETWEEN
SELECT title, released_year FROM books  WHERE released_year NOT BETWEEN 2004 and 2015;
--
-- (CASTING)
SELECT CAST('2017-05-02' AS DATETIME);
show databases;
use new_testing_db;
SELECT name, birthdt FROM people WHERE birthdt BETWEEN '1980-01-01' AND '2000-01-01';
SELECT
    name,
    birthdt
FROM people
WHERE
    birthdt BETWEEN CAST('1980-01-01' AS DATETIME)
    AND CAST('2000-01-01' AS DATETIME);
----
--
-- IN
SELECT title, author_lname FROM books WHERE author_lname IN('Carver', 'Lahiri', 'Smith'); --similar to select authors with last name of Carver OR Lahiri OR Smith.
--
-- NOT IN
SELECT title, released_year FROM books WHERE released_year >= 2000 && released_year NOT IN(2000,2002,2004,2006,2008,2010,2012,2014,2016) ORDER BY released_year;
--
-- % (Modulo - remainder operator) ie. divide by 2 is even.
SELECT title, released_year FROM books WHERE released_year >= 2000 && released_year % 2 = 0 ORDER BY released_year;
--
--
-- CASE STATEMENTS
SELECT title, released_year,
CASE
WHEN released_year >= 2000 THEN 'Modern Lit'
ELSE '20th Century Lit'
END AS GENRE
FROM books;
--
--ANOTHER CASE example
SELECT title, stock_quantity,
CASE
WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'
WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'
ELSE '***'
END AS STOCK
FROM books;
-- another way which is shorter...
SELECT title, stock_quantity,
CASE
WHEN stock_quantity <= 50 THEN '*'
WHEN stock_quantity <= 100 THEN '**'
ELSE '***'
END AS STOCK
FROM books;

--etc
ALTER TABLE Customers
ADD Email varchar(255);

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
MODIFY column_name datatype;

-- delete a row entry --
DELETE FROM table_name WHERE condition;

DELETE FROM books WHERE title = NULL; --WRONG WAY--
DELETE FROM books WHERE title IS NULL; -- RIGHT WAY--

--
-- LOGIC EXERCISES --
-- 1. EVALUATE THE FOLLOWING... SELECT 10 != 10 (ANSWER: 0); --- SELECT 15 > 14 && 99 -5 <= 94 (ANSWER: 1);  --- SELECT 1 IN (5,3) ││ 9 BETWEEN 8 AND 10 (ANSWER: 1);
-- 2. Select all books written before 1980 (non inclusive)
SELECT title, released_year FROM books WHERE released_year < 1980 ORDER BY released_year;
-- 3. Select all books written by Eggers or Chabon.
SELECT title, author_lname, released_year FROM books WHERE author_lname = 'Eggers' OR author_lname = 'Chabon';
--or--
SELECT title, author_lname, released_year FROM books WHERE author_lname IN ('Eggers', 'Chabon') ORDER BY author_lname; --better way!
-- 4. Select all books written by Lahiri, published after 2000.
SELECT title, author_lname, released_year FROM books WHERE author_lname = 'Lahiri' && released_year > 2000 ORDER BY released_year;
-- 5. Select all books with page counts between 100 and 200.
SELECT title, pages FROM books WHERE pages BETWEEN 100 and 200 ORDER BY pages;
-- or --
SELECT title, pages FROM books WHERE pages >= 100 && pages <=200;
-- 6. Select all books where author_lname starts with a 'C' or an 'S'
SELECT title, author_lname FROM books WHERE author_lname LIKE 'C%' OR author_lname LIKE 'S%' ORDER BY author_lname;
-- or--
SELECT title, author_lname FROM books WHERE SUBSTR(author_lname,1,1) = 'C' OR SUBSTR(author_lname,1,1) = 'S';
--or--
SELECT title, author_lname FROM books WHERE SUBSTR(author_lname,1,1) IN ('C','S');
-- 7. If title contains 'stories' then Short Stories .... Just Kids -and- A Heartbreaking Work then Memoir ... Everything else is a novel
SELECT title,author_lname,
CASE
WHEN title LIKE '%stories%' THEN 'Short Stories'
WHEN title LIKE '%Just Kids%' OR title LIKE '%A Heartbreaking Work%' THEN 'Memoir'
ELSE 'Novel'
END AS TYPE
FROM books;
-- 8. Bonus  title, author_lname COUNT books for each author_lname (mind singular and plural  book vs books)
SELECT title, CONCAT(author_fname,author_lname),
CASE
WHEN COUNT(*) <= 1 THEN CONCAT(COUNT(*), ' book')
ELSE CONCAT(COUNT(*), ' books')
END AS COUNT
FROM books GROUP BY author_lname,author_fname;

-- JOINS --
-- relationship basics:
--1 to many: ie. Customers & Orders
-- We Want to Store...
-- 1. A customer's first and last Name
-- 2. A customer's Email
-- 3. The date of purchase
-- 4. The price of the order
-- ****** PRIMARY KEY, FOREIGN KEY
-- Creating the customers and orders tables
CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100)
);
CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY(customer_id) REFERENCES customers(id)
);
-- Inserting some customers and orders
INSERT INTO customers (first_name, last_name, email)
VALUES ('Boy', 'George', 'george@gmail.com'),
       ('George', 'Michael', 'gm@gmail.com'),
       ('David', 'Bowie', 'david@gmail.com'),
       ('Blue', 'Steele', 'blue@gmail.com'),
       ('Bette', 'Davis', 'bette@aol.com');
INSERT INTO orders (order_date, amount, customer_id)
VALUES ('2016/02/10', 99.99, 1),
       ('2017/11/11', 35.50, 1),
       ('2014/12/12', 800.67, 2),
       ('2015/01/03', 12.50, 2),
       ('1999/04/11', 450.25, 5);
-- This INSERT fails because of our fk constraint.  No user with id: 98
INSERT INTO orders (order_date, amount, customer_id)
VALUES ('2016/06/06', 33.67, 98);


- Finding Orders Placed By George: 2 Step Process
SELECT id FROM customers WHERE last_name='George';
SELECT * FROM orders WHERE customer_id = 1;
-- Finding Orders Placed By George: Using a subquery
SELECT * FROM orders WHERE customer_id =
    (
        SELECT id FROM customers
        WHERE last_name='George'
    );
-- Cross Join Craziness a BIG NO NO
SELECT * FROM customers, orders;  -- WRONG WAY, NOT RECOMMENDED!

-- CROSS JOIN (OK WAY) - IMPLICIT INNER JOIN
SELECT * FROM customers,orders WHERE customers.id = orders.customer_id;
SELECT first_name, last_name, order_date, amount FROM customers,orders WHERE customers.id = orders.customer_id;

-- EXPLICIT INNER Join ( THE RIGHT WAY - PREFERRED WAY)
SELECT * FROM customers
JOIN orders
     ON customers.id = orders.customer_id;
-- or
SELECT first_name, last_name, order_date, amount FROM customers
JOIN orders
     ON customers.id = orders.customer_id;

--ARBITRARY JOIN - DON'T DO THIS!!!! - doesnt make sense since customer id and order id is NOT the same! (but customer id = order customer_id )
SELECT * FROM customers
JOIN orders ON customers.id = orders.id;

-- Getting Fancier
SELECT first_name, last_name, order_date, amount FROM customers
JOIN orders
     ON customers.id = orders.customer_id
ORDER BY amount;

SELECT first_name, last_name, sum(amount) AS total_spent
FROM customers
JOIN orders
ON customers.id = orders.customer_id
GROUP BY orders.customer_id
ORDER BY total_spent DESC;

-- LEFT JOIN
SELECT * FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;

SELECT first_name, last_name, IFNULL(SUM(amount), 0) AS total_spent
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY total_spent;

-- RIGHT JOIN
SELECT * FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id;

-- to delete both tables use:
DROP TABLE orders, customers;

INSERT INTO orders (order_date, amount, customer_id) VALUES
('2017/11/05',23.45, 45),
(CURDATE(), 777.77, 109);
-- will have ERROR because of Foreight Key Constraint!

SELECT
    IFNULL(first_name,'MISSING') AS first,
    IFNULL(last_name,'USER') as last,
    order_date,
    amount,
    SUM(amount)
FROM customers
RIGHT JOIN orders
    ON customers.id = orders.customer_id
GROUP BY first_name, last_name;

-- ON DELETE CASCADE
CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY(customer_id)
        REFERENCES customers(id)
        ON DELETE CASCADE
);

INSERT INTO customers (first_name, last_name, email)
VALUES ('Boy', 'George', 'george@gmail.com'),
       ('George', 'Michael', 'gm@gmail.com'),
       ('David', 'Bowie', 'david@gmail.com'),
       ('Blue', 'Steele', 'blue@gmail.com'),
       ('Bette', 'Davis', 'bette@aol.com');

INSERT INTO orders (order_date, amount, customer_id)
VALUES ('2016/02/10', 99.99, 1),
       ('2017/11/11', 35.50, 1),
       ('2014/12/12', 800.67, 2),
       ('2015/01/03', 12.50, 2),
       ('1999/04/11', 450.25, 5);

-- MANY TO MANY
CREATE TABLE reviewers (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100),
last_name VARCHAR(100)
);

CREATE TABLE series (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100),
released_year YEAR(4),
genre VARCHAR(100)
);

CREATE TABLE reviews(
id INT AUTO_INCREMENT PRIMARY KEY,
rating DECIMAL(2,1),
series_id INT,
reviewer_id INT,
FOREIGN KEY (series_id) REFERENCES series(id),
FOREIGN KEY (reviewer_id) REFERENCES reviewers(id)
);

INSERT INTO series (title, released_year, genre) VALUES
    ('Archer', 2009, 'Animation'),
    ('Arrested Development', 2003, 'Comedy'),
    ("Bob's Burgers", 2011, 'Animation'),
    ('Bojack Horseman', 2014, 'Animation'),
    ("Breaking Bad", 2008, 'Drama'),
    ('Curb Your Enthusiasm', 2000, 'Comedy'),
    ("Fargo", 2014, 'Drama'),
    ('Freaks and Geeks', 1999, 'Comedy'),
    ('General Hospital', 1963, 'Drama'),
    ('Halt and Catch Fire', 2014, 'Drama'),
    ('Malcolm In The Middle', 2000, 'Comedy'),
    ('Pushing Daisies', 2007, 'Comedy'),
    ('Seinfeld', 1989, 'Comedy'),
    ('Stranger Things', 2016, 'Drama');


    INSERT INTO reviewers (first_name, last_name) VALUES
        ('Thomas', 'Stoneman'),
        ('Wyatt', 'Skaggs'),
        ('Kimbra', 'Masters'),
        ('Domingo', 'Cortes'),
        ('Colt', 'Steele'),
        ('Pinkie', 'Petit'),
        ('Marlon', 'Crafford');

        INSERT INTO reviews(series_id, reviewer_id, rating) VALUES
            (1,1,8.0),(1,2,7.5),(1,3,8.5),(1,4,7.7),(1,5,8.9),
            (2,1,8.1),(2,4,6.0),(2,3,8.0),(2,6,8.4),(2,5,9.9),
            (3,1,7.0),(3,6,7.5),(3,4,8.0),(3,3,7.1),(3,5,8.0),
            (4,1,7.5),(4,3,7.8),(4,4,8.3),(4,2,7.6),(4,5,8.5),
            (5,1,9.5),(5,3,9.0),(5,4,9.1),(5,2,9.3),(5,5,9.9),
            (6,2,6.5),(6,3,7.8),(6,4,8.8),(6,2,8.4),(6,5,9.1),
            (7,2,9.1),(7,5,9.7),
            (8,4,8.5),(8,2,7.8),(8,6,8.8),(8,5,9.3),
            (9,2,5.5),(9,3,6.8),(9,4,5.8),(9,6,4.3),(9,5,4.5),
            (10,5,9.9),
            (13,3,8.0),(13,4,7.2),
            (14,2,8.5),(14,3,8.9),(14,4,8.9);

--Challenge1
SELECT * FROM series
JOIN reviews
ON series.id = reviews.series_id;

--Challenge2
SELECT title, AVG(rating) AS avg_rating
FROM series
JOIN reviews
ON series.id = reviews.series_id
GROUP BY series.id
ORDER BY avg_rating DESC
;

-- challenge 3
SELECT
first_name,last_name,rating
FROM reviewers
JOIN reviews
ON reviews.id = reviews.reviewer_id;

-- Challenge 4 - Unreviewed series_id
SELECT  title,rating
FROM series
LEFT JOIN reviews
ON series.id = reviews.series_id;

-- Challenge 5 genre avg ratings
SELECT genre, ROUND( AVG(rating), 2)
  AS avg_rating FROM series
JOIN reviews
ON series.id=reviews.series_id
GROUP BY genre;

-- Challenge 6 - Reviewer Stats
-- firstname,lastname,count(howmanyreviews),min(review),max(review), avg(review), status(active if count>=1)
SELECT
first_name,
last_name,
COUNT(rating) AS count,
IFNULL(MIN(rating),0) AS min,
IFNULL(MAX(rating),0) AS max,
IFNULL(AVG(rating),0) AS avg,
CASE
  WHEN COUNT(rating) >= 1
  THEN 'Active'
  ELSE 'Inactive'
END as status
FROM reviewers
LEFT JOIN reviews
ON reviewers.id = reviews.reviewer_id
GROUP BY reviewers.id
ORDER BY count DESC
;
-- OR
SELECT first_name,
       last_name,
       Count(rating)                               AS COUNT,
       Ifnull(Min(rating), 0)                      AS MIN,
       Ifnull(Max(rating), 0)                      AS MAX,
       Round(Ifnull(Avg(rating), 0), 2)            AS AVG,
       IF(Count(rating) > 0, 'ACTIVE', 'INACTIVE') AS STATUS
FROM   reviewers
       LEFT JOIN reviews
              ON reviewers.id = reviews.reviewer_id
GROUP  BY reviewers.id;

-- challenge 7
-- title, rating, reviewer
SELECT
title,
rating,
CONCAT(first_name, ' ',last_name) AS reviewer
FROM reviewers
JOIN reviews
  ON reviewers.id = reviews.reviewer_id
JOIN series
  ON series.id =reviews.series_id
ORDER BY title
;

                    -- Schema Design
-- USERS, PHOTOS, COMMENTS, LIKES, HASHTAGS, FOLLOWERS/FOLLOWEES


CREATE DATABASE ig_clone;
USE ig_clone;

CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (username) VALUES
('BlueTheCat'),
('CharlieBrown'),
('ColtSteele');

CREATE TABLE photos(
id INT AUTO_INCREMENT PRIMARY KEY,
image_url VARCHAR(255) NOT NULL,
user_id INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id)
);

INSERT INTO photos(image_url,user_id) VALUES
('/ldkfewg23', 1),
('/dfghrtte', 2),
('/sdfu56', 2);

CREATE TABLE comments(
id INT AUTO_INCREMENT PRIMARY KEY,
comment_text VARCHAR(255) NOT NULL,
user_id INT NOT NULL,
photo_id INT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (photo_id) REFERENCES photos(id)
);

INSERT INTO comments (comment_text, user_id, photo_id) VALUES
('Meow', 1, 2),
('Amazing Shot!', 3, 2),
('I <3 This',2,1);

CREATE TABLE likes(
user_id INT NOT NULL,
photo_id INT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (photo_id) REFERENCES photos(id),
PRIMARY KEY (user_id,photo_id) -- can only do 1 like per uniqe user to a photo.
);

INSERT INTO likes(user_id, photo_id) VALUES
(1,1),
(2,1),
(1,2),
(1,3),
(3,3);

-- find out how many likes per photo
SELECT image_url, count(likes.user_id) AS number_of_likes FROM photos
JOIN likes
ON likes.photo_id = photos.id
JOIN users
ON  users.id = likes.user_id
GROUP BY image_url
ORDER BY number_of_likes DESC;

-- RELATIONSHIP TROUBLES
CREATE TABLE follows(
follower_id INT NOT NULL,
followee_id INT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY (follower_id) REFERENCES users(id),
FOREIGN KEY (followee_id) REFERENCES users(id),
PRIMARY KEY(follower_id, followee_id)
);

INSERT INTO follows(follower_id, followee_id) VALUES
(1,2),
(1,3),
(3,1),
(2,3);


CREATE TABLE tags (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
tag_name VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE photo_tags(
photo_id INT NOT NULL,
tag_id INT NOT NULL,
FOREIGN KEY (photo_id) REFERENCES photos(id),
FOREIGN KEY (tag_id) REFERENCES tags(id),
PRIMARY KEY(photo_id, tag_id)
);

INSERT INTO tags(tag_name) VALUES
('adorable'),
('cute'),
('sunrise');

INSERT INTO photo_tags (photo_id,tag_id) VALUES
(1,1),
(1,2),
(2,3),
(3,2);

-- refer to igclone.sql for dataset

-- 1. Find the 5 Oldest Users.
SELECT * FROM users
ORDER BY created_at
LIMIT 5;

-- 2. Most Popular Registration Date
SELECT
DAYNAME(created_at) AS day,
COUNT(*) AS total
FROM users
GROUP BY day
ORDER BY total DESC
LIMIT 2;

-- 3. Find the users who have never posted a photo.
SELECT
username
FROM users
LEFT JOIN photos
ON photos.user_id = users.id
WHERE photos.id IS NULL ;

-- 4. Identify most popular photo (and user who created it)
SELECT
username,
photos.id,
photos.image_url,
COUNT(*) AS total
FROM photos
JOIN likes
ON likes.photo_id = photos.id
JOIN users
ON  photos.user_id = users.id
GROUP BY photos.id
ORDER BY total DESC
LIMIT 1
;

-- 5. Calcuate avg number of photos per user
SELECT (SELECT COUNT(*) FROM photos) / (SELECT COUNT(*) FROM users) AS avg;

-- 6. What are the top 5 most commonly used hashtags?
SELECT
tags.tag_name,
COUNT(*) as total
FROM photo_tags
JOIN tags
ON tags.id = photo_tags.tag_id
GROUP BY tags.id
ORDER BY total DESC
LIMIT 5;


-- 6. Find users who have liked every single photo on the site (bots!)
SELECT
username,
COUNT(*) AS num_likes
FROM users
JOIN likes
ON likes.user_id = users.id
GROUP BY likes.user_id
HAVING num_likes = (SELECT COUNT(*) FROM photos);