SQL_Resources.md

#RESOURCES

###SQL:

• W3SQL
• Beginner SQL Tuts
• SQL Tutorial Point
• Inner vs Outter
• Visual Explanation of Joins

###Markdown:

• Markdown Basics
• Mastering Markdown
• MD Cheetsheet

###Screenshot:

• MAC Screenshots

#LEARNING OBJECTIVES

######SELECT SYNTAX

SELECT column-list FROM table-name
 [WHERE clause]
 [GROUP BY clause]
 [HAVING clause]
 [ORDER BY clause]

• WHERE: used when you want to retrieve specific from a table excluding other irrelevant information
• GROUP BY: used along with the group functions to retrieve data grouped according to one or more columns
• HAVING: used to filter data based on the group functions
• ORDER BY: used in a SELECT statement to sort results either in ascending or descending order

######OPERATORS COMPARISON OPERATORS: used to compare the column data with specific values in a condition

LOGICAL OPERATORS: three logical operators AND, OR, and NOT

######COMPARISON KEYWORDS COMPARISON KEYWORDS: used to enhance the search capabilities of a sql query

######GROUP FUNCTIONS

• COUNT: returns the number of rows in the table
• MAX: used to get the maximum value from a column
• MIN: used to get the minimum value from a column
• AVG: used to get the average value of a numeric column.
• SUM: used to get the sum of a numeric column
• DISTINCT: used to select the distinct rows

######JOINS JOIN: used to relate information in different tables

There are four types of joins:

• 1. SIMPLE JOIN: returns all rows from multiple tables where the join condition is met

SELECT column-list 
 FROM table1 JOIN table2 ON (table1.column = table2.column)

• 2. LEFT JOIN: returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal

SELECT column-list 
 FROM table1 LEFT JOIN table2 ON (table1.column = table2.column)

• 3. RIGHT JOIN: join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equa

SELECT column-list 
 FROM table1 RIGHT JOIN table2 ON (table1.column = table2.column)

• 4. FULL JOIN: join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met

SELECT column-list 
 FROM table1 FULL JOIN table2 ON (table1.column = table2.column)

######SUBQUERY SUBQUERY: can be used with SQL SELECT, INSERT, UPDATE, and DELETE as a query within a query

CORRELATED SUBQUERY: both the inner query and the outer query are interdependent

XTRA_SQL_Reading.md

####TERMS AND DEFINITIONS:

• Databases
• Database Schema
• Data
• Relational Database
• Graphical User Interface

####DATA TYPES:

• String
  1. Varchar (short string)
  2. String (long string)
• Numeric
  1. Integer
  2. Decimal/fixed (monetary)
  3. Float
• Date and Time
  1. Date
  2. Time
  3. DateTime

####CREATING DATABASES:

• Creating Schema/Database

CREATE {DATABASE | SCHEMA}[IF NOT EXISTS] data_base_name [DEFAULT] CHARACTER SET [=] collation

CREATE SCHEMA IF NOT EXISTS movie_db_1 DEFAULT CHARACTER SET utf8;
CREATE DATABASE IF NOT EXISTS movie_db_1 CHARACTER SET = utf8;

• Creating Tables

CREATE TABLE table_name (column_name DATA TYPE [NULL | NOT NULL] [, column_name DATA TYPE])

CREATE TABLE movies (title VARCHAR(50) NOT NULL, year INTEGER NULL)

• Inserting Data

INSERT INTO table_name VALUES (data [, data])

INSERT INTO movies VALUES ("Avatar", 2009)

• Data Definition Language (DDL): Manipulates schema
• Data Manipulation Language (DDM): Manipulates data

####CRUD: Four major functions implemented in database applications:

• Create
• Retrieve/Read

#LIMIT
SELECT * FROM movies LIMIT 10 OFFSET 100 == SELECT * FROM movies LIMIT 100, 10

#IS NULL
SELECT * FROM movies WHERE yr IS NULL

#IS NOT NULL
SELECT * FROM movies WHERE yr IS NOT NULL ORDER BY yr DESC

• Update/Edit

#INSERT WITH 'VALUES'
INSERT INTO database_name [(column [, column])] VALUES (DATA) [,(DATA)];
 INSERT INTO movies VALUES ("Avatar", 2009) 
 INSERT INTO movies (title, year) VALUES ("Avatar", 2009)
 INSERT INTO movies (year, title) VALUES (2009, "Avatar")
 INSERT INTO movies VALUES ("Avatar", 2009), ("Avatar 2", NULL) #multiple entries

#INSERT WITH 'SET'
INSERT INTO database_name SET column = data [, column = data] 
 INSERT INTO movies SET title = "Avatar", year = 2009

#UPDATE
UPDATE database_name SET column = data [CLAUSE]
 UPDATE movie SET yr = 1979 WHERE title = "Alien"

• Delete

DELETE FROM database_name [CLAUSE] condition
 DELETE FROM movies WHERE title = 'Alien' AND yr = 1969 #yr is a fail safe

####MANIPULATING SCHEMA:

• Altering Tables

#RENAMING TABLES
RENAME TABLE table_name TO new_table_name [, table_name TO new_table_name]

#DELETING TABLES
DROP TABLE [IF EXISTS] table_name

#REMOVING DATA FROM TABLES
TRUNCATE [TABLE] table_name

• Altering Columns

#ADDING COLUMNS
ALTER TABLE table_name ADD [COLUMN] (column_name data-type [,column_name data-type])

#CHANGING NAMES OF COLUMNS
ALTER TABLE table_name CHANGE [COLUMN] current_column_name new_column_name data-type

#CHANGING DATA TYPE OF COLUMNS
ALTER TABLE table_name CHANGE [COLUMN] current_column_name current_column_name new_data-type

#REMOVING COLUMNS
ALTER TABLE table_name DROP [COLUMN] current_column_name

• Dropping Databases

#DELETING A DATABASE
DROP {DATABASE|SCHEMA} [IF EXISTS] database_name

####JOINING RELATIONAL DATABASES:

• Normalization: process of setting up a table that contains repeated and redundant data from one column of a table and putting that information into another table.

• Keys

  • Primary Keys, id: Used to define each row in a table and cannot be null
  • Unique Key, ssn, email_address: Similar to Primary Keys except that it can be null
  • Foreign Key, genre_id: Special key that describes the relationship between two or more tables

#ADDING NEW COLUMN WITH CONSTRAINT FOREIGN KEY
ALTER TABLE table_name ADD COLUMN new_column_name DATA TYPE, 
ADD CONSTRAINT FOREIGN KEY (new_column_name) REFERENCES table_name(column_name);

####IMPORTANT! STRING FUNCTIONS

String functions