Querying and Retrieving Data

The SELECT statement is used to query a database and get back the data that matches your query.

The SELECT statement has this general format:

Format of SQL SELECT statement

Additional information

SELECT
  Name1  ,Name2  ,Name3 ...     <— can mention one or more columns, or "*" for
FROM                             all columns
   tablename1, tableName2, ...   <— can mention one or more tables
WHERE
      conditions                 <— the "WHERE" clause is optional and can
ORDER BY  colNames               be omitted
;                                <— the "ORDER BY" clause is optional and can
                                 be omitted
                                     It returns the data sorted by this field

We have already seen an example of a basic select from a single table. The power of the statement arises when you select from two or more tables at once. So to find all the people in Africa in our database who listen to the Beatles or the band Fela Kuti, we could use the SQL command shown below. Numbers have been added on the left to help with commenting on the code; these will not appear in actual SQL code.

1    SELECT Person.name, Person.lives_in, ListensTo.music_group_name
2    FROM Person, ListensTo
3    WHERE ListensTo.music_group_name IN ( 'Fela Kuti', 'Beatles' )
4    AND Person.name = ListensTo.person_name
5    AND Person.lives_in = 'Africa' ;

Going through the statement line by line, we can make the following observations:

Line 1 gives the columns that we want to get back in our answer. Notice that the table name can be used to qualify the column so that there is no ambiguity.

Line 2 gives the names of the tables that we will be running the query on.

Line 3 starts our “where” clause. It says which data values or rows will be returned as the answer, based on matching the criteria that follow. The first criterion is that the music_group_name must be one of those in the list given. Notice the way you can compare against a list of items in parentheses.

Line 4 adds another condition. It says that whenever we have found one of those two bands, we look for the same name in the Person table.

Line 5 is the final part of the condition. It says that the “lives in” field for that person should hold the value “Africa.” Voila, we are done. Running the query produces the output:

Figure 23-3. Running the query

image

Grayham Downer appears in the list twice because he matches the criteria twice. He listens to both the target bands. If you were sending out promotional mail based on this query, you would want to ensure that you did not send two mails to him. Database inquiries frequently have results that may seem surprising if you are not familiar with set theory. The keyword “DISTINCT” after “SELECT” will eliminate duplicate records from being returned to you. If you sent the SQL command:

SELECT DISTINCT Person.name
    FROM Person, ListensTo
    WHERE ListensTo.music_group_name IN ( 'Fela Kuti', 'Beatles' )
    AND Person.name = ListensTo.person_name
    AND Person.lives_in = 'Africa' ;

The result set will be:

Grayham Downer
Judith Brown

The significance of primary key and foreign key should now be clearer. You always use a foreign key to relate one table to another. The operation is called “join” because you are merging or joining the data in two or more tables where the data match your conditions. In this case we used person_name which is a foreign key in the ListensTo table and the primary key for the Person table. Because it is a key, that allows us to retrieve the data from Person that corresponds to the name we found in ListensTo. This kind of join is an “inner join” or “equijoin.” In set theory terms, it is data that falls in the intersection of the two tables. There are also “outer joins,” which get you the data that is in one table, but not the other. These are outside the scope of this basic primer.

Let's elaborate on the conditional selections. You can use all the operators shown in Table 23-7 to compare attributes.

Table 23-7. SQL comparison operators

Meaning

SQL operator

Example

equals

=

WHERE lives_in = 'Africa'

greater than

>

WHERE age > 39

less than

<

WHERE age < 21

greater than or equal

>=

WHERE name >= 'Brown'

less than or equal

<=

WHERE age <= 65

not equal to

<>

WHERE name <> 'Brown'

pattern match

LIKE

WHERE name LIKE '%own'

matches any of several choices

IN

WHERE age IN (18, 19, 20)

When you compare a string for being greater than some other string, it does a lexical comparison of the characters. So the name “Crown” is greater than “Brown.” The “like” operator is for pattern-matching, and uses a “%” as a wild card. The example shown in table 23-7 “name LIKE '%own'” will match any names that end with “own.” There are other operators in SQL. The name we select from a table can be a mathematical function of some column in the table. That's expressed like this:

SELECT COUNT(*) FROM Person;

That statement gives you the number of rows in the Person table. There are other functions too. Table 23-8 shows some of them. These come after the SELECT keyword, and the entire statement may also have a WHERE clause that restricts the records that are input to the function.

Table 23-8. Some SQL functions

Meaning

SQL function

Example

Gives the number of rows satisfying the WHERE condition if present.

COUNT(*)

SELECT COUNT(*) FROM Person;

Gives the total of the named column, for all rows that meet the condition. This example adds the ages of people over 21 in our database.

SUM(col)

SELECT SUM(age) FROM Person
      WHERE age > 21;

Calculates the average of the named column. This example gives the average age of the minors.

AVG(col)

SELECT AVG(age) FROM Person
       WHERE age < 21;

Returns the largest value in that column.

MAX(col)

SELECT MAX(age) FROM Person;

Returns the smallest value in that column.

MIN(col)

SELECT MIN(age) FROM Person;
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.144.39.144