SQL: Expert Guides and Resources

SQL is a popular and useful programming language. You can make SQL even more useful if you know the phases of SQL development, the criteria for normal forms, the data types used by SQL, a little bit about set and value functions, as well as some tips on how to filter tables with WHERE clauses.Phases of SQL system developmentIn developing any system, you start at the beginning and go through to the end, and it’s no different with SQL.

Depending on their histories, different SQL implementations support a variety of data types. The SQL specification recognizes nine predefined general types, shown in the lists below.Exact Numerics: INTEGER SMALLINT BIGINT NUMERIC DECIMAL DECFLOAT Approximate Numerics: REAL DOUB

To ensure that database tables are designed in such a way that they will hold your data reliably, you need to be sure that they are not subject to modification anomalies. Normalizing your databases will give you that assurance. Compare the SQL criteria in the following list to the tables in your database. Doing so will alert you to the possibility of anomalies, when you find that your database is not sufficiently normalized.

This Cheat Sheet consists of several helpful tables and lists, containing information that comes up repeatedly when working with structured query language (SQL). In one place, you can get a quick answer to a number of different questions that frequently arise during an SQL development effort.SQL criteria for normal formsTo ensure that database tables are designed in such a way that they will hold your data reliably, you need to be sure that they are not subject to modification anomalies.

SQL gives you options for retrieving, analyzing, and displaying the information you need with the GROUP BY, HAVING, and ORDER BY clauses. Here are some examples of how you can use them. GROUP BY clauses Sometimes, rather than retrieving individual records, you want to know something about a group of records. The GROUP BY clause is the tool you need.

Every column contains one value for each row of a table. SQL statements often refer to such values. A fully qualified column reference consists of the table name, a period, and then the column name (for example, PRICING.Product). Consider the following statement: SELECT PRICING.Cost FROM PRICING WHERE PRICING.

You create an SQL trigger, logically enough, with a CREATE TRIGGER statement. After the trigger is created, it lies in wait — waiting for the triggering event to occur. When the triggering event occurs, bang! The trigger fires.The syntax for the CREATE TRIGGER statement is fairly involved, but you can break it down into understandable pieces.

Referential integrity involves maintaining consistency in a multitable SQL database. You can lose integrity by adding a row to a child table that doesn’t have a corresponding row in the child’s parent table. You can cause similar problems by deleting a row from a parent table if rows corresponding to that row exist in a child table.