SQL - Httqm's Docs

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is the most widely deployed SQL database engine in the world. It is used in countless desktop computer applications as well as consumer electronic devices including cellphones, PDAs, and MP3 players. The source code for SQLite is in the public domain.

SQLite is provided by the sqlite3 Debian package.

Commands :

Command	usage	Example
sqlite3 file.sqlite	Open the SQLite shell
.tables	List tables of the DB
.schema	Show the DB schema
pragma table_info(Client);	List fields and data types of table Client (similar to MySQL command describe)
	submitting a query	`select * from moz_cookies;`
vacuum	Reorganize and defragment the DB file by dropping file space freed by delete and drop commands
.quit (or CTRL-d)	Leave the SQLite shell
;	leave the prompt in case of unrecognized keyword

count (source):

count is used to count the number of rows matching a condition instead of listing them.
Syntax :

SELECT count(attendee_ref) AS attendees FROM Trainings_EventsAndAttendees WHERE (event_ref=17);

AS is used to declare the name of the column containing the result. This is useful to retrieve data from scripts.

How can I, via a SQL query, get the age and name of the oldest guy from the Dalton table ?

Name	Age
Joe	31
Jack	29
William	27
Averell	12

To select the highest value from a numerical field, the SQL syntax is :

SELECT MAX(tableName.fieldName) AS resultName FROM tableName

In our case, getting the age of the oldest guy from our list would be done with :

SELECT MAX(dalton.age) AS oldest FROM dalton

Now, not only we want his age, but also his name. To do this we must select just one single line from the database where the age meets a specific condition. First, let's have this condition be "age = 27". The SQL query will be :

SELECT dalton.name, dalton.age FROM dalton WHERE dalton.age=27

Same thing, but now the condition on the age is to be equal to the highest age found in the table :

SELECT
	dalton.name,
	dalton.age
FROM
	dalton
WHERE
	(dalton.age = (
		SELECT
			MAX(dalton.age) AS oldest
		FROM
			dalton)
		);

Alternate method for MySQL :

SELECT
	dalton.name,
	dalton.age
FROM
	dalton
ORDER BY
	dalton.age DESC
LIMIT 0, 1;

For MSSQL, consider using top 1 instead of limit 0, 1.

like (source : 1, 2) :

like is another keyword that is used in the WHERE clause. Basically, LIKE allows you to do a search based on a pattern rather than specifying exactly what is desired (as in IN) or spell out a range (as in BETWEEN).

Syntax : SELECT columnName FROM tableName WHERE columnName LIKE 'pattern';

Patterns often consist of wildcards :

Wildcard	match
_	any single character
%	any number of characters, including 0 character

Pattern examples :

A_Z : All 3-character strings starting with A, then any character, then Z. ABZ and A2Z would both satisfy the condition, while AKKZ wouldn't because there are two characters between A and Z.
ABC% : All strings that start with ABC. ABCD and ABCABC would both satisfy the condition.
%XYZ : All strings that end with XYZ. WXYZ and ZZXYZ would both satisfy the condition.
%AN% : All strings that contain the pattern AN anywhere. LOS ANGELES and SAN FRANCISCO would both satisfy the condition.

Query examples :

SELECT PibItemNr FROM tbldata_pibitemdetail WHERE (PibItemNr like 'cnt%')

When looking for a character which is a wildcard, we have to escape it first. This can be achieved with the escape command to specify which character becomes the escape character in the query :

SELECT * FROM supplier WHERE supplier_name LIKE '!%' escape '!';

This SQL statement identifies the ! character as an escape character. This statement will return all suppliers whose name is %.

How to duplicate a table ?

select * into destinationTable from sourceTable

LEFT JOIN

In a query such as :

SELECT * FROM table_A LEFT JOIN table_B ON table_A.key_A=table_B.key_B WHERE ;

The LEFT JOIN operation returns :

all rows from table_A
AND rows matching between table_A and table_B. If there is no matching row for table_B in the ON clause, a row with all columns set to NULL is used for table_B.

To find rows in table_A that have no counterpart in table_B, do :

SELECT table_A.* FROM table_A LEFT JOIN table_B ON table_A.key_A=table_B.key_B WHERE table_B.key_B IS NULL;

All flavors of JOIN (details) :

SQL - Structured Query Language and related topics

SQLite