SQL is a standard language for accessing databases.
Our SQL tutorial will teach you how to use SQL to access and manipulate data in:
MySQL, SQL Server, Access, Oracle, Sybase, DB2, and other database systems.
A standard SQL query might look like this:
SELECT first, last, occupation, sum(amount)
FROM tenngive
WHERE occupation = ``ROCKET SCIENTIST’’
GROUP BY first, last, occupation
ORDER BY 4 desc
Assume we are querying a database of campaign contributions. In English, the query means:
Show me the first and last names and occupations of donors who appear in the table called tenngive and the total amount of money that each gave -- but only if they listed their occupation as ``rocket scientist.’’ Return these records in reverse descending order of the summed amount to show which rocket scientist gave the most money.
The bold words represent the SQL ``template.’’ The remaining parts of the query -- such as ``last’’ and ``first’’ -- vary depending on the contents of your database and the questions you want to ask; however, if you can memorize the following template, you can write any basic SQL query. Just remember: You don’t have to use every piece of the template, but the pieces you do use must occur in this order. Read below for more information on what each means.
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
1. SELECT: Every query must have SELECT
A database could have 100 or more fields of data, but you might only be interested in two or three of them. Use SELECT to pick the fields that you wish to display.
Also, use SELECT to calculate a new column of data -- for example, when you want to sum numbers or count items. (See additional information under GROUP BY.)
2. FROM: Every query must have FROM
You must tell the computer which table, or tables, you want to use. Use FROM to identify the table or tables that you are using. If you are using more than one table – in other words, if you are joining tables together – list each one on the FROM line, separated by a comma
3. WHERE: Using WHERE to filter records is optional – but it is mandatory for joining tables.
Filtering: A table might have 1 million records in it, but you might only be interested in records that fit a certain criteria -- for example, perhaps you only want to look at people whose last name is ``SMITH’’ or whose occupation is ``ROCKET SCIENTIST.’’ Example:
SELECT *;
FROM tenngive;
WHERE last = “VIKRAM”
Joining: In addition, when you link two or more tables together, use WHERE to establish the join. Example:
SELECT last, rest, amount, cand_name;
FROM tenngive, tncands;
WHERE id=cand_id
Data type: You must know each field’s data type. In filters, text info must be enclosed in quotations marks. Dates must be enclosed in French braces. Numeric data is not enclosed. Examples:
TEXT: SELECT *;
FROM columbia;
WHERE street = “RANGELINE”
DATES: SELECT *;
FROM hospital;
WHERE dob = #5/17/72#
NUMBERS: SELECT *;
FROM bonus;
WHERE amount = 1000
Equals, greater than, etc.: In addition to saying something equals a particular value, you can specify:
“greater than” with >
“greater than or equal to” with >=
“less than” with < “less than or equal to” with <= “not equal to” with <>
Wildcards: The wildcard in Access is the percent symbol -- * -- and it must be used with the word “like” in place of the equals sign. Example:
SELECT *;
FROM business;
WHERE company LIKE “*MICROSOFT*”
Boolean Logic: The WHERE line is the only place in SQL that you may use “and,” “or” or the symbol for not, which is <>. Examples:
SELECT *;
FROM tenngive;
WHERE last like “*HASLAM*” OR rest like “*HASLAM*”
SELECT *;
FROM tenngive;
WHERE last = “GRUBB” AND occupation = “ATTORNEY”
SELECT *;
FROM tenngive;
WHERE last <> “WOOD”
4. GROUP BY: Use GROUP BY only if you are performing math in the select line; you must GROUP BY all of the fields in the SELECT line except the math function.
This is the trickiest part of basic SQL. GROUP BY works with SELECT to allow the computer to do math. When you tell the computer to sum or count on the SELECT line, you must GROUP BY all other fields that appear in the SELECT line.
The most common math functions are sum() and count(*). Examples:
SELECT donor, sum(amount);
FROM gimme;
GROUP BY donor;
ORDER BY 2 desc
SELECT donor, zip, sum(amount);
FROM gimme;
GROUP BY donor, zip;
ORDER BY 3 desc
SELECT weapon, count(*);
FROM crime;
GROUP BY weapon;
ORDER BY 2 desc
5. HAVING: DON’T WORRY ABOUT THIS ONE – YOU’RE ALMOST NEVER HAVE TO USE IT
Although it is not often used, HAVING filters aggregate data, just as WHERE filters other records. For example, you might total the contributions given by each individual in a campaign finance database and choose to display only those HAVING a sum total greater than $5,000. Example:
SELECT last, rest, sum(amount);
FROM tenngive;
GROUP BY last, rest;
HAVING sum(amount) > 5000;
ORDER BY 3 desc
6. ORDER BY: ORDER BY is optional – it merely sorts your data. The default is alphabetical or chronological order. Stipulate DESC to get reverse order.
You can tell the computer to sort your records in any order you like -- for example, alphabetically or from highest number to lowest number or from lowest to highest. Use ORDER BY to sort your data. Use ORDER BY [field name] DESC if you want the records sorted in descending order (that puts the big numbers on top).
See examples in queries above.
NOTE: You can use numbers as shorthand to specify which field you wish to order by. In the example below, the records are ordered by the third item in the select line -- the summed amount:
SELECT last, rest, sum(amount);
FROM tenngive;
GROUP BY last, rest;
ORDER BY 3
References & Downloads.
http://msdn.microsoft.com/en-us/sqlserver/default
No comments:
Post a Comment