What SQL knowledge do managers need for decision support queries?
by Dan Power
Structured Query Language (SQL)is considered difficult for most managers to learn and use. SQL is a complex language with many capabilities, but managers can learn to manipulate and retrieve data in a decision support query. The knowledge of SQL needed by a manager is limited. The decision support question or query to the database must be structured however to conform to the SQL command language rules. An SQL command is a statement composed of a sequence of key words, identifiers, delimiters and constants. So what are the important facts about writing an SQL query?
First, begin all decision support queries with the key word SELECT. This command key word tells the database program you want to find and retrieve data. You often need to end the query with a semi-colon (;).
Second, list the data fields you want to retrieve. If the field name is unique, the name is adequate. If the name is used in multiple tables, then specify the table name and then the field name using the format tablename.fieldname. If you want multiple fields, then seperate the names using commas. The asterix * can be used to retrieve all fields.
Third, if you want only distinct values from the fields preface the list of field names with the keyword DISTINCT, the default is ALL.
Fourth, the program needs to be told the tables where the data is located. The keyword FROM preceeds the list of tables.
SELECT DISTINCT column_name(s)
Fifth, if only part of the data is needed you can use the keyword WHERE to limit the dataset used to provide results. Operators including equals (=), greater than (>), BETWEEN, LIKE and IN are used to specify a condition. AND and OR operators filter records based on more than one condition. Use single quotes around text values in a condition. The IN keyword allows you to specify discrete values in WHERE criteria, BETWEEN specifies a search range.
WHERE column_name operator value
AND/OR column_name operator value
Sixth, if the data that is retrieved needs to be arranged in an ascending or descending order then the keyword phrase ORDER BY can be used to indicate the field used to order the data.
ORDER BY column_name(s) [ASC|DESC]
Seventh, if data in a field needs to be aggregated or summarized you can use keywords like AVG(), SUM() and COUNT() to create new results. Use these analytical functions to create a new result field.
SELECT AVG(column_name1) FROM table_name
Eighth, in some situations it is necessary to group data prior to applying functions. Also, one may want to limit output based on calculated values. The keywords GROUP BY and HAVING are used in these situations.
SELECT column_name1, SUM(column_name2)
GROUP BY column_name1
HAVING (arithematic function condition)
Ninth, SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables. There are a number of ways to specify joining 2 or more tables. The easiest way to join 2 tables is to use the WHERE keyword and the equal operator.
SELECT column_name3, SUM(column_name2)
FROM table_name1, table_name2
WHERE table_name1.column_name1 = table_name2.column_name1
GROUP BY table_name1.column_name3
Tenth, you can nest queries, meaning having multiple queries to answer a single question. The result of one query is used by another query and you can include nested queries in WHERE and HAVING clauses. If you need to nest queries to retrieve the desired data, consider asking for help.
SQL is not case sensitive; semicolon is the standard way to separate SQL statements; SQL aliases, alternate names, can be defined for database tables and database table columns using the keyword AS.
SELECT column_name AS column_alias
The basic SQL syntax is:
SELECT [ALL/DISTINCT] column_list, [SUM(column_name)]
FROM table_name1[, table_name2]
[WHERE conditional expression]
[GROUP BY column_name]
[HAVING conditional expression]
[ORDER BY column_name(s) [ASC|DESC]]
Square brackets  indicate optional elements.
A gentle introduction to SQL, URL http://sqlzoo.net/ (test queries)
PostgreSQL 8.5 Developer Documentation, Chapter 4. SQL Syntax, http://developer.postgresql.org/pgdocs/postgres/sql-syntax.html
SQL Tutorial - Learn SQL, URL http://www.sql-tutorial.net
SQL Syntax URL http://www.1keydata.com/sql/sql-syntax.html
SQL Tutorial, Version 2.1, URL http://www.firstsql.com/tutor.htm (Advanced)
w3schools.com, SQL Tutorial, URL http://www.w3schools.com/SQl/default.asp
What employees worked more than 240 hours?
SELECT Employee, SUM (Hours)
GROUP BY Employee
HAVING SUM (Hours) > 240
Last update: 2011-03-18 09:48
Author: Daniel Power
You cannot comment on this entry