SQL Syntax, Query Examples and Notes

Who actually believes that SQL can be used by non-technical people? Some links, samples, and other related stuff.

LIKE syntax:
- fieldx LIKE '%99%' will return all rows where fieldx has a "99" in it - MS-Access will also use "*" as a wildcard
- more here: http://www.1keydata.com/sql/sqllike.html
- LIKE '????-01%' - question mark is a single space wildcard, e.g. these will pass - 1995-01, 2102-01, 1995-01-01
A good sql cheatsheet - once you decipher it: http://www.3gwt.net/demo/SQL_redux.html
A list of SQL examples - http://www.fluffycat.com/sql/
A list of standard SQL functions (like avg, mod): http://www.sir.com.au/help/sql_function.htm
NULL: OR (Color IS NULL) -- MS SQL Server
A sample insert:
INSERT INTO `sills` (`id`, `sill`) VALUES ('', '"car"');
Note: backticks are used around the table and field names in the example above. They work in PHP+MySQL, I don't know if they work in other dbs. They usually aren't necessary.
MS SQL Server: CASE ~ the IF function. See linked scribble.
BETWEEN is INCLUSIVE.
SELECT INTO syntax:
SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename
-- from http://www.w3schools.com/Sql/sql_select_into.asp
Use ALIASES in WHERE - you can't because WHERE happens BEFORE the SELECT. Use CTE tables or sub queries to do this - usually negligible performance hit.
Insert multiple rows from another table (source: http://www.1keydata.com/sql/sqlinsert.html):
INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date FROM Sales_Information WHERE Year(Date) = 1998
Trim out the time part of a datetime in TSQL -
- CAST(FLOOR(CAST(mydatetime AS FLOAT)) AS DATETIME)
- OR - this looks better to me, no conversions to float: SELECT DATEADD(dd, DATEDIFF(dd,0,@x), 0)
- from: http://www.sqlservercentral.com/Forums/Topic272895-8-1.aspx
DELETE QUERIES
Basic: DELETE FROM table_name WHERE some_column=some_value;
Delete Query with Join:
- DELETE Contacts
  FROM Contacts INNER JOIN Members ON Contacts.MemberID = Members.MemberID AND Contacts.SiteId = Members.SiteID
  WHERE (Members.DateAdded < CONVERT(DATETIME, '2010-06-08 00:00:00', 102)) AND (Contacts.PhoneExt <> '11')
Create table: CREATE TABLE #temp(gcnumber varchar(255));
JOIN syntax is preferred. You can not do a full outer join with the "=" joins. Plus, catastrophic less error prone.
Single Quotes are used around strings. Escape with a second single quote.
I can never remember what goes first, WHERE or GROUP BY. HAVING goes after GROUP BY.
SELECT column_name, aggregate_function(column_name) FROM table_name
WHERE column_name operator value
GROUP BY column_name
Concatenate: SQL Server = "+"
Temporary tables (SQL Server only?): #spt_space
Allow inserts to specify the PK value - SET identity_insert news ON - [SQL Server only. Turn OFF afterwards.]
SELECT (actually SELECT INTO) from another database (on the same server) - straight forward really:
SELECT * INTO copy_from_restored FROM [server].[restoreddb].[dbo].[tablewanted]; -- that is servername and database name.

Subquery Examples

SELECT TOP 20 TheX, COUNT(TheX) AS TheY FROM (
SELECT (dateadd(dd, datediff(dd,0, LastChanged), 0)) as TheX
FROM [InventoryLocation]) AS TheSub
GROUP BY TheX
ORDER BY TheX DESC

Null Notes

"In SQL a null term in a calculation results in a null."

Got burned by NULL in an dynamically built "IN" criteria from a subquery... from StackOverflow: "An in statement will be parsed identically to field=val1 or field=val2 or field=val3. Putting a null in there will boil down to field=null which won't work."

SQL Server: varchar(30) - int - bigint

Variables ( SQL Server )

DECLARE @find varchar(30);
/* Also allowed: DECLARE @find varchar(30) = 'Man%'; */
SET @find = 'Man%';
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;

NOT TESTED: Clean up messy SQL: http://www.cleancss.com/sql-beautify/

UPDATE SYNTAX EXAMPLES:

UPDATE un_word set cat='female' WHERE cat like 'female%'
(This query was to clean up (trim excess spaces) from some records that had spaces after the word "female")

UPDATE monitor SET last_check=NOW(), last_result='Success' WHERE id=1;

UPDATE survey SET datagroup=2 WHERE datagroup=1

Example with join - from stackoverflow.com.

UPDATE CE
SET sJobNumber = AD.JobNumber
FROM CostEntry CE
INNER JOIN ActiveCostDetails As AD
ON CE.lUniqueID = AD.UniqueID
WHERE CE.SEmployeeCode = '002' AND substring(CostCentre, 3, 1) = sCategoryCode

Tags: SQL, query, syntax, examples, structured query language, tsql