![]() |
![]() |
Back to Papers and Articles | |||||||||||||||||||
SQL Cheat Sheet: Query By Example Copyright 2005 Paragon Corporation ( July 02, 2005) |
||||||||||||||||||||
Why Use SQL Over procedural?Structured Query Language (SQL) is a set-based language as opposed to a procedural language. It is the defacto language of relational databases. The difference between a set-based language vs. a procedural language is that in a set-based language you define what set of data you want or want to operate on and the atomic operation to apply to each element of the set. You leave it up to the Database process to decide how best to collect that data and apply your operations. In a procedural language, you basically map out step by step loop by loop how you collect and update that data.
Example SQL vs. ProceduralIf you were to update say a sales person of all customers in a particular region - your procedural way would look something like this
The SQL way would be:
UPDATE customers SET salesperson = "Mike" WHERE state = "NH"
If you had say 2 or 3 tables you need to check, your procedural quickly becomes difficult to manage as you pile on nested loop after loop. In this article we will provide some common data questions and processes that SQL is well suited for and SQL solutions to these tasks. Most of these examples are fairly standard ANSI-SQL so should work on most relational databases such as IBM DBII, PostGreSQL, MySQL, Microsoft SQL Server, Oracle, Microsoft Access, SQLite with little change. Some examples involving subselects or complex joins or the more complex updates involving 2 or more tables may not work in less advanced relational databases such as MySQL, MSAccess or SQLite. These examples are most useful for people already familiar with SQL. We will not go into any detail about how these work and why they work, but leave it up to the reader as an intellectual exercise. List all records from one table that are in another tableWhat customers have bought from us?
What items are in one table that are not in another table?Example: What customers have never ordered anything from us?SELECT customers.*
FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.customer_id IS NULL
More advanced example using a complex join: What customers have not ordered anything from us in the year 2004 - this one may not work in some lower relational databases (may have to use an IN clause) SELECT customers.*
FROM customers LEFT JOIN orders ON (customers.customer_id = orders.customer_id AND year(orders.order_date) = 2004) WHERE orders.order_id IS NULL
Please note that year is not an ANSI-SQL function and that many databases do not support it, but have alternative ways of doing the same thing.
Same question with an IN clause SELECT customers.* FROM customers WHERE customers.customer_id NOT IN(SELECT customer_id FROM orders WHERE year(orders.order_date) = 2004)
Fun with Statistics - AggregatesHow many customers do we have in Massachusetts and California?
SELECT customer_state As state, COUNT(customer_id) As total
FROM customers
WHERE customer_state IN('MA', 'CA')
GROUP BY customer_state
What states do we have more than 5 customers?
How many states do we have customers in?
Note the above does not work in Microsoft Access or SQLite - they do not support COUNT(DISTINCT ..) Alternative but slower approach for the above - for databases that don't support COUNT(DISTINCT ..), but support derived tables
List in descending order of orders placed customers that have placed more than 5 orders
How do you insert records in a table?Value Insert
Copy data from one table to another table
Creating a new table with a bulk insert from another table
How do you update records in a table?Update from values
UPDATE customers
SET customer_salesperson = 'Billy' WHERE customer_state = 'TX'
Update based on information from another table
UPDATE customers
SET rating = 'Good'
FROM orders
WHERE orderdate > '2005-01-01' and orders.customer_id = customers.customer_id
Please note the date format varies depending on the database you are using and what date format you have it set to. Update based on information from a derived table
Please note the update examples involving additional tables do not work in MySQL, MSAccess, SQLite.
MS Access Specific syntax for doing multi-table UPDATE joins UPDATE customers INNER JOIN orders ON customers.customer_id = orders.customer_id SET customers.rating = 'Good'
MySQL 5 Specific syntax for doing multi-table UPDATE joins
UPDATE customers, orders SET customers.rating = 'Good' WHERE orders.customer_id = customers.customer_id
| ||||||||||||||||||||
Back to Papers and Articles | ||||||||||||||||||||
|