Tips from Sam's Teach Yourself SQL in 10 Minutes:
Lesson 3:
* it is perfectly legal to sort data by a column that is not retrieved.
* If you want to sort descending on multiple columns, be sure each column has its own DESC keyword.
Lesson 4:
* Making the client application (or development language) do the databases job will dramatically impact application performance and will create applications that cannot scale properly. In addition, if data is filtered at the client, the server has to send unneeded data across the network connections, resulting in a waste of network bandwidth usage.
* because AND ranks higher in the order of evaluation, the wrong operators were joined together.
* The biggest advantage of IN is that the IN operator can contain another SELECT statement, enabling you to build highly dynamic WHERE clauses.
Lesson 6:
* The brackets ([]) wildcard is not supported by all DBMSs. - SQLite3 doesn't support it.
* Search patterns that begin with wildcards are the slowest to process.
Lesson 7:
* || is actually the preferred syntax, so more and more DBMSs are implementing support for it. - SQLite3 doesn't support the + syntax.
* an unnamed column cannot be used within a client application because the client has no way to refer to that column. To solve this problem, SQL supports column aliases. An alias is just that, an alternative name for a field or value. Aliases are assigned with the AS keyword.
Lession 8:
* If you do decide to use functions, make sure you comment your code well, so that at a later date you (or another developer) will know exactly what SQL implementation you were writing to* SQLite3 core functions at: http://www.sqlite.org/lang_corefunc.html
Lesson 9:
* Use COUNT(column) to count the number of rows that have values in a specific column, ignoring NULLvalues.* When specifying alias names to contain the results of an aggregate function, try to not use the name of an actual column in the table. Although there is nothing actually illegal about doing so, many SQL implementations do not support this and will generate obscure error messages if you do so.
Lesson 10:
* Every column listed in GROUP BY must be a retrieved column or a valid expression (but not an aggregate function).
* The difference between HAVING and WHERE Here's another way to look it:WHERE filters before data is grouped, and HAVING filters after data is grouped. This is an important distinction; rows that are eliminated by a WHERE clause will not be included in the group. This could change the calculated values which in turn could affect which groups are filtered based on the use of those values in the HAVING clause.
* you should make that distinction yourself. Use HAVING only in conjunction withGROUP BY clauses. Use WHERE for standard row-level filtering.
Lesson 11:
* Breaking up the queries over multiple lines and indenting the lines appropriately as shown here can greatly simplify working with subqueries.
* Subquery SELECT statements can only retrieve a single column. Attempting to retrieve multiple columns will return an error.
Lesson 13:
* It is also worth noting that table aliases are only used during query execution. Unlike column aliases, table aliases are never returned to the client.
* Self joins are often used to replace statements using subqueries that retrieve data from the same table as the outer statement. Although the end result is the same, many DBMSs process joins far more quickly than they do subqueries. It is usually worth experimenting with both to determine which performs better.
Lesson 14:
* The UNION automatically removes any duplicate rows from the query result set (in other words, it behaves just as do multiple WHERE clause conditions in a single SELECT would).
Lesson 15:
* Always Use a Columns List As a rule, never use INSERT without explicitly specifying the column list. This will greatly increase the probability that your SQL will continue to function in the event that table changes occur.
Lesson 16:
* Before you use a WHERE clause with an UPDATE or a DELETE, first test it with a SELECT to make sure it is filtering the right records—it is far too easy to write incorrect WHERE clauses.
Lesson 18:
* Performance Issues Because views contain no data, any retrieval needed to execute a query must be processed every time the view is used. If you create complex views with multiple joins and filters, or if you nest views, you may find that performance is dramatically degraded. Be sure you test execution before deploying applications that use views extensively.
* Using views, you can write the underlying SQL once and then reuse it as needed.
* views are easy to create and even easier to use. Used correctly, views can greatly simplify complex data manipulation.
Lesson 19:
* Because stored procedures are usually stored in a compiled form, the DBMS has to do less work to process the command. This results in improved performance.
Lesson 21:
* Cursors are used primarily by interactive applications in which users need to scroll up and down through screens of data, browsing or making changes.
Lesson 22:
* Performing client-side checks is a time-consuming process. Having the DBMS do the checks for you is far more efficient.
* Primary key values can never be reused. If a row is deleted from the table, its primary key must not be assigned to any new rows.
* After a foreign key is defined, your DBMS does not allow the deletion of rows that have related rows in other tables. * Cascading delete. If enabled, this feature deletes all related data when a row is deleted from a table.
* Indexes improve the performance of retrieval operations, but they degrade the performance of data insertion, modification, and deletion. When these operations are executed, the DBMS has to update the index dynamically.
* Indexes are used for data filtering and for data sorting. If you frequently sort data in a specific order, that data might be a candidate for indexing.* It is always a good idea to revisit indexes on a regular basis to fine-tune them as needed.
* As a rule, constraints are processed more quickly than triggers, so whenever possible, use constraints instead.
