Tutorials:
http://www.w3schools.com/sql/
Forums:
W3Schools: http://w3schools.invisionzone.com/index ... owforum=32
Sitepoint: http://www.sitepoint.com/forums/forumdi ... -amp-MySQL
Litterature:
There are many books written on SQL. Like
http://www.amazon.com/dp/1565927443/
http://shop.oreilly.com/product/9780596523084.do
One of my favourites is
Rudy Limeback's
book: http://simply-sql.com/ from sitepoint http://www.sitepoint.com/books/sql1/
business site: http://r937.com/
personal site: http://rudy.ca/
See also: http://www.sqllessons.com/categories.html
It is a clear and pedagogical book that teach you most of what you may know of SQL as a web developer. I also have a more advanced book, Sams Teach Yourself Transact-SQL in 21 Days that I find as a good complementary book, even if it is some years old. It handles more advanced topics like indexes in greater detail. In addition it has own chapters on stored procedures and triggers.
If you need a more recent book, go to amazon and search for
- beginning sql
- introduction to sql
- sql tutorials
- sql for dummies
- sql teach yourself
- advanced sql
2. Performance
When you use a database system, there may be many bottlenecks that slow down your data driven application. Here are some:
- Bad relational integrity or database model.
- Application programming where the database has a faster solution. See 3. below for an example.
- Inefficient queries.
- Bad use of data types.
PostgreSQL 9.2.4 Performance Tips
PostgreSQL 9.2.4 performance related search
PostgreSQL general performance related search
http://www.esvon.com/pg/services/performance/
http://www.xaprb.com/blog/2006/07/04/ho ... mizations/
3. Application programming versus database manipulation - an example
The following example is taken from Rudy Limeback's book Simply SQL.
Code: Select all
/* Chapter 8: The ORDER BY Clause */
/* Detail and Total rows */
SELECT
*
FROM (
SELECT
customers.name AS customer
, carts.id AS cart
, items.name AS item
, cartitems.qty
, items.price
, cartitems.qty
* items.price AS total
FROM
customers
INNER JOIN carts
ON carts.customer_id = customers.id
INNER JOIN cartitems
ON cartitems.cart_id = carts.id
INNER JOIN items
ON items.id = cartitems.item_id
UNION ALL
SELECT
customers.name AS customer
, NULL AS cart
, CAST(COUNT(items.name) AS CHAR) AS item
, NULL AS qty
, NULL AS price
, SUM(cartitems.qty
* items.price) AS total
FROM
customers
INNER JOIN carts
ON carts.customer_id = customers.id
INNER JOIN cartitems
ON cartitems.cart_id = carts.id
INNER JOIN items
ON items.id = cartitems.item_id
GROUP BY
customers.name
) AS dt
ORDER BY
customer
, cart
, item
;
/* Results should look like this --
customer cart item qty price total
A. Jones 2 72.55
A. Jones 2131 gimmick 3 9.37 28.11
A. Jones 2131 thingum 2 22.22 44.44
B. Smith 3 287.51
B. Smith 2921 dingus 3 29.37 88.11
B. Smith 2921 whatsis 2 93.70 187.40
B. Smith 3002 doohickey 1 12.00 12.00
C. Brown 4 342.94
C. Brown 2937 thingum 1 22.22 22.22
C. Brown 3001 thingamabob 3 22.22 66.66
C. Brown 3001 whatsis 2 93.70 187.40
C. Brown 3937 thingum 3 2.22 66.66
D. White 1 59.95
D. White 3197 whatchamacallit 1 59.95 59.95
E. Baker 2 69.00
E. Baker 2461 doohickey 2 12.00 24.00
E. Baker 2461 whatnot 3 15.00 45.00
G. Scott 1 179.85
G. Scott 3321 whatchamacallit 3 59.95 179.85
H. Clark 2 132.55
H. Clark 3081 dingus 3 29.37 88.11
H. Clark 3081 thingum 2 22.22 44.44
*/
- Try to figure out how you would have done the same with application code.
- Comment on the following statement (page 181 in the book)
The UNION query that produces totals as well as details, and then interleaves them, is more complex than the simple detail query, but nowhere near as complex as the application programming required to achieve the same effect.