Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo

SQL: Select Queries


  • Please log in to reply
3 replies to this topic

#1 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 01 September 2009 - 09:04 AM

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:

http://forum.codecal...=1&d=1251835336

Specifying the Columns

You can specify what columns you want to return also.

Example:

SELECT name, age FROM people;

The resulting output is:

http://forum.codecal...=1&d=1251835336


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:

http://forum.codecal...=1&d=1251835336

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:

http://forum.codecal...=1&d=1251835336

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:

http://forum.codecal...=1&d=1251835336

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:

http://forum.codecal...=1&d=1251835336

Attached Thumbnails

  • where2.JPG
  • selectAll.JPG
  • where1.JPG
  • orderByDesc.JPG
  • orderByAsc.JPG
  • limitResults.JPG

  • 2

#2 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 01 September 2009 - 09:19 AM

Very nice work, Chili5. +rep
  • 0

#3 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 01 September 2009 - 01:32 PM

Very nice +rep
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#4 ArekBulski

ArekBulski

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 480 posts

Posted 02 September 2009 - 01:06 AM

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