spacemonkeyvt · July 7, 2016 21:29 · Jul 7, 2016 · Jul 7, 2016
diff --git a/gistfile1.txt → sql_examples.txt b/gistfile1.txt → sql_examples.txt
diff --git a/gistfile1.txt b/gistfile1.txt
@@ -0,0 +1,168 @@
+CREATE///////////////////////////
+
+CREATE TABLE student (id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT, age INT); 
+
+.tables /* Shows the table created */ 
+
+.schema /* Shows the schema used to create the table */
+
+SELECT * FROM student; /* Show all rows in the student table */ 
+
+
+
+
+
+
+
+CREATE TABLE cohort (id INTEGER PRIMARY KEY, start_date DATE NOT NULL, end_date DATE NOT NULL);
+
+.tables /* Shows the tables created */ 
+
+.schema /* Shows the schema used to create the tables */
+
+SELECT * FROM cohort; /* Show all rows in the cohort table */
+
+
+
+
+
+/* CTRL + L to clear the REPL */ 
+
+
+
+UPDATE///////////////////////
+
+
+INSERT INTO student (first_name, last_name, age) VALUES ("Adam", "Dahan", 28);
+INSERT INTO student (first_name, last_name, age) VALUES ("Billy", "Bob", 18);
+INSERT INTO student (first_name, last_name, age) VALUES ("Jon", "Frankel", 29);
+INSERT INTO student (first_name, last_name, age) VALUES ("Simon", "Leyderman", 27);
+INSERT INTO student (first_name, last_name, age) VALUES ("Yoni", "Shoshan", 29);
+INSERT INTO student (first_name, last_name, age) VALUES ("Jean", "YA", 30);
+INSERT INTO student (first_name, last_name, age) VALUES ("Eric", "Thomas", 19);
+INSERT INTO student (first_name, last_name, age) VALUES ("Joe", "Fresh", 22);
+
+SELECT * FROM student; /* Show all rows in the student table */
+
+/* Change some display settings to make the output more readable */ 
+.mode column
+
+.headers on
+
+SELECT * FROM student; /* Show all rows in the student table */
+
+
+
+
+
+insert into cohort (start_date, end_date) values ('06/01/2016', '08/01/2016');
+insert into cohort (start_date, end_date) values ('08/01/2016', '10/01/2016');
+
+
+select * from cohort; /* Show all rows in the student table */
+
+
+update student set cohort_id = 2 where id >= 5;
+
+/* Show how the two tables associate via foreign key */
+
+
+READ///////////////////////////////
+
+select * from student where first_name = "Adam";
+
+select * from student where first_name = "adam"; /* Case-sensative */ 
+
+select * from student where first_name like 'a%'; /* Fuzzy case-insensative */ 
+
+select * from student where first_name like 'j%';
+
+select * from student where age > 20;
+
+select * from student where age < 20;
+
+select * from student where age = 18;
+
+/* AND */ 
+
+select * from student where age = 18 AND first_name = "Adam"; /* Returns nothing */
+
+select * from student where age = 18 AND first_name = "Billy"; /* 1 row */ 
+
+select * from student where age > 25 AND first_name like 'j%';
+
+/* OR */ 
+
+select * from student where age = 17 OR first_name = "Adam";
+
+select * from student where age = 18 OR first_name = "Adam"; 
+
+
+DELETE //////////////////////////////////
+
+delete from student where first_name = "Joe";
+
+insert into student (first_name, last_name, age) VALUES ("Joe", "Fresh", 22);
+insert into student (first_name, last_name, age) VALUES ("Joe", "Fresh", 23);
+insert into student (first_name, last_name, age) VALUES ("Joe", "Fresh", 24);
+
+delete from student where first_name = "Joe" AND age > 23;
+
+delete * from student; /* DELETE ALL */ 
+
+
+ALTER ////////////////////////////
+
+alter table student add column cohort_id INTEGER;
+
+
+
+Question 4
+There are two tables in a database:
+
+Invoices
+Customers
+Invoices have a number (string) and total (integer) and are sent to customers.
+
+a. What additional column (name and type) do we need to establish this relationship between the two tables?
+
+b. Which table is the parent vs child in this relationship?
+
+JOINS
+
+SQL defines 3 major kinds of joins:
+
+The INNER JOIN
+
+
+The OUTER JOIN
+
+
+The CROSS JOIN (We don’t teach this).
+/*
+
+INNER JOIN */SELECT student.first_name,
+         student.cohort_id
+FROM student
+INNER JOIN cohort
+    ON cohort.id = student.cohort_id; /* Outer
+JOIN */SELECT student.first_name,
+         student.cohort_id
+FROM student LEFT OUTER
+JOIN cohort
+    ON cohort.id = student.cohort_id;
+AGGREGATES
+
+/* Get the count of students */
+select count(*) from student;
+
+/* Get the average age of our students */
+select avg(age) from student;
+DROPPING
+
+drop table student;
+drop COLUMN unsupported.
+RENAMING
+
+ALTER TABLE new_person RENAME TO person;
+OTHER