SQL

SQL Two major catagories
Data Manipulationn Langulage (DML)
-Add, Query, Manimpulate, Delete, from Tables and Data Sets 
Data Definitoin Language (DDL)
-Define tables, indexes and relationships

Relational Database 
- a database cabaple of representing relationships between different sets of data

Rows and columns
Rows logical set of data items (although columns can be quried also)
Record, a query result that my not translate directy into a table row
column - typically a set of object of the same kind and usage, span rows
Database is a collection of tables

DBMS - Database Managment System
-examles are SQL, SQL Lite, mySQL, SQL Server

SQL two major usage context
-used to interact with DB using vendor supplied interface
-embedded into another programming language allowing language to manipulate query
	db using the language itself

This course is Standard SQL 

SELECT statement
	SELECT * FROM table;
-basis of most SQL queries that return a result

INSERT statment - used to add data to a table
	INSERT INTO table (a, b, c) VALUES (1, 2 ,3);
	INSERT INTO TABLE (a, b c) VALUES (1, 2,3) ORDERED BY b;

UPDATE - change values of existing rows in a table
	UPDATE table SET a = 7, b = 6; 

DELETE - remove rows from a table
	DELETE FROM table WHERE a - 7;

COUNT function -  find how many records match a query without displaying actual data
	SELECT COUNT(*) FROM table; 
-use to count number of rows in a query
-aggrigate function, returns one value for a set of results
-COUNT functions is one of more commenly used functions in SQL

Select just rows your intereste in 
WHERE 
SELECT * FROM table WHERE field = 'something'

Select just col your intersted in
SELECT [col, col, ...] FROM table;

AS - changes the column names when returned
	SELECT [col] AS [whatever];
-can also be written in less clear fashion
	SELECT [col] [whatever];
-Probably good practice to always include AS keyword for clarity
-Column headers are returned by DB interface, often used in your code
-Column headers can be used to specify intermediate results 
-AS can be important in preventing namespace collision

AND 
-used in WHERE queries to add conditionals

.................................................................
CRUD - Create, Retrieve, Update, Delete

INSERT - put a new row into a table
	INSERT INTO table (col, col, col, ...) VALUES (stuff, stuff, stuff);

UPDATE 
	UPDATE table SET [col = 'data', col = 'data', ...] WHERE [col = 'data'];
- you can update values and set them to NULL if you want

DELETE - removes rows from a table uses the WHERE clause
	DELETE FROM table WHERE [col = 'data'];
-good practice to use SELECT statement first to make sure you know what 
 yoru about to delete



...............................................................
Database - a collections of tables
- may also have indexes, triggers, storage procecures, other metadata
- each table has a common set of data
- understanding how a particular database is organized can help you understand your app
 	or the app you need to work with


Table - collectoin of data organized in rows and columns
-normally a collection of related things 
-may have relationships with others tables
-tables in a database are generally related to each other
-some systems allow queries to cross databases
-typycally tables are used by one app or a set of apps and tables used by different
	applications are usually kept in a different database

SQL - a standard language for describing relational databases and queries
- Standard SQL is SQL 2011
- most SQL implimintations are pre standdards and have backward compatibility
	for their particular SQL flavor, lack of compliance can exist
- none of current implimintaitons of SQL support standard completely, all of them 
	to some degree
- Core SQL is supported in all SQL systems
- things like data types, and function impliminations vary system to system

SQL statmenets begin with a keyword and end with s semicolon
-semicolon is always required
-some impliminations don't require it for single line statements
-most SQL drivers do not rquire semicolon for standalone statements
-most systems always allow it, good practice to always use it
-SQL statements are not case sensitive
-some systems user defined symbols are case sensitive
- good practice write your SQL consistently
- comments are introduced by '-- ', ends at end of line
- C style comments also /**/ recognized
	 most dbs support above, confirm it is supported before you use it
- statement may have one or more clauses depending on syntax of statement
- FROM clause specifies table
- WHERE clause specifies a condition that must be satified for each row selected
- Functions are used to preform specific operations on data
	SELECT COUNT(*) FROM Album WHERE Label = 'Columbia';
- Expressions are used to derive values from data 
	-normal and logical example below
	SELECT Name, Population / 100000 AS PopMM 
		FROM Country,
		WHERE Population >= 100000
		ODER BY Population DESC; 
...........................................................
CREATE TABLE test (
	id INTEGER PRIMARY KEY,
	a INTEGER,
	b  TEXT,
	c TEXT NOT NULL,
	e TEXT DEFAULT 'Howdy!'
);
-NOT NULL constraint makes sure a value is there
-PRIMARY KEY specific to SQL lite makes sequential id numbers in table


DROP TABLE table;
-deletes a table from the database
- IF EXIST clause can be added t end to avoide errors

