18.2 Basic SQL query statements

18.2 Basic SQL query statements

18.2.1 Using the SELECT statement and logical operators

One way or another, most of the database applications on the Web (or Internet) are SQL based. This may include all kinds of online banking, search engines, and shopping. For most of them, a user-friendly interface is designed to hide the fact that they are actually using the SQL query.

The most frequently used SQL command is the SELECT statement, which generates a table and returns data to the user. For example, we have frequently used

SELECT * FROM people;

in our previous examples to display all data from the table people. The general syntax of SELECT consists of three parts:

SELECT field1, field2, field3,..., fieldN
FROM table1, table2, ..., tableK
WHERE search_condition;

The first part begins with keyword SELECT and is used to build the fields of a table. The keyword FROM is to tell the database system where to find the fields. The keyword WHERE provides a search condition so that you can search for the data you require.

For some simple use of the SELECT statement, let's consider the database and table people that we created in sections 18.1.2 and 18.1.3.

The following SELECT statement returns all data from the table people whose salary is more than 25,000:

SELECT * FROM people WHERE salary > 25000;

The asterisk after SELECT means that you are selecting all data. You can also combine multiple search conditions together in your SQL statement. For example, to locate all females whose salary is more than 25,000 you can use

Example: ex18-15.sql - Logical AND

 1:  SELECT *
 2:  FROM people
 3:  WHERE salary > 25000 AND sex='F';

To find out who is responsible for the business in Tokyo or Paris, you can use the logical OR keyword. For example,

Example: ex18-16.sql - Logical OR

 1:  SELECT *
 2:  FROM people
 3:  WHERE location='Tokyo' OR location='Paris';

SQL is a flexible language. For example, you can also compare the date of birth within the WHERE keyword, such as

Example: ex18-17.sql - Flexible Comparison

 1:  SELECT *
 2:  FROM people
 3:  WHERE birth >='196011';

This statement will select the people who were born after January 1, 1960. The results of these three examples are shown in Figs 18.1418.16 respectively.

To select particular columns, you need to replace the asterisk by the names of fields separated by commas. For example, to get the name, sex, and date of birth of all male staff, you can use the SQL statement in ex18-18.sql below:

Example: ex18-18.sql - Selecting Fields

 1:  SELECT name, sex, birth
 2:  FROM people
 3:  WHERE sex='M';

The result is shown in Fig. 18.17. Also, new fields can be generated from existing ones and added to the table. For example, the following adds a new field to the table:

Example: ex18-19.sql - Adding A New Field

 1:  SELECT name, sex, birth, (salary * 1.07) AS new_salary
 2:  FROM people
 3:  WHERE sex='M';

The result of this example is shown in Fig. 18.18.

SQL is flexible. You can rewrite line 1 of ex18-19.sql as

SELECT name, sex, birth, (salary * 1.07) new_salary
SELECT name, sex, birth, salary * 1.07 new_salary

and still produce the same result.

18.2.2 Sorting and multiple sorting of records

Sometimes, it is more convenient to examine the query result if the output is sorted by order. To sort a result, you can use the ORDER BY clause as in the example below:

Example: ex18-20.sql - SQL Sorting

 1:  SELECT name, sex, birth
 2:  FROM people
 3:  WHERE sex='M'
 4:  ORDER BY birth;

This example returns a result ordered by birthday (see Fig. 18.19).

By default, the ORDER BY clause sorts the results in ascending order. To sort in reverse order, the keyword DESC can be used (see Fig. 18.20).

Example: ex18-21.sql - SQL Sorting With Descending Order

 1:  SELECT name, sex, location, birth
 2:  FROM people
 3:  WHERE sex='M'
 4:  ORDER BY birth DESC;

You can also perform sorting on multiple fields. For example, if you want to group the people by location and then sort staff within each group by birth dates, you can issue the query

Example: ex18-22.sql - Multiple Sorting

 1:  SELECT name, sex, location, birth
 2:  FROM people
 3:  WHERE sex='M'
 4:  ORDER BY location, birth DESC;

This statement sorts the data in the location field and then orders the data in the field birth within location. A screen shot is shown in Fig. 18.21.

From this example, you can see that the data in the location field are sorted in ascending order. The data in the birth field are sorted in descending order and bounded or grouped by location.

18.2.3 Building new fields with expressions

For a database and table to store information about your employees, it may be more convenient to include age as a field. However if you put a data value into the age field, you may need to change it every year. Thus in practice, the age field in many cases will be implemented at run time.

If you want to find out the age of an employee, you can use the following steps:

Step 1. Get the current year minus the year of birth as age.

Step 2. Get the current month and date (mm-dd) and compare to the month and date of birth.

