SQL: Select Queries

[chili5]

Writing SELECT queries for SQL databases

When you want to get results from a database you issue a select query. With this query you can limit the results based on certain criteria, sort it, and subsort it. You can do a lot with this including selecting data from multiple tables. This is done using joins which is a topic for another day.


First we are going to create a database called test with a people table.

The code to create our database is:

CREATE DATABASE test;

Now we need to create a table, and populate it with some data.

CREATE TABLE `test`.`people` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) NOT NULL ,
`age` SMALLINT NOT NULL
) ENGINE = MYISAM ;

This SQL code creates a table with 3 fields in it. The id field is a unique field that we can use to refer to any records we want. Then we have a name field that is a variable character field. The age field is an integer field to hold a persons age.

Since this is just a tutorial on select queries, I am going to give you some queries that we can use to demonstrate select queries.

INSERT INTO `people` (`id`, `name`, `age`) VALUES
(1, 'James', 16),
(2, 'Joe', 5),
(3, 'Jack', 9),
(4, 'Bill', 15),
(5, 'Topa', 93);

Selecting all the data

To select all data we simply use the * to mean select all. Then we name the table.

The basic syntax is:

SELECT field names | * FROM tableName;

This code is to select all the data:

SELECT * FROM people;

The result set will look like this:



Specifying the Columns

You can specify what columns you want to return also.

Example:

SELECT name, age FROM people;

The resulting output is:




Where Conditions

Using the WHERE clause you can limit the results that are returned. You might want to return all names of people who are less than 18? How about all names that start with J? All the math operators like less than, greater than, and so on you can use here.

Example:

SELECT name, age FROM people WHERE age < 18

The result is:



Notice how all the ages are less than 18?

How about all names that start with J? This is where pattern matching is important.

SELECT name, age FROM people WHERE name LIKE 'J%'

The result is:



Notice, how all the names start with J. That is what are where clause says.

Try this:

SELECT name, age FROM people WHERE name LIKE 'j%'

Notice, how the result is the same as above? The pattern matching does not depend on case.

ORDER BY and Sorting

When we use the ORDER BY clause we can sort our results by any field we want. Let us see how it works when we sort by age. You can also specify if you want to sort in ascending order or in descending order.

SELECT name, age FROM people ORDER BY age DESC;

Output:



The code said to select all the names and ages from people and to order it by age in descending order. So the first row is the largest age and so n.

We can easily sort by ascending order:

SELECT name, age FROM people ORDER BY age ASC;

The output is:

[Jordan]

Very nice work, Chili5. +rep

[WingedPanther]

Very nice +rep

[ArekBulski]

Creating tables from queries is very useful I think. Thanks.