Filtering and Concatenating in SQL

More basic notes on how to work in SQL, that I'm picking up as I go through Getting Started with SQL, an O'Reilly guide by Thomas Nield.

You can combine or concatenate fields using a double pipe || . You just enter field names in a table separated by the double pipes and use 'AS' to specify a new field to enter them in.

WHERE is used to filter data in SQL, as shown in this simple example.

This SQL query:

SELECT * FROM STATION_DATA

WHERE REPORT_CODE LIKE 'C%'

. . . will search the REPORT_CODE field for any entries which begin with the letter 'C'.

You can use <> or ! to filter for all entries except the specified string, or BETWEEN to search for values between two specified numbers.

SELECT * FROM STATION_DATA

WHERE YEAR BETWEEN 1980 AND 2000

Using IN before a set of values separated by commas in parentheses lets you search a specified group of values.

SELECT * FROM STATION_DATA

WHERE MONTH IN (10,11,12)

Parentheses can also be used to group Boolean queries in SQL.

Calculations in SQLiteStudio

To create a new field in a SQLtable, when using SQLiteStudio, you enter code in this fashion:

SELECT * FROM PRODUCT;

SELECT

PRODUCT_ID,

DESCRIPTION,

PRICE,

PRICE * 1.07 AS TAXED_PRICE

FROM PRODUCT;

So the first line selects data from the table named, 'PRODUCT'. Then we select three three individual fields, entering the command 'SELECT' on one line, and then each field that you want to display on three separate line.

The sixth line is used to calculate the value for a new field. The code is completed with a another reference to the source table followed by a semi-colon. When running the code you need to click on the play button, or press F9 in SQLiteStudio to execute the query

SQLite Studio

If you want a free program to view SQL databases, try SQLite Studio http://sqlitestudio.pl/?act=download You just download the files for this program in a zip file and extract it; there's no actual installation wizard. To start it just click on the file named, 'SQLiteStudio.exe',. SQLite Studio is recommended by Thomas Nield, the author of Getting Started with SQL: A Hands-on Approach for Beginners, a new O'Reilly Guide. The O'Reilly Guide makes some practice data available here, https://github.com/thomasnield/oreilly_getting_started_with_sql .

.

Once you have a SQL database open, you can activate the editor by going to Tools . . . Open SQL Editor

To get all of the data from a table in the database you run this command:

SELECT * FROM CUSTOMER

SELECT followed an asterisk [to select all of the data] FROM [the database name].