Synonym (SQL 2005)

This a new feature in SQL 2005. It provides aliasing at the database level. It is similar to the 'AS' command that provides aliasing at the query level. (See article http://builder.com.com/5100-6388_.html?part=rss&tag=feed&subj=bldr)

Example:

USE Inventory

GO

CREATE SYNONYM SalesHistory

FOR SalesData.dbo.SalesHistory;

GO

RIGHT JOIN

The same as a left join but the table order is changed.

Returns all the rows from the second table, even if there are no matches in the first table.

This is a good means of find elements in one table but not in another.

Syntax:

SELECT field1, field2, field3, ... fieldN
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield;

(see http://www.w3schools.com/sql/sql_join.asp)

SQL Parser

It looks like there is one in Java, PHP.

I might be able to use code from SQLLight, but there is no nice clean library that I can just call.

http://www.sqlite.org/arch.html

Tutorial

MSAN Transact-SQL Statements Tutorial

Count() Function

Count(*) counts the rows in a table.

DROP TABLE

DROP TABLE - removes the table and its definition from the database.

Books

The question is do I really need a book?


This looks like a good book: http://websiteowner.info/books/databases/sqlreference.asp











Book on SQLServer2005: http://www.mhprofessional.com/product.php?isbn=0072261528

Is there a book on SQLServer2003?

Here is one for SQL Server 2000: http://www.amazon.com/SQL-Server-2000-Complete-Reference/dp/0072125888

Query within Query (SubQuery)

I was searching through some Borland C++ querys to an ACCESS database and I could not find some of the tables being referred to in the code. I was about to go ask my boss, when it hit me that perhaps these were not table but internal queries in ACCESS. Yes, that is what they were.

This is about 12 pages of information on subquerys: http://www.aspfree.com/c/a/MS-SQL-Server/Subqueries-and-Query-Expressions/

Example:

SELECT city
FROM offices
WHERE target >
(
SELECT SUM(quota)
FROM salesreps
WHERE rep_office = office
)

Transact-SQL Reference

This is the MS location for the Transact-SQL Reference

Unions

This is the first UNION SQL code I have written:

SELECT Field1, Field2, Field3 FROM Table1
UNION SELECT Field1, Field2, Field3 FROM MasterTOStatus;

And it work!

SQL Documentation

Microsoft Transcat-SQL Reference online document.

SQL 2005 Server

• Elements of Transact-SQL
• Query Fundamentals
• Using SQL Server Tutorials
• Query Fundamentals

SQL Access 2000

• Fundamental Microsoft Jet SQL
• Intermediate Microsoft Jet SQL
• Advanced Microsoft Jet SQL

IBM Informix

• Syntax
• Reference
• Tutorial

SQL Comments

The standard SQL comment is '--'. This works in SQL Server. However, Access doesn't permit comments.