Step 3. If the current month and date is less than the month and date of birth decrease the age by 1.

In terms of SQL statements, the year difference (step1) can be expressed as

LEFT(CURRENT_DATE,4)  LEFT(birth,4)

This is an arithmetic expression and the result is a number. The entire date format used in MySQL is yyyy-mm-dd, where the LEFT() function can be used to extract the first four characters from a string. The result is the difference of the current year and the year of birth.

In order to perform steps 2 and 3, you need a logical expression from SQL.

As you might expect, the RIGHT() function can extract the month and date (mm-dd). For example, the following expression can be used to extract the five characters from the month and date string of birth:

RIGHT(birth,5)

To perform the comparison described in step 3, the following logical expression or comparison is used:

RIGH(CURRENT_DATE,5) < RIGHT(birth,5)

SQL will return the value 1 if this expression is true and 0 otherwise. Now you can perform query with age as follows:

Example: ex18-23.sql - Calculating Age

 1:  SELECT name, sex, location, salary,
 2:   ((LEFT(CURRENT_DATE,4)  LEFT(birth,4)) 
 3:    (RIGHT(CURRENT_DATE,5) < RIGHT(birth,5)))
 4:  AS age
 5:  FROM people
 6:  ORDER BY age;

Soon after the name, sex, location, and salary fields, a simple mathematical expression (lines 23) is used to declare a new field called age. Since age is generated at run time, it will not store or affect the actual data of the database. A screen shot of this example is shown in Fig. 18.22.

18.2.4 The LIKE operator and wildcards

In some cases, you may want to query or search for a partial pattern. For example, the following example uses the LIKE operator to search for any employee whose name begins with "J":

Example: ex18-24.sql - Using The LIKE operator

 1:   SELECT *
 2:   FROM people
 3:   WHERE name LIKE 'J%';

The LIKE operator together with the percentage symbol "%" causes the system to return all names begin with letter "J." A screen shot is shown in Fig. 18.23. When used inside a LIKE expression, the percentage symbol works as a wildcard. If you change line 3 to

WHERE location LIKE '%York%';

this SQL query will return all the records where the location field contains the word "York." When dealing with data query, references to data are always case sensitive. For example, the following query will return nothing since there is no "york" in the table:

WHERE location LIKE '%york%';

Another wildcard often used in SQL query is the underscore "_." The underscore is the single-character wildcard and will accept any single character for the query. For example, it can be used to find every employee who was born in the 1960s, like this:

Example: ex18-25.sql - The Underscore Wildcard

 1:   SELECT *
 2:   FROM people
 3:   WHERE birth LIKE '%196_%';

The result is shown in Fig. 18.24.

18.2.5 Concatenation of fields

Some SQL implementations use the double pipe symbol "||" to concatenate two strings. In MySQL, the functions CONCAT() and CONCAT_WS() are used. The calling format for CONCAT() is

CONCAT(str1,str2,...)

This function returns the concatenated string from the argument strings, and returns NULL if any argument is null. Any numeric argument is converted to the equivalent string form. Some calling examples are

select CONCAT('Web','Technologies'); Return the string "WebTechnologies"

select CONCAT('Web',NULL,'Database'); Return the string NULL

select CONCAT(1234,5678); Return the string "12345678"

Another concatenation function in MySQL is CONCAT_WS(). The first argument of this function is the separator for the rest of the arguments. The calling format for this function is

CONCAT_WS(separator, str1, str2,...)

The separator can be a string as well as the rest of the arguments. The separator is added between the strings to be concatenated. Some examples are

select CONCAT_WS(",","name","sex","birth"); Return the string "name,sex,birth"

select CONCAT_WS("#","name","salary"); Return the string "name#salary"

select CONCAT_WS("#","name",NULL,"salary"); Return the string "name#salary"

The CONCAT_WS() function ignores any null data. To put this function in use, consider the example

Example: ex18-26.sql - Concatenate Fields

 1:  SELECT name,sex,birth,
 2:  CONCAT_WS(',',salary, (salary * 1.07))
 3:  AS salary_projection
 4:  FROM people;

This example displays the fields name, sex, birth, and a new field called salary_projection. This new field is a concatenation of salary and (salary * 1.07). A screen shot is shown in Fig. 18.25.

You can also use this function to populate another table.

Example: ex18-27.sql - Concatenate Fields II

 1:  CREATE TABLE information
 2:  SELECT name,
 3:  CONCAT_WS(',',sex,birth,salary)
 4:  AS description
 5:  FROM people;

This example first creates a table called information. Then a SELECT statement with the CONCAT_WS() function is used to populate the new table from the existing table people. The table information now contains two fields, name and description. If you issue the SELECT * FROM information; statement, you will have the result shown in Fig. 18.26.