Tutorials And Tips

SQL

BASIC
SELECT - Selects data from a table or field in a table.
Syntax: "SELECT fieldname FROM tablename"

WHERE - Adds a condition to your SELECT statement.
Syntax: "SELECT * FROM tablename WHERE field ='value'"

AND - Joins 2 or more conditions in a WHERE clause. If ALL conditions are met!
Syntax: "SELECT * FROM tablename WHERE field ='value' AND field2 ='value2'"

OR - Joins 2 or more conditions in a WHERE clause. If ANY conditions are met!
Syntax: "SELECT * FROM tablename WHERE field ='value' OR field2 ='value2'"

BETWEEN - Selects a range of data BETWEEN 2 conditions.
Syntax: SELECT * FROM tablename WHERE fieldname BETWEEN 'value1' AND 'value2'

DISTINCT - Returns each value once! Basically no duplicate values!
Syntax: SELECT DISTINCT fieldname FROM tablename

IS NULL / ISNULL - Used in Having/Where clause to return only the empty (Null) records of a particular field.
Syntax: Tablename.Field Is Null

HAVING - Similar to a "where clause" but used with Group By to filter on certain fields.
Syntax: Having Tablename.Field='Jones'

SORTING AND GROUPING

DESC - Descending Order.

ASC - Acsending Order.

GROUP BY - Groups by a given field.
Syntax: "SELECT * FROM tablename GROUP BY field2"

ORDER BY - Puts the records in a particular order by a given field.
Syntax: "SELECT * FROM tablename ORDER BY field2 DESC"

ADD RECORDS
INSERT INTO - Inserts new rows(records) into a table.
Syntax: INSERT INTO table_name (fieldname, fieldname2) VALUES (value1, value2)

TABLE & DATABASE DESIGN]

CREATION
CREATE - Used in such functions like CREATE TABLE and CREATE index etc.

CREATE DATABASE - Creates a database
Syntax: CREATE DATABASE database_name

CREATE TABLE - Creates a table. Syntax: CREATE TABLE tablename(fieldname datatype)
Syntax: CREATE TABLE tablename (fieldname1 varchar,fieldname2 varchar,fieldname3 varchar,fieldname4 int)

DELETION\DROP

DROP DATABASE - Deletes a database
Syntax: DROP DATABASE database_name

DROP TABLE - Deletes a table and all it's contents.
Syntax: DROP TABLE tablename

DELETE TABLE - Deletes all data in the table!
Syntax: DELETE TABLE table_name

ALTERING

ALTER TABLE - Alter Table allows you to alter the design of a table.
Syntax: ALTER TABLE tablename ADD fieldname datatype
Syntax: ALTER TABLE tablename DROP COLUMN fieldname Drop Column deletes that fieldname

ALIASES

COLUMN ALIAS - Allows you to use aliases.
Systax:SELECT fieldname AS aliasName FROM tablename 'Aliasname can be anything
E.G: SELECT FruitType AS Family, FruitName AS Name FROM tbl_fruity

TABLE ALIAS - As above but with tables!
Systax:SELECT fieldname FROM tablename AS aliasname 'Aliasname can be anything
E.G: SELECT FruitType, FruitName FROM tbl_fruity AS FRUIT

JOINS
JOIN - Used in LEFT JOIN, INNER JOIN AND RIGHT JOIN.

INNER JOIN - Joins two tables together returning ONLY those records that are in both tables with the same ID.
Syntax: ... From Tablename1 Inner Join Tablename2 On Tablename1.FieldID=Tablename2.FieldID

LEFT JOIN - As for Inner Join BUT returns all the records on the left table and only those that match from the right on the ID field. Those that don't match are set to Null int he returned fields.
Syntax: ... From Tablename1 Left Join Tablename2 On Tablename1.FieldID=Tablename2.FieldID

ON - Which fields the two tables involved are joined on. Can hold more than one field.
Syntax: ... On ((Tablename1.FieldID1 = Tablename2.FieldID1) AND (Tablename1.FieldID2 = Tablename2.FieldID2))

RIGHT JOIN - As for Left Join but the reverse. All the records in the right table and only those that match on the left are returned.
Syntax: ... From Tablename1 Inner Join Tablename2 On Tablename1.FieldID=Tablename2.FieldID

INDEXES
INDEX - An index is similar to a Primary Key in Access. Examples follow below!

CREATE INDEX - Creates a index ona table! This index ALLOWS duplicates to be created.
Syntax: CREATE INDEX index_name ON tablename (fieldname)

CREATE UNIQUE INDEX - Creates a UNIQUE index on the table. Meaning no 2 records can have the same index value!
Syntax: CREATE UNIQUE INDEX index_name ON table_name (fieldname)

DROP INDEX - Deletes an Index from a table.
Syntax: DROP INDEX tablename.index_name

FUNCTIONS - Built-in functions for SQL! Syntax for all is:
SELECT function(fieldname) FROM tablename

SUM - Returns the SUM of said field!
Syntax:SELECT SUM(fieldname) FROM tablename

COUNT - Returns the COUNT of said field!
Syntax:SELECT COUNT(fieldname) FROM tablename

AVG - Returns the AVERAGE value of said field!
Syntax:SELECT AVG(fieldname) FROM tablename

TOP - Returns the TOP ? records in table. Where ? is a number!
Syntax:SELECT TOP 5 FROM tablename

MAX - Returns the MAXIMUM value of said field.
Syntax:SELECT MAX(fieldname) FROM tablename

MIN - Returns the MINIMUM value of said field.
Syntax:SELECT MIN(fieldname) FROM tablename

---------------------------------------------------------------