MySQL training

book-data.sql

DROP TABLE Cats;

CREATE TABLE books 
	(
		book_id INT NOT NULL AUTO_INCREMENT,
		title VARCHAR(100),
		author_fname VARCHAR(100),
		author_lname VARCHAR(100),
		released_year INT,
		stock_quantity INT,
		pages INT,
		PRIMARY KEY(book_id)
	);

INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)
VALUES
('The Namesake', 'Jhumpa', 'Lahiri', 2003, 32, 291),
('Norse Mythology', 'Neil', 'Gaiman',2016, 43, 304),
('American Gods', 'Neil', 'Gaiman', 2001, 12, 465),
('Interpreter of Maladies', 'Jhumpa', 'Lahiri', 1996, 97, 198),
('A Hologram for the King: A Novel', 'Dave', 'Eggers', 2012, 154, 352),
('The Circle', 'Dave', 'Eggers', 2013, 26, 504),
('The Amazing Adventures of Kavalier & Clay', 'Michael', 'Chabon', 2000, 68, 634),
('Just Kids', 'Patti', 'Smith', 2010, 55, 304),
('A Heartbreaking Work of Staggering Genius', 'Dave', 'Eggers', 2001, 104, 437),
('Coraline', 'Neil', 'Gaiman', 2003, 100, 208),
('What We Talk About When We Talk About Love: Stories', 'Raymond', 'Carver', 1981, 23, 176),
("Where I'm Calling From: Selected Stories", 'Raymond', 'Carver', 1989, 12, 526),
('White Noise', 'Don', 'DeLillo', 1985, 49, 320),
('Cannery Row', 'John', 'Steinbeck', 1945, 95, 181),
('Oblivion: Stories', 'David', 'Foster Wallace', 2004, 172, 329),
('Consider the Lobster', 'David', 'Foster Wallace', 2005, 92, 343);

cats-data.sql

DROP TABLE Cats;


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', 'Maine Coon', 11),
       ('Egg', 'Persian', 4),
       ('Misty', 'Tabby', 13),
       ('George Michael', 'Ragdoll', 9),
       ('Jackson', 'Sphynx', 7);
     

JS Monday Test Topics

1. JavaScript Basics (Operators, Loops, Conditionals)
2. Array & Array Iterators
3. Objects
4. String Functions

Assignments
https://github.com/varmab/javascript

JavaScript Fundamentals
https://docs.google.com/presentation/d/1LRpvMsdmTmJ4PBjYV5OoMpRX9p5FLk1utr16SSAS1rs/edit?usp=sharing

JavaScript Objects
http://slides.com/codingsastra/javascript-basics-objects?token=Zd1QDtvT

JavaScript Strings
http://slides.com/codingsastra/javascript-basics-strings?token=qgHY-GfG

http://bit.ly/2WsQ1sk

mysql-slides

MySQL

https://webdev.slides.com/coltsteele/mysql#/

Installing MySQL

https://webdev.slides.com/coltsteele/mysql-97#/

Getting started with MySQL

https://webdev.slides.com/coltsteele/mysql-97-98#/

MySQL CRUD

https://webdev.slides.com/coltsteele/mysql-99-100#/

MySQL Refining Sections

https://webdev.slides.com/coltsteele/mysql-99-101#/

MySQL Data Types

https://webdev.slides.com/coltsteele/mysql-99-102#/

MySQL Logical Operators

https://webdev.slides.com/coltsteele/mysql-99-103#/

MySQL Relationships & Joins

https://webdev.slides.com/coltsteele/mysql-99-104#/

MySQL Node

https://webdev.slides.com/coltsteele/mysql-105#/

MySQL Schema Design

https://webdev.slides.com/coltsteele/mysql-106#/

MySQL Triggers

https://webdev.slides.com/coltsteele/mysql-113#/17

Next Week

API Project: https://www.dropbox.com/s/6m1rg67nykbae64/APIProject.pdf?dl=0

People Trigger

DELIMITER $$

CREATE TRIGGER must_be_adult
     BEFORE INSERT ON people FOR EACH ROW
     BEGIN
          IF NEW.age < 18
          THEN
              SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Must be an adult!';
          END IF;
     END;
$$

DELIMITER ;

Students

CREATE TABLE Students(
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	first_name varchar(100));

CREATE TABLE Papers(
	title VARCHAR(255),
	grade INT,
	student_id INT NOT NULL,
	CONSTRAINT `fk_students_papers` FOREIGN KEY (`student_id`) 
  	 REFERENCES `students` (`id`) ON DELETE CASCADE
);

INSERT INTO students (first_name) VALUES 
('Caleb'), ('Samantha'), ('Raj'), ('Carlos'), ('Lisa');

INSERT INTO papers (student_id, title, grade ) VALUES
(1, 'My First Book Report', 60),
(1, 'My Second Book Report', 75),
(2, 'Russian Lit Through The Ages', 94),
(2, 'De Montaigne and The Art of The Essay', 98),
(4, 'Borges and Magical Realism', 89);

SELECT first_name, title, grade 
FROM Students inner join Papers 
ON students.id=Papers.student_id;

SELECT first_name, 
	CASE
		WHEN title=NULL THEN 'MISSING'
		ELSE title
	END as title, 
	grade 
FROM Students left join Papers 
ON students.id=Papers.student_id;

SELECT first_name, title, grade 
FROM Students right join Papers 
ON students.id=Papers.student_id;

Training DB

CREATE TABLE Books(
 id int not null auto_increment primary key,
 title varchar(100),
 author varchar(100)
);

INSERT INTO Books(title,author) 
VALUES('JS Fundamentals','John’),
('HTML Basics','David’),
('Node in Action','Smith');