Go through the tutorial

[CaptainEmerson]

Go through this tutorial: http://www.w3schools.com/sql/

[clarrain]

Note: SQL is not case sensitive

Most Important SQL Commands:

SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database CREATE DATABASE - creates a new database ALTER DATABASE - modifies a database CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index

[clarrain]

General Syntax Notes on SELECT:

SELECT column_name,column_name FROM table_name; for specific columns

SELECT * FROM table_name; for all columns

SELECT DISTINCT column_name,column_name FROM table_name; for distinct (different) values

SELECT column_name,column_name FROM table_name WHERE column_name operator value; for records that fit a specific criterion, single quotes around text values

SELECT column_name,column_name FROM table_name WHERE column_name operator value OR column_name operator value; for records that fit one of two criterions, single quotes around text values

SELECT column_name,column_name FROM table_name WHERE column_name operator value AND column_name operator value; for records that fit two criterions, single quotes around text values

SELECT column_name,column_name FROM table_name WHERE column_name operator value AND (column_name operator value OR column_name operator value); for combining and and or parameters, single quotes around text values

SELECT column_name, column_name FROM table_name ORDER BY column_name ASC/DESC, column_name ASC/DESC; used to sort the result-set by one or more columns, sorts the records in ascending order by default

SELECT TOP number/percent column_name(s) FROM table_name; used to specify the number of records to return

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern; search for a specified pattern in a column "%" sign is used to define wildcards (missing letters) both before and after the pattern

SELECT column_name(s) FROM table_name WHERE column_name NOT LIKE pattern; allows you to select records that do NOT match the pattern

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...); allows you to specify multiple values in a WHERE clause.

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; selects values within a range - can be numbers, text, or dates to display the products outside the range of the previous example, use NOT BETWEEN

SELECT column_name AS alias_name FROM table_name; used to give a database table, or a column in a table, a temporary name

SELECT column_name(s) FROM table_name AS alias_name; used to give a database table, or a column in a table, a temporary name

[clarrain]

SQL Operators:

= Equal <> Not equal. Note: In some versions of SQL this operator may be written as != > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern IN To specify multiple possible values for a column

[clarrain]

SQL Wildcard Characters

% A substitute for zero or more characters _ A substitute for a single character [charlist] Sets and ranges of characters to match [^charlist] or [!charlist] Matches only a character NOT specified within the brackets

[clarrain]

Things to explore at a later date

• Joins
• Select into
• Creating databases
• Creating tables

[clarrain]

Additional Syntax Notes: SQL Union

Combines the result of two or more select statements - each select statement must have the same # of columns: must be similar data types, and in same order. The column names in the result set are usually equal to the column names in the first select statement statement in the union.

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; selects only distinct values

SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2; allows duplicate values

[clarrain]

Additional Syntax Notes: SQL Create View

view: a virtual table w/ rows and columns based on the result-set of an SQL statement - fields are fields from one or more real tables in the database

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition updates a view

DROP VIEW view_name deletes a view

[clarrain]

Important Note:

If you want to keep your queries simple and easy to maintain, do not allow time components in your dates!

[clarrain]

SQL NULL

NULL values are treated differently from other values. NULL is used as a placeholder for unknown/inapplicable values. If you insert a record w/ no value, a NULL value will be saved.

NULL values can be tested with:

SELECT column_name,column_name FROM table_name WHERE column_name IS NULL; looks for NULL values

SELECT column_name,column_name FROM table_name WHERE column_name IS NOT NULL; looks for only records w/ no NULL values

NULL Functions

Use IFNULL() or COALESCE() functions to specify how to treat NULL values

SELECT column_name,column_name* (column_name+IFNULL(column_name,0__ FROM table_name

[clarrain]

Data Types

Data Type	Description	Name (MySQL)
integer	Integer numerical	Int (Integer)
float	Approximate numerical. A floating number in base 10 exponential notation.	Float
string (fixed)	Character string of fixed length.	Char
string (variable)	Character string of variable length.	Varchar

[clarrain]

SQL Functions

Aggregate Functions

Return a single value, calculated from values in a column.

• AVG( ) - Returns the average value of a numeric column
• COUNT( ) - Returns the number of rows
• FIRST( ) - Returns the first value
• LAST( ) - Returns the last value
• MAX( ) - Returns the largest value
• MIN( ) - Returns the smallest value
• SUM( ) - Returns the sum

Scalar Functions

Return a single value, based on the input value.

• UCASE( ) - Converts a field to upper case
• LCASE( ) - Converts a field to lower case
• MID( ) - Extract characters from a text field
• LEN( ) - Returns the length of a text field
• ROUND( ) - Rounds a numeric field to the number of decimals specified
• NOW( ) - Returns the current system date and time
• FORMAT( ) - Formats how a field is to be displayed

[clarrain]

Aggregate Function Syntax and Additional Notes

AVG( )

SELECT AVG(column_name) FROM table_name;

COUNT( )

SELECT COUNT(column_name) FROM table_name; returns the number of values (NULL values will not be counted) of the specified column

SELECT COUNT( * ) FROM table_name; returns the number of records in a table

SELECT COUNT(DISTINCT column_name) FROM table_name; returns the number of distinct values of the specified column

FIRST( ) & LAST( )

Only supported by Microsoft Access. MYSQL workaround:

SELECT column_name FROM table_name ORDER BY column_name ASC LIMIT 1; returns the first value of the selected column

SELECT column_name FROM table_name ORDER BY column_name DESC LIMIT 1; returns the last value of the selected column

MAX( )

SELECT MAX(column_name) FROM table_name;

MIN( )

SELECT MIN(column_name) FROM table_name;

SUM( )

SELECT SUM(column_name) FROM table_name;

GROUP BY

Used with aggregate functions to group the result-set by one or more columns

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

HAVING

Works like WHERE with aggregate functions

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value;

[clarrain]

Scalar Function Syntax and Additional Notes

UCASE( )

SELECT UCASE(column_name) FROM table_name;

LCASE( )

SELECT LCASE(column_name) FROM table_name;

MID( )

SELECT MID(column_name,start,length) AS some_name FROM table_name;

Parameter	Description
column_name	Required. The field to extract characters from
start	Required. Specifies the starting position (starts at 1)
length	Optional. The number of characters to return. If omitted, function returns the rest of the text

LEN( )

SELECT LEN(column_name) FROM table_name;

ROUND( )

Note: 0.5 is rounded to the nearest even integer SELECT ROUND(column_name,decimals) FROM table_name;

Parameter	Description
column_name	Required. The field to round
decimals	Required. Specifies the number of decimals to be returned.

NOW( )

SELECT NOW() FROM table_name;

FORMAT( )

SELECT FORMAT(column_name,format) FROM table_name;

Parameter	Description
column_name	Required. The field to be formatted.
format	Required. Specifies the format.

[clarrain]

Quick Reference Syntax guide here from W3Schools.

Tutorial complete! :smile: