Oct 18
Aside
1.
"Even the most amateur web developer knows to follow
three important rules for data validation:
1. Never trust the client.
2. Never trust the client.
3. Never trust the damn client! "
2.
3.
more SQL syntax
Review the SQL we've done, and look at a few more syntax notions.
Walk through bits of
Note in particular:
- SQL is a "real" programming language (though it isn't usually used that way)
- A subquery can nest SELECT within SELECT within SELECT ....
- Temporary "tables" (effectively collections of data with column labels) are generated in the midst of sql statements.
More examples:
SELECT colums FROM table
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];
Comparison operators & keywords :
=
<> !=
<
>
>=
<=
AND
OR
NOT
LIKE
IN
BETWEEN ... AND ...
IS NULL
e.g.
SELECT a, b, c FROM foo WHERE thing IN ('this', 'that');
SELECT COUNT(*) FROM employee; --- number of rows returned.
MAX()
MIN()
AVG()
SUM()
SELECT dept, SUM (salary)
FROM employee
GROUP BY dept;
HAVING is similar to WHERE but can also include group functions.
SELECT dept, SUM (salary)
FROM employee
GROUP BY dept
HAVING SUM (salary) > 2500;
aliases
Often tables are given short names,
e.g. "FROM Person p" or "FROM Person as p"
after which p.id is Person.id.
For homework, I'm asking you to look at these :
http://en.wikibooks.org/wiki/SQL_Exercises
warehouse
pieces and providers
planet express
ORM
