SQL - Sorting, Retrieving, and Filtering Data

http://connect.greenbeacon.com/Microsoft-SQL-Server.jpg

Query Terms and Syntax Rules

query: a command that you use to retrieve data from one or more tables.

clause: a component  of an SQL statement.

keywords: reserved words.

statement: keywords and data in the SQL query.

FROM keyword: to indicate the table to retrieve data from.

SELECT keyword: gives you the ability to retrieve data from the table by telling the database which column(s) to display.

Syntax are the programming rules for the language. There are two main rules in SQL syntax.

1. The keywords are not case sensitive. However, it is a good practice to capitalize keywords.
2. An SQL statement can be written on any number of lines. However, it is a good practice to break up individual statements.

Keywords, column names, or table names must be separated by a space. If you have more than one column name, it must be separated by a comma between each column name. End each query with a semicolon to indicated the end of the query. The following is an example of a simple query:

SELECT Column Name, Column Name

FROM TableName;

note: Microsoft Access requires brackets [] around the name if the name contains spaces.

SELECT
[Last Name]
FROM Customers

note:  For MySQL, the character to use around names with embedded spaces is an accent grave(`).

SELECT `last name`
FROM customers

note: Oracle requires double quotes to be used around column names containing spaces. Also, column names surrounded by double quotes are case sensitive.

SELECT
"Last Name"
FROM Customers;

Just as keywords are not case sensitive,  neither are column names, unless they are surrounded by double quotes (Oracle).

SQL is flexible and varies slightly from among DBMSs. The majority of queries can be transferred from one database to another.

Data Retrieval

This is from Dataspora, on the Big Data problem that companies face:

While our ability to capture and store ever-larger amounts of data continues to accelerate at an exponential pace, our ability to analyze and interpret data has not enjoyed this same pace of innovation. The ability of human processing and intuition to discover system circuitry and drivers of outcomes is grossly exceeded by sheer data quantity and system variables and the dynamic nature of these systems. While businesses are able to capture and store increasingly valuable data from their customers, business leaders still struggle to turn this data deluge into actionable information to improve their bottom line.

The most common request of a SQL developer is to produce a report. Once the data is in a database, it is the job of the analyst to extract useful information from all of that big data.

The SELECT statement allows you to retrieve data from tables stored in the database. Refer to the table  using column and table names:

SELECT FirstName, LastName
FROM Customer;

note: Microsoft SQL Server does not require the a semicolon (;) at the end of each statement; however, MySQL and Oracle require the semicolon.

Columns can be displayed in any order.

To display all columns in a table, use an asterisk(*) after the SELECT keyword:

SELECT *
FROM Customer;

Sorting Data Using the WHERE Clause

If data is to be displayed, it normally needs to be sorted in a particular order. To do this, use any of these clauses:

WHERE

ORDER BY 

GROUP BY
HAVING

The WHERE clause lets you select a subset of data for only those rows of interest. Operators are used in conjunction with WHERE to indicate how you want the data to be retrieved. The following are the operators you can use:

Arithmetic Operators

• Plus (+)
• Minus (-)
• Multiply (*)
• Divide (/)
• Modulus (%) -used to return the remainder in division

Comparison Operators

• =    Equals

SELECT FirstName, LastName

FROM Customer

WHERE ZipCode = 11579;

SELECT FirstName, LastName

FROM Customer

WHERE State = 'SC';

• < >  Does not equal
• !=             Does not equal
• <          Less than
• <=        Less than or equal to
• !<         Not less than
• >         Greater than
• >=       Greater than or equal to
• !>        Not greater than
• IS NULL Is a Null value

SELECT FirstName, LastName

FROM Customer 

WHERE HomePhone IS NULL;

Character Operators

Character operators are used to perform wildcard-character searches to allow you to find matches against part of a column value.

• LIKE: Matches parts of a value, any characters (can be zero characters). Used with the percent (%) or asterisk (*) symbol.

SELECT FirstName, LastName, HomePhone, Address, City, State, ZipCode

FROM Customer

WHERE FirstName LIKE 'VIN*';

The above SQL selects any FirstName with characters that start with VIN.

Oracle is case sensitive, so it is better to use the UPPER function to convert the data to uppercase. Ex:

SELECT FirstName, LastName, HomePhone, Address, City, State, ZipCode

FROM Customer

WHERE UPPER (FirstName) LIKE 'VIN*';

• The underscore (_) is used to match single characters, can be any character.
• [characterlist] is exactly one character in the character list enclosed in square brackets [].
• A caret symbol (^) plus a characterlist enclosed in square brackets (exactly one character not in the character set).

Logical Operators

Logical operators are used to separate two or more conditions in a WHERE clause.

• AND: Both expressions on either side of the AND operator have to be true for data to be returned.

• NOT: Match any condition opposite of the one being defined. Example using LIKE operator:

SELECT FirstName, LastName, HomePhone, Address, City, State, ZipCode

FROM Customer

WHERE FirstName NOT LIKE 'VIN*';

• OR: Either expression on either side of the OR operator have to be true for data to be returned.

Set Operators

Set operators perform multiple queries and return results as a single-query result.

• INTERSECT: Returns only the rows found by two queries.

• MINUS: Returns rows from the first query, not present in the second query.

• UNION: Returns results of two SELECT statements without duplicate rows.

• UNION ALL: Returns results of two queries with duplicate rows.

Miscellaneous Operators

Shorthand methods for writing SQL statements.

• BETWEEN: A shorter method for checking a range of values.

SELECT FirstName, LastName, HomePhone, Address, City, State, ZipCode

FROM Customer

WHERE ZipCode BETWEEN 11579 AND 74514;

• IN: A shorter method for specifying a range of conditions, either as a list of literal values, or as the result of a subquery.

SELECT FirstName, LastName, HomePhone, Address, City, State, ZipCode

FROM Customer

WHERE State IN ('SC','NC','GA',VA');

Sorting Data Using the ORDER BY Clause

• ASC keyword: Used to sort a column in ascending order.

• Data type: The type of data a column can hold (text and numbers).

• Clause: Used in the SELECT statement for selection and manipulation of data.

• DESC keyword: Used to sort a column in descending order.

• ORDER BY Clause: Used to retrieve specified column(s) in ascending or descending order.

SELECT FirstName, LastName, HomePhone, Address, City, State, ZipCode

FROM Customer

ORDER BY LastName;

note: You can use any column name in the ORDER BY clause, even column names not specified in the SELECT statement. The ORDER BY clause defaults to ascending order, unless specified otherwise.

Use DESC (descending) and ASC (ascending) with the ORDER BY clause, to indicate the sort direction of a column.

SELECT FirstName, LastName, HomePhone, Address, City, State, ZipCode

FROM Customer

ORDER BY LastName DESC;

Sorting Data Using the GROUP BY Clause

The GROUP BY keyword separates data that is returned from a SELECT statement into groups. The GROUP BY clause sorts groups of data calculated from aggregate functions. Aggregate functions return a single value on values stored in a column. 

The WHERE clause can be used with the GROUP BY clause; the GROUP BY clause must be placed after the WHERE clause.

Every column name you indicate in the SELECT statement, must also be present in the GROUP BY clause, except for those created using the AS keyword.

SELECT COUNT (ID) AS TotalCustomers, State

FROM Customer

WHERE State IN ('SC','NC','GA',VA') 
GROUP BY State;

http://www.techonthenet.com/sql/group_by.php

Sorting Data Using the HAVING Clause

The WHERE clause allows selection based on rows; the HAVING clause allows selection based on groups and is usually used in place of the WHERE clause. When using the HAVING clause, you must also use the GROUP BY clause. The HAVING clause must be placed after the GROUP BY clause.

SELECT COUNT (ID) AS TotalCustomers, State

FROM Customer
GROUP BY State

HAVING State IN ('SC','NC','GA',VA') ;

http://www.techonthenet.com/sql/index.php

http://www.amazon.com/SQL-Pocket-Guide-Guides/dp/1449394094

http://www.amazon.com/Microsoft-Access-Wordware-Applications-Library/dp/1598220608

http://www.mysql.com, The world's most popular open source database