INSERT INTO table SELECT id, name, description FROM item; 
- can use results of s SELECT statement in insert
	INSERT INTO TEST (a, b, c ) SELECT id, name descrition FROM itme;
-can be used to store queries for instance 


LIMIT keword used to limit query return 

NULL
-You can not test for NULL it isnot a value a = NULL doesn't work
-instead use IS NULL condition or IS NOT NULL condition

Constraints
DEFAULT will let you set default values
UNIQUE requires unique value
-NULL values may be exmpted from uinque constraint
 sometimes you can combine constraints (NOT NULL) or find particula constraints 
 for your DB system

ALTER TABLE table ADD col [constraint, constraint, ...]

ID Columns hold unique values for each row in a table, typically they are automatically populated,
	each DB system does it differently

Operators
AND < > =
LIKE operator selects by text inside of fields
 SELECT Name, Continent, Population FROM Country WHERE Name LIKE '%island%' ORDER BY Name; 
 -% wild card in SQL matches 0 or more chars
 -_ matches any single charachter
 
IN ('Europe,')
 SELECT Name, Continent, Population FROM Country WHERE Continent IN ('Europe, 'Asis') ORDER BY Name;

SELECT DISTINCT allows you to query without duplicates
	SELECT DISTINCT col FROM table;
	SELECT DISTINCT col, col FROM table;
	-the second will show the distinct of both columns together
	-SELECT DISTICNT does sort automatically in it's algorithm

ODER BY
DESC clause can be added on the end
	SELECT Name FROM Country ORDER BY Name DESC;
ASC clause not commenly uses but opposite of DESC
	SELECT Name, Continent FROM Country ORDER BY Continent, Name; 
	-Orders by the Continent and also by the names in each continent group
-mixing up these clauses can allow complex sorting of rows

<<<<<<< HEAD
CASE used for conditionals: 
	SELECT * FROM booltest; 
	SELECT 
	 CASE WHEN a THEN 'true' ELSE 'false' END as boolA,
	 CASE WHEN b THEN 'true' ELSE 'false' END as boolB
	 FROM booltest
	;
	SELECT 
	 CASE a WHEN 1 THEN 'true' ELSE 'false' END as boolA,
	 CASE b WHEN 1  THEN 'true' ELSE 'false' END as boolB
	 FROM booltest
	;
-use case when you need a conditional in SQL

...............Relationships........................
Joins
Inner Joins, default and most common, INNER keyword typically omitted
LEFT JOIN rows where condition is met + all rows of table on left where condition si not met
RIGHT OUTER JOIN not ussually suppported, generaly can be written a s left join
FULL OUTER JOIN all rows from both tables including those where condition are met, not implimented for the most part
=======

...........MySQL..........
InnoDB is the standard MySQL engine now, it's new and good

% sign in a string is a  wile card 
_ can be used as a blank matching 
	'_a%' getes everything with second letter as a

REGEXP can match with a regex
	SELECT stuff FROM table WHERE row REGEX regex; 

Literal Strings
you can concaticnate strings in MySQL with
SELECT 'hello' ',' 'world';
Also MySQL can use double quotes but is not standard sql
to do a single quote in a literal string use double '' (non standard) 
Also using an escape char will work \'  (non standard)

DESCRIBE table;
 used to get table information

SHOW TABLE STATUS LIKE table;
	used to get more detailed table information

DROP TABLE IF EXIST table; 
	fairly obvious, but useful if you want to test
	first before drop

Constraints
	used when creating table
	CREATE TABLE TEST( 
		a INTEGER NOT NULL DEFAULT 47,
		B VARCHAR(255) UNIQUE NOT NULL
		c INTEGER PRIMARY KEY AUTO_INCREMENT
		b SERIAL #does everything above with less code
	);


SELECT LAST_INSERT_ID()
	will tell you the last id of anything inserted

Foreign key constraints example 
	CREATE TABLE lend (
	  id INT AUTO_INCREMENT PRIMARY KEY,
	  stamp TIMESTAMP, 
	  c_id INT,
	  b_id INT,
	  FOREIGN KEY (c_id) REFERENCES client(id), 
	  FOREIGN KEY (b_id) REFERENCES book(id)
	);

Alter statmentent
	ALTER TABLE table ADD col constraints;
	ALTER TABLE table DROP col; 
	ALTER TABLE table BB VARCHAR(10) AFTER a;

MySQL three types of data
	Numeric
	String
	Date and Times
	Specialy types 
		Boolians 
		Enumerations
		Sets
	Integer types
		varous sizes
	Fixed point types
		fixed decimal
	Floating point types
		real numbers
	Fixed length char strings
	Variable length strings
	Binary Strings
	BLOB (binary large object)
	TEXT 
		like a blob but non binary
		stores char encoding
		treated as text
	Date and Time 
		standard sql format
		auto time stamps
		time zone supported
	Enum and SET
	 ENUM for a single falue from a list
	 SET for several fvaule from a list
	 SET is stored as a bitmap

MySQL maximum storage per row
 65,535 bytes

Integer types
	TINYINT
	SMALLINT
	MEDUMINT
	INTEGER
	BIGINT
Decimal Type
	DECIMAL(precsion, scale)
	NUMERIC (alias for DEIMAL)
Floating point
	FLOAT
	DOUBLE
	REAL (alias for float)
String Types
	Char Strings
	Binary STring
		BINARY()
		VARBINARY()
	Fixed and Variable length
	Large Object Storage
	CHAR(length) uses all lenght
	VARCHAR(length) uses variable size up to lenght

Date and Time Types
 DATE and TIME
 YEAR
 DATETIME
 TIMESTAMP

Bit Type
Boolean type
 BOOLEAN()
 BOOL() is an alias

Enumeration Types
	CREATE TABLE test(
		id SERIAL,
		a ENUM(value1, value2, value3)
	)
Sets
	can be used to store sets of information in rows
	anything in a field must be of that set
	CREATE TABLE test(
		id SERIAL,
		a SET(value1, value2, value3)
	)

SHOW VARIABLES

Common MySQL Functions
	String functions:
		LENGTH
		CHAR_LENGTH
		LEFT(string, no_of_chars)
		Mid(string, start, end)
		RIGHT(string, left_start)
		CONCAT
		CONCAT_WS - with seperators
		LOCATE('bar', 'foobarbaz') 
			- finds where string is in other string
		UPPER()
		LOWER()
	Numeric Functions
		Operators 
		+, *, +, 
		/ -gets you a float
		DIV gets you a int
		MOD gets you remainder
		% also means MOD
		POWER(num, to_power_of)
		POW alias for above
		ABS() - absolute value
		SIGN()
		CONV(num, from_base, to_base) - gives 39 
			-convert numbers to different base types
			-can be base 2 to base 36
		PI() gives pi
		ROUND(num_to_round, number_of_digits)
		TRUNCATE(num, aount_to_runcate)
		CEILING() -always rounds up
		FOOR() always round down
		RAND(seed) -random number

	Date and Time
		NOW() -current time
		CURRENT_TIMESTAMP() alias for now
		UNIX_TIMESTAMP() 
		DAYNAME(NOW());
		DAYOFMONTH
		DAYOFYEAR
		MONTH
		TIME_TO_SECONS('00:30:00') -
		SEC_TO_TIME()
		ADDTIME('1:00:00', 00:29:45)
		SUBTIME('1:00:00', 00:29:45)
		ADDDATE('0000-00-00', INTERVAL 31 DAY) - adds 31 days
		SUBDATE('0000-00-00', INTERVAL 31 DAY) 
			- Interval can be multiple months, weeks...
		Time Zone Support
			SHOW VARIALBES LIKE '%time_zone%'
			-you can set the time zone variables
			SET time_zone = 'US/Pacific'
		DATE_FORMAT(NOW()), '%W, %D of %M, %Y');

	Aggregate functions
		-act on a set of rows rather than row by row
		COUNT([col or *]) -will count values
		GROUP_CONCAT()
			SELECT GROUP_CONCAT() FROM Country WHERE Region = "Western Eruope"

	SELECT 
		CASE WHEN a THEN 'true' ELSE 'false'  As boolA,
		CASE WHEN a THEN 'true' ELSE 'false'  As boolA
		FROM booltest

	SELECT 
		CASE a WHEN 1 THEN 'true' ELSE 'false'  As boolA,
		CASE b WHEN 0 THEN 'true' ELSE 'false'  As boolA
		FROM booltest

	Transactions 
		-make sure related queries are perfomed as a group
		START TRANSACTION;
		COMMIT; 
		ROLLBACK; -you can use it to test an execution
		-transactions can increase perormance 

	Trigger
		-allows to set up conditions where updating one table updates another
		CREATE TRIGGER table AFTER INSERT ON table
			FOR EACH ROW
			UPDATE TABLE1 SET ...
		-can yse triggers to prevent things also

	Sub Select aka Sub Query


//......................Things I use...............................
SELECT
	Department.*,
	d.firstName AS "directorFirstName", 
    d.lastName AS "directorLastName",
	v.firstName AS "vpFirstName", 
    v.lastName AS "vpLastName"
 FROM Department
	LEFT OUTER JOIN Resource AS d
		ON Department.directorId = d.id
	LEFT OUTER JOIN Resource AS v
		ON Department.vpId = v.id;












		









>>>>>>> 344d341a391d868b81fcddf9ef09a4af7917a6af